JdbcTemplate
1、简介
JdbcTemplate是Spring框架中提供的一个对象,是对原始繁琐的JDBC API对象的简单封装,Spring框架提供了很多操作模板类。如:操作关系型数据的JdbcTemplate和HibernateTemplate,操作nosql数据库的RedisTemplate了,操作消息队列的JmsTemplate等
2、开发步骤
- 导入spring-jdbc和spring-tx的依赖
- 创建数据库表和实体
- 创建JDBCTemplate对象
- 执行数据库操作
- 增删改,用update方法
- 查询,多个用query方法,单个用queryForObject方法,需传入BeanPropertyRowMapper的泛型对象以自动将查询结果注入新建对象并返回
1、导入spring-jdbc和spring-tx的依赖
pom.xml
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
2、创建数据库表和实体
create table account(
id int primary key auto_increment,
name varchar(20) ,
money decimal(20,2)
)
Account.java
public class Account {
private String name;
private double money;
//....省略get、set、toString
3、4
public void test1(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("123456");
// 3、创建JDBCTemplate对象
JdbcTemplate jdbcTemplate = new JdbcTemplate();
// 设置数据源对象
jdbcTemplate.setDataSource(dataSource);
// 4、执行操作
int update = jdbcTemplate.update("insert into account(name,money) value(?,?)", "tom", 5000);
System.out.println(update);
}
3、Spring创建模板对象
applicationContext.xml下配置bean
<!-- 数据源对象 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!-- JDBCTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
抽取jdbc.properties
jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=123456
applicationContext.xml的beans标签头添加context命名空间
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"
抽取jdbc配置文件并配置数据源、模板对象**(重点)**
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 数据源对象 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- JDBCTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
测试代码
public void test2(){
ClassPathXmlApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
int update = jdbcTemplate.update("insert into account(name,money) value(?,?)", "zoe", 15000);
System.out.println(update);
}
4、Jdbc模板CRUD测试
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {
@Autowired
private JdbcTemplate jdbcTemplate;
//更新
@Test
public void testUpdate(){
jdbcTemplate.update("update account set money = ? where id = ?",6666,2);
}
//删除
@Test
public void testDelete(){
jdbcTemplate.update("delete from account where id = ?",3);
}
//查询一个
@Test
public void testQueryOne(){
Account account = jdbcTemplate.queryForObject("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 2);
System.out.println(account);
}
//查询多个
@Test
public void testQueryAll(){
List<Account> accountList = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
System.out.println(accountList);
}
//查询特殊值
@Test
public void testQueryCount(){
Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
System.out.println("count = " + count);
}
}