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

操作即增删改查（一）"/>

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

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.apachemons.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.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""xmlns:xsi=""xmlns:context=""xsi:schemaLocation="://www.springframework/schema/beans/spring-beans.xsd://www.springframework/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;}@Overridepublic 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 {@Resourceprivate JdbcTemplate jdbcTemplate;//添加记录，返回影响的行数@Overridepublic 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());}//添加记录，返回主键@Overridepublic 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() {@Overridepublic 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();}//批量添加账户记录，返回受影响的行数@Overridepublic 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() {@Overridepublic 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());}@Overridepublic int getBatchSize() {// 批量添加记录的总条数return accounts.size();}}).length;}@Overridepublic Integer countAccountsByUserId(Integer userId) {return null;}@Overridepublic Account queryAccountById(Integer userId) {return null;}@Overridepublic List<Account> queryAccountsByParams(Integer userId, String type, String createTime, String aname) {return null;}@Overridepublic Integer updateAccount(Account account) {return null;}@Overridepublic Integer updateAccountsBatch(List<Account> accounts) {return null;}@Overridepublic Integer deleteAccountById(Integer id) {return null;}@Overridepublic 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 {@Resourceprivate AccountDao accountDao;@Testpublic 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 {@Resourceprivate JdbcTemplate jdbcTemplate;@Testpublic void test01(){String sql = "select count(1) from account";Integer total = jdbcTemplate.queryForObject(sql,Integer.class);System.out.println("总记录"+total);}}