DBCP is a utility class in Tomcat.
DataBase Connection Pool (DBCP) is a Java DataBase Connection Pool developed by Apache. Through the DataBase Connection Pool, programs can automatically manage the release and disconnection of DataBase connections.
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,commons-pool.jar Since establishing a database connection is a very time-consuming and resource-consuming behavior, some connections are established with the database through connection pool and stored in memory. When an application needs to establish a database connection, it can apply for a connection from the connection pool and then put it back.
DBCP database connection pool operations are as follows:
1, import driver JAR package (DBCP connection pool JAR package is usually dependent on the pool package)
Principle: THE DBCP package generates many connection objects, which are centrally managed by the POOL package.
2, create database tool class: dbutils.java
Advantages of tools:
- 1. Improve performance
The database connection pool DBCP technology is used to store multiple connection objects in the connection pool for use. When no connection is needed, save the connection object to the pool to improve performance!
- 2, avoid multithreaded access to the connection object chaos
The connection is stored in the ThreadLocal class, which is equivalent to the map structure. It uses the current thread object as the key to save the conn connection object, avoiding the conn connection object inconsistency caused by multi-thread access to the business layer and the DAO layer. Get the connection from threadLocal, or if null, get the connection from the pool and set it to threadLocal.
- 3. Unify commit and rollback transactions
If the connection is not null, the transaction is committed or rolled back, and then the connection is closed to clean up any connections bound by the current thread.
- 4, convenient to close the connection
Close statement, ResultSet, and other objects except conn connection objects.
DBCP core setup code is as follows:
// create database connection pool ds = new BasicDataSource(); // Set connection information ds.setDriverclassName (" com.mysql.cj.jdbc.driver "); ds.setUrl(URL); ds.setUsername(USERNAME); ds.setPassword(PASSWORD); Ds.setmaxidle (30); // Set the connection pool information. Ds.setminidle (2); // Set the initial connection number ds.setInitialSize(2); Ds.setmaxwaitmillis (4000); Ds.setdefaultautocommit (false);Copy the codeThe complete code is as follows:
package com.yueqian.store.common; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.Map; import org.apache.tomcat.dbcp.dbcp2.BasicDataSource; Public class DBUtils {private static final String URL = "JDBC: mysql: / / 127.0.0.1:3306 / store? useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false"; private static final String USERNAME = "root"; private static final String PASSWORD = "root"; private static final BasicDataSource ds; The static {/ / load the Driver try {/ / a DriverManager. RegisterDriver (. New com. Mysql. Cj. JDBC Driver ()); // class.forname (" com.mysql.cj.jdbc.driver ()"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } // create a database connection pool ds = new BasicDataSource(); // Set connection information ds.setDriverclassName (" com.mysql.cj.jdbc.driver "); ds.setUrl(URL); ds.setUsername(USERNAME); ds.setPassword(PASSWORD); Ds.setmaxidle (30); // Set the connection pool information. Ds.setminidle (2); // Set the initial connection number ds.setInitialSize(2); Ds.setmaxwaitmillis (4000); Ds.setdefaultautocommit (false); } // add a thread object to the ThreadLocal class, and use the current thread object as the key to store the conn object. Private static ThreadLocal<Connection> ThreadLocal = new ThreadLocal<Connection>(); Public static Connection getConnection() {public static Connection getConnection() { ThreadLocal stores the key as the current thread object and the value as conn connection. Connection conn = threadLocal.get(); If (conn == null) {try {conn = ds.getConnection(); // If the connection is null, get the connection from the connection pool. } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } // Set the resulting connection to threadLocal threadlocal.set (conn); } return conn; } public static void close(ResultSet rs, ResultSet rs, ResultSet rs, Statement stmt) { if (rs ! = null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (stmt ! = null) { try { stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }}}}} // Commit transaction public static void commit() {Connection conn = threadlocal.get (); if(conn ! = null){ try { conn.commit(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally {// Close the connection try {conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } // Clean up threadlocal.remove (); Public static void rollback() {Connection conn = threadlocal.get (); if(conn ! = null){ try { conn.rollback(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally {// Close the connection try {conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } // Clean up threadlocal.remove (); }}}}Copy the code
3. Business layer
Multiple Dao layer processes are invoked using the same connection object. After all transactions are completed, the unified operation succeeds, and the DBUtils.com MIT () method is called to commit the transaction. If a transaction fails, all the transactions will be rolled back.
package com.yueqian.store.service; import java.util.List; import com.yueqian.store.common.DBUtils; import com.yueqian.store.dao.ProductTypeDao; import com.yueqian.store.domain.ProductType; Public class ProductTypeService {private ProductTypeDao typeDao = new ProductTypeDao(); ** @return */ public List<ProductType> findAllProType() {List<ProductType> List = null; List = typeDao.findallProType (); // Execute subsequent DAO methods using the same connection object // commit the transaction dbutils.mit (); } catch (Exception e) {// Transaction rollback dbutils.rollback (); } return list; }}Copy the code4, the dao layer
The DAO layer directly calls the dbutils.getConnection () method to get the connection, and throws all the exceptions in the DAO layer up to the business layer. The business layer deals with them uniformly, and finally commits and rolls back transactions uniformly. The DAO layer does not handle exceptions, but always declares to throw.
package com.yueqian.store.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.yueqian.store.common.DBUtils; import com.yueqian.store.domain.ProductType; Public class ProductTypeDao {/** * query all commodity category tables * @return * @throws SQLException Throws all exceptions at the DAO layer to the service layer for unified processing. */ public List<ProductType> findAllProType()throws SQLException{Connection conn = null; PreparedStatement pstm = null; List<ProductType> list = new ArrayList<ProductType>(); ResultSet rs = null; ProductType proType = null; conn = DBUtils.getConnection(); String sql = "SELECT p.product_type_id,p.name FROM product_types p"; try { pstm = conn.prepareStatement(sql); rs = pstm.executeQuery(); while(rs.next()) { proType = new ProductType(); proType.setTypeId(rs.getInt(1)); proType.setTypeName(rs.getString(2)); list.add(proType); } } finally { DBUtils.close(rs, pstm); } return list; }}Copy the code5, the controller layer
Invoke the business layer to process the corresponding logic
- BaseServlet.java
This is a reflection mechanism that converts the parameter type passed to the String to the corresponding type. Other servlets integrate this class to facilitate type conversions. The parameters are as follows (front-end name name, request object req, bytecode integer.class of type to be converted)
package com.yueqian.store.controller; import java.sql.Date; import java.text.SimpleDateFormat; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; public class BaseServlet extends HttpServlet { private static final long serialVersionUID = 1L; private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // Get the request parameters, Public <T> T paseParamter(String parseName, HttpServletRequest req, Class<T> CLZ) {String parmValue = req.getParameter(parseName); T result = null; / / CLZ type Integer. The class String. The class Double. Class, Float. Class, Date. Class try {if (parmValue! = null | | parmValue. Length () > 0) {/ / because there is no String parameter Float type constructor, If (CLZ == date.class) {// Convert paramValue to a value of type long longValue = sdf.parse(parmValue).getTime(); / / create a specified type String parameter constructor result. = CLZ getDeclaredConstructor (long. Class). NewInstance (longValue); } else {/ / paramValue as specified type constructor parameter result = CLZ. GetDeclaredConstructor (String. The class). NewInstance (parmValue); } } } catch (Exception e) { e.printStackTrace(); } return result; }}Copy the code- ProductServlet
Specific use is as follows:
package com.yueqian.store.controller; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.yueqian.store.dao.ProductDAO; import com.yueqian.store.dao.ProductTypeDao; import com.yueqian.store.domain.ProductInfo; import com.yueqian.store.domain.ProductType; import com.yueqian.store.service.ProductService; import com.yueqian.store.service.ProductTypeService; public class ProductServlet extends BaseServlet { /** * */ private static final long serialVersionUID = 1L; private ProductService productService = new ProductService(); private ProductTypeService typeService = new ProductTypeService(); @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String uri = req.getRequestURI(); If (uri) indexOf ("/PRD/list ") > = 0) {/ / commodity list < ProductInfo > findAllProduct = productService. FindAllProduct (); // Set the list to the request page req.setAttribute("findAllProduct", findAllProduct); Req.getrequestdispatcher ("/product/product_list.jsp").forward(req, resp); } else if (uri indexOf ("/PRD/add ") > = 0) {/ / view all categories List < ProductType > findAllProType = typeService. FindAllProType (); Req.setattribute ("findAllProType", findAllProType); req.getRequestDispatcher("/product/product_add.jsp").forward(req, resp); } } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String uri = req.getRequestURI(); If (uri.indexof ("/ PRD /add") > 0) {// Obtain the requested parameter Integer typeId = super.paseParamter("typeId", req, integer.class); String productName = super.paseParamter("productName", req, String.class); String desc = super.paseParamter("desc", req, String.class); Float price = super.paseParamter("price", req, Float.class); System.out.println("productName:"+productName+"-------------------------"); System.out.println("price:"+price+"-------------------------"); // The item name in the database cannot be null. If it is null, Returns the information and other data of the user fills out the if (productName = = null | | productName. Equals (" ")) {the req. SetAttribute (" MSG ", "name of commodity can't be empty!" ); req.setAttribute("typeId", typeId); req.setAttribute("desc", desc); req.setAttribute("price", price); req.setAttribute("findAllProType", this.typeService.findAllProType()); Req.getrequestdispatcher ("/product/product_add.jsp").forward(req, resp); return; } // add new ProductInfo info = new ProductInfo(); if (typeId ! = null && typeId > 0) { info.setProductTypeId(typeId); } info.setProductName(productName); info.setDesc(desc); info.setPrice(price); int count = productService.saveInfo(info); System.out.println(info.getProductName()+"========================"); Resp.sendredirect (req.getContextPath() + "/ PRD /add? count=" + count + "&from=add&proId=" + info.getProductId()); }}}Copy the code6, the view layer
The data from the servlet is presented to the user on the page via JSP
<%@page import="com.yueqian.store.domain.ProductType"%> <%@page import="java.util.List"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <! DOCTYPE HTML > < HTML > <head> <meta charset="UTF-8"> <title> <script type="text/javascript"> window.onload = function() { var returnNode = document.getElementById('returnBtn'); returnNode.onclick = function() { location.href = "<%=request.getContextPath()%>/main.jsp"; } } </script> </head> <body> <% List<ProductType> typeList = (List<ProductType>) request.getAttribute("findAllProType"); String count = request.getParameter("count"); String from = request.getParameter("from"); String proId = request.getParameter("proId"); </button> <form action='<%=request.getContextPath()%>/ PRD /add' Method ="post"> Commodity category: <select name="typeId"> <option value="0"> Please select commodity category </option> <% if (typeList! = null && typeList.size() > 0) { for (ProductType types : typeList) { %> <option value='<%=types.getTypeId()%>'<%=request.getAttribute("findAllProType").equals(types.getTypeId())? Selected = "selected", "selected = 0" % > > < % = types. The getTypeName () % > < option > < %}} % > < / select > < br / > name of commodity: <input type="text" name="productName" maxLength ="30" /> <textarea rows="7" cols="30" name="desc"><% Object desc = request.getParameter("desc"); if(desc ! = null) out.print(desc); %></textarea> <input type="text" name="price" maxlength="10" <% Object price = request.getParameter("price"); if(price ! = null) out.print(price); % > / > < br / > < input type = "submit" value = "add" > < / form > < % if (count! = null && from ! = null) { int intCount = Integer.parseInt(count); String f = ("add".equals(from) ? "Input" : ""); If (intCount > 0) {out.print(f + "+" + count + "); }else{out.print(f+" failed!") ); } } Object msg = request.getAttribute("msg"); if(msg! =null){ out.print(msg); } %> </body> </html>Copy the codeOk, part of the project code is shown above. In this section you will learn how to optimize the database connection, MVC layer to create web project flow, and reflection mechanism to convert the types passed by the front end.
This is the use of DBCP connection pool, which can greatly improve the performance of the system. Maybe we have not realized the efficiency of the small project, I believe that in the future when we contact with larger projects, we will often use connection pool, continue to move forward…