Java中使用Mybatis批量插入数据时Mapper.xml中的sql如下：

<insert id="batchSave">

into t_emp(emp_name,emp_email,dept_id) VALUES

<foreach collection="list" item="emp" separator=",">

(#{emp.empName}, #{emp.empEmail}, #{emp.deptId})

</foreach>

</insert>

DAO层方法:

/**

*

* @Title: batchSave

* @Description: 批量保存

* @param List

* @throws Exception

*/

public void batchSave(List<Emp> empList);

修改MyBatis测试类，增加测试方法testBatchSave

@Test

public void testBatchSave() throws IOException {

SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

SqlSession openSession = sqlSessionFactory.openSession();

try {

EmpMapper mapper = openSession.getMapper(EmpMapper.class);

List<Emp> empList = new ArrayList<Emp>();

empList.add(new Emp(null,"Lucy","Lucy@sina.com",1));

empList.add(new Emp(null,"Lily","Lily@sina.com",2));

empList.add(new Emp(null,"Lizy","Lizy@sina.com",3));

//一定要先检查empList不为空再插入

if(empList!=null){

mapper.batchSave(empList);

}

//别忘了事务最后要提交

openSession.commit();

} finally {

openSession.close();

}

}

出现错误如下：

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1......

解决办法：

1.检查SQL语句是不是出现SQL固定的关键字(不论大小写，都不能出现)；

2.不确定的话将表名，字段名用`[ESC下面的按钮]引用起来；

3.检查Java代码中批量的插入的List是不是为空，一定要判断不为空再调用批量插入函数；

4.连接mysql的url后面加上&amp;allowMultiQueries=true;

5.使用mybtis的Batch类型的Executor使用是否正确；

SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);

下面列出MySQL所有关键字，希望给使用MySQL的朋友提供一些参考帮助。

ADD ALL ALTER

ANALYZE AND AS

ASC ASENSITIVE BEFORE

BETWEEN BIGINT BINARY

BLOB BOTH BY

CALL CASCADE CASE

CHANGE CHAR CHARACTER

CHECK COLLATE COLUMN

CONDITION CONNECTION CONSTRAINT

CONTINUE CONVERT CREATE

CROSS CURRENT_DATE CURRENT_TIME

CURRENT_TIMESTAMP CURRENT_USER CURSOR

DATABASE DATABASES DAY_HOUR

DAY_MICROSECOND DAY_MINUTE DAY_SECOND

DEC DECIMAL DECLARE

DEFAULT DELAYED DELETE

DESC DESCRIBE DETERMINISTIC

DISTINCT DISTINCTROW DIV

DOUBLE DROP DUAL

EACH ELSE ELSEIF

ENCLOSED ESCAPED EXISTS

EXIT EXPLAIN FALSE

FETCH FLOAT FLOAT4

FLOAT8 FOR FORCE

FOREIGN FROM FULLTEXT

GOTO GRANT GROUP

HAVING HIGH_PRIORITY HOUR_MICROSECOND

HOUR_MINUTE HOUR_SECOND IF

IGNORE IN INDEX

INFILE INNER INOUT

INSENSITIVE INSERT INT

INT1 INT2 INT3

INT4 INT8 INTEGER

INTERVAL INTO IS

ITERATE JOIN KEY

KEYS KILL LABEL

LEADING LEAVE LEFT

LIKE LIMIT LINEAR

LINES LOAD LOCALTIME

LOCALTIMESTAMP LOCK LONG

LONGBLOB LONGTEXT LOOP

LOW_PRIORITY MATCH MEDIUMBLOB

MEDIUMINT MEDIUMTEXT MIDDLEINT

MINUTE_MICROSECOND MINUTE_SECOND MOD

MODIFIES NATURAL NOT

NO_WRITE_TO_BINLOG NULL NUMERIC

ON OPTIMIZE OPTION

OPTIONALLY OR ORDER

OUT OUTER OUTFILE

PRECISION PRIMARY PROCEDURE

PURGE RAID0 RANGE

READ READS REAL

REFERENCES REGEXP RELEASE

RENAME REPEAT REPLACE

REQUIRE RESTRICT RETURN

REVOKE RIGHT RLIKE

SCHEMA SCHEMAS SECOND_MICROSECOND

SELECT SENSITIVE SEPARATOR

SET SHOW SMALLINT

SPATIAL SPECIFIC SQL

SQLEXCEPTION SQLSTATE SQLWARNING

SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT

SSL STARTING STRAIGHT_JOIN

TABLE TERMINATED THEN

TINYBLOB TINYINT TINYTEXT

TO TRAILING TRIGGER

TRUE UNDO UNION

UNIQUE UNLOCK UNSIGNED

UPDATE USAGE USE

USING UTC_DATE UTC_TIME

UTC_TIMESTAMP VALUES VARBINARY

VARCHAR VARCHARACTER VARYING

WHEN WHERE WHILE

WITH WRITE X509

XOR YEAR_MONTH ZEROFILL