The content of the content
1. Database connection pool
* concept and function
* C3P0
* Druid
* Custom JDBC tool class
2. Spring JDBC : JDBC Template
3. Transaction control in Srping
##Database connection pool
1. Concept: in fact, it is a container (Collection) for database connection.
After the system is initialized, the container is created and some connection objects are applied in the container. When the user comes to access the database, the connection objects are obtained from the container. After the user accesses the database, the connection objects are returned to the container.
2. Function:
1. Save resources
2. Efficient user access
3. Implementation:
1. Standard interface: under DataSource javax.sql package
1. Method:
* get connection: getConnection()
* return Connection: Connection.close(). If the Connection object Connection is obtained from the Connection pool, the Connection will not be closed again if the Connection.close() method is called. It's a return Connection
2. Generally, we don't implement it. We have database manufacturers to implement it
1. C3P0: database connection pool technology
2. Druid: database connection pool implementation technology, provided by Alibaba
4. C3P0: database connection pool technology
* step:
1. Import the jar package (two) c3p0-0.9.5.2.jar and mchange-commons-java-0.2.12.jar,
* Note: do not forget to import the database driver jar package
2. Define configuration file:
* Name: c3p0.properties or c3p0-config.xml
* path: directly put the file in the src directory.
3. Create core object: database connection pool object new ComboPooledDataSource()
4. Get connection: getConnection
* Code:
//1. Create database connection pool object DataSource ds = new ComboPooledDataSource(); //2. Get connection object Connection conn = ds.getConnection();
5. Druid: database connection pool implementation technology, provided by Alibaba
* step:
1. Import the jar package druid-1.0.9.jar
2. Define configuration file:
* in the form of properties
* it can be called any name and placed in any directory
3. Load the configuration file. Properties
4. Get database connection pool object: get druiddatasourcefactory.createdatesource (property parameter name) through the factory
5. Get connection: get DataSource.getConnection through database connection pool
* Code:
//3. Load configuration file
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//4. Get connection pool object
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//5. Get connection
Connection conn = ds.getConnection();6. Spring's built-in data source
7. Define JDBC tool class
1. Define a class of JDBC utils
2. Provide static code block to load configuration file and initialize connection pool object
3. Method of provision
1. Get connection method: get connection through database connection pool
2. Release resources
3. How to get the connection pool
* Code:
public class JDBCUtils {
//1. Define the member variable DataSource
private static DataSource ds ;
static{
try {
//1. Load configuration file
Properties pro = new Properties();
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
//2. Get DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Get connection
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* Release resources
*/
public static void close(Statement stmt,Connection conn){
/* if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();//Return connection
} catch (SQLException e) {
e.printStackTrace();
}
}*/
close(null,stmt,conn);
}
public static void close(ResultSet rs , Statement stmt, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();//Return connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Get connection pool method
*/
public static DataSource getDataSource(){
return ds;
}
}
## Spring JDBC
* concept: simple encapsulation of JDBC by Spring framework. Provides a JDBC template object to simplify the development of JDBC
* procedure:
1. Import jar package
* spring-jdbc-5.0.2.RELEASE.jar
* spring-tx-5.0.2.release.jar (transaction related)
2. Create the JdbcTemplate object. Data source dependent
* JdbcTemplate template = new JdbcTemplate(DataSource ds);
3. Call the method of JdbcTemplate to complete CRUD operation
* update(): execute DML statement. Add, delete and change statements
* queryForMap(): query result encapsulates result set as map set, column name as key, value as value encapsulates this record as a map set
* Note: the result set length of this method query can only be 1
* queryForList(): query result encapsulates result set as list set
* Note: encapsulate each record into a Map set, and then load the Map set into the List set
* query(): query results, encapsulating the results as JavaBean objects
* query parameter: RowMapper
* in general, we use BeanPropertyRowMapper to implement classes. Automatic encapsulation of data into JavaBean s
* new beanpropertyrowmapper < type > (type. class)
* queryForObject: query result, encapsulating the result as an object
* Note: generally used for queries of aggregate functions
4. Practice:
* requirements:
1. Modify the salary of No.1 data to 10000
2. Add a record
3. Delete the record just added
4. Query the record with id 1 and encapsulate it as a Map set
5. Query all records and encapsulate them as a List
6. Query all records and encapsulate them as a List set of Emp objects
7. Total records queried
* Code:
public class JdbcTemplateDemo2 {
//Junit unit test, which allows methods to execute independently
//1. Get the JDBC template object
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
/**
* 1. Modify the salary of No.1 data to 10000
*/
@Test
public void test1(){
//2. definition of sql
String sql = "update emp set salary = 10000 where id = 1001";
//3. implementation of sql
int count = template.update(sql);
System.out.println(count);
}
/**
* 2. Add a record
*/
@Test
public void test2(){
String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
int count = template.update(sql, 1015, "Guo Jing", 10);
System.out.println(count);
}
/**
* 3.Delete the record you just added
*/
@Test
public void test3(){
String sql = "delete from emp where id = ?";
int count = template.update(sql, 1015);
System.out.println(count);
}
/**
* 4.Query the record with id 1001 and encapsulate it as a Map set
* Note: the result set length of this method query can only be 1
*/
@Test
public void test4(){
String sql = "select * from emp where id = ? or id = ?";
Map<String, Object> map = template.queryForMap(sql, 1001,1002);
System.out.println(map);
//{id=1001, ename = Monkey King, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
}
/**
* 5. Query all records and encapsulate them as a List
*/
@Test
public void test5(){
String sql = "select * from emp";
List<Map<String, Object>> list = template.queryForList(sql);
for (Map<String, Object> stringObjectMap : list) {
System.out.println(stringObjectMap);
}
}
/**
* 6. Query all records and encapsulate them as a List collection of Emp objects
*/
@Test
public void test6(){
String sql = "select * from emp";
List<Emp> list = template.query(sql, new RowMapper<Emp>() {
@Override
public Emp mapRow(ResultSet rs, int i) throws SQLException {
Emp emp = new Emp();
int id = rs.getInt("id");
String ename = rs.getString("ename");
int job_id = rs.getInt("job_id");
int mgr = rs.getInt("mgr");
Date joindate = rs.getDate("joindate");
double salary = rs.getDouble("salary");
double bonus = rs.getDouble("bonus");
int dept_id = rs.getInt("dept_id");
emp.setId(id);
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBonus(bonus);
emp.setDept_id(dept_id);
return emp;
}
});
for (Emp emp : list) {
System.out.println(emp);
}
}
/**
* 6. Query all records and encapsulate them as a List collection of Emp objects
*/
@Test
public void test6_2(){
String sql = "select * from emp";
List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
/**
* 7. Total records queried
*/
@Test
public void test7(){
String sql = "select count(id) from emp";
Long total = template.queryForObject(sql, Long.class);
System.out.println(total);
}
}
##Transaction control in Spring
1. API introduction
1. PlatformTransactionManager
Description: the PlatformTransactionManager interface provides methods for transaction operations, including three specific operations
* get transaction status information
TransactionStatus getTransaction(TransactionDefinition definition)
* commit transaction
void commit(TransactionStatus status)
* rollback transaction
void rollback(TransactionStatus status)
Note: we all use its implementation classes in development, and the objects for real transaction management are as follows:
* org.springframework.jdbc.datasource.DataSourceTransactionManager
Use when using Spring JDBC or iBatis for persistent data
* org.springframework.orm.hibernate5.HibernateTransactionManager
Use when persisting data with Hibernate version
2. TransactionDefinition
1. Description: TransactionDefinition is the definition information object of a transaction. There are the following methods
* get transaction object name: String getName()
* get transaction isolation level: int getlsolationLevel()
* get transaction propagation behavior: int getPropagationBehavior()
* get transaction timeout: int getTimeout()
* get whether the transaction is read-only: boolean isReadOnly()
2. Transaction isolation level: reflects the processing attitude when the transaction is submitted for concurrent access
* ISOLATION DEFAULT: the default level, belonging to one of the following
* isolation "read" uncommitted: uncommitted data can be read
* ISOLATION READ COMMITTED: only the submitted data can be read to solve the dirty read problem (Oracle default level)
* isolation repeatable "read: whether to read the modified data submitted by other transactions to solve the problem of non repeatable reading (MySQL default level)
* ISOLATION SERIALIZABLE: whether to read the data added after other transaction commits to solve the phantom reading problem
3. Communication of affairs
* REQUIRED: if there is no transaction at present, create a new transaction. If there is already a transaction, join it. General selection (default)
* SUPPORTS: SUPPORTS the current transaction. If there is no transaction at present, it will be executed in non transaction mode (no transaction)
4. Timeout
* the default value is - 1, no timeout limit. If so, set in seconds.
5. Is it a read-only transaction
* it is generally set to read-only when querying.
3. TransactionStatus
* Note: the TransactionStatus interface describes the status information of the transaction object at a certain time point, including 6 specific operations
* refresh transaction: void flush()
* get whether there is a storage point: boolean hasSavepoint()
* get whether the transaction is completed: boolean isCompleted()
* get whether the transaction is a new one: boolean isNewTransaction()
* get whether the transaction is rolled back: boolean isRollbackOnly()
* set transaction rollback: void setRollbackOnly()
2. Declarative transaction control based on XML (configuration mode)
1. Environment construction:
1. Import jar package:
2. Create a spring configuration file and import constraints
aop and tx namespaces need to be imported here
<?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:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.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"> </beans>
3. Prepare database tables and entity classes
4. Write service interface and implementation class
5. Write Dao interface and implementation class
6. Configure the business layer and persistence layer classes in the configuration file
2. Configuration steps:
1. Configure transaction manager
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"></property> </bean>
2. Configure notification reference transaction manager for transactions
* constraint tx namespace and constraint of import transaction, as well as aop's
* configure transaction notifications using tx:advice Tags
Properties:
id: a unique identifier for transaction notification
Transaction manager: provides a transaction manager reference for transaction notifications
<tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> ... </tx:attributes> </tx:advice>
3. Configure transaction properties
* configure transaction attributes with tx: attributes flag under tx: advice
* configure transaction properties
Name: Specifies the name of the method
Isolation: Specifies the isolation level of the transaction. The DEFAULT value is DEFAULT, which means the DEFAULT isolation level of the database is used.
Propagation: used to specify the propagation behavior of a transaction. The default value is REQUIRED, which means there must be transaction, adding, deleting and changing options. The query method can select SUPPORTS.
Read only: used to specify whether the transaction is read-only. Only query methods can be set to true. The default value is false, which means read and write.
Timeout: used to specify the timeout of the transaction. The default value is - 1, which means never timeout. If a value is specified, in seconds.
Rollback for: used to specify an exception. When the exception is generated, the transaction is rolled back. When other exceptions are generated, the transaction is not rolled back. There is no default value. Indicates that any exception is rolled back.
No rollback for: used to specify an exception. When the exception is generated, the transaction will not be rolled back. When other exceptions are generated, the transaction will be rolled back. There is no default value. Indicates that any exception is rolled back.
<tx:attributes> <tx:method name="*" propagation="REQUIRED" read-only="false"/> <tx:method name="find*" propagation="SUPPORTS" read-only="true"></tx:method> </tx:attributes>
4. Configure AOP pointcut expression
5. Configure the corresponding relationship between pointcut expression and transaction notification
<! -- configure AOP -- > <aop:config> <! -- configure pointcut expressions -- > <aop:pointcut id="pt1" expression="execution(* com.lyp.service.impl.*.*(..))"></aop:pointcut> <! -- establish correspondence between pointcut expression and transaction notification -- > <aop:advisor advice-ref="txAdvice" pointcut-ref="pt1"></aop:advisor> </aop:config>
Based on the above steps, the code is as follows:
<!-- Configure transaction manager --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- Configure notifications for transactions--> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="*" propagation="REQUIRED" read-only="false"/> <tx:method name="find*" propagation="SUPPORTS" read-only="true"></tx:method> </tx:attributes> </tx:advice> <!-- To configure aop--> <aop:config> <!-- Configure pointcut expressions--> <aop:pointcut id="pt1" expression="execution(* com.lyp.service.impl.*.*(..))"></aop:pointcut> <!--Establish correspondence between pointcut expression and transaction notification --> <aop:advisor advice-ref="txAdvice" pointcut-ref="pt1"></aop:advisor> </aop:config>
