解决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 ‘ename like ‘%A%’’ at line 1 Query: select * from EMPLOYEE where 1=1and ename like ? Parameters: [%A%]
Exception in thread “main” java.lang.NullPointerException
at com.lanqiao.empsys.dao.EmployeeDao.main(EmployeeDao.java:71)
我的源码是这样:
public class EmployeeDao {
//多线程安全
QueryRunner runner = new QueryRunner(true);//解决like查询的问题
/**
* 部门列表
* @return
*/
public List loadDeptno() {
List departments = new ArrayList<>();
//1.得到数据库连接
String sql = “select * from department”;
try(Connection conn = JdbcUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
) {
final ResultSet rs = ps.executeQuery();
while (rs.next()) {
departments.add(new Department(rs.getInt(“deptno”), rs.getString(“dname”), rs.getString(“loc”)));
}
} catch (SQLException e) {
System.out.println(“登录失败:”+e.getMessage());
}
return departments;
}
public List<Employee> query(Employee employee){
StringBuffer sql = new StringBuffer("select * from EMPLOYEE where 1=1");
List<Object> params = new ArrayList<>();
if (StringUtils.isNotBlank(employee.getEname())){
sql.append("and ename like ?");
params.add("%"+employee.getEname()+"%");
}
if (StringUtils.isNotBlank(employee.getJob())){
sql.append("and job = ?");
params.add(employee.getJob());
}
if (employee.getDeptno() !=null){
sql.append("and deptno = ?");
params.add(employee.getDeptno());
}
try {
try(Connection conn = JdbcUtil.getConnection()) {
//BeanListHandler是ResultSetHandler接口的实现类
//只要查询的结果集中的列名与JavaBean类的属性相同结果集处理器(ResultSetHandler)就会使用反射技术将列值赋予相应属性
return runner.query(conn,sql.toString(),new BeanListHandler<Employee>(Employee.class),params.toArray());
}
} catch (SQLException e) {
System.out.println("查询员工失败:"+e.getMessage());
}
return null;
}
public static void main(String[] args) {
List<Employee> employees = new EmployeeDao().query(new Employee("A",null,null));
for (Employee e : employees) {
System.out.println(e);
}
}
}
运行之后出现:
原因是sql语句前后应该有空格,不然它会连在一起,然后就报错。
解决如下:
在箭头后面加个空格即可解决。