提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  后端

当前栏目

Java使用JDBC连接Oracle_MSSQL实例代码

JAVAOracle实例连接JDBC代码 mssql 使用
2023-06-13 09:15:15 时间

一、Statement

复制代码代码如下:


importjava.sql.*;

publicclassTestJDBC{

 publicstaticvoidmain(String[]args){
  Connectionoracle_conn=null;
  Statementoracle_stmt=null;
  ResultSetoracle_rs=null;

  Connectionmssql_conn=null;
  Statementmssql_stmt=null;
  ResultSetmssql_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*fromVideoBaseInfo");

   while(mssql_rs.next()){
    System.out.println("正在插入VideoId："+mssql_rs.getInt("VideoId")+"的记录...");
    oracle_stmt.executeUpdate("insertintoVIDEO_BASEINFOvalues("
      +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(ClassNotFoundExceptione){
   e.printStackTrace();
  }catch(SQLExceptione){
   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(SQLExceptione){
    e.printStackTrace();
   }
  }
 }

}

二、PreparedStatement

复制代码代码如下:


importjava.sql.*;

publicclassTestPreparedStatement{

 publicstaticvoidmain(String[]args){
  Connectionoracle_conn=null;
  PreparedStatementoracle_stmt=null;
  ResultSetoracle_rs=null;

  Connectionmssql_conn=null;
  Statementmssql_stmt=null;
  ResultSetmssql_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("insertintoVideo_ItemInfovalues(?,?,?,?,?,?,?,?,?)");

   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*fromVideoItemInfo");

   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(ClassNotFoundExceptione){
   e.printStackTrace();
  }catch(SQLExceptione){
   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(SQLExceptione){
    e.printStackTrace();
   }
  }
 }
}

三、CallableStatement

复制代码代码如下:
importjava.sql.*;
publicclassTestProc{

 /**
 *@paramargs
 */
 publicstaticvoidmain(String[]args)throwsException{

  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT","scott","tiger");
  CallableStatementcstmt=conn.prepareCall("{callp(?,?,?,?)}");
  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

复制代码代码如下:
importjava.sql.*;
publicclassTestBatch{


 publicstaticvoidmain(String[]args)throwsException{
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT","scott","tiger");
  /*
  Statementstmt=conn.createStatement();
  stmt.addBatch("insertintodept2values(51,"500","haha")");
  stmt.addBatch("insertintodept2values(52,"500","haha")");
  stmt.addBatch("insertintodept2values(53,"500","haha")");
  stmt.executeBatch();
  stmt.close();
  */

  PreparedStatementps=conn.prepareStatement("insertintodept2values(?,?,?)");
  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

复制代码代码如下:
importjava.sql.*;
publicclassTestTransaction{


 publicstaticvoidmain(String[]args){

  Connectionconn=null;
  Statementstmt=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("insertintodept2values(51,"500","haha")");
   stmt.addBatch("insertintodept2values(52,"500","haha")");
   stmt.addBatch("insertintodept2values(53,"500","haha")");
   stmt.executeBatch();
   conn.commit();
   conn.setAutoCommit(true);
  }catch(ClassNotFoundExceptione){
   e.printStackTrace();
  }catch(SQLExceptione){

   e.printStackTrace();

   try{
    if(conn!=null)
    {
     conn.rollback();
     conn.setAutoCommit(true);
    }
   }catch(SQLExceptione1){
    e1.printStackTrace();
   }
  }finally{
   try{
    if(stmt!=null)
     stmt.close();
    if(conn!=null)
     conn.close();
   }catch(SQLExceptione){
    e.printStackTrace();
   }
  }  

 }

}

六、ScrollResultSet

复制代码代码如下:
importjava.sql.*;

publicclassTestScroll{
 publicstaticvoidmain(Stringargs[]){

  try{
   neworacle.jdbc.driver.OracleDriver();
   Stringurl="jdbc:oracle:thin:@192.168.0.1:1521:SXT";
   Connectionconn=DriverManager
     .getConnection(url,"scott","tiger");
   Statementstmt=conn.createStatement(
     ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_READ_ONLY);
   ResultSetrs=stmt
     .executeQuery("select*fromemporderbysal");
   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(SQLExceptione){
   e.printStackTrace();
  }
 }
}

七、UpdateResultSet

复制代码代码如下:
importjava.sql.*;
publicclassTestUpdataRs{
   publicstaticvoidmain(Stringargs[]){

 try{
    neworacle.jdbc.driver.OracleDriver();
    Stringurl="jdbc:oracle:thin:@192.168.0.1:1521:SXT";
    Connectionconn=DriverManager.getConnection(url,"scott","tiger");
    Statementstmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

    ResultSetrs=stmt.executeQuery("select*fromemp2");

    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(SQLExceptione){
    e.printStackTrace();
  }
   }
}

相关文章