导入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xxx</groupId>
<artifactId>SpringJdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<name>SpringJdbc</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- //引入jdbc的包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<!-- //引入mysql数据库驱动包-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.4.RELEASE</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
</dependencies>
<build>
</build>
</project>
实体类
package com.xxx.po;
import java.util.Date;
/**
* @Author 陈平安
* @Date 2022/6/20 16:58
* @PackageName:com.xxx.po
* @ClassName: Account
* @Description: TODO
* @Version 1.0
*/
public class Account {
Integer accountid;//账户id
String AccountName;//账户名称
String accountType;//账户 类别
Double money;//
Date createDate;//
Date updateDate;//
Integer userID;//
public Account() {
}
public Account(String accountName, String accountType, Double money, Integer userID) {
AccountName = accountName;
this.accountType = accountType;
this.money = money;
this.userID = userID;
}
@Override
public String toString() {
return "Account{" +
"accountid=" + accountid +
", AccountName='" + AccountName + '\'' +
", accountType='" + accountType + '\'' +
", money=" + money +
", createDate=" + createDate +
", updateDate=" + updateDate +
", userID=" + userID +
'}';
}
public Integer getAccountid() {
return accountid;
}
public void setAccountid(Integer accountid) {
this.accountid = accountid;
}
public String getAccountName() {
return AccountName;
}
public void setAccountName(String accountName) {
AccountName = accountName;
}
public String getAccountType() {
return accountType;
}
public void setAccountType(String accountType) {
this.accountType = accountType;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Date getUpdateDate() {
return updateDate;
}
public void setUpdateDate(Date updateDate) {
this.updateDate = updateDate;
}
public Integer getUserID() {
return userID;
}
public void setUserID(Integer userID) {
this.userID = userID;
}
}
db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8
jdbc.user=root
jdbc.password=cjd
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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/task/spring-task-4.1.xsd">
<!-- http://www.springframework.org/schema/tx-->
<context:component-scan base-package="com.xxx"></context:component-scan>
<!-- <task:annotation-driven></task:annotation-driven>-->
<context:property-placeholder location="jdbc.properties"></context:property-placeholder>
<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>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!--注入数据源-->
<property name="dataSource" ref="dataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="add" propagation="REQUIRED"/>
<tx:method name="update" propagation="REQUIRED"/>
<tx:method name="delete" propagation="REQUIRED"/>
<tx:method name="query" read-only="true"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="cut" expression="execution(* com.xxx.service..*.*(..))"/>
</aop:config>
</beans>
10个方法接口:
package com.xxx.Dao;
import com.xxx.po.Account;
import java.util.List;
/**
* @Author 陈平安
* @Date 2022/6/20 17:02
* @PackageName:com.xxx.Dao
* @ClassName: IAaccount
* @Description: TODO
* @Version 1.0
*/
public interface IAaccount {
public int addAccount(Account account);
public int addAccountHasKey(Account account);
public int addAccountBath(List<Account> accounts);
public int queryAccount(int userID);
public Account queryAccountID(int accountid);
public List<Account> queryAccountParmy(Integer userID, String accountName, String
accountType);
public int UpdateAccount(Account account);
public int updateAccountBath(List<Account> accounts);
public int deleteAccount(int accountid);
public int deleteAccountBatch(Integer[] ids);
public int outAccount(Integer accountId, Double money);
public int inAccount(Integer accountId, Double money);
}
package com.xxx.Dao.impl;
import com.xxx.Dao.IAaccount;
import com.xxx.po.Account;
import org.apache.commons.lang.StringUtils;
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.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author 陈平安
* @Date 2022/6/20 17:24
* @PackageName:com.xxx.impl
* @ClassName: ImpleAccount
* @Description: TODO
* @Version 1.0
*/
@Repository
public class ImpleAccount implements IAaccount {
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public int addAccount(Account account) {
String sql = "insert into tb_account (account_name,account_type,money,user_id) values (?,?,?,?)";
Object[] objects = {account.getAccountName(), account.getAccountType(),
account.getMoney(), account.getUserID()};
int nows = jdbcTemplate.update(sql, objects);
return nows;
}
@Override
public int addAccountHasKey(Account account) {
String sql = "insert into tb_account (account_name,account_type,money,user_id) values (?,?,?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, account.getAccountName());
ps.setString(2, account.getAccountType());
ps.setDouble(3, account.getMoney());
ps.setInt(4, account.getUserID());
return ps;
}, keyHolder);
int key = keyHolder.getKey().intValue();
return key;
}
@Override
public int addAccountBath(List<Account> accounts) {
String sql = "insert into tb_account (account_name,account_type,money,user_id) values (?,?,?,?)";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Account account = accounts.get(i);
ps.setString(1, account.getAccountName());
ps.setString(2, account.getAccountType());
ps.setDouble(3, account.getMoney());
ps.setInt(4, account.getUserID());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return rows;
}
@Override
public int queryAccount(int userID) {
String sql = "select count(1) from tb_account where user_id = ?";
int count = jdbcTemplate.queryForObject(sql, Integer.class, userID);
return count;
}
@Override
public Account queryAccountID(int accountid) {
String sql = "select * from tb_account where account_id =?";
Account account = jdbcTemplate.queryForObject(sql, (resultSet, i) -> {
Account account1 = new Account();
account1.setAccountid(accountid);
account1.setAccountName(resultSet.getString("account_name"));
account1.setAccountType(resultSet.getString("account_type"));
account1.setMoney(resultSet.getDouble("money"));
account1.setUserID(resultSet.getInt("user_id"));
return account1;
}, accountid);
return account;
}
@Override
public List<Account> queryAccountParmy(Integer userID, String accountName,
String accountType) {
String sql = "select * from tb_account where user_id = ?";
List<Object> list = new ArrayList<>();
list.add(userID);
if (StringUtils.isNotBlank(accountName)) {
sql += " and account_name like concat('%',?,'%')";
list.add(accountName);
}
if (StringUtils.isNotBlank(accountType)) {
sql += " and account_type = ?";
list.add(accountType);
}
Object[] objects = list.toArray();
List<Account> accountList = jdbcTemplate.query(sql, objects, (ResultSet resultSet, int i) -> {
Account account1 = new Account();
account1.setAccountid(resultSet.getInt("user_id"));
account1.setAccountName(resultSet.getString("account_name"));
account1.setAccountType(resultSet.getString("account_type"));
account1.setMoney(resultSet.getDouble("money"));
account1.setUserID(resultSet.getInt("user_id"));
return account1;
});
return accountList;
}
@Override
public int UpdateAccount(Account account) {
String sql = "update tb_account set account_name = ? ,account_type = ?,money = ? ";
Object[] objects = {account.getAccountName(), account.getAccountType(), account.getMoney()};
int rows = jdbcTemplate.update(sql, objects);
return rows;
}
/**
* @param accounts
* @return
*/
@Override
public int updateAccountBath(List<Account> accounts) {
String sql = "update tb_account set account_name = ? ,account_type = ?,money = ? ";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Account account = accounts.get(i);
ps.setString(1, account.getAccountName());
ps.setString(2, account.getAccountType());
ps.setDouble(3, account.getMoney());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return rows;
}
@Override
public int deleteAccount(int accountid) {
String sql = "delete from tb_account where account_id = ?";
int row = jdbcTemplate.update(sql, accountid);
return row;
}
@Override
public int deleteAccountBatch(Integer[] ids) {
String sql = "delete from tb_account where account_id = ?";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1, ids[i]);
}
@Override
public int getBatchSize() {
return ids.length;
}
}).length;
return rows;
}
@Override
public int outAccount(Integer accountId, Double money) {
String sql = "update tb_account set money = money - ? where account_id = ?";
Object[] objects = {money,accountId};
return jdbcTemplate.update(sql,objects);
}
@Override
public int inAccount(Integer accountId, Double money) {
String sql = "update tb_account set money = money + ? where account_id = ?";
Object[] objects = {money,accountId};
return jdbcTemplate.update(sql,objects);
}
}
测试:
package com.xxx.Test;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* @Author 陈平安
* @Date 2022/6/20 16:46
* @PackageName:com.xxx.Test
* @ClassName: test01
* @Description: TODO
* @Version 1.0
*/
public class test01 {
@Test
public void testjdbc(){
ApplicationContext ac =new ClassPathXmlApplicationContext("spring.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
String sql = "select count(1) from tb_account";
Integer integer = jdbcTemplate.queryForObject(sql,Integer.class);
System.out.println(integer);
}
}
结果:
测试案例的进一步封装:
package com.xxx.Test;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* @Author 陈平安
* @Date 2022/6/20 16:46
* @PackageName:com.xxx.Test
* @ClassName: test01
* @Description: TODO
* @Version 1.0
*/
public class test02 {
private JdbcTemplate jdbcTemplate;
@Before
public void init() {
ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
}
@Test
public void testjdbc() {
String sql = "select count(1) from tb_account";
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(integer);
}
}
测试案例的再一步封装:
package com.xxx.Test;
import com.xxx.Dao.IAaccount;
import com.xxx.po.Account;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.annotation.Resource;
/**
* @Author 陈平安
* @Date 2022/6/20 16:46
* @PackageName:com.xxx.Test
* @ClassName: test01
* @Description: TODO
* @Version 1.0
*/
public class test03 extends BaseTest{
@Resource
private JdbcTemplate jdbcTemplate;
@Resource
private IAaccount iAaccount;
@Before
public void init() {
ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
}
@Test
public void testjdbc() {
String sql = "select count(1) from tb_account";
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(integer);
}
@Test
public void test() {
Account account = new Account("account03", "jianhang03", 3000.0, 3);
int row = iAaccount.addAccount(account);
System.out.println(row);
}
}
package com.xxx.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* @Author 陈平安
* @Date 2022/6/20 16:55
* @PackageName:com.xxx.Test
* @ClassName: BaseTest
* @Description: TODO
* @Version 1.0
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class BaseTest {
}
运行结果:
以下是10个方法的测试案例:
package com.xxx.Test;
import com.xxx.Dao.IAaccount;
import com.xxx.po.Account;
import com.xxx.service.AccountService;
import org.junit.Test;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
/**
* @Author 陈平安
* @Date 2022/6/21 10:36
* @PackageName:com.xxx.Test
* @ClassName: AddTest01
* @Description: TODO
* @Version 1.0
*/
public class AddTest01 extends BaseTest {
@Resource
private IAaccount iAaccount;
@Test
public void test() {
Account account = new Account("account03", "jianhang03", 3000.0, 3);
int row = iAaccount.addAccount(account);
System.out.println(row);
}
@Test
public void test01() {
Account account = new Account("account05", "jianhang04", 30001.0, 4);
int key = iAaccount.addAccountHasKey(account);
System.out.println(key);
}
@Test
public void test02() {
Account account = new Account("account05", "jianhang04", 30001.0, 4);
Account account2 = new Account("account06", "jianhang06", 30006.0, 6);
Account account3 = new Account("account07", "jianhang07", 30007.0, 7);
List<Account> list = new ArrayList<>();
list.add(account);
list.add(account2);
list.add(account3);
int key = iAaccount.addAccountBath(list);
System.out.println("数值======" + key);
}
@Test
public void test03() {
// Account account = new Account("account05", "jianhang04", 30001.0, 4);
int key = iAaccount.queryAccount(0);
System.out.println("数值1======" + key);
}
@Test
public void test04() {
Account account = iAaccount.queryAccountID(1);
// int key = iAaccount.queryAccount(0);
System.out.println("数值1======" + account.toString());
}
@Test
public void test05() {
List<Account> accountList = iAaccount.queryAccountParmy(1, "account01", null);
System.out.println(accountList.toString());
// int key = iAaccount.queryAccount(0);
//System.out.println("数值1======"+account.toString());
}
@Test
public void test06() {
Account account = new Account("account05000", "jianhang04", 30001.0, 4);
account.setAccountid(1);
int key = iAaccount.UpdateAccount(account);
System.out.println("数值1======" + key);
}
@Test
public void test07() {
Account account = new Account("account05", "jianhang04", 30001.0, 4);
account.setAccountid(1);
Account account2 = new Account("account06", "jianhang06", 30006.0, 6);
account.setAccountid(2);
Account account3 = new Account("account07", "jianhang07", 30007.0, 7);
account.setAccountid(3);
List<Account> list = new ArrayList<>();
list.add(account);
list.add(account2);
list.add(account3);
int key = iAaccount.updateAccountBath(list);
System.out.println("数值1======" + key);
}
@Test
public void test08() {
Integer[] id = {2, 3, 4};
int key = iAaccount.deleteAccountBatch(id);
System.out.println("数值1======" + key);
}
@Resource
private AccountService accountService;
@Test
public void test09() {
int code = accountService.updateAccontByTranfer(10, 11, 100.0);
if (code == 1){
System.out.println("success");
}else {
System.out.println("fail");
}
}
}
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
在此更正一i下配置文件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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/task/spring-task-4.1.xsd">
<!-- http://www.springframework.org/schema/tx-->
<context:component-scan base-package="com.xxx"></context:component-scan>
<!-- <task:annotation-driven></task:annotation-driven>-->
<context:property-placeholder location="jdbc.properties"></context:property-placeholder>
<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>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!--注入数据源-->
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>-->
<!-- <tx:advice id="txAdvice" transaction-manager="transactionManager">-->
<!-- <tx:attributes>-->
<!-- <tx:method name="add" propagation="REQUIRED"/>-->
<!-- <tx:method name="update" propagation="REQUIRED"/>-->
<!-- <tx:method name="delete" propagation="REQUIRED"/>-->
<!-- <tx:method name="query" read-only="true"/>-->
<!-- </tx:attributes>-->
<!-- </tx:advice>-->
<aop:config>
<aop:pointcut id="cut" expression="execution(* com.xxx.service..*.*(..))"/>
</aop:config>
</beans>
数据库表: