Spring 之 Spring JdbcTemplate的使用
1.准备
源码下载链接
https://share.weiyun.com/Iax0hI64
0. 数据表
create table user(
uid int not null PRIMARY key AUTO_INCREMENT,
username VARCHAR(20) not null,
password VARCHAR(20) not null
)ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into user values(1,"zhenqk","12345");
insert into user values(2,"sun","12345");
insert into user values(3,"小白","123");
insert into user values(4,"zhen","12345");
1.导入依赖
<dependencies>
<!-- 测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- spring ioc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<!-- spring aop-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<!-spring jdbc-->
<dependency>
<groupId>.org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.1.RELEASE</version>
<type>pom</type>
</dependency>
<!- 数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<!- mysqljar -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
2.db.properties
druid.driver=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/kdb2
druid.username=root
druid.password=root
3.applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
https://www.springframework.org/schema/tx/spring-tx.xsd ">
<context:component-scan base-package="com.ccut"></context:component-scan>
<!--扫描aop注解-->
<!--<aop:aspectj-autoproxy></aop:aspectj-autoproxy>-->
<!--引入外部的资源文件-->
<context:property-placeholder location="classpath:db.properties"></context:property-placeholder>
<!--连接数据库的数据源信息 可以使用连接池进行连接
数据源:可以使用该配置完成数据库的连接操作
-->
<bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource" >
<!--<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/bjpowernode"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>-->
<property name="driverClassName" value="${druid.driver}"></property>
<property name="url" value="${druid.url}"></property>
<property name="username" value="${druid.username}"></property>
<property name="password" value="${druid.password}"></property>
</bean>
<!--将数据源交给spring下jdbc模块中的核心处理类
JdbcTemplate核心的类,操作的类
-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="druidDataSource"></property>
</bean>
<bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager" id="transactionManager">
<property name="dataSource" ref="druidDataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>
2.实体类User.java
main/java/com/ccut/pojo/User.java
public class User {
private int uid;
private String username;
private String password;
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public User() {
}
public User(int uid, String username, String password) {
this.uid = uid;
this.username = username;
this.password = password;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
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;
}
}
3.测试方法使用
import java.util.List;
public class Test {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicatinContext.xml");
JdbcTemplate jdbcTemplate1 = context.getBean("jdbcTemplate", JdbcTemplate.class);
@org.junit.Test
public void testDB(){
System.out.println(jdbcTemplate1);
}
/**
* DML语句
* 使用的是共同的update方法,只需要替换不同的sql即可
*/
@org.junit.Test
public void testUpdate(){
String sql = "update user set username=?,password=? where uid=?";
//参数赋值按照占位符顺序
jdbcTemplate1.update(sql,"jeck","abc",3);
}
/**
* DQL语句
*/
@org.junit.Test
public void testqueryObject(){//获取到一条记录通过一个Java中实体类对象进行接收
String sql = "select uid,username,password from user where uid=?";
//将数据库查询出来的条数据通过RowMapper对象进行封装为泛型类型
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
//参数赋值按照占位符顺序
User user = jdbcTemplate1.queryForObject(sql, rowMapper, 2);
System.out.println(user);
}
@org.junit.Test
public void testqueryValue(){//获取到一个独立的数据
String sql = "select count(uid) from user";
//参数赋值按照占位符顺序
int count = jdbcTemplate1.queryForObject(sql, Integer.class);
System.out.println(count);
}
@org.junit.Test
public void testqueryList(){//获取到多条记录,通过list集合进行接收查询数据
String sql = "select uid,username,password from user";
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
//参数赋值按照占位符顺序
List<User> userList = jdbcTemplate1.query(sql, rowMapper);
System.out.println(userList);
}
// 测试 事务
@org.junit.Test
public void testUserDao0(){
UserDao userDaoImpl = context.getBean("userDaoImpl", UserDao.class);
User u=new User(1,"王振强","12345");
Boolean b=userDaoImpl.testUpdate(u);
System.out.println(b);
}
}
4. 与dao结合使用
1.dao层
IUserDao.java
public interface UserDao {
/**
* 获取一条记录
*/
User testqueryObject(Integer uid);
Boolean testUpdate(User user);
}
Impl.UserDaoImpl.java
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public User testqueryObject(Integer uid) {
String sql = "select uid,username,password from user where uid=?";
//将数据库查询出来的条数据通过RowMapper对象进行封装为泛型类型
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
//参数赋值按照占位符顺序
return jdbcTemplate.queryForObject(sql, rowMapper, uid);
}
@Override
@Transactional(isolation=Isolation.REPEATABLE_READ,propagation = Propagation.REQUIRES_NEW)
public Boolean testUpdate(User user){
String sql = "update user set username=?,password=? where uid=?";
//参数赋值按照占位符顺序
// 异常代码 int o=3/0;
int a=jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getUid());
if(a>0){
return true;
}
return false;
}
}
2. test类
/**
* 测试在dao层使用spring中JDBC模块
*/
@org.junit.Test
public void testUserDao(){
UserDao userDaoImpl = context.getBean("userDaoImpl", UserDao.class);
User user = userDaoImpl.testqueryObject(2);
System.out.println(user);
}
不停的思考,就会不停的进步