一个简单的用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.blogjava.net/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);
  }
  
 }
 
}