在劫

吾生也有涯,而知也无涯 。

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

JDBC
JDBC API提供了以下接口和类:

  • DriverManager: 这个类管理数据库驱动程序的列表。确定内容是否符合从Java应用程序使用的通信子协议正确的数据库驱动程序的连接请求。识别JDBC在一定子协议的第一个驱动器将被用来建立数据库连接。
  • Driver: 此接口处理与数据库服务器通信。很少直接直接使用驱动程序(Driver)对象,一般使DriverManager中的对象,它用于管理此类型的对象。它也抽象与驱动程序对象工作相关的详细信息
  • Connection : 此接口与接触数据库的所有方法。连接对象表示通信上下文,即,与数据库中的所有的通信是通过此唯一的连接对象。
  • Statement : 可以使用这个接口创建的对象的SQL语句提交到数据库。一些派生的接口接受除执行存储过程的参数。
  • ResultSet: 这些对象保存从数据库后,执行使用Statement对象的SQL查询中检索数据。它作为一个迭代器,可以通过移动它来检索下一个数据。
  • QLException: 这个类用于处理发生在数据库应用程序中的任何错误。

JDBC库中所包含的API通常与数据库使用于:

  • 连接到数据库
  • 创建SQL或MySQL语句
  • 在数据库中执行SQL或MySQL查询
  • 查看和修改数据库中的数据记录

创建JDBC驱动程序

  1. 注册驱动程序
Class.forName("com.mysql.jdbc.Driver");
  1. 打开一个连接
#DriverManager.getConnection()方法来创建一个Connection对象,它代表一个数据库的物理连接
static final String USER = "root";
static final String PASS = "pwd123456";
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
  1. 执行一个查询
#需要使用一个类型为Statement或PreparedStatement的对象,并提交一个SQL语句到数据库执行查询
stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
stmt = conn.createStatement();
String sql;
sql = "DELETE FROM Employees";
ResultSet rs = stmt.executeUpdate(sql);

4.从结果集中提取数据

while(rs.next()){
    //Retrieve by column name
    int id  = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");

    //Display values
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}

5.清理环境资源

rs.close();
stmt.close();
conn.close();
#完整程序
import java.sql.*;

public class FirstExample {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/test";

   //  Database credentials -- 数据库名和密码自己修改
   static final String USER = "username";
   static final String PASS = "password";

   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end FirstExample - by www.yiibai.com

注册JDBC驱动程序

#方式一:
	Class.forName("oracle.jdbc.driver.OracleDriver");
#方式二:
#使用静态DriverManager.registerDriver()方法来注册驱动程序。如果使用的是非JDK兼容的JVM(如Microsoft提供的),则应使用registerDriver()方法
	Driver myDriver = new oracle.jdbc.driver.OracleDriver();
	DriverManager.registerDriver( myDriver );

数据库URL配置

RDBMS 	     JDBC驱动程序名称 	                   URL格式
MySQL 	     com.mysql.jdbc.Driver 	           jdbc:mysql://hostname/databaseName
ORACLE 	     oracle.jdbc.driver.OracleDriver 	   jdbc:oracle:thin:@hostname:portNumber:databaseName
PostgreSQL   org.postgresql.Driver 	           jdbc:postgresql://hostname:port/dbname
DB2 	     com.ibm.db2.jdbc.net.DB2Driver 	   jdbc:db2:hostname:port Number/databaseName
Sybase 	     com.sybase.jdbc.SybDriver 	           jdbc:sybase:Tds:hostname: portNumber/databaseName
	//localhost:3306本地连接,端口号3306
	//ms:要连接的数据库名
	//mysql数据库用的是gbk编码,而项目数据库用的是utf-8编码。这时候如果添加了useUnicode=true&characterEncoding=UTF-8
	//useSSL:使用JDBC跟你的数据库连接的时候,你的JDBC版本与MySQL版本不兼容,MySQL的版本更高一些,在连接语句后加上“useSSL=‘true’” ,就可以连接到数据库了
	private static String DB_URL = "jdbc:mysql://localhost:3306/ms?useUnicode=true&characterEncoding=utf-8&useSSL=false";
DriverManager.getConnection()方法:
    getConnection(String url)
    getConnection(String url, Properties prop)
    getConnection(String url, String user, String password)

JDBC Statement,CallableStatement和PreparedStatement接口定义了可用于发送SQL或PL/SQL命令,并从数据库接收数据的方法和属性。

Statement 		用于对数据库进行通用访问,在运行时使用静态SQL语句时很有用。 Statement接口不能接受参数。
PreparedStatement 	当计划要多次使用SQL语句时使用。PreparedStatement接口在运行时接受输入参数。
CallableStatement 	当想要访问数据库存储过程时使用。CallableStatement接口也可以接受运行时输入参数。

创建Statement对象

Statement stmt = null;
try {
   stmt = conn.createStatement( );
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}
  boolean execute (String SQL) : 如果可以检索到ResultSet对象,则返回一个布尔值true; 否则返回false。使用此方法执行SQLDDL语句或需要使用真正的动态SQL,可使用于执行创建数据库,创建表的SQL语句等等。
  int executeUpdate (String SQL): 返回受SQL语句执行影响的行数。使用此方法执行预期会影响多行的SQL语句,例如:INSERT,UPDATE或DELETE语句。
  ResultSet executeQuery(String SQL):返回一个ResultSet对象。 当您希望获得结果集时,请使用此方法,就像使用SELECT语句一样。

先关闭Connection对象,它也会关闭Statement对象。 但是,应该始终显式关闭Statement对象,以确保正确的清理顺序。

PreparedStatement对象

PreparedStatement接口扩展了Statement接口,它添加了比Statement对象更好一些优点的功能。此语句可以动态地提供/接受参数。

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}
#JDBC中的所有参数都由 ? 符号作为占位符,这被称为参数标记。 在执行SQL语句之前,必须为每个参数(占位符)提供值。

setXXX()方法将值绑定到参数,其中XXX表示要绑定到输入参数的值的Java数据类型
每个参数标记是它其顺序位置引用。第一个标记表示位置1,下一个位置2等等。 该方法与Java数组索引不同(它不从0开始)。
所有Statement对象与数据库交互的方法(a)execute(),(b)executeQuery()和(c)executeUpdate()也可以用于PreparedStatement对象。 但是,这些方法被修改为可以使用输入参数的SQL语句。
CallableStatement对象
存在三种类型的参数:IN,OUT和INOUT。 PreparedStatement对象只使用IN参数

参数 	描述
IN 	创建SQL语句时其参数值是未知的。 使用setXXX()方法将值绑定到IN参数。
OUT 	由SQL语句返回的参数值。可以使用getXXX()方法从OUT参数中检索值。
INOUT 	提供输入和输出值的参数。使用setXXX()方法绑定变量并使用getXXX()方法检索值。

JDBC结果集
java.sql.ResultSet接口表示数据库查询的结果集。

ResultSet接口的方法可以分为三类:
    浏览方法:用于移动光标。
    获取方法:用于查看光标指向的当前行的列中的数据。
    更新方法:用于更新当前行的列中的数据。 然后在基础数据库中更新数据。
    createStatement(int RSType, int RSConcurrency);
    prepareStatement(String SQL, int RSType, int RSConcurrency);
    prepareCall(String sql, int RSType, int RSConcurrency);
	第一个参数表示ResultSet对象的类型,第二个参数是两个ResultSet常量之一,用于指定结果集是只读还是可更新
ResultSet.CONCUR_READ_ONLY 	创建只读结果集,这是默认值。
ResultSet.CONCUR_UPDATABLE 	创建可更新的结果集

更新结果集

1、public void updateString(int columnIndex, String s) throws SQLException 	将指定列中的String值更改为指定的s值。
2、public void updateString(String columnName, String s) throws SQLException 	与前前的方法类似,除了使用列的名称而不是列的索引指定。
1 	public void updateRow() 	更新数据库中当前行
2 	public void deleteRow() 	从数据库中删除当前行
3 	public void refreshRow() 	刷新结果集中的数据以反映数据库中最近的任何更改。
4 	public void cancelRowUpdates() 	取消对当前行所做的任何更新。
5 	public void insertRow() 	在数据库中插入一行。 只有当光标指向插入行时,才能调用此方法。

JDBC事物

#名为conn的Connection对象
conn.setAutoCommit(false);
完成更改后,若要提交更改,那么可在连接对象上调用commit()方法,如下所示:
conn.commit( );
否则,要使用连接名为conn的数据库回滚更新,请使用以下代码 -
conn.rollback( );
try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();

   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22, 'Sita', 'Singh')";
   stmt.executeUpdate(SQL);
   // If there is no error.
   conn.commit();
}catch(SQLException se){
   // If there is any error.
   conn.rollback();
}

使用保存点

Connection对象有两种新的方法可用来管理保存点 -
    setSavepoint(String savepointName): - 定义新的保存点,它还返回一个Savepoint对象。
    releaseSavepoint(Savepoint savepointName): - 删除保存点。要注意,它需要一个Savepoint对象作为参数。 该对象通常是由setSavepoint()方法生成的保存点。
try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();

   //set a Savepoint
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Employees " +
                "VALUES (106, 24, 'Curry', 'Stephen')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Employees " +
                "VALUES (107, 32, 'Kobe', 'Bryant')";
   stmt.executeUpdate(SQL);
   // If there is no error, commit the changes.
   conn.commit();

}catch(SQLException se){
   // If there is any error.
   conn.rollback(savepoint1);
}
import java.sql.*;

public class CommitAndRollback {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "123456";

 public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Set auto commit as false.
      conn.setAutoCommit(false);

      //STEP 5: Execute a query to create statment with
      // required arguments for RS example.
      System.out.println("Creating statement...");
      stmt = conn.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE,
                           ResultSet.CONCUR_UPDATABLE);

      //STEP 6: INSERT a row into Employees table
      System.out.println("Inserting one row....");
      String SQL = "INSERT INTO Employees " +
                    "VALUES (106, 28, 'Curry', 'Stephen')";
      stmt.executeUpdate(SQL);  

      //STEP 7: INSERT one more row into Employees table
      SQL = "INSERT INTO Employees " +
                    "VALUES (107, 32, 'Kobe', 'Bryant')";
      stmt.executeUpdate(SQL);

      //STEP 8: Commit data here.
      System.out.println("Commiting data here....");
      conn.commit();

      //STEP 9: Now list all the available records.
      String sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);
      System.out.println("List result set for reference....");
      printRs(rs);

      //STEP 10: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
      // If there is an error then rollback the changes.
      System.out.println("Rolling back data here....");
      try{
         if(conn!=null)
            conn.rollback();
      }catch(SQLException se2){
         se2.printStackTrace();
      }//end try

   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main

   public static void printRs(ResultSet rs) throws SQLException{
      //Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
     }
     System.out.println();
   }//end printRs()
}//end JDBCExample
import java.sql.*;

public class JDBCSavepoint {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "123456";

public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Set auto commit as false.
      conn.setAutoCommit(false);

      //STEP 5: Execute a query to delete statment with
      // required arguments for RS example.
      System.out.println("Creating statement...");
      stmt = conn.createStatement();

      //STEP 6: Now list all the available records.
      String sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);
      System.out.println("List result set for reference....");
      printRs(rs);

      // STEP 7: delete rows having ID grater than 104
      // But save point before doing so.
      Savepoint savepoint1 = conn.setSavepoint("ROWS_DELETED_1");
      System.out.println("Deleting row....");
      String SQL = "DELETE FROM Employees " +
                   "WHERE ID = 106";
      stmt.executeUpdate(SQL);  
      // oops... we deleted too wrong employees!
      //STEP 8: Rollback the changes afetr save point 2.
      conn.rollback(savepoint1);

    // STEP 9: delete rows having ID grater than 104
      // But save point before doing so.
      Savepoint savepoint2 = conn.setSavepoint("ROWS_DELETED_2");
      System.out.println("Deleting row....");
      SQL = "DELETE FROM Employees " +
                   "WHERE ID = 107";
      stmt.executeUpdate(SQL);  

      //STEP 10: Now list all the available records.
      sql = "SELECT id, first, last, age FROM Employees";
      rs = stmt.executeQuery(sql);
      System.out.println("List result set for reference....");
      printRs(rs);

      //STEP 10: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
      // If there is an error then rollback the changes.
      System.out.println("Rolling back data here....");
      try{
         if(conn!=null)
            conn.rollback();
      }catch(SQLException se2){
         se2.printStackTrace();
      }//end try

   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main

   public static void printRs(ResultSet rs) throws SQLException{
      //Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
     }
     System.out.println();
   }//end printRs()
}//end JDBCExample

JDBC异常

SQLException方法
方法 			描述
getErrorCode( ) 获取与异常关联的错误代码。
getMessage( ) 	获取驱动程序处理的错误的JDBC驱动程序的错误消息,或获取数据库错误的Oracle错误代码和消息。
getSQLState( ) 	获取XOPEN SQLstate字符串。 对于JDBC驱动程序错误,不会从此方法返回有用的信息。 对于数据库错误,返回五位数的XOPEN SQLstate代码。 此方法可以返回null。
getNextException( ) 	获取异常链中的下一个Exception对象。
printStackTrace( ) 	打印当前异常或可抛出的异常,并将其追溯到标准错误流。
printStackTrace(PrintStream s) 	将此throwable及其回溯打印到指定的打印流。
printStackTrace(PrintWriter w) 	打印这个throwable,它是回溯到指定的打印器(PrintWriter)。
import java.sql.*;

public class TryCatchFinally {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "123456";

   public static void main(String[] args) {
   Connection conn = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      Statement stmt = conn.createStatement();
      String sql;
      sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end JDBCExample

JDBC批量处理
使用Statement对象进行批处理

  • 使用createStatement()方法创建Statement对象。
  • 使用setAutoCommit()将自动提交设置为false。
  • 使用addBatch()方法在创建的Statement对象上添加SQL语句到批处理中。
  • 在创建的Statement对象上使用executeBatch()方法执行所有SQL语句。
  • 最后,使用commit()方法提交所有更改。
Statement stmt = conn.createStatement();

// Set auto-commit to false
conn.setAutoCommit(false);

// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(200,'Ruby', 'Yang', 30)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create one more SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(201,'Java', 'Lee', 35)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create one more SQL statement
String SQL = "UPDATE Employees SET age = 35 " +
             "WHERE id = 100";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

使用PrepareStatement对象进行批处理

  • 使用占位符创建SQL语句。
  • 使用prepareStatement()方法创建PrepareStatement对象。
  • 使用setAutoCommit()将自动提交设置为false。
  • 使用addBatch()方法在创建的Statement对象上添加SQL语句到批处理中。
  • 在创建的Statement对象上使用executeBatch()方法执行所有SQL语句。
  • 最后,使用commit()方法提交所有更改。
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(?, ?, ?, ?)";

// Create PrepareStatement object
PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false
conn.setAutoCommit(false);

// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "JDBC" );
pstmt.setString( 3, "Li" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "CSharp" );
pstmt.setString( 3, "Liang" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();

//add more batches
.
.
.
.
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

使用Statement对象的批处理的典型步骤序列 -

  • 使用createStatement()方法创建Statement对象。
  • 使用setAutoCommit()将自动提交设置为false。
  • 使用addBatch()方法在创建的Statement对象上添加SQL语句到批处理中。
  • 在创建的Statement对象上使用executeBatch()方法执行所有SQL语句。
  • 最后,使用commit()方法提交所有更改。
import java.sql.*;

public class BatchingWithStatement {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "123456";

   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      // Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      // Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      // Create statement
      System.out.println("Creating statement...");
      stmt = conn.createStatement();

      // Set auto-commit to false
      conn.setAutoCommit(false);

      // First, let us select all the records and display them.
      printRows( stmt );

      // Create SQL statement
      String SQL = "INSERT INTO Employees (id, first, last, age) " + 
                   "VALUES(200,'Curry', 'Stephen', 30)";
      // Add above SQL statement in the batch.
      stmt.addBatch(SQL);

      // Create one more SQL statement
      SQL = "INSERT INTO Employees (id, first, last, age) " +
            "VALUES(201,'Kobe', 'Bryant', 35)";
      // Add above SQL statement in the batch.
      stmt.addBatch(SQL);

      // Create one more SQL statement
      SQL = "UPDATE Employees SET age = 35 " +
            "WHERE id = 100";
      // Add above SQL statement in the batch.
      stmt.addBatch(SQL);

      // Create an int[] to hold returned values
      int[] count = stmt.executeBatch();

      //Explicitly commit statements to apply changes
      conn.commit();

      // Again, let us select all the records and display them.
      printRows( stmt );

      // Clean-up environment
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main

public static void printRows(Statement stmt) throws SQLException{
   System.out.println("Displaying available rows...");
   // Let us select all the records and display them.
   String sql = "SELECT id, first, last, age FROM Employees";
   ResultSet rs = stmt.executeQuery(sql);

   while(rs.next()){
      //Retrieve by column name
      int id  = rs.getInt("id");
      int age = rs.getInt("age");
      String first = rs.getString("first");
      String last = rs.getString("last");

      //Display values
      System.out.print("ID: " + id);
      System.out.print(", Age: " + age);
      System.out.print(", First: " + first);
      System.out.println(", Last: " + last);
   }
   System.out.println();
   rs.close();
}//end printRows()
}//end JDBCExample

使用PrepareStatement对象进行批处理的典型步骤顺序 -

  • 使用占位符创建SQL语句。
  • 使用prepareStatement()方法创建PrepareStatement对象。
  • 使用setAutoCommit()将自动提交设置为false。
  • 使用addBatch()方法在创建的Statement对象上添加SQL语句到批处理中。
  • 在创建的Statement对象上使用executeBatch()方法执行所有SQL语句。
  • 最后,使用commit()方法提交所有更改。
import java.sql.*;

public class BatchingWithPrepareStatement {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "123456";

   public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement stmt = null;
   try{
      // Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      // Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      // Create SQL statement
      String SQL = "INSERT INTO Employees(id,first,last,age) " +
                   "VALUES(?, ?, ?, ?)";

      // Create preparedStatemen
      System.out.println("Creating statement...");
      stmt = conn.prepareStatement(SQL);

      // Set auto-commit to false
      conn.setAutoCommit(false);

      // First, let us select all the records and display them.
      printRows( stmt );

      // Set the variables
      stmt.setInt( 1, 400 );
      stmt.setString( 2, "Python" );
      stmt.setString( 3, "Zhang" );
      stmt.setInt( 4, 33 );
      // Add it to the batch
      stmt.addBatch();

      // Set the variables
      stmt.setInt( 1, 401 );
      stmt.setString( 2, "C++" );
      stmt.setString( 3, "Huang" );
      stmt.setInt( 4, 31 );
      // Add it to the batch
      stmt.addBatch();

      // Create an int[] to hold returned values
      int[] count = stmt.executeBatch();

      //Explicitly commit statements to apply changes
      conn.commit();

      // Again, let us select all the records and display them.
      printRows( stmt );

      // Clean-up environment
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main

public static void printRows(Statement stmt) throws SQLException{
   System.out.println("Displaying available rows...");
   // Let us select all the records and display them.
   String sql = "SELECT id, first, last, age FROM Employees";
   ResultSet rs = stmt.executeQuery(sql);

   while(rs.next()){
      //Retrieve by column name
      int id  = rs.getInt("id");
      int age = rs.getInt("age");
      String first = rs.getString("first");
      String last = rs.getString("last");

      //Display values
      System.out.print("ID: " + id);
      System.out.print(", Age: " + age);
      System.out.print(", First: " + first);
      System.out.println(", Last: " + last);
   }
   System.out.println();
   rs.close();
}//end printRows()
}//end JDBCExample

JDBC存储过程调用
JDBC流ASCII和二进制数据

PreparedStatement对象可以使用输入和输出流来提供参数数据。能够将整个文件放入可以容纳大值的数据库列,例如CLOB和BLOB数据类型。
有以下方法可用于流式传输数据 -
    setAsciiStream():此方法用于提供大的ASCII值。
    setCharacterStream():此方法用于提供较大的UNICODE值。
    setBinaryStream():此方法用于提供较大的二进制值。
import java.sql.*;
import java.io.*;
import java.util.*;

public class StreamingData {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "123456";

   public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement pstmt = null;
   Statement stmt = null;
   ResultSet rs = null;
   try{
      // Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      // Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //Create a Statement object and build table
      stmt = conn.createStatement();
      createXMLTable(stmt);

      //Open a FileInputStream
      File f = new File("xml_data.xml");
      long fileLength = f.length();
      FileInputStream fis = new FileInputStream(f);

      //Create PreparedStatement and stream data
      String SQL = "INSERT INTO XML_Data VALUES (?,?)";
      pstmt = conn.prepareStatement(SQL);
      pstmt.setInt(1,125);
      pstmt.setAsciiStream(2,fis,(int)fileLength);
      pstmt.execute();

      //Close input stream
      fis.close();

      // Do a query to get the row
      SQL = "SELECT Data FROM XML_Data WHERE id=125";
      rs = stmt.executeQuery (SQL);
      // Get the first row
      if (rs.next ()){
         //Retrieve data from input stream
         InputStream xmlInputStream = rs.getAsciiStream (1);
         int c;
         ByteArrayOutputStream bos = new ByteArrayOutputStream();
         while (( c = xmlInputStream.read ()) != -1)
            bos.write(c);
         //Print results
         System.out.println(bos.toString());
      }
      // Clean-up environment
      rs.close();
      stmt.close();
      pstmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(pstmt!=null)
            pstmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main

public static void createXMLTable(Statement stmt) 
   throws SQLException{
   System.out.println("Creating XML_Data table..." );
   //Create SQL Statement
   String streamingDataSql = "CREATE TABLE XML_Data " +
                             "(id INTEGER, Data LONG)";
   //Drop table first if it exists.
   try{
      stmt.executeUpdate("DROP TABLE XML_Data");
   }catch(SQLException se){
   }// do nothing
   //Build table.
   stmt.executeUpdate(streamingDataSql);
}//end createXMLTable
}//end JDBCExample
posted on 2017-07-10 22:33  长嘴大耳怪  阅读(240)  评论(0编辑  收藏  举报