2021-06-19

解决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语句前后应该有空格，不然它会连在一起，然后就报错。
解决如下：
在箭头后面加个空格即可解决。