Java使用JDBC连接Oracle_MSSQL实例代码

一、statement

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

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

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

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

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

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

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();
   }
    }
}