接着上篇文章继续、spring boot中使用spring JdbcTemplate(一)——连接池创建多个连接
现在我们看一下如何使用spring的JDBCTemplate进行增删改查操作。
1、增加:
public int insert(JdbcTemplate jdbcTemplate) {
String sql = "insert into student(id,name,age,address) values(?,?,?,?)";
int row = jdbcTemplate.update(sql, 2, "xiaohong", 13, "bbb");
return row;
}
2、修改:
public int update(JdbcTemplate jdbcTemplate) {
String sql = "update student set name=? where id= ?";
int row = jdbcTemplate.update(sql, "xiaoxiao", 1);
return row;
}
3、删除
public int delete(JdbcTemplate jdbcTemplate) {
String sql = "delete from student where id=?";
int row = jdbcTemplate.update(sql, 2);
return row;
}
4、查询
查询——查询不带参数返回List<Map<String,Object>
public List<Map<String, Object>> query(JdbcTemplate jdbcTemplate) {
String sql = "select * from student";
return jdbcTemplate.queryForList(sql);
}
查询——
查询带参数返回List<Map<String,Object>>
public List<Map<String, Object>> queryWithObject(JdbcTemplate jdbcTemplate) {
String sql = "select * from student where id=?";
return jdbcTemplate.queryForList(sql, 1);
}
查询——返回自定义类型,
List<Student>
public List<Student> queryAndReturnObject(JdbcTemplate jdbcTemplate) {
String sql = "select * from student";
List<Student> students = jdbcTemplate.query(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setAddress(resultSet.getString("address"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
return student;
}
});
return students;
}
查询——返回自定义类型,List<StudentNew>,在实体类中做rowMapper
public class StudentNew implements RowMapper<StudentNew>, Serializable {
private int id;
private String name;
private int age;
private String address;
public StudentNew() {
}
public StudentNew(int id, String name, int age, String address) {
this.id = id;
this.name = name;
this.age = age;
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public StudentNew mapRow(ResultSet resultSet, int i) throws SQLException {
StudentNew studentNew = new StudentNew();
studentNew.setId(resultSet.getInt("id"));
studentNew.setAge(resultSet.getInt("age"));
studentNew.setName(resultSet.getString("name"));
studentNew.setAddress(resultSet.getString("address"));
return studentNew;
}
}
public List<StudentNew> queryAndReturnObject2(JdbcTemplate jdbcTemplate) {
String sql = "select * from student";
List<StudentNew> studentNews = jdbcTemplate.query(sql, new StudentNew());
return studentNews;
}
查询——
带条件查询返回List<StudentNew>
public List<StudentNew> queryAndReturnObject3(JdbcTemplate jdbcTemplate) {
String sql = "select * from student where id =?";
List<StudentNew> studentNews = jdbcTemplate.query(sql, new StudentNew(), 1);
return studentNews;
}
综上,就是在spring JdbcTemplate中如何进行增删改查。