Java 从Access数据库读取数据,插入到JavaDB数据库表中

时间:2022-07-25 21:45:07

一段代码,实现的功能是从Access数据库读取表数据,然后依次全部插入到JavaDB数据库表中,类似于做拷贝.


       import java.sql.*;


        String sql_insert =null;//将sql_insert定义成全局变量,是为了出错时,在catch部分能打印sql_insert内容,有助于调试.
        
        ResultSet rs = null;
        try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         String url = "jdbc:odbc:Driver={Microsoft Access Driver " +
            "(*.mdb, *.accdb)};DBQ=path to  database\\Database1.accdb";
//红色处应填入实际地址
        Connection con = DriverManager.getConnection(url);
        System.out.println("Connected!");
        
            Statement stmt = null;
            
 
           // SQL query command
            String SQL = "SELECT * FROM data";
            stmt = con.createStatement();
            //stmt.execute(SQL);
            
            rs = stmt.executeQuery(SQL);
 
          
            String driver = "org.apache.derby.jdbc.ClientDriver";//在derby.jar里面
        //String dbName="EmbeddedDB";
       String dbURL = "jdbc:derby://localhost:1527/PassWords;territory=zh_CN;user=admin;password=123456"
;// create=true表示当数据库不存在时就创建它        
       try {          
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(dbURL);//启动嵌入式数据库
            Statement st = conn.createStatement();

                        
            while(rs.next()){
                byte[] bts=null;
                String des = null;
                String usname = null;
                 String psw = null;
                String tips = null;
                String other = null;
                
                String ID = new String(rs.getBytes("ID"),"gbk");
                int id = Integer.parseInt(ID);
                
                bts = rs.getBytes("DESCRIPTION");
                if(bts!=null )
                    des = new String(bts,"gbk");
                
                bts = rs.getBytes("USERNAME");
                if(bts !=null)
                    usname = new String(bts,"gbk");
                
                bts = rs.getBytes("PASSWORD");
                if(bts !=null)
                    psw = new String(bts,"gbk");
               
                bts = rs.getBytes("TIPS");
                if(bts !=null)
                    tips = new String(bts,"gbk");
               
                
                bts = rs.getBytes("OTHERS");
                if(bts != null)
                    other =  new String(bts,"gbk");
                
                sql_insert = "insert into APP.PSW(ID,DESCRIPTION,USERNAME,PASSWORD,TIPS,OTHERS) VALUES ("+id+",'"+des+"','"+usname+"','"+psw+"','"+tips+"','"+other+"')";
            st.executeUpdate(sql_insert);//插入一条数据
                
            }
        } catch(Exception e){
            System.out.println(sql_insert);
            e.printStackTrace();
            
        }
                    
            
        con.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception: "+ e.toString());
        } catch (ClassNotFoundException cE) {
            System.out.println("Class Not Found Exception: "+
                cE.toString());
        }