mysql limit 语法错误_Mysql limit 关键字位置错误导致的SQL语法错误 java.sql.SQLException: You have an error in your SQL ...

很明显这是一个SQL语法错误

java.sql.SQLException: 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 'AND EmployeeName ='八戒'' at line 1 Query: SELECT EmployeeID,EmployeeName,EmployeeGender,position.PositionName,department.DepartmentName,CardNumber,EmployeeState FROM employee inner join position ON position.PositionID = employee.PositionID inner join department ON department.DepartmentID = employee.DepartmentID WHERE 1=1 LIMIT ?,? AND EmployeeName ='八戒' Parameters: [0, 10]

我使用的是MySQL5.7。

想利用字符串拼接，做条件查询的功能。

错误代码实例

EmployeeDao employeeDao = new EmployeeDao();

@Override

public ListfindAll(int offset,int pageSize,String employeeName,String deptId) {

String sql ="SELECT EmployeeID,EmployeeName,EmployeeGender,position.PositionName,department.DepartmentName,CardNumber,EmployeeState "

+ "FROM employee inner join position ON position.PositionID = employee.PositionID "

+ "inner join department ON department.DepartmentID = employee.DepartmentID "

+ "WHERE 1=1 LIMIT ?,? ";

Object[] params = {offset,pageSize};

if(employeeName!=null) {

sql = sql +"AND EmployeeName ='"+employeeName.trim()+"'";

}

if(deptId!=null) {

sql = sql +"AND DepartmentID = \" "+deptId.trim()+" \"";

}

Listquery = employeeDao.query(sql, Employee.class,params);

return query;

}

在SQLyog跑一下sql 果然错了

错误原因

LIMIT 0,10 的位置错了，应该放到最后

修改后可以正确的查询了

同样修改Eclipse中的代码

正确代码

EmployeeDao employeeDao = new EmployeeDao();

@Override

public ListfindAll(int offset,int pageSize,String employeeName,String deptId) {

String sql ="SELECT EmployeeID,EmployeeName,EmployeeGender,position.PositionName,department.DepartmentName,CardNumber,EmployeeState "

+ "FROM employee inner join position ON position.PositionID = employee.PositionID "

+ "inner join department ON department.DepartmentID = employee.DepartmentID "

+ "WHERE 1=1 ";

String limit = "LIMIT ?,?";

Object[] params = {offset,pageSize};

if(employeeName!=null) {

sql = sql +"AND EmployeeName ='"+employeeName.trim()+"'";

}

if(deptId!=null) {

sql = sql +"AND DepartmentID = \" "+deptId.trim()+" \"";

}

Listquery = employeeDao.query(sql+limit, Employee.class,params);

return query;

}

单元测试代码

EmployeeServiceImpl employeeServiceImpl=new EmployeeServiceImpl();

@Test

public void findAll() {

ListfindAll = employeeServiceImpl.findAll(0,10,"八戒",null);

Assert.assertTrue(findAll != null);

for (Employee employee : findAll) {

System.out.println(employee);

}

}