Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 NUTZ DAO 中关于使用Sql批量操作仅执行了一次的问题
发布于 2555天前 作者 thineagle 2631 次浏览 复制 上一个帖子 下一个帖子
标签: 无

项目中使用nutzdaoSql的addbatch进行批量操作， 代码如下

                StringBuffer sqlBf = new StringBuffer();
		sqlBf.append(" insert into dn_test_2 ");
		sqlBf.append("   (colunm_a, colunm_b) ");
		sqlBf.append("   select oo.colunm_a, @pageNum ");
		sqlBf.append("     from (select co.colunm_a, rownum rn ");
		sqlBf.append("             from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co ");
		sqlBf.append("            where rownum <= @endNo) oo ");
		sqlBf.append("    where rn >= @startNo ");
		
		Sql sql = Sqls.create(sqlBf.toString());
		sql.params().set("pageNum",1).set("startNo",1).set("endNo",1);
		sql.addBatch();
		sql.params().set("pageNum",2).set("startNo",2).set("endNo",2);
		sql.addBatch();
		sql.params().set("pageNum",3).set("startNo",3).set("endNo",3);
		sql.addBatch();
 
		NutDao baseDao = new   NutDao(getDataSource());
		baseDao.execute(sql);

最终结果是只执行了一条插入语句，语句如下

insert into dn_test_2    (colunm_a, colunm_b)    select oo.colunm_a, 1      from (select co.colunm_a, rownum rn              from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co             where rownum <= 1) oo     where rn >= 1

请问下这是什么问题呢？

19 回复

日志贴一下

日志如下

[DAS.SYS][][2017-11-17 10:48:15,474][DEBUG][:]Using 95 castor for Castors(org.nutz.castor.Castors:115)
[DAS.SYS][][2017-11-17 10:48:15,561][INFO ][:]Init file-pool by: C:\Users\Administrator/.nutz/tmp/dao/ [200000](org.nutz.filepool.NutFilePool:23)
[DAS.SYS][][2017-11-17 10:48:15,562][DEBUG][:]file-pool.home: 'C:\Users\Administrator\.nutz\tmp\dao'(org.nutz.filepool.NutFilePool:37)
[DAS.SYS][][2017-11-17 10:48:15,563][INFO ][:]file-pool.cursor: 0(org.nutz.filepool.NutFilePool:66)
[DAS.SYS][][2017-11-17 10:48:15,691][DEBUG][:]Jdbcs init complete(org.nutz.dao.jdbc.Jdbcs:90)
[DAS.SYS][][2017-11-17 10:48:15,692][INFO ][:]Get Connection from DataSource for JdbcExpert, if you lock at here, check your database server and configure(org.nutz.dao.jdbc.Jdbcs:103)
[DAS.SYS][][2017-11-17 10:48:16,640][DEBUG][:]select expert : org.nutz.dao.impl.jdbc.oracle.OracleJdbcExpert(org.nutz.dao.impl.DaoSupport:189)
[DAS.SYS][][2017-11-17 10:48:16,645][DEBUG][:]JDBC Driver --> 11.2.0.2.0(org.nutz.dao.impl.DaoSupport:200)
[DAS.SYS][][2017-11-17 10:48:16,646][DEBUG][:]JDBC Name   --> Oracle JDBC driver(org.nutz.dao.impl.DaoSupport:201)
[DAS.SYS][][2017-11-17 10:48:16,646][DEBUG][:]JDBC URL    --> jdbc:oracle:thin:@192.168.10.205:1521:develop(org.nutz.dao.impl.DaoSupport:203)
[DAS.SYS][][2017-11-17 10:48:16,646][DEBUG][:]Database info --> ORACLE:[Oracle - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options](org.nutz.dao.impl.DaoSupport:222)
[DAS.SYS][][2017-11-17 10:48:16,712][DEBUG][:]delete from dn_test_1(org.nutz.dao.impl.sql.run.NutDaoExecutor:388)
[DAS.SYS][][2017-11-17 10:48:16,777][DEBUG][:]delete from dn_test_2(org.nutz.dao.impl.sql.run.NutDaoExecutor:388)
[DAS.SYS][][2017-11-17 10:48:16,781][DEBUG][:]insert into dn_test_1 (COLUNM_A) values ('code_1')(org.nutz.dao.impl.sql.run.NutDaoExecutor:388)
[DAS.SYS][][2017-11-17 10:48:16,783][DEBUG][:]insert into dn_test_1 (COLUNM_A) values ('code_2')(org.nutz.dao.impl.sql.run.NutDaoExecutor:388)
[DAS.SYS][][2017-11-17 10:48:16,787][DEBUG][:]insert into dn_test_1 (COLUNM_A) values ('code_3')(org.nutz.dao.impl.sql.run.NutDaoExecutor:388)
[DAS.SYS][][2017-11-17 10:48:16,793][INFO ][:]Get Connection from DataSource for JdbcExpert, if you lock at here, check your database server and configure(org.nutz.dao.jdbc.Jdbcs:103)
[DAS.SYS][][2017-11-17 10:48:16,913][DEBUG][:]select expert : org.nutz.dao.impl.jdbc.oracle.OracleJdbcExpert(org.nutz.dao.impl.DaoSupport:189)
[DAS.SYS][][2017-11-17 10:48:16,914][DEBUG][:]JDBC Driver --> 11.2.0.2.0(org.nutz.dao.impl.DaoSupport:200)
[DAS.SYS][][2017-11-17 10:48:16,914][DEBUG][:]JDBC Name   --> Oracle JDBC driver(org.nutz.dao.impl.DaoSupport:201)
[DAS.SYS][][2017-11-17 10:48:16,914][DEBUG][:]JDBC URL    --> jdbc:oracle:thin:@192.168.10.205:1521:develop(org.nutz.dao.impl.DaoSupport:203)
[DAS.SYS][][2017-11-17 10:48:16,914][DEBUG][:]Database info --> ORACLE:[Oracle - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options](org.nutz.dao.impl.DaoSupport:222)
[DAS.SYS][][2017-11-17 10:48:16,915][DEBUG][:]insert into dn_test_2    (colunm_a, colunm_b)    select oo.colunm_a, ?      from (select co.colunm_a, rownum rn              from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co             where rownum <= ?) oo     where rn >= ?
    | 1 | 2 | 3 |
    |---|---|---|
    | 1 | 1 | 1 |
    | 2 | 2 | 2 |
    | 3 | 3 | 3 |
  For example:> "insert into dn_test_2    (colunm_a, colunm_b)    select oo.colunm_a, 1      from (select co.colunm_a, rownum rn              from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co             where rownum <= 1) oo     where rn >= 1"(org.nutz.dao.impl.sql.run.NutDaoExecutor:388)

那就没问题了，参数矩阵是3条，示例sql总是打印一条的

但是表中插入的数据只有第一条， 后面2条的数据没有插入。

你看错数据库了吧

而且, 你这是带分页的, select不到数据,就不会插入了

select 是肯定能查到数据的
建表如下

drop table dn_test_1;
drop table dn_test_2;

create table dn_test_1(
       colunm_a varchar(8)
);

create table dn_test_2(
       colunm_a varchar(8),
       colunm_b varchar(8)
);

表数据如下

		dao.dbInsert("insert into dn_test_1 (COLUNM_A) values ('code_1')");
		dao.dbInsert("insert into dn_test_1 (COLUNM_A) values ('code_2')");
		dao.dbInsert("insert into dn_test_1 (COLUNM_A) values ('code_3')");

麻烦试试看啊，看看结果

分别执行了以下语句

insert into dn_test_2   (colunm_a, colunm_b)   select oo.colunm_a, 1    from (select co.colunm_a, rownum rn   from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co     where rownum <= 1) oo   where rn >= 1

insert into dn_test_2   (colunm_a, colunm_b)   select oo.colunm_a, 2    from (select co.colunm_a, rownum rn   from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co     where rownum <= 2) oo   where rn >= 2

insert into dn_test_2   (colunm_a, colunm_b)   select oo.colunm_a, 3    from (select co.colunm_a, rownum rn   from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co     where rownum <= 3) oo   where rn >= 3

表中有三条记录， 不知道为啥sql.addbatch就不行

我试试看. 另外, 你把SQL改一下, 看看会出什么结果

                StringBuffer sqlBf = new StringBuffer();
		sqlBf.append(" insert into dn_test_2 ");
		sqlBf.append("   (colunm_a, colunm_b) ");
		sqlBf.append("   values (@pageNum, @startNo)");

就是不用select, 改成单纯的values插入, 验证一下你的环境下普通sql的batch是否有问题

试验了 ， 普通的没有问题，能插入。

嗯，看来是oracle的问题了

会考虑调整吗，还是说我必须换一种写法，有什么建议没

oracle的问题, 只能拆成一条条insert

没太明白， oracle哪里的问题呢？ 我把我的代码改了下，赋值参数的时候改成

                Sql sql = Sqls.create(sqlBf.toString());
		sql.params().set("pageNum",3).set("startNo",3).set("endNo",3);
		sql.addBatch();
		sql.params().set("pageNum",2).set("startNo",2).set("endNo",2);
		sql.addBatch();
		sql.params().set("pageNum",1).set("startNo",1).set("endNo",1);
		sql.addBatch();

意思就是按pagenum的倒序处理， 就成功了 这是什么原理呢？

不知道呢

ok， 那我们也在查查，如果有结果麻烦通知下， 谢谢.

使用原生jdbc试了下，一样的问题

                        ds = getDataSource();
			connection = ds.getConnection();
			connection.setAutoCommit(false);
			
			StringBuffer sqlBf = new StringBuffer();
			sqlBf.append(" insert into dn_test_2 ");
			sqlBf.append("   (colunm_a, colunm_b) ");
			sqlBf.append("   select oo.colunm_a, ? ");
			sqlBf.append("     from (select co.colunm_a, rownum rn ");
			sqlBf.append("             from (select it.colunm_a from dn_test_1 it order by it.colunm_a) co ");
			sqlBf.append("            where rownum <= ?) oo ");
			sqlBf.append("    where rn >= ? ");
			
			PreparedStatement sqlStmt = connection.prepareStatement(sqlBf.toString());
			
			sqlStmt.setInt(1, 1);
			sqlStmt.setInt(2, 1);
			sqlStmt.setInt(3, 1);
			sqlStmt.addBatch();
			
			sqlStmt.setInt(1, 2);
			sqlStmt.setInt(2, 2);
			sqlStmt.setInt(3, 2);
			sqlStmt.addBatch();
			
			sqlStmt.setInt(1, 3);
			sqlStmt.setInt(2, 3);
			sqlStmt.setInt(3, 3);
			sqlStmt.addBatch();
			
			sqlStmt.executeBatch();
			
			connection.commit();

找个dba问问？

嗯 好

添加回复
请先登陆
回到顶部