一、Spring的JdbcTemplate本质
- Spring的JdbcTemplate是对JDBC进行的一个封装,它提供了一套JDBC的模板,简化了大量重复的JDBC代码,使用者在进行数据持久层操作时会更简单,提高开发效率;并且代码看起来更简洁,更利于维护。
二、使用Spring的JdbcTemplate要引入以下依赖包
- spring-jdbc-4.3.9RELEASE.jar
- spring-tx-4.3.9RELEASE.jar
- mysql-connector-java-5.1.10.jar
三、JdbcTemplate主要提供以下五类方法
- execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
- update方法:update方法用于执行新增、修改、删除等语句;
- batchUpdate方法:用于执行批处理(新增、修改、删除等)相关语句;
- query方法及queryForXXX方法:用于执行查询相关语句;
- call方法:用于执行存储过程、函数相关语句。
四、JdbcTemplate的数据源配置
- 配置数据源信息 DataConnection.properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc\:mysql\://127.0.0.1\:3306/chndb
jdbc.username=root
jdbc.password=root
initPoolSize=5
jdbc.maxpoolsize=60
- Spring整合资源,配置C3P0数据源,配置JdbcTemplate模板
<!-- 配置资源文件路径 -->
<context:property-placeholder location="classpath:DataConnection.properties"/>
<!-- 配置C3P0连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- 注入属性 -->
<property name="driverClass" value="${jdbc.driverClassName}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- 配置JdbcTemplate模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
五、JdbcTemplate的execute方法使用
- 用来执行数据库的DDL语句(CREATE,ALTER,DROP),如下创建user表
@Test
public void testExecute() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("test14.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
StringBuilder createSql = new StringBuilder();
createSql.append(" CREATE TABLE `user` ( ");
createSql.append(" `username` varchar(20) DEFAULT NULL, ");
createSql.append(" `password` varchar(20) DEFAULT NULL, ");
createSql.append(" `money` decimal(8,2) DEFAULT NULL ");
createSql.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
jdbcTemplate.execute(createSql.toString());
}
- 数据库user表
六、JdbcTemplate的update方法使用
- 数据的增加、删除、修改(DML)方法实质都是使用模板的update方法
- 操作代码
@Test
public void testUpdate() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("test14.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
String insertSql = " INSERT INTO user (username, password, money) VALUES (?, ?, ?) ";
Object[] insertObjects = new Object[]{"Jack", "10101010", 100.0};
int insertRows = jdbcTemplate.update(insertSql, insertObjects);
String updateSql = " UPDATE user SET username = ? ";
Object[] updateObjects = new Object[]{"Lucy"};
int updateRows = jdbcTemplate.update(updateSql, updateObjects);
String deleteSql = " DELETE FROM user WHERE username = ? ";
Object[] deleteObjects = new Object[]{"Lucy"};
int deleteRows = jdbcTemplate.update(deleteSql, deleteObjects);
System.out.println("新增数据:" + insertRows + "条");
System.out.println("修改数据:" + updateRows + "条");
System.out.println("删除数据:" + deleteRows + "条");
}
新增数据:1条
修改数据:1条
删除数据:1条
七、JdbcTemplate的batchUpdate方法使用
- 操作代码
@Test
public void testBatchUpdate() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("test14.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
String insertSql = " INSERT INTO user (username, password, money) VALUES (?, ?, ?) ";
List<Object[]> batchList = new ArrayList<Object[]>();
batchList.add(new Object[]{"Jack", "10101010", 100.0});
batchList.add(new Object[]{"Lucy", "10101010", 1000.0});
batchList.add(new Object[]{"Mary", "10101010", 10000.0});
int[] insertRows = jdbcTemplate.batchUpdate(insertSql, batchList);
System.out.println("新增数据:" + insertRows.length + "条");
}
- 数据库结果
八、JdbcTemplate的query及queryForXXX使用方法
- 创建对象映射类
package com.sitech.test14;
public class User {
private String username;
private String password;
private Double money;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
@Override
public String toString() {
return "User [username=" + username + ", password=" + password
+ ", money=" + money + "]";
}
}
- 查询某个值
@Test
public void testQuery() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("test14.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
String selectSql = " SELECT COUNT(*) FROM user ";
int insertRows = jdbcTemplate.queryForObject(selectSql, Integer.class);
System.out.println("总数据:" + insertRows + "条");
}
总数据:3条
- 查询某个对象
@Test
public void testQueryForObject() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("test14.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
String selectSql = " SELECT * FROM user WHERE username = ? ";
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
User user = jdbcTemplate.queryForObject(selectSql, rowMapper, "Jack");
System.out.println(user);
}
User [username=Jack, password=10101010, money=100.0]
- 查询对象集合,这里展示一下RowMapper的实现原理,RowMapper的实现类,这里也可以用于单个对象的查询
package com.sitech.test14;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class UserMapper
implements RowMapper<User> {
/**
* @param: ResultSet rs 结果集
* @param: int count 用于计数
*/
@Override
public User mapRow(ResultSet rs, int count) throws SQLException {
String username = rs.getString("username");
String password = rs.getString("password");
Double money = rs.getDouble("money");
User user = new User();
user.setUsername(username);
user.setPassword(password);
user.setMoney(money);
return user;
}
}
- 操作代码
@Test
public void testQueryForList() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("test14.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
String selectSql = " SELECT * FROM user";
List<User> userList = jdbcTemplate.query(selectSql, new UserMapper());
System.out.println(userList);
}
[User [username=Jack, password=10101010, money=100.0], User [username=Lucy, password=10101010, money=1000.0], User [username=Mary, password=10101010, money=10000.0]]
九、总结
JdbcTemplate是Spring框架自带的对JDBC操作的封装,目的是提供统一的模板方法使对数据库的操作更加方便、友好,效率也不错。但是功能还是不够强大(比如不支持级联属性),在实际应用中还需要和hibernate、mybaties等框架混合使用。