欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Java使用JDBC连接Oracle_MSSQL实例代码

程序员文章站 2024-02-13 19:16:10
一、statement 复制代码 代码如下:import java.sql.*; public class testjdbc {  public stati...

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