一個簡單的用jdbc操作數據庫的例子,有時候我們處理一些小問題的時候會發現很有用.

這是用來從一個Access的數據庫文件area.mdb(一個全國省份城市的數據庫)中提取出我需要的信息到MS SQLServer 2000數據庫里的例子.

package com.test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
?*
?* CopyRight (C) http://www.aygfsteel.com/ilovezmh? All rights reserved.<p>
?*
?* WuHan Inpoint Information Technology Development,Inc.<p>
?*
?* Author zhu<p>
?*
?* @version 1.0??? 2007-1-17
?*
?* <p>Base on : JDK1.5<p>
?*
?*/

public class City {
?
?static String driver1="sun.jdbc.odbc.JdbcOdbcDriver";
?static String driver2="net.sourceforge.jtds.jdbc.Driver";
?static String url1="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\TDdownload\\area\\area.mdb";
?static String url2="jdbc:jtds:sqlserver://localhost:1433/test;SelectMethod=cursor;characterEncoding=GBK";
?
?public static void main(String arg[]) throws IOException,SQLException{
??
??
??Connection conn1=null;
??Connection conn2=null;
??Statement ps1=null;
??//Statement ps2=null;
??ResultSet rs1=null;
??//ResultSet rs2=null;
??String sql1=null;
??String sql2=null;
??PreparedStatement pstmt =null;
??
??try{
???Class.forName(driver1);
???Class.forName(driver2);
???conn1 = DriverManager.getConnection(url1,"","");
???conn2= DriverManager.getConnection(url2,"sa","sa");
???ps1 = conn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
???//ps2 = conn2.createStatement();
??}
??catch(ClassNotFoundException e){
???System.out.print(e);
??}
??catch (SQLException e) {
???// TODO 自動生成 catch 塊
???e.printStackTrace();
??}
??
??try{???
???sql1="select * from area";
???rs1 = ps1.executeQuery(sql1);
???sql2 = "insert into tbcity(code,name,parentid,type) values (?,?,?,?)";
???pstmt=conn2.prepareStatement(sql2);
???
???int code=0;
???int parentid=0;
???String name=new String();
???while(rs1.next()){????
????code=rs1.getInt(2);
????name=rs1.getString(3);
????parentid=rs1.getInt(4);
????//sql2="insert into TBCITY(code,name,parentid,type) values ("+code+",'"+name+"',"+parentid+",3)";
????//ps2.executeUpdate(sql2);
????pstmt.setInt(1,code);
????pstmt.setString(2, name);
????pstmt.setInt(3,parentid);
????pstmt.setInt(4, 3);
????pstmt.addBatch();
???}???
???pstmt.executeBatch(); ???
?????
???System.out.println("轉換完成!謝謝使用");
???ps1.close();
???//ps2.close();
???pstmt.close();
???conn1.close();
???conn2.close();
??}
??catch(Exception e){
???System.out.print(e);
??}
??
?}
?
}