Spring-JDBC

最新推荐文章于 2022-07-17 22:49:49 发布

ノラガミ

最新推荐文章于 2022-07-17 22:49:49 发布

阅读量95

点赞数

本文链接：https://blog.csdn.net/weixin_45407027/article/details/103190636

版权

Spring-JDBC

一、SpringJDBC整合

1. 创建Maven 普通工程(quick-start工程)

2. 添加坐标

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>
<!-- spring 测试环境 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>4.3.2.RELEASE</version>
    <scope>test</scope>
</dependency>

<!-- spring 框架坐标依赖添加 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>

<!-- aop -->
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.8.9</version>
</dependency>

<!-- mysql 驱动包 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>
<!-- c3p0 连接池 -->
<dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
</dependency>

<!-- spring jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>

<!-- spring事务 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>

3. 准备资源文件

创建资源目录 src/main/resources
准备jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&characterEncoding=utf8
jdbc.user=root
jdbc.password=root

准备bean.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
        http://www.springframework.org/schema/context/spring-context.xsd">
    
    <context:component-scan base-package="com.xyz"/>

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!-- 数据源c3p0 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!-- 配置JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

4. 执行测试

准备TestSpringJdbc

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class TestSpringJdbc {

    @Test
    public void test(){
        ApplicationContext ac =new ClassPathXmlApplicationContext("bean.xml");
        JdbcTemplate jdbcTemplate= (JdbcTemplate) ac.getBean("jdbcTemplate");
        Integer total = jdbcTemplate.queryForObject("select count(*) from account",Integer.class);
        System.out.println("总数-->"+total);
    }
}

二、账户模块CRUD 实现

1. 持久层方法定义

添加

添加记录返回影响行数
添加记录返回主键
批量添加记录(excel 导入导出技术-apache poi)

/**
 * 账户添加
 *    添加账户返回影响行数
 *    添加记录返回主键
 *    批量添加
 */
public int saveAccount(Account account);

public int saveAccountHasKey(Account account);

public int saveAccountBatch(List<Account> accounts);

查询

聚合查询(count sum max min …) ，用于统计
记录详情查询，用于修改场景
多条件查询(分页) ，用于列表展示(分页展示)
多表连接(主外键关联)
分组查询

/**
 * 账户查询
 *    聚合查询  sum  max  min count
 *    详情查询   根据主键id 查询
 *    多条件查询(分页) 根据账户名 账户类型  账户创建时间 用户id  (页码 每页大小)
 *    多表连接查询
 *    分组查询
 */
public int countAccountsByUserId(Integer userId);

public Account queryAccountById(Integer id);

public List<Account> queryAccountsByParams(String aname,String type,String time,Integer userId);

更新

单条记录更新
批量更新

/**
 * 账户更新
 *    根据主键更新
 *    批量更新
 */
public int updateAccountById(Account account);

public int updateAccountsBatch(List<Account> accounts);

删除

单条记录删除
批量删除

/**
 * 账户删除
 *    单条记录删除
 *    批量删除
 */
public  int deleteAccountById(Integer id);

public int deleteAccountsByIds(Integer[] ids);

2. 持久层方法实现

添加实现

对于SpringJdbc 实现记录添加、更新、删除时，使用update(单条记录) | batchUpdate(批量更新) 方法

@Repository
public class AccountDaoImpl implements IAccountDao {
    @Resource
    private JdbcTemplate jdbcTemplate;
       
    @Override
    public int saveAccount(Account account) {
        return jdbcTemplate.update("insert into account(aname, type, money, remark, create_time, update_time, user_id) " +
                        "values(?,?,?,?,?,?,?) ", account.getAname(), account.getType(), account.getMoney(),
                account.getRemark(), account.getCreateTime(), account.getUpdateTime(), account.getUserId());
    }

    /**
    参数1:PreparedStatementCreator  --->{
    	PreparedStatement createPreparedStatement(Connection con) throws SQLException;
    // 当前PreparedStatement 在返回时返回的为带有主键的ps 对象 con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
    参数2: KeyHolder  用于接收返回的主键 实现类GeneratedKeyHolder  --{
   		 Number getKey() throws InvalidDataAccessApiUsageException;// 返回主键
    }
    }    
    **/
    @Override
    public int saveAccountHasKey(Account account) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("insert into account(aname, type, money, remark," +
                        " create_time, update_time, user_id) " +
                        "values(?,?,?,?,?,?,?) ", Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, account.getAname());
                ps.setString(2, account.getType());
                ps.setBigDecimal(3, account.getMoney());
                ps.setString(4, account.getRemark());
                ps.setObject(5, account.getCreateTime());
                ps.setObject(6, account.getUpdateTime());
                ps.setInt(7, account.getUserId());
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }
   
    /**
        批量添加-->batchUpdate(批量添加 批量更新 批量删除)
        参数1:添加的sql 语句
        参数2:BatchPreparedStatementSetter-->{
           //  根据索引i 获取当前对象 设置占位置
           void setValues(PreparedStatement ps, int i) throws SQLException;
           // 设置当前批量执行的元素个数(集合的size)
           int getBatchSize();
        }
            
    **/
	@Override
    public int saveAccountBatch(List<Account> accounts) {
        return jdbcTemplate.batchUpdate("insert into account(aname, type, money, remark," +
                " create_time, update_time, user_id) " +
                "values(?,?,?,?,?,?,?) ", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Account account = accounts.get(i);
                ps.setString(1, account.getAname());
                ps.setString(2, account.getType());
                ps.setBigDecimal(3, account.getMoney());
                ps.setString(4, account.getRemark());
                ps.setObject(5, account.getCreateTime());
                ps.setObject(6, account.getUpdateTime());
                ps.setInt(7, account.getUserId());
            }

            @Override
            public int getBatchSize() {
                return accounts.size();
            }
        }).length;
    }
}

查询实现

queryForObject 聚合查询javabean 对象

query 列表查询

@Override
public int countAccountsByUserId(Integer userId) {
    Integer total = jdbcTemplate.queryForObject("select count(*) from account where user_id=?", Integer.class, userId);
    return total;
}

/*
    参数2:RowMapper 接口-->{
        // 使用rs 对象对接口进行包装(java bean)
       	T mapRow(ResultSet rs, int rowNum) throws SQLException;
    }
	*/
@Override
public Account queryAccountById(Integer id) {
    return jdbcTemplate.queryForObject("select * from account where id=?", new Object[]{id}, new RowMapper<Account>() {
        @Override
        public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
            Account account = new Account();
            account.setAname(rs.getString("aname"));
            account.setUpdateTime(rs.getDate("update_time"));
            account.setCreateTime(rs.getDate("create_time"));
            account.setType(rs.getString("type"));
            account.setRemark(rs.getString("remark"));
            account.setMoney(rs.getBigDecimal("money"));
            account.setUserId(rs.getInt("user_id"));
            account.setId(rs.getInt("id"));
            return account;
        }
    });
}

@Override
public List<Account> queryAccountsByParams(String aname, String type, String time, Integer userId) {
    StringBuffer stringBuffer = new StringBuffer("select * from account where  1=1 ");
    List<Object> params = new ArrayList<Object>();
    if (StringUtils.isNotBlank(aname)) {
        stringBuffer.append("  and aname like concat('%',?,'%') ");
        params.add(aname);
    }

    if (StringUtils.isNotBlank(type)) {
        stringBuffer.append("  and  type=? ");
        params.add(type);
    }
    if (StringUtils.isNotBlank(time)) {
        stringBuffer.append("  and  create_time >=? ");
        params.add(time);
    }
    if (null != userId) {
        stringBuffer.append(" and  user_id  =? ");
        params.add(userId);
    }
    // pageNum  pageSize  第一个?=(pageNum-1)*pageSize
    stringBuffer.append("limit ?,?");
    return jdbcTemplate.query(stringBuffer.toString(), new RowMapper<Account>() {
        @Override
        public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
            Account account = new Account();
            account.setAname(rs.getString("aname"));
            account.setUpdateTime(rs.getDate("update_time"));
            account.setCreateTime(rs.getDate("create_time"));
            account.setType(rs.getString("type"));
            account.setRemark(rs.getString("remark"));
            account.setMoney(rs.getBigDecimal("money"));
            account.setUserId(rs.getInt("user_id"));
            account.setId(rs.getInt("id"));
            return account;
        }
    }, params.toArray());
}

更新

@Override
public int updateAccountById(Account account) {
    String sql="update account set aname=?,type=?,money=?,remark=?,update_time=? where id=?";
    return jdbcTemplate.update(sql, account.getAname(), account.getType(), account.getMoney(),
                               account.getRemark(), account.getUpdateTime(), account.getId());
}

//参考批量化添加
@Override
public int updateAccountsBatch(List<Account> accounts) {
    String sql="update account set aname=?,type=?,money=?,remark=?,update_time=? where id=?";
    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Account account = accounts.get(i);
            ps.setObject(1, account.getAname());
            ps.setObject(2, account.getType());
            ps.setBigDecimal(3, account.getMoney());
            ps.setString(4, account.getRemark());
            ps.setObject(5, account.getUpdateTime());
            ps.setInt(6, account.getId());
        }

        @Override
        public int getBatchSize() {
            return accounts.size();
        }
    }).length;
}

删除

@Override
public int deleteAccountById(Integer id) {
    String sql="delete from account where id=?";
    return jdbcTemplate.update(sql,id);
}

// 参考 批量化添加 更新
@Override
public int deleteAccountsByIds(Integer[] ids) {
    return jdbcTemplate.batchUpdate("delete from account where id=?", new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setObject(1,ids[i]);
        }

        @Override
        public int getBatchSize() {
            return ids.length;
        }
    }).length;
}

三、Spring JDBC事务控制

账户转账案例

A账户转出指定的金额到 B账户具体代码实现

Service接口方法定义

public interface IAccountService {
    /**
     * 转账业务方法定义
     * @param sourceAid
     * @param targetAid
     * @param money
     * @return
     */
    public int updateAccountMoney(Integer sourceAid, Integer targetAid, BigDecimal money);
}

业务方法实现

@Override
public int updateAccountMoney(Integer sourceAid, Integer targetAid, BigDecimal money) {
    int result = 0;
    /**
     *   sourceAid -100  原账户减去 转账金额
     *   targetAid+100   目标账户 加上转账金额
     */
    int r1 = accountDao.updateAccountOut(sourceAid, money);
    int r2 = accountDao.updateAccountIn(targetAid, money);
    //Connection
    if (r1 > 0 && r2 > 0) {
        result = 1;
    }
    return result;
}

Dao 层代码
    @Override
    public int updateAccountOut(Integer sourceAid, BigDecimal money) {
        String sql="update account set money=money-? where id=?";
        return jdbcTemplate.update(sql,money,sourceAid);
    }

    @Override
    public int updateAccountIn(Integer targetAid, BigDecimal money) {
        String sql="update account set money=money+? where id=?";
        return jdbcTemplate.update(sql,money,targetAid);
    }

问题:有可能出现数据异常(转出成功转入失败 )，违反事务ACID 特性

事务四大特性-ACID

原子性：共生死
一致性：事务执行前后数据必须保持一致
隔离性：事务与事务在执行过程中不能相互影响(执行更新的同时不能同时执行删除操作!)
持久性：数据一旦改变数据被写入到硬盘永久改变

Spring 事务控制

接口定义

PlatformTransactionManager 可以提供不同实现
- DataSourceTransactionManager(原生的JDBC操作)
- HibernateTransactionManager
- JpaTransactionManager
- JtaTransactionManager(多数据源)

具体配置(XML || 注解)

共同的约束配置
- tx aop 约束添加

<?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:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
    <aop:aspectj-autoproxy/>
    <!-- 配置事务管理器 -->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

1. XML 配置

<!-- 配置事务通知 -->
<tx:advice id="txAdvice" transaction-manager="txManager">
    <tx:attributes>
        <!-- 对 以update 开头的service 方法进行增强 -->
        <tx:method name="update*" propagation="REQUIRED"/>
        <tx:method name="save*" propagation="REQUIRED"/>
        <tx:method name="del*" propagation="REQUIRED"/>
    </tx:attributes>
</tx:advice>

<!-- 配置aop 声明切入点 通知 -->
<aop:config>
    <aop:pointcut id="cut" expression="execution(* com.shsxt.service..*.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="cut"></aop:advisor>
</aop:config>

2. 注解配置(声明式事务)

<!-- 开启声明式事务 -->
<tx:annotation-driven transaction-manager="txManager"/>

方法级别使用

@Transactional(propagation = Propagation.REQUIRED)

事务传播特性:

事务传播行为介绍:
@Transactional(propagation=Propagation.REQUIRED) 
如果有事务, 那么加入事务, 没有的话新建一个(默认情况下)
    
@Transactional(propagation=Propagation.NOT_SUPPORTED) 
容器不为这个方法开启事务

@Transactional(propagation=Propagation.REQUIRES_NEW) 
不管是否存在事务,都创建一个新的事务,原来的挂起,新的执行完毕,继续执行老的事务

@Transactional(propagation=Propagation.MANDATORY) 
必须在一个已有的事务中执行,否则抛出异常

@Transactional(propagation=Propagation.NEVER) 
必须在一个没有的事务中执行,否则抛出异常(与Propagation.MANDATORY相反)
    
@Transactional(propagation=Propagation.SUPPORTS) 
如果其他bean调用这个方法,在其他bean中声明事务,那就用事务.如果其他bean没有声明事务,那就不用事务.

ノラガミ

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Spring-JDBC

Spring-JDBC一、SpringJDBC整合1. 创建Maven 普通工程(quick-start工程)2. 添加坐标<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12&lt...
复制链接

扫一扫

Spring-JDBC

Spring-JDBC

一、SpringJDBC整合

1. 创建Maven 普通工程(quick-start工程)

2. 添加坐标

3. 准备资源文件

4. 执行测试

二、账户模块CRUD 实现

1. 持久层方法定义

添加

查询

更新

删除

2. 持久层方法实现

添加实现

查询实现

更新

删除

三、Spring JDBC事务控制

账户转账案例

Service接口方法定义

业务方法实现

事务四大特性-ACID

Spring 事务控制

接口定义

具体配置(XML || 注解)

1. XML 配置

2. 注解配置(声明式事务)

“相关推荐”对你有帮助么？