MySql 和 Oracle 批量插入

1、MySql方式 批量插入

INSERT INTO tbl (id, name, age)
VALUES
	(1, "zhangsan", 12),
	(2, "lisi", 24),
	(3, "wangwu", 30);

2、Oracle方式 批量插入

（1）方式一

INSERT INTO tbl (id, name, age)
SELECT id, name, age
FROM
(
	( SELECT 1 AS id, 'zhangsan' AS name, 12 AS age FROM dual )
	UNION
	( SELECT 2 AS id, 'lisi' AS name, 24 AS age FROM dual )
	UNION
	( SELECT 3 AS id, 'wangwu' AS name, 30 AS age FROM dual )
)

（2）方式二

    注：有些版本不支持

INSERT ALL 
INTO tbl (id, name, age) VALUES (1, 'zhangsan', 12) 
INTO tbl (id, name, age) VALUES (2, ' lisi ', 24) 
INTO tbl (id, name, age) VALUES (3, ' wangwu ', 30)
SELECT 1 FROM dual;

3、其他

（1）Jdbc方式1

statement.addBatch(sql1);//sql是完整的insert into tbl () values ();

statement.addBatch(sql2);

statement.addBatch(sql3);

statement.executeBatch();

（2）Jdbc方法2

String sql = "INSERT INTO tbl(id, name, age) VALUES(?,?,?)";

List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(new Object[]{1, "zhangsan", 12});
batchArgs.add(new Object[]{2, "lisi", 24});
batchArgs.add(new Object[]{3, "wangwu", 30});

jdbcTemplate.batchUpdate(sql, batchArgs);

（3）Hql的批量插入