（转）Java使用JDBC连接Oracle_MSSQL实例

转自：http://blog.csdn.net/tungkee/article/details/6620561

一、Statement
[java] view plaincopy
import java.sql.*; 
 
public class TestJDBC { 
 
    public static void main(String[] args) { 
        Connection oracle_conn = null; 
        Statement oracle_stmt = null; 
        ResultSet oracle_rs = null; 
         
        Connection mssql_conn = null; 
        Statement mssql_stmt = null; 
        ResultSet mssql_rs = null; 
                 
        try { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
            oracle_conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.215:1521:orcl", "username", "password"); 
             
            oracle_stmt = oracle_conn.createStatement(); 
             
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
            mssql_conn = DriverManager.getConnection("jdbc:sqlserver://192.168.0.213:1433;DatabaseName=VIS", "username", "password"); 
             
            mssql_stmt = mssql_conn.createStatement(); 
            mssql_rs = mssql_stmt.executeQuery("select * from VideoBaseInfo"); 
             
            while(mssql_rs.next()) { 
                System.out.println("正在插入VideoId：" + mssql_rs.getInt("VideoId") + "的记录..."); 
                oracle_stmt.executeUpdate("insert into VIDEO_BASEINFO values("  
                        + mssql_rs.getInt("VideoId") + ",'" 
                        + mssql_rs.getString("VideoName") + "','" 
                        + mssql_rs.getString("VideoVersion") + "'," 
                        + mssql_rs.getInt("VideoMp4Items") + "," 
                        + mssql_rs.getInt("VideoRmvbItems") + ",'" 
                        + mssql_rs.getString("VideoAliasName") + "','" 
                        + mssql_rs.getString("VideoAge") + "'" 
                        + ")"); 
            } 
             
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                if(oracle_rs != null) { 
                    oracle_rs.close(); 
                    oracle_rs = null; 
                } 
                 
                if(oracle_stmt != null) { 
                    oracle_stmt.close(); 
                    oracle_stmt = null; 
                } 
                 
                if(oracle_conn != null) { 
                    oracle_conn.close(); 
                    oracle_conn = null; 
                } 
                 
                if(mssql_rs != null) { 
                    mssql_rs.close(); 
                    mssql_rs = null; 
                } 
                 
                if(mssql_stmt != null) { 
                    mssql_stmt.close(); 
                    mssql_stmt = null; 
                } 
                 
                if(mssql_conn != null) { 
                    mssql_conn.close(); 
                    mssql_conn = null; 
                } 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
} 

二、PreparedStatement
[java] view plaincopy
import java.sql.*; 
 
public class TestPreparedStatement { 
 
    public static void main(String[] args) { 
        Connection oracle_conn = null; 
        PreparedStatement oracle_stmt = null; 
        ResultSet oracle_rs = null; 
         
        Connection mssql_conn = null; 
        Statement mssql_stmt = null; 
        ResultSet mssql_rs = null; 
                 
        try { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
            oracle_conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.215:1521:orcl", "username", "password"); 
             
            oracle_stmt = oracle_conn.prepareStatement("insert into Video_ItemInfo values(?, ?, ?, ?, ?, ?, ?, ?, ?)"); 
             
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
            mssql_conn = DriverManager.getConnection("jdbc:sqlserver://192.168.0.213:1433;DatabaseName=VIS", "username", "password"); 
 
            mssql_stmt = mssql_conn.createStatement(); 
            mssql_rs = mssql_stmt.executeQuery("select * from VideoItemInfo"); 
             
            while(mssql_rs.next()) { 
                System.out.println("正在插入ItemIndex：" + mssql_rs.getInt("ItemIndex") + "的记录..."); 
                oracle_stmt.setInt(1, mssql_rs.getInt("ItemIndex")); 
                oracle_stmt.setInt(2, mssql_rs.getInt("VideoId")); 
                oracle_stmt.setString(3, mssql_rs.getString("VideoItemName")); 
                oracle_stmt.setString(4, mssql_rs.getString("VideoExtName")); 
                oracle_stmt.setDouble(5, mssql_rs.getDouble("VideoSize")); 
                oracle_stmt.setString(6, mssql_rs.getString("VideoPath")); 
                oracle_stmt.setString(7, mssql_rs.getString("VideoType")); 
                oracle_stmt.setDate(8, mssql_rs.getDate("VideoDate")); 
                oracle_stmt.setString(9, mssql_rs.getString("ApplicationWay")); 
                 
                oracle_stmt.executeUpdate(); 
            } 
            System.out.println("插入数据到Video_ItemInfo表中操作已完成！"); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                if(oracle_rs != null) { 
                    oracle_rs.close(); 
                    oracle_rs = null; 
                } 
                 
                if(oracle_stmt != null) { 
                    oracle_stmt.close(); 
                    oracle_stmt = null; 
                } 
                 
                if(oracle_conn != null) { 
                    oracle_conn.close(); 
                    oracle_conn = null; 
                } 
                 
                if(mssql_rs != null) { 
                    mssql_rs.close(); 
                    mssql_rs = null; 
                } 
                 
                if(mssql_stmt != null) { 
                    mssql_stmt.close(); 
                    mssql_stmt = null; 
                } 
                 
                if(mssql_conn != null) { 
                    mssql_conn.close(); 
                    mssql_conn = null; 
                } 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
} 

三、CallableStatement
[java] view plaincopy
import java.sql.*; 
public class TestProc { 
 
    /**
     * @param args
     */ 
    public static void main(String[] args) throws Exception { 
         
        Class.forName("oracle.jdbc.driver.OracleDriver"); 
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger"); 
        CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}"); 
        cstmt.registerOutParameter(3, Types.INTEGER); 
        cstmt.registerOutParameter(4, Types.INTEGER); 
        cstmt.setInt(1, 3); 
        cstmt.setInt(2, 4); 
        cstmt.setInt(4, 5); 
        cstmt.execute(); 
        System.out.println(cstmt.getInt(3)); 
        System.out.println(cstmt.getInt(4)); 
        cstmt.close(); 
        conn.close(); 
    } 
 
} 

四、Batch
[java] view plaincopy
import java.sql.*; 
public class TestBatch { 
 
 
    public static void main(String[] args) throws Exception { 
        Class.forName("oracle.jdbc.driver.OracleDriver"); 
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger"); 
        /*
        Statement stmt = conn.createStatement();
        stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
        stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
        stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
        stmt.executeBatch();
        stmt.close();
        */ 
         
        PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)"); 
        ps.setInt(1, 61); 
        ps.setString(2, "haha"); 
        ps.setString(3, "bj"); 
        ps.addBatch(); 
         
        ps.setInt(1, 62); 
        ps.setString(2, "haha"); 
        ps.setString(3, "bj"); 
        ps.addBatch(); 
         
        ps.setInt(1, 63); 
        ps.setString(2, "haha"); 
        ps.setString(3, "bj"); 
        ps.addBatch(); 
         
        ps.executeBatch(); 
        ps.close(); 
         
        conn.close(); 
 
    } 
 
} 

五、Transaction
[java] view plaincopy
import java.sql.*; 
public class TestTransaction { 
 
 
    public static void main(String[] args) { 
         
        Connection conn = null; 
        Statement stmt = null; 
         
        try { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger"); 
             
            conn.setAutoCommit(false); 
            stmt = conn.createStatement(); 
            stmt.addBatch("insert into dept2 values (51, '500', 'haha')"); 
            stmt.addBatch("insert into dept2 values (52, '500', 'haha')"); 
            stmt.addBatch("insert into dept2 values (53, '500', 'haha')"); 
            stmt.executeBatch(); 
            conn.commit(); 
            conn.setAutoCommit(true); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } catch(SQLException e) { 
             
            e.printStackTrace(); 
             
            try { 
                if(conn != null) 
                { 
                    conn.rollback(); 
                    conn.setAutoCommit(true); 
                } 
            } catch (SQLException e1) { 
                e1.printStackTrace(); 
            } 
        }finally { 
            try { 
                if(stmt != null) 
                    stmt.close(); 
                if(conn != null) 
                    conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
         
 
    } 
 
} 

六、ScrollResultSet
[java] view plaincopy
import java.sql.*; 
 
public class TestScroll { 
    public static void main(String args[]) { 
 
        try { 
            new oracle.jdbc.driver.OracleDriver(); 
            String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT"; 
            Connection conn = DriverManager 
                    .getConnection(url, "scott", "tiger"); 
            Statement stmt = conn.createStatement( 
                    ResultSet.TYPE_SCROLL_INSENSITIVE, 
                    ResultSet.CONCUR_READ_ONLY); 
            ResultSet rs = stmt 
                    .executeQuery("select * from emp order by sal"); 
            rs.next(); 
            System.out.println(rs.getInt(1)); 
            rs.last(); 
            System.out.println(rs.getString(1)); 
            System.out.println(rs.isLast()); 
            System.out.println(rs.isAfterLast()); 
            System.out.println(rs.getRow()); 
            rs.previous(); 
            System.out.println(rs.getString(1)); 
            rs.absolute(6); 
            System.out.println(rs.getString(1)); 
            rs.close(); 
            stmt.close(); 
            conn.close(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    } 
} 

七、UpdateResultSet
[java] view plaincopy
import java.sql.*; 
public class TestUpdataRs { 
    public static void main(String args[]){ 
     
    try{ 
        new oracle.jdbc.driver.OracleDriver(); 
        String url="jdbc:oracle:thin:@192.168.0.1:1521:SXT"; 
        Connection conn=DriverManager.getConnection(url,"scott","tiger"); 
        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); 
         
        ResultSet rs=stmt.executeQuery("select * from emp2"); 
         
        rs.next(); 
        //更新一行数据 
        rs.updateString("ename","AAAA"); 
        rs.updateRow(); 
 
        //插入新行 
        rs.moveToInsertRow(); 
        rs.updateInt(1, 9999); 
        rs.updateString("ename","AAAA"); 
        rs.updateInt("mgr", 7839); 
        rs.updateDouble("sal", 99.99); 
        rs.insertRow(); 
        //将光标移动到新建的行 
        rs.moveToCurrentRow(); 
 
        //删除行 
        rs.absolute(5); 
        rs.deleteRow(); 
 
        //取消更新 
        //rs.cancelRowUpdates(); 
 
      }catch(SQLException e){ 
        e.printStackTrace(); 
      } 
    } 
}