Spring数据访问

发表于 2019-05-14 更新于 2020-12-01 阅读次数：本文字数： 12k 阅读时长 ≈ 11 分钟

使用JdbcTemplate

需要导入的jar包：
springJdbc需要的依赖

直接连接c3p0

public class TestJdbcTemplate {

    @Test
    public void testSave1() throws PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring");
        dataSource.setUser("root");
        dataSource.setPassword("123");

        JdbcTemplate jt = new JdbcTemplate(dataSource);
        String sql = "INSERT INTO users(name,age) VALUES (?,?)";
        jt.update(sql,"xiaoming",6);
    }
}

使用spring管理jdbcTemplate

使用xml配置

创建实体类

public class User {
    private int id;
    private String name;
    private int age;

    public User() {
    }

    public User(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

dao层

接口

public interface UserDao {
    void save(User user);
    void delete(Integer id);
    void update(User user);
    User getById(Integer id);
    List<User> getAll();
    int getTotalCount();
}

接口实现类

public class UserDaoImpl implements UserDao {

    private JdbcTemplate jt;

    public void setJt(JdbcTemplate jt) {
        this.jt = jt;
    }

    @Override
    public void save(User user) {
        String sql = "INSERT INTO users(name,age) VALUES (?,?)";
        jt.update(sql,user.getName(),user.getAge());
    }

    @Override
    public void delete(Integer id) {
        String sql = "DELETE FROM users WHERE id = ?";
        jt.update(sql,id);
    }

    @Override
    public void update(User user) {
        String sql = "UPDATE users set name = ?,age = ? WHERE id= ?";
        jt.update(sql,user.getName(),user.getAge(),user.getId());
    }

    @Override
    public User getById(Integer id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        User user = jt.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                return RowMapperHandler(rs);
            }
        },id);
        return user;
    }

    @Override
    public List<User> getAll() {
        String sql = "SELECT * FROM users";
        List<User> userList = jt.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                return RowMapperHandler(rs);
            }
        });
        return userList;
    }

    @Override
    public int getTotalCount() {
        String sql = "SELECT count(1) from users";
        Integer count = jt.queryForObject(sql,Integer.class);
        return count;
    }

    /**
     * 将重复内容提取出来
     * @param rs
     * @return
     * @throws SQLException
     */
    private User RowMapperHandler(ResultSet rs) throws SQLException {
        User user = new User();
        user.setName(rs.getString("name"));
        user.setAge(rs.getInt("age"));
        return user;
    }
}

配置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"
       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">

    <!-- 加载配置文件 -->
    <context:property-placeholder location="classpath:db.properties"/>
    <!-- 连接池 -->
    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <!-- jdbcTemplate 只有自己写的类才能使用呢 -->
    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- userDao -->
    <bean name="userDao" class="com.xm.dao.UserDaoImpl">
        <property name="jt" ref="jdbcTemplate"/>
    </bean>
</beans>

<!-- 配置文件 -->
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/spring
jdbc.username = root
jdbc.password = 123

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestSpringJdbc {

    @Resource(name = "userDao")
    private UserDao userDao;

    @Test
    public void testSave(){
        User user = new User("test3",45);
        userDao.save(user);
    }

    @Test
    public void testDelete(){
        userDao.delete(3);
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setName("test00");
        user.setAge(12);
        user.setId(1);
        userDao.update(user);
    }

    @Test
    public void testGetById(){
        User user = userDao.getById(2);
        System.out.println(user);
    }

    @Test
    public void testGetAll(){
        List<User> users = userDao.getAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

    @Test
    public void testCount(){
        int totalCount = userDao.getTotalCount();
        System.out.println(totalCount);
    }
}

使用注解配置

除了使用xml，也可以使用注解进行依赖注入，但是要注意注解只能针对自己编写的类

将最后的userDaoBean去掉，增加注解扫描
在UserDaoImpl添加注解@Repository("userDao"),对成员变量添加注解@Resource(name = "jdbcTemplate"),在测试时对变量进行自动装配即可

事务管理

Spring使用TransactionManager事务管理接口，其中DataSourceTransantionManager用于JDBC操作
事务的定义接口：TransactionDefinition

public interface TransactionDefinition {

    /**
     * 支持当前事务，，如果不存在，就新建一个
     */
    int PROPAGATION_REQUIRED = 0;

    /**
     * 支持当前事务，如果不存在，就不使用事务
     */
    int PROPAGATION_SUPPORTS = 1;

    /**
     * 支持当前事务，如果不存在就抛出异常
     */
    int PROPAGATION_MANDATORY = 2;

    /**
     * 如果有事务存在，挂起当前事务，创建一个新的事务
     */
    int PROPAGATION_REQUIRES_NEW = 3;

    /**
     * 以非事务方式运行，如果有事务存在，挂起当前事务
     */
    int PROPAGATION_NOT_SUPPORTED = 4;

    /**
     * 以非事务方式运行，如果有事务存在，就抛出异常
     */
    int PROPAGATION_NEVER = 5;

    /**
     * 如果有事务存在，则嵌套事务执行
     */
    int PROPAGATION_NESTED = 6;


    /**
     * 使用数据库默认级别
     */
    int ISOLATION_DEFAULT = -1;

    /**
     * 未提交读
     */
    int ISOLATION_READ_UNCOMMITTED = Connection.TRANSACTION_READ_UNCOMMITTED;

    /**
     * 提交读
     */
    int ISOLATION_READ_COMMITTED = Connection.TRANSACTION_READ_COMMITTED;

    /**
     * 可重复读
     */
    int ISOLATION_REPEATABLE_READ = Connection.TRANSACTION_REPEATABLE_READ;

    /**
     * 串行化
     */
    int ISOLATION_SERIALIZABLE = Connection.TRANSACTION_SERIALIZABLE;


    /**
     * 超时时间，-1为不超时，单位为秒
     */
    int TIMEOUT_DEFAULT = -1;


    /**
     * 返回传播方式
     */
    int getPropagationBehavior();

    /**
     * 返回隔离级别
     */
    int getIsolationLevel();

    /**
     * 返回超时时间
     */
    int getTimeout();

    /**
     * 设置是否是只读事务
     */
    boolean isReadOnly();

    /**
     * 返回该事务的名称
     */
    @Nullable
    String getName();

}

Xml配置事务

加入tx事务名称空间
配置事务管理器

<bean name="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="transfer" propagation="REQUIRED"/>
        <!-- 可以对方法名使用通配符 -->
        <tx:method name="save*" propagation="REQUIRED"/>
        <tx:method name="add*" propagation="REQUIRED"/>
        <tx:method name="insert*" propagation="REQUIRED"/>
        <tx:method name="create*" propagation="REQUIRED"/>
        <tx:method name="update*" propagation="REQUIRED"/>
        <tx:method name="modify*" propagation="REQUIRED"/>
        <tx:method name="edit*" propagation="REQUIRED"/>
        <tx:method name="delete*" propagation="REQUIRED"/>
        <tx:method name="remove*" propagation="REQUIRED"/>
        <tx:method name="drop*" propagation="REQUIRED"/>
        <tx:method name="get*" propagation="SUPPORTS"/>
        <tx:method name="select*" propagation="SUPPORTS"/>
        <tx:method name="find*" propagation="SUPPORTS"/>
        <tx:method name="query*" propagation="SUPPORTS"/>
        <tx:method name="search*" propagation="SUPPORTS"/>
    </tx:attributes>
</tx:advice>

配置切面（将通知织入切入点），注意要引入aspectjweaver依赖

<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
        <!-- 传播行为 -->
        <tx:method name="transfer" propagation="REQUIRED"/>
        <!-- 可以对方法名使用通配符 -->
        <tx:method name="save*" propagation="REQUIRED"/>
        <tx:method name="add*" propagation="REQUIRED"/>
        <tx:method name="insert*" propagation="REQUIRED"/>
        <tx:method name="create*" propagation="REQUIRED"/>
        <tx:method name="update*" propagation="REQUIRED"/>
        <tx:method name="modify*" propagation="REQUIRED"/>
        <tx:method name="edit*" propagation="REQUIRED"/>
        <tx:method name="delete*" propagation="REQUIRED"/>
        <tx:method name="remove*" propagation="REQUIRED"/>
        <tx:method name="drop*" propagation="REQUIRED"/>
        <tx:method name="get*" propagation="SUPPORTS" read-only="true"/>
        <tx:method name="select*" propagation="SUPPORTS" read-only="true"/>
        <tx:method name="find*" propagation="SUPPORTS" read-only="true"/>
        <tx:method name="query*" propagation="SUPPORTS" read-only="true"/>
        <tx:method name="search*" propagation="SUPPORTS" read-only="true"/>
    </tx:attributes>
</tx:advice>

<!-- 配置切面 -->
<aop:config>
    <aop:pointcut id="txPointcut" expression="execution(* com.xm.service..*.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="txPointcut"/>
</aop:config>

使用注解配置事务

首先开启注解事务管理

<beans>
    <context:component-scan base-package="com.xm.dao"/>
    <context:component-scan base-package="com.xm.service"/>
    <context:property-placeholder location="classpath:db.properties"/>

    <!-- 连接池 -->
    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- 开启注解事务管理 -->
    <tx:annotation-driven/>
</beans>

在Service层使用注解

package com.xm.service;

import com.xm.dao.AccountDao;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@Service("accountService")
//对所有方法启动事务注解
@Transactional
public class AccountServiceImpl implements AccountService{

    @Resource(name = "accountDao")
    private AccountDao accountDao;

    @Override
    //可以针对具体事务设置事务隔离级别
    @Transactional(propagation = Propagation.REQUIRED)
    public void transfer(Integer from, Integer to, Double money) {
        accountDao.subMoney(from,money);
//        int a = 9/0;
        accountDao.addMoney(to,money);
    }
}