JDBC 批处理

最新推荐文章于 2024-10-05 15:32:05 发布

qjbagu

最新推荐文章于 2024-10-05 15:32:05 发布

阅读量112

点赞数

分类专栏： JDBC 文章标签： JDBC SQL Oracle 数据结构框架

JDBC 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

code]person表如下：


CREATE TABLE person(pid NUMBER PRIMARY KEY NOT NULL,
                    name VARCHAR2(10),
                    birthday DATE,
                    money NUMBER(7,2));

代码如下：


package jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchTest {
 /**
  * @param args
  * @throws SQLException
  */
 public static void main(String[] args) throws SQLException {
  // TODO 自动生成方法存根
  createBatch();
 }
 static void create(int i) throws SQLException {
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
   // 2.建立连接
   conn = JdbcUtils.getConnection();
   // conn = JdbcUtilsSing.getInstance().getConnection();
   // 3.创建语句
   String sql = "insert into person(pid,name,birthday, money) values (?,?,?,?) ";
   ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
   ps.setInt(1, 1007 + i);
   ps.setString(2, "name" + i);
   ps.setDate(3, new Date(System.currentTimeMillis()));
   ps.setFloat(4, 1000 + i);
   ps.executeUpdate();
   ps.executeUpdate();
  } finally {
   JdbcUtils.free(rs, ps, conn);
  }
 }
 static void createBatch() throws SQLException {
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
   // 2.建立连接
   conn = JdbcUtils.getConnection();
   // conn = JdbcUtilsSing.getInstance().getConnection();
   // 3.创建语句
   String sql = "insert into person(pid,name,birthday, money) values (?,?,?,?) ";
   ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
   for (int i = 0; i < 100; i++) {
    ps.setInt(1, 1007 + i);
    ps.setString(2, "name" + i);
    ps.setDate(3, new Date(System.currentTimeMillis()));
    ps.setFloat(4, 1000 + i);
    ps.addBatch();
   }
  ps.executeBatch();
  } finally {
   JdbcUtils.free(rs, ps, conn);
  }
 }
}

错误信息：


java.sql.SQLException: 违反协议
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
 at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:150)
 at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:464)
 at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:1175)
 at jdbc.JdbcUtils.free(JdbcUtils.java:46)
 at jdbc.BatchTest.createBatch(BatchTest.java:64)
 at jdbc.BatchTest.main(BatchTest.java:18)
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException
 at java.lang.System.arraycopy(Native Method)
 at oracle.jdbc.driver.T4CRowidAccessor.unmarshalOneRow(T4CRowidAccessor.java:202)
 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:610)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
 at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10580)
 at jdbc.BatchTest.createBatch(BatchTest.java:62)
 at jdbc.BatchTest.main(BatchTest.java:18)

为什么会违反协议？问题好像在 ps.executeBatch(); 这句上，
还有就是上面的程序会执行且给数据库存入数据，但搞不清楚为什么会违反协议，什么协议啊？
第二个代码：


package jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchTest {
 /**
  * @param args
  * @throws SQLException
  */
 public static void main(String[] args) throws SQLException {
  long start = System.currentTimeMillis();
  for (int i = 0; i < 100; i++)
   create(i);
  long end = System.currentTimeMillis();
  System.out.println("create:" + (end - start));
  start = System.currentTimeMillis();
  createBatch();
  end = System.currentTimeMillis();
  System.out.println("createBatch:" + (end - start));
 }
 static void create(int i) throws SQLException {
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
   conn = JdbcUtils.getConnection();
   String sql = "insert into person(pid,name,birthday, money) values (?,?, ?, ?) ";
   ps = conn.prepareStatement(sql);
   ps.setInt(1, 2000+i);
   ps.setString(2, "name" + i);
   ps.setDate(3, new Date(System.currentTimeMillis()));
   ps.setFloat(4, 100f + i);
   ps.executeUpdate();
  } finally {
   JdbcUtils.free(rs, ps, conn);
  }
 }
 static void createBatch() throws SQLException {
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
   conn = JdbcUtils.getConnection();
   String sql = "insert into person(pid,name,birthday, money) values (?,?, ?, ?) ";
   ps = conn.prepareStatement(sql);
   for (int i = 0; i < 100; i++) {
    ps.setInt(1, 3000+i);
    ps.setString(2, "person" + i);
    ps.setDate(3, new Date(System.currentTimeMillis()));
    ps.setFloat(4, 100f + i);
    ps.addBatch();
   }
   int[] is = ps.executeBatch();
  } finally {
   JdbcUtils.free(rs, ps, conn);
  }
 }
}

错误信息如下：


create:5500
Exception in thread "main" java.sql.BatchUpdateException: ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005144)
 at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
 at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10656)
 at jdbc.BatchTest.createBatch(BatchTest.java:64)
 at jdbc.BatchTest.main(BatchTest.java:24)

这又是怎么回事啊，也搞不明白啊。主键是pid,谢谢。