Spring的 jdbcTemplate操作
spring针对javaee三层,每一层都有解决技术,在dao层中使用 jdbcTemplate,可以持久层进行增删改查操作。下面对jdbcTemplate的使用进行演示。
1,导入依赖
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>4.3.0.RELEASE</spring.version>
<mysql.version>5.1.6</mysql.version>
<druid.version>1.0.9</druid.version>
</properties>
<dependencies>
<!--Spring框架核心库 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${spring.version}</version>
</dependency>
<!--log4j日志包 -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.6.1</version>
</dependency>
<!-- MySql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
</dependencies>
2,pojo User
public class User {
private Integer uid;
private String uname;
private Integer money;
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public Integer getMoney() {
return money;
}
public void setMoney(Integer money) {
this.money = money;
}
}
3,创建一个类测试JdbcTemalte增删改查
package dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import pojo.User;
public class UserDao {
static JdbcTemplate jdbcTemplate=null;
static {
// 配置数据库连接
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setPassword("root");
dataSource.setUsername("root");
dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&&characterEncoding=UTF-8");
// 创建JdbcTemplate对象,用来操作数据库
jdbcTemplate=new JdbcTemplate(dataSource);
}
@Test
public void add() {
String sql ="insert into user(uname,money) values(?,?)";
jdbcTemplate.update(sql, "刘璐12",1000);
}
@Test
public void delete() {
String sql ="delete from user where uid=?";
jdbcTemplate.update(sql, 1);
}
@Test
public void update() {
String sql ="update user set money=? where uid=?";
jdbcTemplate.update(sql, 999,2);
}
/**
* queryForObject()
*/
@Test
public void selectCount() {
String sql ="select count(*) from user";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
}
/**
* queryForObject(sql,rowMapper,arg)
* sql 语句
* rowMapper 接口
* arg 参数
*/
@Test
public void selectObject() {
Object obj[]= {2};
String sql ="select uid,uname,money from user where uid=?";
User user = jdbcTemplate.queryForObject(sql,new RowMapper<User>() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
//把数据库中得到的数据封装到User中返回。
//rowNum,表示查询的数据编号,0开始。
User user=new User();
user.setMoney(rs.getInt("money"));
user.setUname(rs.getString("uname"));
user.setUid(rs.getInt("uid"));
return user;
}
},obj);
}
/**
* query(sql, new RowMapper<User>()
*/
@Test
public void selectList() {
String sql ="select * from user";
List<User> list = jdbcTemplate.query(sql, new RowMapper<User>() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
//把数据库中得到的数据封装到User中返回。
//rowNum,表示查询的数据编号,0开始。
User user=new User();
user.setMoney(rs.getInt("money"));
user.setUname(rs.getString("uname"));
user.setUid(rs.getInt("uid"));
return user;
}});
}
}
4,dao层整合JdbcTemplate(xml文件方式)
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:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.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
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd">
<!-- 数据库连接池druid -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
<bean id="userDao" class="dao.UserDao1">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
dao层写一个类操作数据库
public class UserDao1 {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void add() {
String sql="insert into user(uname,money)values(?,?)";
jdbcTemplate.update(sql,"巴贝",900);
}
}
测试
public class UserService {
public static void main(String[] args) {
ConfigurableApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao1 bs=(UserDao1) ctx.getBean("userDao");
bs.add();
}
}
5,dao层整合JdbcTemplate(注解方式)
applicationContext.xml
<!-- 开启注解扫描 -->
<context:component-scan base-package="dao"></context:component-scan>
<!-- 数据库连接池druid -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
dao层
@Component
public class UserDao1 {
@Autowired
private JdbcTemplate jdbcTemplate;
public void add() {
String sql="insert into user(uname,money)values(?,?)";
jdbcTemplate.update(sql,"小贝",900);
}
}
Spring的事务管理
(1)什么是事务?
就是把多件事情当做一件事情来处理。也就是大家同在一条船上,要活一起活,要over一起over !
(2)事务特性(ACID)
原子性:一个事务不可在分割,要么都执行要么都不执行。
一致性:一个事务的执行会使数据从一个一致状态切换到另一个一致的状态。
隔离性:一个事务的执行不受其他事物的干扰
持久性: 一个事务一旦提交,则会永久的改变数据库的数据
(3)基于注解实现事务
例子:转账
applicationContext.xml
<!-- 开启注解扫描 -->
<context:component-scan base-package="service,dao"></context:component-scan>
<!-- 数据库连接池druid -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
<!--配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- 数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!--开启事务扫描 -->
<tx:annotation-driven transaction-manager="transactionManager"/>
dao层
@Component
public class UserDao1 {
@Autowired
private JdbcTemplate jdbcTemplate;
public void addMoney() {
//小贝向巴贝转100块钱。原有钱都是900
String sql="update user set money=money-? where uname=?";
jdbcTemplate.update(sql,100,"小贝");
}
public void lessMoney() {
String sql="update user set money=money+? where uname=?";
jdbcTemplate.update(sql,100,"巴贝");
}
}
service层
@Transactional //默认值是事务的传播行为:Propagation.REQUIRED。
@Service
public class UserService {
@Autowired
UserDao1 ud;
public void testMoney() {
ud.lessMoney();
//int n=10/0;//测试当发生异常的时候,不会提交事务,数据库中数据不会发生改变。
ud.addMoney();
}
}
(4)基于xml配置文件实现事务
applicationContext.xml
<!-- 开启注解扫描 -->
<context:component-scan base-package="service,dao"></context:component-scan>
<!-- 数据库连接池druid -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
<!--配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- 数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 通知 -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!-- 传播行为 -->
<tx:method name="update*" propagation="REQUIRED"/>
<tx:method name="get*" propagation="SUPPORTS" read-only="true"/>
</tx:attributes>
</tx:advice>
<!-- 切面 -->
<aop:config>
<aop:advisor advice-ref="txAdvice" pointcut="execution(* service.*.*(..))"/>
</aop:config>
dao层,同注解。
service层
@Service
public class UserService {
@Autowired
UserDao1 ud;
public void updateMoney() {//方法名称要和配置文件的规则一样
ud.lessMoney();
//int n=10/0;
ud.addMoney();
}
}
声明式事务管理(注解)总结:3步
1,配置事务管理器
2,配置事务注解
3,在要使用事务的方法所在类上添加注解