Spring使用JdbcTemplate操作数据库
Spring对数据库的操作在jdbc上面做了深层次的封装,提供了JdbcTemplate或NamedParameterJdbcTemplate模板。
JdbcTemplate是Spring里最基本的JDBC模板,利用JDBC和简单的索引参数查询提供对数据库的简单访问。
NamedParameterJdbcTemplate能够在执行查询时把值绑定到SQL里的命名参数,而不是使用索引参数。
传统JDBC API与Spring JdbcTemplate比较:
//JDBC API
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("select count(*) from COUNT student")
if(resultSet.next()){
Integer count = resultSet.getInt("COUNT");
}
//JDBC Template
Integer count = jdbcTemplate.queryForObject("select count(*) from student",Integer.class);
注意:JdbcTemplate采用默认的自动提交。
JdbcTemplate快速入门
主要依赖项:
<!--mysql驱动包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<!--spring jdbc-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
辅助依赖:
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
配置数据源
1、在resources目录下新建application.properties
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
jdbc.username=root
jdbc.password=root
jdbc.driverClassName=com.mysql.jdbc.Driver
2、读取配置项
@Data
@Configuration
@PropertySource("classpath:application.properties")
public class AppPropertiesConfig {
@Value("${jdbc.url}")
private String jdbcUrl;
@Value("${jdbc.username}")
private String userName;
@Value("${jdbc.password}")
private String password;
@Value("${jdbc.driverClassName}")
private String driverClassName;
}
3、创建DataSource
Spring本身提供了一个简单的数据源实现类DriverManagerDataSource,它位于org.springframework.jdbc.datasource包中。这个类实现了javax.sql.DataSource接口,但它并没有提供池化连接的机制,每次调用getConnection()获取新连接时,只是简单地创建一个新的连接。因此,这个数据源类比较适合在单元测试或简单的独立应用中使用,因为它不需要额外的依赖类。
@Bean("springBuiltDataSource")
public DataSource springBuiltDataSource(AppPropertiesConfig propertiesConfig){
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(propertiesConfig.getDriverClassName());
ds.setUrl(propertiesConfig.getJdbcUrl());
ds.setUsername(propertiesConfig.getUserName());
ds.setPassword(propertiesConfig.getPassword());
return ds;
}
创建JdbcTemplate
@Bean("jdbcTemplateOne")
public JdbcTemplate jdbcTemplateOne(DataSource dataSource){
return new JdbcTemplate(dataSource);
}
使用JdbcTemplate
@Resource(name = "jdbcTemplateOne")
private JdbcTemplate jdbcTemplate;
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from tb_student");
System.out.println(JSON.toJSONString(list));
JdbcTemplate常用API
常用方法
1、 execute方法主要用于建表相关,平常使用较少。
String sql = "create table user_test (id bigint, name varchar(100))";
jdbcTemplate.execute(sql);
2、update方法支持单条数据的增删改
//直接拼接参数
jdbcTemplate.update("insert into user_test(id, name) values(1, 'harvey')");
//使用占位符
jdbcTemplate.update("insert into user_test(id, name) values (?, ?)", 2, "hello");
//指定参数数组和对应的JDBC类型
jdbcTemplate.update("insert into user_test(id, name) values (?, ?)", new Object[]{3, "world"}, new int[]{
Types.BIGINT, Types.VARCHAR
});
3、batchUpdate支持批量数据的增删改
//批量几条SQL
jdbcTemplate.batchUpdate("insert into user_test(id, name) values(10, 'harvey')",
"insert into user_test(id, name) values(11, 'helloWorld')");
//批量指定参数
List<Object[]> paramList = new ArrayList();
Object[] one = new Object[]{15, "harvey"};
paramList.add(one);
Object[] two = new Object[]{16, "world"};
paramList.add(two);
jdbcTemplate.batchUpdate("insert into user_test(id, name) values(?, ?)", paramList);
4、query方法,支持返回实体的映射转换
//映射查询结果,一般是定义实体进行映射
List<Map<String, Object>> list = jdbcTemplate.query("select * from user_test", new RowMapper<Map<String, Object>>() {
@Override
public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
Map<String, Object> rowMap = new HashMap();
rowMap.put("id", resultSet.getLong("id"));
rowMap.put("name", resultSet.getString("name"));
return rowMap;
}
});
System.out.println("list:"+JSON.toJSONString(list));
List<Map<String, Object>> list2 = jdbcTemplate.query("select * from user_test", new ResultSetExtractor<List<Map<String, Object>>>() {
@Override
public List<Map<String, Object>> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
List<Map<String, Object>> resultList = new ArrayList();
while (resultSet.next()){
Map<String, Object> rowMap = new HashMap();
rowMap.put("id", resultSet.getLong("id"));
rowMap.put("name", resultSet.getString("name"));
resultList.add(rowMap);
}
return resultList;
}
});
System.out.println("list2:" + JSON.toJSONString(list2));
Spring jdbc中目前有两个主要的RowMapper实现,使用它们应该能解决大部分的场景了:SingleColumnRowMapper和BeanPropertyRowMapper。
- SingleColumnRowMapper:返回单列数据
- BeanPropertyRowMapper:当查询数据库返回的是多列数据,且需要将这些多列数据映射到某个具体的实体类上
5、queryForMap方法,以Map类型返回查询结果
//直接指定返回类型
Map<String, Object> userMap = jdbcTemplate.queryForObject("select * from user_test where id=1", HashMap.class);
System.out.println("userMap:" + JSON.toJSONString(userMap));
//通过RowMapper映射成期望的类型
Map<String, Object> userMap2 = jdbcTemplate.queryForObject("select * from user_test where id=1",
new RowMapper<Map<String, Object>>() {
@Override
public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
Map<String,Object> rowInfo = new HashMap();
rowInfo.put("id", resultSet.getLong("id"));
rowInfo.put("name", resultSet.getString("name"));
return rowInfo;
}
});
System.out.println("userMap2:" + JSON.toJSONString(userMap2));
//查询返回某一个值:查询表中数据总数
int count = jdbcTemplate.queryForObject("select count(*) fromuser_test", Integer.class);
System.out.println("count:" + count);
6、queryForList方法,以List类型返回查询结果
//查询列表,默认是HashMap类型来接收
List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from user_test");
System.out.println("mapList:" + JSON.toJSONString(mapList));
//指定查询参数
List<Map<String, Object>> mapList2 = jdbcTemplate.queryForList("select * from user_test where id=?", 1);
System.out.println("mapList2:" + JSON.toJSONString(mapList2));
//查询列表,指定接收查询结果的类型,注意这里只能是查询一列数据
List<Long> idList = jdbcTemplate.queryForList("select id from user_test", Long.class);
System.out.println("idList:" + idList);
//查询列表,指定查询参数和接收类型,注意这里只能是查询一列数据
List<Long> idList2 = jdbcTemplate.queryForList("select id from user_test where name like concat('%', ?, '%')", Long.class, "har");
System.out.println("idList2:" + idList2);
queryForList的坑:不支持自定义bean,这个class只是支持Integer,String这些基本类型。
提供回调类
1、预编译语句及存储过程创建回调:用于根据JdbcTemplate提供的连接创建相应的语句
- PreparedStatementCreator:通过回调获取JdbcTemplate提供的Connection,由用户使用该Conncetion创建相关的PreparedStatement;
- CallableStatementCreator:通过回调获取JdbcTemplate提供的Connection,由用户使用该Conncetion创建相关的CallableStatement;
int count = jdbcTemplate.execute(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
return connection.prepareStatement("select count(*) from user_test");
}
}, new PreparedStatementCallback<Integer>() {
public Integer doInPreparedStatement(java.sql.PreparedStatement pst)
throws SQLException, DataAccessException {
pst.execute();
ResultSet rs = pst.getResultSet();
rs.next();
return rs.getInt(1);
}
});
System.out.println(count);
首先使用PreparedStatementCreator创建一个预编译语句,其次由JdbcTemplate通过PreparedStatementCallback回调传回,由用户决定如何执行该PreparedStatement。此处我们使用的是execute方法。以下是我们mysql中常用的插入时返回自增的主键:
String sql = "insert into tb_class(class_code, class_name, is_deleted) values(?,?, 0) ";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
// 设置返回的主键字段名
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "43102");
ps.setString(2, "开发四班");
return ps;
}
}, keyHolder);
// 获取到插入数据生成的ID,主键是自增的
System.out.println(keyHolder.getKey().longValue());
2、预编译语句设值回调:用于给预编译语句相应参数设值
- PreparedStatementSetter:通过回调获取JdbcTemplate提供的PreparedStatement,由用户来对相应的预编译语句相应参数设值;
- BatchPreparedStatementSetter:;类似于PreparedStatementSetter,但用于批处理,需要指定批处理大小;
String insertSql = "insert into user_test(id, name) values (?,?)";
int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
public void setValues(PreparedStatement pst) throws SQLException {
pst.setObject(1, "21");
pst.setObject(2, "world");
}
});
System.out.println("count:"+count);
List<Object[]> dataList = new ArrayList();
dataList.add(new Object[]{25, "张桑"});
dataList.add(new Object[]{26, "林冲"});
dataList.add(new Object[]{27, "宋江"});
String insertSql = "insert into user_test(id, name) values (?,?)";
int[] result = jdbcTemplate.batchUpdate(insertSql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
//批操作中的每个操作都使用相同的sql语句,而该接口则sql语句中的占位符设置具体的值。
Object[] obj = dataList.get(i);
preparedStatement.setObject(1, obj[0]);
preparedStatement.setObject(2, obj[1]);
}
@Override
public int getBatchSize() {
//用来返回批次的大小
return dataList.size();
}
});
//返回 [1, 1, 1]
System.out.println(Arrays.toString(result));
3、自定义功能回调:提供给用户一个扩展点,用户可以在指定类型的扩展点执行任何数量需要的操作
- ConnectionCallback:通过回调获取JdbcTemplate提供的Connection,用户可在该Connection执行任何数量的操作;
- StatementCallback:通过回调获取JdbcTemplate提供的Statement,用户可以在该Statement执行任何数量的操作;
- PreparedStatementCallback:通过回调获取JdbcTemplate提供的PreparedStatement,用户可以在该PreparedStatement执行任何数量的操作;
- CallableStatementCallback:通过回调获取JdbcTemplate提供的CallableStatement,用户可以在该CallableStatement执行任何数量的操作;
4、结果集处理回调:通过回调处理ResultSet或将ResultSet转换为需要的形式
- RowMapper:用于将结果集每行数据转换为需要的类型,用户需实现方法mapRow(ResultSet rs, int rowNum)来完成将每行数据转换为相应的类型。
- RowCallbackHandler:用于处理ResultSet的每一行结果,用户需实现方法processRow(ResultSet rs)来完成处理,在该回调方法中无需执行rs.next(),该操作由JdbcTemplate来执行,用户只需按行获取数据然后处理即可。
- ResultSetExtractor:用于结果集数据提取,用户需实现方法extractData(ResultSet rs)来处理结果集,用户必须处理整个结果集;
NamedParameterJdbcTemplate常用API
在经典的 JDBC 用法中, SQL 参数是用占位符 ? 表示,并且受到位置的限制。定位参数的问题在于, 一旦参数的顺序发生变化, 就必须改变参数绑定。
什么是具名参数
SQL 按名称(以冒号开头)而不是按位置进行指定. 具名参数更易于维护, 也提升了可读性。具名参数由框架类在运行时用占位符取代。
创建NamedParameterJdbcTemplate
@Bean("namedJdbcTemplateOne")
public NamedParameterJdbcTemplate namedJdbcTemplateOne(DataSource dataSource){
return new NamedParameterJdbcTemplate(dataSource);
}
NamedParameterJdbcTemplate示例
Map<String, Object> sqlParams = new HashMap();
sqlParams.put("name", "%har%");
List<Map<String, Object>> list = namedJdbcTemplateOne.queryForList("select * from user_test where name like :name", sqlParams);
System.out.println(JSON.toJSONString(list));
SQL分页查询
使用JdbcTemplate都是使用SQL进行查询的,如果需要分页,则需要对SQL进行修改。下面提供了几种常用数据库的分页SQL拼接的工具类及分页对象:
/**
* 分页对象
*/
public final class PageBean<T> implements Serializable {
//当前页码
private Integer currentPage = 1;
//每页显示的记录数
private Integer pageSize = 10;
//总记录数
private Integer totalSize = 0;
//本页的数据列表
private List<T> recordList;
//总页数
private Integer totalPageCount = 0;
//开始行号
private Integer startIndex = 0;
//页码列表的开始索引
private Integer beginPageIndex;
//页码列表的结束索引
private Integer endPageIndex;
public PageBean(int currentPage, int pageSize, int totalSize, List<T> recordList) {
this.currentPage = currentPage;
this.pageSize = pageSize;
this.totalSize = totalSize;
this.recordList = recordList;
//计算beginPageIndex 和 endPageIndex
//>>总页数不多于10页,则全部显示
if (totalPageCount <= 10) {
beginPageIndex = 1;
endPageIndex = totalPageCount;
}
//总页数多于10页,则显示当前页附近的共10个页码
else {
//当前页附近的共10个页码(前4个+当前页+后5个)
beginPageIndex = currentPage - 4;
endPageIndex = currentPage + 5;
//当前面的页码不足4个时,则显示前10个页码
if (beginPageIndex < 1) {
beginPageIndex = 1;
endPageIndex = 10;
}
//当后面的页码不足5个时,则显示后10个页码
if (endPageIndex > totalPageCount) {
endPageIndex = totalPageCount;
beginPageIndex = totalPageCount - 10 + 1;
}
}
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalSize() {
return totalSize;
}
public void setTotalSize(Integer totalSize) {
this.totalSize = totalSize;
}
public List<T> getRecordList() {
return recordList;
}
public void setRecordList(List<T> recordList) {
this.recordList = recordList;
}
public Integer getTotalPageCount() {
//总页数
totalPageCount = totalSize / pageSize;
if (totalSize == 0 || totalSize % pageSize != 0) {
totalPageCount++;
}
return totalPageCount;
}
public void setTotalPageCount(Integer totalPageCount) {
this.totalPageCount = totalPageCount;
}
public void setStartIndex(Integer startIndex) {
this.startIndex = startIndex;
}
public Integer getStartIndex() {
//开始行号=(当前页数-1)x每页显示的条数
return (currentPage - 1) * pageSize;
}
public Integer getBeginPageIndex() {
return beginPageIndex;
}
public void setBeginPageIndex(Integer beginPageIndex) {
this.beginPageIndex = beginPageIndex;
}
public Integer getEndPageIndex() {
return endPageIndex;
}
public void setEndPageIndex(Integer endPageIndex) {
this.endPageIndex = endPageIndex;
}
}
/**
* SQL的分页工具类
*/
public final class SqlPageUtil {
private SqlPageUtil(){}
/**
* @MethodName populateMySQLPageQuerySQL
* @Description mysql的分页拼接
* @param sqlStr
* @param startIndex 起始索引
* @param currentSize 每天显示的记录数
* @Return java.lang.String 拼接后的分页语句
*/
public static String populateMySQLPageQuerySQL(String sqlStr, int startIndex, int currentSize){
StringBuffer pageBuffer = new StringBuffer();
if (startIndex == 0 && currentSize <= 0) {
pageBuffer.append(sqlStr);
} else {
pageBuffer.append(sqlStr);
pageBuffer.append(" limit " + startIndex + "," + currentSize);
}
return pageBuffer.toString();
}
/**
* @MethodName populateOraclePageQuerySQL
* @Description oracle的分页拼接
* @param sqlStr
* @param startIndex 起始索引
* @param currentSize 每天显示的记录数
* @Return java.lang.String 拼接后的分页语句
*/
public static String populateOraclePageQuerySQL(String sqlStr, int startIndex, int currentSize) {
StringBuffer pageBuffer = new StringBuffer();
if (startIndex == 0 && currentSize <= 0) {
pageBuffer.append(sqlStr);
} else {
pageBuffer.append(" SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( ");
pageBuffer.append(sqlStr);
pageBuffer.append(" ) ROW_ ");
if (startIndex == 0) {
pageBuffer.append(" WHERE ROWNUM <= ");
pageBuffer.append(startIndex + currentSize);
} else if (currentSize <= 0) {
pageBuffer.append(" WHERE ROWNUM > ");
pageBuffer.append(startIndex);
} else {
pageBuffer.insert(0, "SELECT * FROM ( ");
pageBuffer.append(" WHERE ROWNUM <= ");
pageBuffer.append(startIndex + currentSize);
pageBuffer.append(") WHERE ROWNUM_ > ");
pageBuffer.append(startIndex);
}
}
return pageBuffer.toString();
}
/**
* @MethodName populateDB2PageQuerySQL
* @Description DB2的分页拼接
* @param sqlStr
* @param startIndex 起始索引
* @param currentSize 每天显示的记录数
* @Return java.lang.String 拼接后的分页语句
*/
public static String populateDB2PageQuerySQL(String sqlStr, int startIndex, int currentSize) {
if (startIndex == 0 ) {
return currentSize>1?sqlStr + " fetch first " + currentSize + " rows only":
" fetch first 1 row only";
}
return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
+ sqlStr + " fetch first " + String.valueOf(startIndex+currentSize)
+ " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
+ startIndex + " order by rownumber_";
}
}