文章目录
一、什么是JDBCTemplate?
JDBCTemplate是spring框架对jdbc操作的封装,简化我们实际开发的操作。
二、传统JDBC操作有何弊端?
xscj表
create database dbtest;
use dbtest;
create table if not exists`student`(
`id` int not null auto_increment,
`cid` int not null,
`name` varchar(20) not null,
`gender` varchar(10) not null,
`score` int not null,
primary key(`id`)
);
insert into student(cid,name,gender,score) value(1,'王琳','female',80);
insert into student(cid,name,gender,score) value(1,'王杰','male',90);
insert into student(cid,name,gender,score) value(2,'林一帆','female',85);
insert into student(cid,name,gender,score) value(3,'张伟','male',99);
insert into student(cid,name,gender,score) value(3,'赵琳','female',96);
insert into student(cid,name,gender,score) value(2,'程明','male',59);
从数据库查询一条数据,并封装到对象中,需要这些操作,操作起来非常繁琐
@Test
public void demo1() throws SQLException {
Connection connection= JDBCUtils.getConnection();
Student s1=new Student();
String sql="select * from student where id=1";
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet resultSet = pstmt.executeQuery();
resultSet.next();
s1.setCid(resultSet.getInt("cid"));
s1.setGender(resultSet.getString("gender"));
s1.setId(resultSet.getInt("id"));
s1.setName(resultSet.getString("name"));
s1.setScore(resultSet.getInt("score"));
System.out.println(s1);
}
JDBCTemplate做的就是帮我们把这些封装的过程给省略掉,由它帮助我们去完成,这样能大大增加开发的效率(代码能少敲一点是一点)。
三、JDBCTemplate的使用
1. 导入jar包
spring-beans-5.0.0.RELEASE.jar
spring-core-5.0.0.RELEASE.jar
spring-jdbc-5.0.0.RELEASE.jar
spring-tx-5.0.0.RELEASE.jar
commons-logging-1.2.jar
2. 创建JdbcTemplate对象
- 需要数据源DataSource。
3. 调用JdbcTemplate完成CRUD操作
- update():执行DML语句。增、删、改语句
- queryForMap():查询结果将结果集封装为map集合,将列名作为key。但结果只能有一条数据。
- queryForList():查询结果将结果集封装为list集合。结果先封装成Map对象,再封装到List集合中。
- query():查询结果,将结果封装为JavaBean对象。
- 参数:RowMapper。
- 通常使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装。
new BeanPropertyRowMapper<T>(T.class)
- queryForObject:查询结果,将结果封装为对象。
一般用于聚合函数的查询
特别提一下,为了能让jdbctemplate自动封装查询的结果到对象中,对象中的变量名要与表中的字段名字相对应才行。Windows系统上可以忽略大小写,但是Linux系统上则不行。
四、实战练习
xscj数据表
create database dbtest;
use dbtest;
create table if not exists`student`(
`id` int not null auto_increment,
`cid` int not null,
`name` varchar(20) not null,
`gender` varchar(10) not null,
`score` int not null,
primary key(`id`)
);
insert into student(cid,name,gender,score) value(1,'王琳','female',80);
insert into student(cid,name,gender,score) value(1,'王杰','male',90);
insert into student(cid,name,gender,score) value(2,'林一帆','female',85);
insert into student(cid,name,gender,score) value(3,'张伟','male',99);
insert into student(cid,name,gender,score) value(3,'赵琳','female',96);
insert into student(cid,name,gender,score) value(2,'程明','male',59);
1.更新数据
代码一下子就变得非常简洁,不需要关闭等操作了。
@Test
public void testUpdate() {
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
String sql="update student set score=? where id=?";
int cnt = jdbcTemplate.update(sql, 80, 6);
System.out.println(cnt);
}
2.插入数据
@Test
public void testInsert(){
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
String sql="insert into student(cid,name,gender,score) value(?,?,?,?)";
int cnt = jdbcTemplate.update(sql, 1,"王玉民","male",99);
System.out.println(cnt);
}
3.删除数据
@Test
public void testDelete(){
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
String sql="delete from student where id=?";
int cnt = jdbcTemplate.update(sql, 7);
System.out.println(cnt);
}
4.查询数据
4.1按id查询,并封装到Map集合
@Test
public void testFindById(){
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
String sql="select * from student where id=?";
Map<String, Object> map = jdbcTemplate.queryForMap(sql, 1);
System.out.println(map);
}
4.2查询所有,并封装到List集合
@Test
public void testFindAll(){
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
String sql="select * from student";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
for(Map<String,Object> map:list){
System.out.println(map);
}
}
4.3查询所有,并封装为Student对象的List集合
注意,属性名一定要与数据包中的字段一样。
@Test
public void testFindAll1(){
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
String sql="select * from student";
List<Student> list = jdbcTemplate.query(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student s1 = new Student();
s1.setCid(resultSet.getInt("cid"));
s1.setGender(resultSet.getString("gender"));
s1.setId(resultSet.getInt("id"));
s1.setName(resultSet.getString("name"));
s1.setScore(resultSet.getInt("score"));
return s1;
}
});
for(Student student:list){
System.out.println(student);
}
}
@Test
public void testFindAll2() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
String sql = "select * from student";
List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
for (Student student : list) {
System.out.println(student);
}
}
4.4查询总记录数
@Test
public void testTotal() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
String sql = "select count(id) from student";
Long total = jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(total);
}
五、完整代码
Github项目链接,含完整jar包和项目代码:代码。欢迎star。
项目结构
Student.java
package com.jxj4869.domain;
public class Student {
private Integer id;
private Integer cid;
private String name;
private String gender;
private Integer score;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", cid=" + cid +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", score=" + score +
'}';
}
}
JdbcTemplateDemo.java
package com.jxj4869.domain;
public class Student {
private Integer id;
private Integer cid;
private String name;
private String gender;
private Integer score;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", cid=" + cid +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", score=" + score +
'}';
}
}
JDBCUtils.java
package com.jxj4869.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils {
private static DataSource ds;
static {
try {
//1.加载配置文件
Properties pro = new Properties();
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
//2.获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
public static void close(Statement stmt, Connection conn) {
close(null, stmt, conn);
}
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static DataSource getDataSource() {
return ds;
}
}
druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbtest?serverTimezone=UTC
username=root
password=root
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000