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

最新推荐文章于 2023-08-18 20:53:51 发布

兰特RR

最新推荐文章于 2023-08-18 20:53:51 发布

阅读量1.9k

点赞数 3

CC 4.0 BY-SA版权

文章标签： mysql limit分页

本文链接：https://blog.youkuaiyun.com/weixin_42209440/article/details/96476612

本文介绍了一个SQL语法错误的排查及修复过程，展示了如何通过调整SQL语句中LIMIT子句的位置来解决错误，并提供了完整的错误代码实例及修正后的代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很明显这是一个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);
		}
	}