In addition to providing IOC and AOP core functions, the Spring JDBC framework provides the following functions: Jdbc-based data access is also provided, making it easier to access persistence layer data. To use the Spring JDBC environment, you first need a Spring integrated JDBC environment.

Add dependent coordinates

Org.springframework spring-context 5.2.4.RELEASE org.springframework spring-test 5.2.4.RELEASE test org.aspectj Aspectjweaver 1.9.5 org.spring Framework Spring – JDBC 5.2.4.RELEASE org.spring Framework Spring -tx 5.2.4.RELEASE mysql Mysql-connector-java 8.0.19com. McHange c3P0 0.9.5.5 Adding a JDBC configuration file Create a JDBC. Properties configuration file in the SRC /main/resources directory. Set the corresponding configuration information driver name jdbc.driver= com.mysql.cj.jdbc.driver

JDBC database connection url = JDBC: mysql: / / localhost: 3306 / (database name)? useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

Jdbc.user =(database account)

Database user password jdbc.password=(Database password) The following configuration is optional

Specifies the number of initial connections for the connection pool. The value must be between minPoolSize and maxPoolSize. Default:3 initialPoolSize=20

Specifies the maximum number of connections to remain in the connection pool. Default:15 maxPoolSize=100

Specifies the minimum number of connections to remain in the connection pool minPoolSize=10

Maximum idle time. If not used within 60 seconds, the connection is discarded. If it is 0, it is never discarded. Default:0 maxIdleTime=600

The number of simultaneous connections c3P0 obtains when the pool runs out of connections. Default:3 acquireIncrement=5

The JDBC standard to control the number of PreparedStatements loaded within the data source. maxStatements=5

Check all idle connections in the connection pool every 60 seconds. Default:0 idleConnectionTestPeriod=60 Changes the Spring configuration file

< context: the property – placeholder location = “JDBC. Properties” / > spring. The XML

<context:component-scan base-package=”com.xxxx” />

<context:property-placeholder location=”jdbc.properties” /> configure data sources; When an application needs to establish a database connection, it simply applies for one from the connection pool and then puts it back. C3P0 or DBCP can be selected

DataBase Connection Pool (DBCP) : DataBase connection pool. Is a Java connection pool project on Apache and a connection pool component used by Tomcat. Using DBCP alone requires two packages: commons-dbcp.jar and commons-pool.jar DBCP. There is no automatic recycling of idle connections.

C3P0 is an open source JDBC connection pool that implements a data source and supports the JDBC3 specification and standard extensions to JDBC2. Currently, open source projects using it include Hibernate, Spring, etc. C3p0 has the function of automatically reclaiming idle connections.

C3P0 Data source configuration

C3P0 Additional configuration (the corresponding values are specified in jdbc.properties) DBCP Data source configuration template class Configuration Spring sets up the repeated operations in JDBC into a template class: Org. Springframework. JDBC. Core. JdbcTemplate. Select connect, right-click “New database”, and set the name and encoding format of the database. Create a table. Use JUnit to test whether the jdbcTemplate bean is retrieved

Public class SpringJdbcTest01 {

@test public void testQueryCount() {ApplicationContext CTX = new ClassPathXmlApplicationContext(“spring.xml”); JdbcTemplate JdbcTemplate = (JdbcTemplate) ctx.getBean(” JdbcTemplate “);

String SQL = "select count(1) from tb_account"; / / perform query operations (no arguments) Integer total = jdbcTemplate. QueryForObject (SQL, Integer. Class); System.out.println(" total :" + total);Copy the code

}

@test public void testQueryCountByUserId() {ApplicationContext CTX = new ClassPathXmlApplicationContext(“spring.xml”); JdbcTemplate JdbcTemplate = (JdbcTemplate) ctx.getBean(” JdbcTemplate “);

String SQL = "select count(1) from tb_account where user_id =?" ; / / perform query operations (parameters) Integer total = jdbcTemplate. QueryForObject (SQL, the Integer class, 1); System.out.println(" total :" + total);Copy the code

}} Public class SpringJdbcTest02 {

private JdbcTemplate jdbcTemplate;

@ Before public void init () {/ / get the Spring context ApplicationContext ac = new ClassPathXmlApplicationContext (” Spring. XML “); JdbcTemplate = (JdbcTemplate) ac.getBean(” JdbcTemplate “); }

@test public void testQueryCount() {// Define SQL statement String SQL = “select count(1) from tb_account”; / / perform query operations (no arguments) Integer total = jdbcTemplate. QueryForObject (SQL, Integer. Class); System.out.println(” total :” + total); }

@test public void testQueryCountByUserId() {// Define SQL statement String SQL = “select count(1) from tb_account where user_id = ?” ; / / perform query operations (parameters) Integer total = jdbcTemplate. QueryForObject (SQL, the Integer class, 1); System.out.println(” total :” + total); }} annotation encapsulation @ RunWith is a runner @ RunWith junit 4 (takeup. Class) is used to run @ RunWith (SpringJUnit4ClassRunner. Class) make a test run on Spring test environment @contextConfiguration Spring uses annotations to introduce multiple configuration files when integrating JUnit4 testing. @contextConfiguration (Locations= “CLASspath: The applicationcontext.xml “) @ ContextConfiguration (locations = {” classpath: spring XML “, “The classpath: bean. XML”}) @ RunWith (SpringJUnit4ClassRunner. Class) / / the junit test to the spring environment @ ContextConfiguration (locations = {” classpath:spring.xml “}) // Set the resource file to load public class SpringJdbcTest03 {

@Resource private JdbcTemplate jdbcTemplate;

@test public void testQueryCount() {// Define SQL statement String SQL = “select count(1) from tb_account”; / / perform query operations (no arguments) Integer total = jdbcTemplate. QueryForObject (SQL, Integer. Class); System.out.println(” total :” + total); }} Generic encapsulation defines a parent class that sets common configuration information

/ * *

Common test environment, You need to use the environment directly derived classes can * / @ RunWith (SpringJUnit4ClassRunner. Class) / / the junit test to the spring environment @ ContextConfiguration (locations = {” classpath:spring.xml “}) // Set the resource file to load. Public Class BaseTest {} inherits the common test classes

public class SpringJdbcTest04 extends BaseTest {

@Resource private JdbcTemplate jdbcTemplate;

@test public void testQueryCount() {// Define SQL statement String SQL = “select count(1) from tb_account”; / / perform query operations (no arguments) Integer total = jdbcTemplate. QueryForObject (SQL, Integer. Class); System.out.println(” total :” + total); When the Spring Jdbc environment is integrated, Spring Jdbc is used to perform the account single table CRUD operations.

The Account interface method definition defines the entity class Account.java

package com.xxxx.entity;

import java.util.Date;

/ * *

User Account */ public class Account {

private Integer accountId; // Account ID, primary key private String accountName; // Account name private String accountType; // Account type private Double money; // account amount private String remark; // Account Remarks Private Integer userId; // User ID, private Date createTime; Private Date updateTime; // Change the time

public Account() {

}

public Account(String accountName, String accountType, Double money, String remark, Integer userId) { this.accountName = accountName; this.accountType = accountType; this.money = money; this.remark = remark; this.userId = userId; }

@override public String toString() {return “Account{” +” accountId= “+ accountId +”, AccountName = “+ accountName +” + “, accountType= “+ accountType +” + “, money= “+ money +”, Remark = “+ remark +” + “, userId= “+ userId +”, createTime= “+ createTime +”, updateTime= “+ updateTime + ‘} ‘; }

public Integer getAccountId() { return accountId; }

public void setAccountId(Integer accountId) { this.accountId = accountId; }

public String getAccountName() { return accountName; }

public void setAccountName(String accountName) { this.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 String getRemark() { return remark; }

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

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; }} Define the interface class iAccountDao.java

package com.xxxx.dao;

import com.xxxx.entity.Account; import java.util.List;

/ * *

User module interface definition

  1. Add Account Add Account Record, return the number of affected rows Add account Record, return the primary key of the record Add Account Record batch, return the number of affected rows
  2. Querying accounts Querying the total number of accounts of a specified user, returns the number of records Querying the details of the specified account records, Returns the account object Multiple Criteria Querying the account list of a specified user, returns the account set
  3. Update account Update account records to return the number of affected rows Update account records in batches to return the number of affected rows
  4. Delete account Delete account records and return the number of affected rows Delete account records in batches and return the number of affected rows

*/ public interface IAccountDao {

/ * *

  • Add account
  • Add an account record that returns the number of affected rowsCopy the code
  • @param account
  • @return

*/ public int addAccount(Account account) ;

/ * *

  • Add account
  • Add an account record and return the primary key of the recordCopy the code
  • @param account
  • @return

*/ public int addAccountHasKey(Account account);

/ * *

  • Add account
  • Add account records in batches and return the number of affected rowsCopy the code
  • @param accounts
  • @return

*/ public int addAccountBatch(List accounts);

/ * *

  • Query account
  • Query the total number of records of a specified userCopy the code
  • @param userId
  • @return

*/ public int queryAccountCount(Integer userId);

/ * *

  • Query account
  • Query the details of a specified account record and return the account objectCopy the code
  • @param accountId
  • @return

*/ public Account queryAccountById(Integer accountId);

/ * *

  • Query account
  • Multi-criteria query the account list of a specified userCopy the code
  • @param userId
  • @param accountName
  • @param accountType
  • @param createTime
  • @return

*/ public List queryAccountsByParams(Integer userId, String accountName, String accountType, String createTime);

/ * *

  • Update account
  • Update the account record to return the number of affected rowsCopy the code
  • @param account
  • @return

*/ public int updateAccountById(Account account);

/ * *

  • Update account
  • Update account records in batches, returning the number of affected rowsCopy the code
  • @param accounts
  • @return

*/ public int updateAccountBatch(List accounts);

/ * *

  • Delete the account
  • Deletes the account record and returns the number of affected rowsCopy the code
  • @param accountId
  • @return

*/ public Integer deleteAccoutById(Integer accountId);

/ * *

  • Delete user
  • Delete account records in batches and return the number of affected rowsCopy the code
  • @param ids
  • @return

*/ public int deleteAccountBatch(Integer[] ids); } Define the interface implementation class package com.XXXX.dao.impl;

import com.xxxx.dao.IAccountDao; import com.xxxx.entity.Account; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Component; import javax.annotation.Resource; import java.util.List;

/ * *

*/ @repository Public class AccountDaoImpl implements IAccountDao {

@resource Private JdbcTemplate JdbcTemplate;

@Override public int addAccount(Account account) { return 0; }

@Override public int addAccountHasKey(Account account) { return 0; }

@Override public int addAccountBatch(List accounts) { return 0; }

@Override public int queryAccountCount(Integer userId) { return 0; }

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

@Override public List queryAccountsByParams(Integer userId, String accountName, String accountType, String createTime) { return null; }

@Override public int updateAccountById(Account account) { return 0; }

@Override public int updateAccountBatch(List accounts) { return 0; }

@Override public Integer deleteAccoutById(Integer accountId) { return null; }

@Override public int deleteAccountBatch(Integer[] ids) { return 0; In the development of enterprise projects, records may be added in a variety of ways, such as adding a single record or adding multiple records in batches. There are three ways to add account records: adding a single record returns the number of affected rows, adding a single record returns the primary key, and adding multiple records in batches.

Add account record /**

Add a single record, Return number of affected rows @param Account @return / @Override public int addAccount(Account Account) {String SQL = “Insert into Tb_account (account_name,account_type,money,remark, “+” user_id,create_time,update_time) values (? ,? ,? ,? ,? , now (), now ()); Object[] objs = {account.getAccountName(),account.getAccountType(), account.getMoney(),account.getRemark(),account.getUserId()}; return jdbcTemplate.update(sql,objs); } Test method /*

Add account records, */ @test public void testAddAccount() {// Prepare data to add Account Account = new Account(” zhang “, “CCB”,100.0, “pocket money”,1);

Int row = accountDao.addAccount(Account); int row = accountDao.addAccount(account); System.out.println(” Add account affected rows: “+ row); } Add record returns primary key /**

Add a single record and return the primary key

@param account

@return */ @override public int addAccountHasKey(Account Account) {String SQL = “Insert into Tb_account (account_name,account_type,money,remark, “+” user_id,create_time,update_time) values (? ,? ,? ,? ,? , now (), now ()); KeyHolder keyHolder = new GeneratedKeyHolder(); Jdbctemplate. update(connection -> {// precompile SQL statement, And set to return to the primary key PreparedStatement ps = connection. The prepareStatement (SQL Statement. The RETURN_GENERATED_KEYS); // Set ps.setString(1,account.getAccountName()); ps.setString(2,account.getAccountType()); ps.setDouble(3,account.getMoney()); ps.setString(4,account.getRemark()); ps.setInt(5,account.getUserId()); return ps; },keyHolder); // Return the primary key Integer key = keyholder.getKey ().intValue();

return key; } Test method

/ * *

Add account records, Return primary key */ @test public void testAddAccountHasKey() {// Prepare the data to be added.

/ / call object to add method, return to the primary key int key = accountDao. AddAccountHasKey (account); System.out.println(” Add primary key returned by account: “+ key); } Add account records in batches /**

Add multiple records and return the number of affected rows

@param accounts

@return */ @override public int addAccountBatch(Final List Accounts) {String SQL = “Insert into Tb_account (account_name,account_type,money,remark, “+” user_id,create_time,update_time) values (? ,? ,? ,? ,? , now (), now ()); int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement preparedStatement, Int I) throws SQLException {/ / set parameters preparedStatement. SetString (1, accounts. The get (I). GetAccountName ()); preparedStatement.setString(2,accounts.get(i).getAccountType()); preparedStatement.setDouble(3,accounts.get(i).getMoney()); preparedStatement.setString(4,accounts.get(i).getRemark()); preparedStatement.setInt(5,accounts.get(i).getUserId()); } @Override public int getBatchSize() { return accounts.size(); } }).length;

return rows; } Test method

/ * *

Batch add data, Return the number of affected rows */ @test public void testAddAccountBatch() {// Prepare data to be added Account Account = new Account(” Wang Wu “, “Agricultural Bank of China”,2000.0, “Salary”,3); Account account2 = new Account(” zhao Liu “, “Bank of China”,280.0, “bonus”,3); Account account3 = new Account(” tianqi “, “ICBC”,800.0, “pocket money”,3); List accountList = new ArrayList<>(); accountList.add(account); accountList.add(account2); accountList.add(account3);

/ / call object to add method, return to the primary key int rows = accountDao. AddAccountBatch (accountList); System.out.println(” batch add account affected rows: “+ rows); } Account record query Implement account record query There are three query methods: query the number of all account records of a specified user, query the details of a single account record, and query the account records of a specified user by multiple criteria.

Query the total number of user account records /**

Query the total number of accounts for a specified user, @param userId @return / @override public int queryAccountCount(Integer userId) {String SQL = “select count(1) from accountCount (1) From tb_account where user_id =?” ; int count = jdbcTemplate.queryForObject(sql,Integer.class,userId); return count; } Test method /*

Query the total number of records in a user’s account, Returns the total number of records / @ Test public void testQueryAccountCount () {/ / query ID of 1 user account to the total number of records int total = accountDao. QueryAccountCount (1); System.out.println(” total records: “+ total); } select * from user where user = ‘user’; Return the Account object @param accountId @return / @Override Public Account queryAccountById(Integer accountId) {String SQL = “SELECT * From tb_account where account_id =?” ; Account account = jdbcTemplate.queryForObject(sql, new Object[]{accountId}, (resultSet, i) -> { Account acc = new Account(); Acc. SetAccountId (resultSet. Get int (” account_id “)); Acc. SetMoney (resultSet. GetDouble (” money “)); Acc. SetAccountName (resultSet. Get string (” account_name “)); Acc. SetAccountType (resultSet. Get string (” account_type “)); Acc. SetRemark (resultSet. Get string (” remark “)); Acc. SetCreateTime (resultSet. GetDate (” create_time “)); Acc. SetUpdateTime (resultSet. GetDate (” update_time “)); Acc. SetUserId (resultSet. Get int (” user_id “)); return acc; }); return account; } Test method /*

To query the record details of a specified account, Returns the Account / @ Test public void testQueryAccountById () {/ / query with ID 1 Account records details of the Account Account = accountDao. QueryAccountById (1); System.out.println(” account details: “+ account.toString()); } query user account records with multiple conditions /*

Multi-criteria query the account list of a specified user

@param userId userId

@param accountName accountName (fuzzy query)

@param accountType accountType

@param createTime Time when the account was created

@return */ @Override public List queryAccountsByParams(Integer userId, String accountName, String accountType, String createTime) { String sql = “select * from tb_account where user_id = ? “; List params = new ArrayList<>(); params.add(userId);

// If the account name is not empty, If (stringutils.isnotBlank (accountName)) {SQL += “and account_name like concat(‘ % ‘,? , ‘%’);” params.add(accountName); If (stringutils.isnotBlank (accountType)) {SQL += “and account_type =? “; params.add(accountType); If (stringutils. isNotBlank(createTime)) {SQL += “and create_time >? “; params.add(createTime); }

Object[] objs = params.toarray ();

List accountList = jdbcTemplate.query(sql, objs, (resultSet, rowNum) -> { Account acc = new Account(); Acc. SetAccountId (resultSet. Get int (” account_id “)); Acc. SetMoney (resultSet. GetDouble (” money “)); Acc. SetAccountName (resultSet. Get string (” account_name “)); Acc. SetAccountType (resultSet. Get string (” account_type “)); Acc. SetRemark (resultSet. Get string (” remark “)); Acc. SetCreateTime (resultSet. GetDate (” create_time “)); Acc. SetUpdateTime (resultSet. GetDate (” update_time “)); Acc. SetUserId (resultSet. Get int (” user_id “)); return acc; });

return accountList; } Test method

/ * *

Query user account records with multiple conditions, Return an account set */ @test public void testQueryAccountByParams(){// query the accountList of a user. List accountList = accountDao.queryAccountsByParams(3,null,null,null); / / query the user account List by specifying the conditions accountList02 = accountDao. QueryAccountsByParams (3, “zhang”, null, null);

System.out.println(accountList.toString()); System.out.println(accountList02.toString()); } Update the account record to update the account record

Updates the specified account record to return the number of affected rows

@param account

@return */ @Override public int updateAccountById(Account account) { String sql = “update tb_account set account_name = ? , account_type = ? , ” + ” money = ? ,remark = ? ,user_id = ? ,update_time = now() ” + ” where account_id = ? “; Object[] objs = {account.getAccountName(),account.getAccountType(), account.getMoney(), account.getRemark(),account.getUserId(), account.getAccountId()}; return jdbcTemplate.update(sql,objs); } Test method

/ * *

Update specified account records, Return the number of affected rows / @test public void testUpdateAccount(){// Prepare the data to be modified Account Account = new Account(” Zhang SAN 1 “, “Construction Bank 1”,500.0, “Double pocket money”,1); account.setAccountId(1); int row = accountDao.updateAccountById(account); System.out.println(” Change account returns number of affected rows: “+ row); } update account records /* Update account records Return the number of affected rows @param accounts @return / @Override public int updateAccountBatch(List accounts) {String SQL = “update tb_account set account_name = ? , account_type = ? , ” + ” money = ? ,remark = ? ,user_id = ? ,update_time = now() ” + ” where account_id = ? “; int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, Int I) throws SQLException {// Set parameter ps.setString(1,accounts.get(I).getAccountName()); ps.setString(2,accounts.get(i).getAccountType()); ps.setDouble(3,accounts.get(i).getMoney()); ps.setString(4,accounts.get(i).getRemark()); ps.setInt(5,accounts.get(i).getUserId()); ps.setInt(6,accounts.get(i).getAccountId()); } @Override public int getBatchSize() { return accounts.size(); } }).length; return rows; } Test method /*

Update account records in bulk, Return the number of affected rows */ @test public void testUpdateAccountBatch(){// Prepare data to be modified Account Account = new Account(” A3 “, “CCB 3”,300.0, “Double pocket money 3″,3); account.setAccountId(3); Account account2 = new Account(” a4 “, “CCB 4”,400.0, “double pocket money 4”,3); account2.setAccountId(4); List accountList = new ArrayList<>(); accountList.add(account); accountList.add(account2);

int rows = accountDao.updateAccountBatch(accountList); System.out.println(” Batch modify account record return number of affected rows: “+ rows); } Delete account record to delete account record /**

Deletes the account record and returns the number of affected rows

@param accountId

@return */ @Override public Integer deleteAccoutById(Integer accountId) { String sql = “delete from tb_account where account_id= ? “; Object[] objs = {accountId}; return jdbcTemplate.update(sql,objs); } Test method

/ * *

Delete the account records, returns the number of rows affected / @ Test public void testDeleteAccount () {/ / delete the record with ID 1 account int row = accountDao. DeleteAccoutById (1); System.out.println(” Delete account record returns number of affected rows: “+ row); } Delete account records in batches /*

Delete account records in batches and return the number of affected rows

@param ids

@return */ @override public int deleteAccountBatch(Integer[] IDS) {String SQL = “Delete from TB_account WHERE Account_id =?” ; int row = 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 row; } Test method

/ * *

*/ @test public void testDeleteAccountBatch(){// delete account records with multiple ids Integer[] ids = new Integer[]{2,3}; int rows = accountDao.deleteAccountBatch(ids); System.out.println(” Batch delete account records return number of affected rows: “+ rows); }