在spring中为了更加方便的操作JDBC,在JDBC的基础之上定义了一个抽象层,此设计的目的是为不同类型的JDBC操作提供模板方法,每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务,通过这种方式,可以尽可能保留灵活性,将数据库存取的工作量降到最低。
1、配置并测试数据源
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.6.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.2.6.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.2.6.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.2.6.RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
db.properties
mysql.username=root
mysql.password=a123456
mysql.url=jdbc:mysql://localhost:3306/fztx
mysql.driverClassName=com.mysql.jdbc.Driver
spring.xml
<!--配置第三方bean-->
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="username" value="${mysql.username}"></property>
<property name="password" value="${mysql.password}"></property>
<property name="url" value="${mysql.url}"></property>
<property name="driverClassName" value="${mysql.driverClassName}"></property>
</bean>
<!--引入外部属性资源文件-->
<context:property-placeholder location="db.properties"></context:property-placeholder>
JdbcTest.java
ClassPathXmlApplicationContext ioc;
@Before
public void before(){
ioc = new ClassPathXmlApplicationContext("spring.xml");
}
@Test
public void test01(){
DruidDataSource bean = ioc.getBean(DruidDataSource.class);
System.out.println(bean);
}
2、给spring容器添加JdbcTemplate
spring容器提供了一个JdbcTemplate类,用来方便操作数据库。
spring.xml
<!--配置JdbcTemplate Bean组件-->
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
<property name="dataSource" ref="dataSource" ></property>
</bean>
测试
@Test
public void test02(){
JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
Long along = jdbcTemplate.queryForObject("select count(*) from t_user",Long.class);
System.out.println(along);
}
3.查询某个值,并以对象的方式返回
/**
* 查询实体
*/
@Test
public void test04(){
JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
// 数据库字段名和属性名一样 利用BeanPropertyRowMapper
User user = jdbcTemplate.queryForObject("select * from t_user where id=1", new BeanPropertyRowMapper<User>(User.class));
/*User user = jdbcTemplate.queryForObject("select * from t_user where id=1",
(resultSet, i) -> {
User o=new User();
o.setId(resultSet.getInt("id"));
o.setRealname(resultSet.getString("realname"));
return o;
});*/
System.out.println(user);
}
4.查询返回集合对象
/**
* 查询List<实体>
*/
@Test
public void test05(){
JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
// 数据库字段名和属性名一样 利用BeanPropertyRowMapper
List<User> user = jdbcTemplate.query("select * from t_user where id > 1", new BeanPropertyRowMapper<User>(User.class));
/*User user = jdbcTemplate.queryForObject("select * from t_user where id=1",
(resultSet, i) -> {
User o=new User();
o.setId(resultSet.getInt("id"));
o.setRealname(resultSet.getString("realname"));
return o;
});*/
System.out.println(user);
}
5.新增
/**
* 新增
*/
@Test
public void test06(){
JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
// 数据库字段名和属性名一样 利用BeanPropertyRowMapper
int result =
jdbcTemplate.update("insert into t_user(realname,cardno,balance) values (?,?,?)",
"王麻子","12345677",800);
System.out.println(result);
}
6.修改
/**
* 修改
*/
@Test
public void test07(){
JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
// 数据库字段名和属性名一样 利用BeanPropertyRowMapper
int result =
jdbcTemplate.update("update t_user set balance=balance+200 where id=?",
4);
System.out.println(result);
}
7.删除
/**
* 删除
*/
@Test
public void test08(){
JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
// 数据库字段名和属性名一样 利用BeanPropertyRowMapper
int result =
jdbcTemplate.update("delete from t_user where id=?",
4);
System.out.println(result);
}
8、使用具备具名函数的JdbcTemplate
spring.xml
<bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" id="namedParameterJdbcTemplate">
<constructor-arg type="javax.sql.DataSource" ref="dataSource"/>
</bean>
/**
* 具名参数处理NamedParameterJdbcTemplate
*/
@Test
public void test09(){
NamedParameterJdbcTemplate jdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);
Map<String,Object> map=new HashMap<String,Object>();
map.put("id",4);
int result =
jdbcTemplate.update("delete from t_user where id=:id",map);
System.out.println(result);
}
9、整合Dao、service
spring.xml
<!--扫描包-->
<context:component-scan base-package="cn.tulingxueyuan"></context:component-scan>
UserDaoImpl.java
package com.tuling.dao.impl;
import com.alibaba.druid.pool.DruidDataSource;
import com.tuling.dao.IUserDao;
import com.tuling.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
* @author fanzitianxing
* @title: UserDaoImpl
* @projectName spring_transaction
* @description: TODO
* @date 2021/7/1914:34
*/
@Repository
public class UserDaoImpl implements IUserDao {
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource(DruidDataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public User getUser() {
return jdbcTemplate.queryForObject("select * from t_user where id=1",new BeanPropertyRowMapper<User>(User.class));
}
}
UserServiceImpl.java
package com.tuling.service.impl;
import com.tuling.dao.IUserDao;
import com.tuling.entity.User;
import com.tuling.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @author fanzitianxing
* @title: UserServiceImpl
* @projectName spring_transaction
* @description: TODO
* @date 2021/7/1914:31
*/
@Service
public class UserServiceImpl implements IUserService {
@Autowired
IUserDao userDao;
public User getUser() {
return userDao.getUser();
}
}
测试:
@Test
public void test10(){
IUserService userService = ioc.getBean(IUserService.class);
User user = userService.getUser();
System.out.println(user);
}