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

很明显这是一个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 List<Employee> findAll(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()+" \"";
		}
		List<Employee> query = employeeDao.query(sql, Employee.class,params);
		return query;
	}

在SQLyog跑一下sql 果然错了

错误原因

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

修改后可以正确的查询了

同样修改Eclipse中的代码

正确代码

EmployeeDao  employeeDao = new EmployeeDao();
	@Override
	public List<Employee> findAll(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()+" \"";
		}
		List<Employee> query = employeeDao.query(sql+limit, Employee.class,params);
		return query;
	}

单元测试代码

EmployeeServiceImpl employeeServiceImpl=new EmployeeServiceImpl();
	@Test
	public void findAll() {
		 List<Employee> findAll = employeeServiceImpl.findAll(0,10,"八戒",null); 
		 
		 Assert.assertTrue(findAll != null);
		 for (Employee employee : findAll) {
			System.out.println(employee);
		}
	}