我再项目开发过程中遇到需要对一张主表和一张明细表同时插入大数量,目前我采取的方式是直接利用statement将拼装sql语句,然后向数据库每1000条数据提交一次。基本类似如下的格式:
begin
insert into zb values (zb_seq.nextval,'aa');
insert into mxb values (mxb_seq.nextval, zb_seq.currval, 'bb');
insert into mxb values (mxb_seq.nextval, zb_seq.currval, 'bb');
insert into mxb values (mxb_seq.nextval, zb_seq.currval, 'bb');
insert into zb values (zb_seq.nextval,'cc');
insert into mxb values (mxb_seq.nextval, zb_seq.currval, 'dd');
insert into mxb values (mxb_seq.nextval, zb_seq.currval, 'dd');
insert into mxb values (mxb_seq.nextval, zb_seq.currval, 'dd');
end;
现在我看过关于使用PreparedStatement加executeBatch方法可以更快,主要想提高预编译sql文件的次数,从而达到提高效率。根据用去的需求,批量插入的记录越多越好,比如插入10W笔数据,那么主表需要插入10W,明细表至少是10W。目前想改进之后的数据库插入方式为:
insert into zb values (zb_seq.nextval,?);
insert into mxb values (mxb_seq.nextval, zb_seq.currval, ?);
想请大家帮我看看,解答几个问题:
1.PreparedStatement里面是否可以放多个sql?
2.我主表插入一条记录,明细表需要插入主表刚才插入的guid,该种模式是否适用?该怎么写?
3.尾部的错误到底该如何解决?
下面贴出的我的测试java类
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class testBatch {
public void execBatchSql(){
Connection conn = getConnection();
try{
conn.setAutoCommit(false);
long beginTime = System.currentTimeMillis();
PreparedStatement pst = null;
for (int i=0;i<10000;i++){
pst = conn.prepareStatement("insert into zb values (zb_seq.nextval,?)");
pst.setString(1, "testzb"+i);
pst.addBatch();
pst = conn.prepareStatement("insert into mxb values (mxb_seq.nextval, zb_seq.currval, ?)");
for(int j=7;j<10;j++){
pst.setString(1, "testmxb"+i+j);
pst.addBatch();
}
if(i%1000 == 0){
pst.executeBatch();
conn.commit();
pst.clearBatch();
}
}
pst.executeBatch();
long endTime = System.currentTimeMillis();
System.out.println("用时:"+(endTime-beginTime)+"毫秒");
pst.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
public Connection getConnection(){
Connection conn = null;
try{
String driverClassName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@10.40.6.68:1521:jszx";
String user = "fszg";
String password = "fszg";
Class cc = Class.forName(driverClassName);
Driver driver = (Driver)cc.newInstance();
DriverManager.registerDriver(driver);
conn = DriverManager.getConnection(url,user,password);
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public static void main(String[] agrs){
testBatch t = new testBatch();
t.execBatchSql();
}
}
下面是我的建表语法,是oracle9i的数据库。
create table zb
(
guid number(20),
name varchar2(20)
);
create table mxb
(
guid number(20),
f_guid number(20),
name varchar2(20)
);
-- Create sequence
create sequence zb_seq
minvalue 3304000000000000001
maxvalue 9999999999999999999
start with 3304000000000000001
increment by 1
cache 20;
-- Create sequence
create sequence mxb_seq
minvalue 3306000000000000001
maxvalue 9999999999999999999
start with 3306000000000000001
increment by 1
cache 20;
但是我现在调试的时候却有问题,提示:
java.sql.BatchUpdateException: ORA-08002: sequence ZB_SEQ.CURRVAL is not yet defined in this session
at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
at cn.com.gsoft.fszg.testBatch.execBatchSql(testBatch.java:26)
at cn.com.gsoft.fszg.testBatch.main(testBatch.java:63)
分享到:
相关推荐
JDBC(Java Data Base Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
主要介绍了Java使用Jdbc连接Oracle执行简单查询操作,结合实例形式详细分析了java基于jdbc实现Oracle数据库的连接与查询相关操作技巧,需要的朋友可以参考下
java jdbc oracle代码
java中的JDBC纯驱动方式连接Oracle数据库.docx
java中jdbc连接oracle代码及jar包
oracle中date类型的操作,增删改查oracle中date类型的操作,增删改查
Java开发连接Oracle的JDBC驱动包
This book covers a lot of material about Oracle's implementation of JDBC. It provides both the beginner and the advanced Oracle or Java user with all the information needed to be successful. ...
Java连接数据库JDBCoracle
jdbcoracle(多国语言支持) jdbcoracle(多国语言支持)
Flink JDBC Connector 支持Oracle , Flink 1.13.6 支持Oracle 11.2.0.4
JAVA用jdbc联接ORACLE的例子。
java中xml文件的处理及oracle中xmltype的插入和读取.pdf
java jdbc连接oracle的小例子 ,含有数据库文件,导入直接用
java使用JDBC和ODBC连接oracle数据库,虽然ODBC连接数据库不常用,但是大家还是可以了解一下
java编程中连接Oracle的JDBC的jar包
针对oracle中blob字段的操作,能批量快速的插入大字段,效率非常高
NULL 博文链接:https://abstractforever.iteye.com/blog/606827
java利用jdbc连接数据库之插入数据.docx