java具名参数_Spring 具名参数NamedParameterJdbcTemplate

importjava.util.HashMap;importjava.util.Map;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.jdbc.core.BeanPropertyRowMapper;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.jdbc.core.RowMapper;importorg.springframework.jdbc.core.namedparam.EmptySqlParameterSource;importorg.springframework.jdbc.core.namedparam.MapSqlParameterSource;importorg.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;importorg.springframework.jdbc.core.namedparam.SqlParameterSource;importorg.springframework.stereotype.Repository;importcom.myth.springJDBC.exception.AddFailedException;importcom.myth.springJDBC.po.Employee;

@Repositorypublic classEmployeeDao {

@AutowiredprivateJdbcTemplate jdbcTemplate;

@AutowiredprivateNamedParameterJdbcTemplate namedParameterJdbcTemplate;privateString sql;

//这个使用的 是 传统的 JdbcTemplatepublic Employee getEmployee(intid) {

sql= "Select * from employees where id = ?";

RowMapper rowMapper = new BeanPropertyRowMapper(Employee.class);

Employee employee=jdbcTemplate.queryForObject(sql, rowMapper,id);returnemployee;

}//引入一个 空 的Map EmptySqlParameterSource.INSTANCE

publicInteger getCount() {

sql= "select count(*) from employees";int result = namedParameterJdbcTemplate.queryForObject(sql, EmptySqlParameterSource.INSTANCE, Integer.class);returnresult;

}

/*这里也可以使用BeanPropertySqlParameterSource

* 这个意思就是把Map 转换为对象 来对待

*/public voidinsertEmployee(Employee employee) {

sql= "INSERT INTO employees values (:ID,:ln,:email,:departID)";

Map paramMap = new HashMap<>();

paramMap.put("ID", employee.getId());

paramMap.put("ln", employee.getLast_name());

paramMap.put("email", employee.getEmail());

paramMap.put("departID", employee.getDept_id());try{

namedParameterJdbcTemplate.update(sql, paramMap);

System.out.println("添加成功");

}catch(Exception e) {throw new AddFailedException("添加失败");

}

}

//要注意具名参数要和Bean中的属性值要一致。

public void updateEmployee(Employee employee) {

sql = "UPDATE employees set LAST_NAME =:last_name,EMAIL=:email,DEPT_ID =:dept_id WHERE ID = :id";

SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);

try {

namedParameterJdbcTemplate.update(sql, paramSource);

System.out.println("修改成功");

} catch (Exception e) {

System.out.println(e.toString());

throw new AddFailedException("修改失败");

}

}/*这样子 会报错

* public void deleteEmployee(int id) {

sql = "DELETE FROM EMPLOYEES WHERE ID = :ID";

Map paramMap = new HashMap<>();

paramMap.put("ID", id);

try {

namedParameterJdbcTemplate.update(sql, paramMap);

System.out.println("删除成功");

} catch (Exception e) {

throw new AddFailedException("删除失败");

}

}*/

//必须传入Employee 只传入int id 会报错

public voiddeleteEmployee(Employee employee) {

sql= "DELETE FROM EMPLOYEES WHERE ID = :ID";

Map paramMap = new HashMap<>();

paramMap.put("ID", employee.getId());try{

namedParameterJdbcTemplate.update(sql, paramMap);

System.out.println("删除成功");

}catch(Exception e) {throw new AddFailedException("删除失败");

}

}

}