Spring整合jdbc及其crud操作即增删改查（一）

原创

wx5ba7ab4695f27 2022-03-01 13:39:35 博主文章分类：J2EE ©著作权

©著作权归作者所有：来自51CTO博客作者wx5ba7ab4695f27的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.工程的坐标依赖

<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

创建db.propertie

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

可选配置

initialPoolSize=20   
maxPoolSize=100   
minPoolSize=10   
maxIdleTime=600
acquireIncrement=5   
maxStatements=5   
idleConnectionTestPeriod=60

创建spring—db.xml

<!-- 加载 properties 配置文件 -->
 <context:property-placeholder location="db.properties" />

配置数据源，选择c3p0

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

C3P0 其他额外配置(对应的值在 db.properties 文件中指定)

<!-- 指定连接池中保留的最大连接数. Default:15-->   
<property name="maxPoolSize" value="${maxPoolSize}"/> 
  <!-- 指定连接池中保留的最小连接数-->
   <property name="minPoolSize" value="${minPoolSize}"/>  
    <!-- 指定连接池的初始化连接数  取值应在 minPoolSize 与 maxPoolSize 之间.Default:3-->  
     <property name="initialPoolSize" value="${initialPoolSize}"/>   <!-- 最大空闲时间,60 秒内未使用则连接被丢弃。若为 0 则永不丢弃。 Default:0-->   
     <property name="maxIdleTime" value="${maxIdleTime}"/>   <!-- 当连接池中的连接耗尽的时候 c3p0 一次同时获取的连接数. Default:3--> 
       <property name="acquireIncrement" value="${acquireIncrement}"/>   
       <!-- JDBC 的标准,用以控制数据源内加载的 PreparedStatements 数量。   但由于预缓存的 statements 属于单个 connection 而不是整个连接池所以设置这个参数需 要考虑到多方面的因数.如果 maxStatements 与 maxStatementsPerConnection 均为 0,则缓 存被关闭。Default:0-->   
<property name="maxStatements" value="${maxStatements}"/>   <!-- 每 60 秒检查所有连接池中的空闲连接.Default:0 -->   <property name="idleConnectionTestPeriod" value="${idleConnectionTestPeriod}"/>

dbcp 数据源配置

<!-- 配置 dbcp 数据源--> 
<bean id="myDataSource" class="org.apache.commons.dbcp2.BasicDataSource"> 
     <property name="driverClassName" value="${driver}" /> 
     <property name="url" value="${url}"/> 
     <property name="username" value="${user}"/> 
     <property name="password" value="${password}"/> 
     <!-- 连接池启动时的初始值 -->   
     <property name="initialSize" value="1"/>   
     <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢
释放一部分，一直减少到 maxIdle 为止 -->   
     <property name="maxIdle" value="2"/>   
     <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请一些连接，以避免洪峰来时再申请而造成的性能开销 -->   
 <property name="minIdle" value="1"/>   
</bean>

Spring把JDBC中重复的操作建立成了一个模板类，org.springframework.jdbc.core.JdbcTemplate ，配置文件中加入

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

创建数据库

Spring整合jdbc及其crud操作即增删改查（一）_sql

具体实现

spring-jdbc.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.sxt"></context:component-scan>
    <!--加载properties配置文件-->
    <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>

编写po层，和数据库形成映射

package com.sxt.po;

import java.math.BigDecimal;
import java.util.Date;

public class Account {
    private Integer id;
    private String name;
    private BigDecimal money;
    private Integer userId;
    private Date createTime;
    private Date updateTime;
    private String remark;
    private String type;

    public Account() {
    }

    public Account(String name, BigDecimal money, Integer userId, Date createTime, Date updateTime, String remark, String type) {
        this.name = name;
        this.money = money;
        this.userId = userId;
        this.createTime = createTime;
        this.updateTime = updateTime;
        this.remark = remark;
        this.type = type;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                ", userId=" + userId +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                ", remark='" + remark + '\'' +
                ", type='" + type + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public BigDecimal getMoney() {
        return money;
    }

    public void setMoney(BigDecimal money) {
        this.money = money;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

编写dao层，接口

package com.sxt.dao;

import com.sxt.po.Account;

import java.util.List;

public interface AccountDao {
    /**
     * 添加记录，返回影响的行数
     * @param account
     * @return
     */
    public Integer saveAccount(Account account);

    /**
     * 添加记录，返回主键
     * @param account
     * @return
     */
    public Integer saveAccountHasPrimaryKey(Account account);

    /**
     * 批量添加账户记录，返回受影响的行数
     * @param accounts
     * @return
     */
    public Integer saveAccountsBatch(List<Account> accounts);

    /**
     * 统计账户记录-聚合查询
     * @param userId
     * @return
     */
    public Integer countAccountsByUserId(Integer userId);

    /**
     * 根据Id，查询记录详情
     * @param userId
     * @return
     */
    public Account queryAccountById(Integer userId);

    /**
     * 多条件查询
     * @param userId
     * @param type
     * @param createTime
     * @param aname
     * @return
     */
    public List<Account> queryAccountsByParams(Integer userId,String type,String createTime,String aname);

    /**
     * 更新账户记录
     * @param account
     * @return
     */
    public  Integer updateAccount(Account account);

    /**
     * 批量更新
     * @param accounts
     * @return
     */
    public  Integer updateAccountsBatch(List<Account> accounts);


    /**
     * 根据id 删除记录
     * @param id
     * @return
     */
    public  Integer deleteAccountById(Integer id);

    /**
     * 批量删除，删除的id为一个数组
     * @param ids
     * @return
     */
    public Integer deleteAccountsBatch(Integer[] ids);

}

编写impl层的daoImpl，继承接口，实现方法

package com.sxt.dao.impl;

import com.sxt.dao.AccountDao;
import com.sxt.po.Account;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
@Repository
public class AccountDaoImpl implements AccountDao {
    @Resource
    private JdbcTemplate jdbcTemplate;
    //添加记录，返回影响的行数
    @Override
    public Integer saveAccount(Account account) {
        String sql="insert into account(aname,type,money,user_id,create_time,update_time,remark) " +
                " values(?,?,?,?,?,?,?)";
        return  jdbcTemplate.update(sql,account.getName(),account.getType(),account.getMoney(),account.getUserId(),account.getCreateTime(),account.getUpdateTime(),account.getRemark());
    }
    //添加记录，返回主键
    @Override
    public Integer saveAccountHasPrimaryKey(Account account) {
        String sql="insert into account(aname,type,money,user_id,create_time,update_time,remark) " +
                " values(?,?,?,?,?,?,?)";
        KeyHolder keyHolder=new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps= con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1,account.getName());
                ps.setString(2,account.getType());
                ps.setBigDecimal(3,account.getMoney());
                ps.setInt(4,account.getUserId());
                ps.setObject(5,account.getCreateTime());
                ps.setObject(6,account.getUpdateTime());
                ps.setString(7,account.getRemark());
                return ps;
            }
        }, keyHolder);
        // 返回主键
        return keyHolder.getKey().intValue();
    }


    //批量添加账户记录，返回受影响的行数
    @Override
    public Integer saveAccountsBatch(List<Account> accounts) {
        String sql="insert into account(aname,type,money,user_id,create_time,update_time,remark) " +
                " values(?,?,?,?,?,?,?)";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Account account= accounts.get(i);
                ps.setString(1,account.getName());
                ps.setString(2,account.getType());
                ps.setBigDecimal(3,account.getMoney());
                ps.setInt(4,account.getUserId());
                ps.setObject(5,account.getCreateTime());
                ps.setObject(6,account.getUpdateTime());
                ps.setString(7,account.getRemark());
            }

            @Override
            public int getBatchSize() {
                // 批量添加记录的总条数
                return accounts.size();
            }
        }).length;
    }

    @Override
    public Integer countAccountsByUserId(Integer userId) {
        return null;
    }

    @Override
    public Account queryAccountById(Integer userId) {
        return null;
    }

    @Override
    public List<Account> queryAccountsByParams(Integer userId, String type, String createTime, String aname) {
        return null;
    }

    @Override
    public Integer updateAccount(Account account) {
        return null;
    }

    @Override
    public Integer updateAccountsBatch(List<Account> accounts) {
        return null;
    }

    @Override
    public Integer deleteAccountById(Integer id) {
        return null;
    }

    @Override
    public Integer deleteAccountsBatch(Integer[] ids) {
        return null;
    }
}

借助spring框架的测试类注解，达到简便扫描方法

package com.sxt;

import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-jdbc.xml"})
public class TestBase {

}

继承TestBase

package com.sxt;

import com.sxt.dao.AccountDao;
import com.sxt.po.Account;
import org.junit.Test;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.Date;

public class TestAccountDao extends TestBase {
    @Resource
    private AccountDao accountDao;

    @Test
    public void test01(){
    //BigDecimal.valueOf(8000)  int无法转换
        System.out.println(accountDao.saveAccount(new Account("中国好男人", BigDecimal.valueOf(8000),1,new Date(),new Date(),"男儿志","1")));
    }
}

查询总记录

package com.sxt;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-jdbc.xml"})
public class TestJdbcTemplate {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void test01(){
        String sql = "select count(1) from account";
        Integer total = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println("总记录"+total);
    }

}