1. Tool class encapsulation
Tool class encapsulation mainly extracts all the operations of the database and puts them in the DbUtils tool class, so as to realize code reuse. Implementing the effect of code layering and improving the logical structure of code. In order to modify the function of the module, it is necessary to modify only part of the code.
Case realization: query, add, delete and modify emp table
1.1 Write the parameters of the connection object into the configuration file by loading the driver class, load the configuration file with the class loader, and read and write the parameters through the IO stream.
Configuration file: db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/myschool?useSSL=true&characterEncoding=utf8 username=root password=root
1.2 Encapsulation of DbUtils Tool Class Encapsulation for Addition, Deletion and Amendment of emp Table
Emphasis: Query data in the database (using reflection and introspection mechanisms)
Basic step description of query: all records in query table
(1) Get the connection object conn and execute the command object pstat
(2) pstat executes the SQL statement and returns the result set to the result set object rs
(3) Get the table column name set (table header name set) through the result set object.
(4) Traverse the result set and get the corresponding value of each row and column through the column name set.
(5) Create object T = class1. newInstance () with generic class object, and obtain attribute descriptor with column name and generic class object (corresponding to each attribute in entity class).
(6) Get the setXXX() method of each attribute with the attribute descriptor, call the reflection method invoke(t,value); assign value to each attribute.
(7) Finally, the t-object is added to the list set to read the data in the database and traverse the output.
public class DbUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static{
try {
Properties prop = new Properties();
InputStream is = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
prop.load(is);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
System.out.println("Connection failed!");
e.printStackTrace();
}
return null;
}
public static void executeUpdate(String sql,Object...params){
Connection conn = DbUtils.getConnection();
if(conn!=null){
PreparedStatement pstat=null;
try {
pstat = conn.prepareStatement(sql);
if(params!=null){
for (int i = 0; i < params.length; i++) {
pstat.setObject(i+1,params[i]);
}
}
pstat.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
release(conn,pstat,null);
}
}
}
public static <T> List<T> findAll(String sql, Object[] params, Class<T> class1){
List<T> list = new ArrayList<>();
Connection conn=null;
PreparedStatement pstat=null;
ResultSet rs=null;
try {
conn = DriverManager.getConnection(url,username,password);
pstat = conn.prepareStatement(sql);
rs = pstat.executeQuery();
while(rs.next()){
try {
T t = class1.newInstance();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// System.out.println(columnCount);
for (int i = 0; i < columnCount; i++) {
//Get the column names for each column
String columnLabel = metaData.getColumnLabel(i + 1);
//Get the corresponding value for this row of column names
Object object = rs.getObject(columnLabel);
try {
//Get the attribute descriptor for each column
PropertyDescriptor pd = new PropertyDescriptor(columnLabel,class1);
if(pd!=null) {
//Get its set method from attribute descriptor
Method writeMethod = pd.getWriteMethod();
//Assigning the values of each column to the attributes corresponding to the entity class
writeMethod.invoke(t, object);
}
} catch (Exception e) {
// e.printStackTrace();
continue;
}
}
list.add(t);
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
release(conn,pstat,rs);
}
return list;
}
public static <T> T findByEmpno(String sql,Class<T> class1,Object...params){
Connection conn=null;
PreparedStatement pstat=null;
ResultSet rs=null;
T t = null;
try {
conn = DriverManager.getConnection(url,username,password);
pstat = conn.prepareStatement(sql);
pstat.setObject(1,params[0]);
rs = pstat.executeQuery();
if(rs.next()){
try {
t=class1.newInstance();
ResultSetMetaData metaData = rs.getMetaData();
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnLabel = metaData.getColumnLabel(i + 1);
Object value = rs.getObject(columnLabel);
try {
PropertyDescriptor pd = new PropertyDescriptor(columnLabel,class1);
if(pd!=null){
Method writeMethod = pd.getWriteMethod();
writeMethod.invoke(t,value);
}
} catch (IntrospectionException e) {
// e.printStackTrace();
continue;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return t;
}
public static void release(Connection conn, Statement stat, ResultSet rs){
try {
if(rs!=null){
rs.close();
}
if(stat!=null){
stat.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}The above encapsulated tool classes are used in conjunction with DAO design patterns.
2.DAO Setup Mode
DAO(Database Access Object Database Access Object)
In order to reduce the coupling, the design mode of DAO encapsulation database operation is proposed.
It can separate business logic from database access. Relatively speaking, the database is relatively stable, in which DAO components rely on the database system to provide database access interface, isolating different database implementations.
Components of the DAO model
1 DAO interface (mainly adding modified query deletion method)
2 DAO Implementation Class
3 entity classes (domain), beans, entity, pojo, model)
PO (VO)(Persistent Object, Value Object)
VO (View Object)
DTO (Data Transfer Object)
Role: Used to transfer data through entity classes between data access code and business logic code
- Entity class characteristics:
private modification is commonly used for attributes
Provide public-modified getter/setter method
Entity classes provide parametric construction methods, and provide parametric construction according to business
Implementing java.io.Serializable interface and supporting serialization mechanism
4 Database Connection and Closing Tool Class
Design package name:
domain stores entity classes
Utls Storage Tool Class
dao storage interface
dao.impl stores implementation classes
Refer to the DAO pattern design case. https://blog.csdn.net/SjwFdb_1__1/article/details/100128177
3. Custom Implementing Connection Pool
Users need to get a connection to the database every time they request, and it usually takes a relatively large amount of resources and a long time to create a connection to the database. Assuming that the website has 100,000 visits a day, the database server needs to create 100,000 connections, which greatly wastes the resources of the database, and easily causes the memory overflow and extension of the database server.
Java provides a specification (interface) for connection pool implementation. Specification is written in Java. We need to implement DataSource interface!
The cases are as follows:
Note: There are many abstract methods in the DataSource interface. We just need to implement the getConnection method and then define ralease() to release resources and add connection objects from the new connection pool.
public class DbUtilsPool implements DataSource {
private static String driver;
private static String url;
private static String username;
private static String password;
private static List<Connection> connections = Collections.synchronizedList(new LinkedList<>());
static{
try {
Properties prop = new Properties();
InputStream is = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
prop.load(is);
is.close();
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
Class.forName(driver);
for (int i = 0; i < 5; i++) {
Connection conn = DriverManager.getConnection(url, username, password);
connections.add(conn);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//Take the connection object from the connection pool, return the connection object in existence, return null in nonexistence
@Override
public Connection getConnection() throws SQLException {
return connections.remove(0);
}
//Put the connection object back into the connection pool
public static void release(Connection conn){
connections.add(conn);
}
@Override
public Connection getConnection(String s, String s1) throws SQLException {
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter printWriter) throws SQLException {
}
@Override
public void setLoginTimeout(int i) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
@Override
public <T> T unwrap(Class<T> aClass) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> aClass) throws SQLException {
return false;
}
}Test code:
public class DbUtilsPoolTest {
@Test
public void testGetConn(){
for (int i = 0; i < 8; i++) {
DbUtilsPool myPool = new DbUtilsPool();
Connection conn = null;
try {
conn = myPool.getConnection();
System.out.println("Connect"+conn.hashCode()+"Success!");
myPool.release(conn);
} catch (Exception e) {
System.out.println("Connection timeout, please reconnect......");
}
}
}
}Test results:
Duplicate connection objects are generated because there are five connection objects in the connection pool. When we need more than five connection objects, duplicate connection objects will be generated.