“You meet better and better people as you get better and better. Hello, I am Mengyangchen, come on with me!

[Question Raised] Before we connected to the database, we would create and disconnect the Connection object once. Such frequent operations greatly affected the database access efficiency and increased the amount of code.

This is like opening a restaurant, when the guests come, you hire a waiter to serve the guests, when the guests leave, you fire the waiter.

The article directories

01. Why database connection pool
02. Implementation of database connection pool
C3P0: Database connection pool technology
Druid: Database connection pool implementation technology
05.Spring JDBC
06. Login case

01. Why database connection pool

In order to avoid frequent database connection creation, database connection pool was created. The database connection pool allocates, manages, and releases database connections. It allows applications to reuse existing database connections rather than re-establish them.

The database Connection pool creates a number of database connections during initialization and puts them into the database Connection pool. When an application accesses the database, it requests a Connection from the pool. If a Connection is free, it is returned, otherwise a new Connection is created. After the Connection is used, the Connection pool takes back the Connection and delivers it to other threads to reduce the number of database connections and improve the efficiency of database access.

Database connection pool concept: actually is a container (collection), store database connection container. Features: Saving resources, efficient user access.

02. Implementation of database connection pool

Standard interface: DataSource interface in javax. SQL package. To retrieve database connection objects, JDBC provides javax.sql. DataSource interface, which is responsible for establishing a Connection to the database and defining methods that return a Connection object. 1. Method:

GetConnection () Connection getConnection(String username, String password) If the Connection object Connection is fetched from the Connection pool, then the connection.close () method is called and the Connection is no longer closed. I'm going to return the connectionCopy the code

2. Generally we do not implement it, by the database vendor to implement. The class that implements the DataSource interface is often referred to as the DataSource, which stores all the information needed to establish a database connection. The data source contains database connection pools. The commonly used

C3P0: Database connection pool technology

Procedure: 1. Import the jar package c3P0-0.9.5.2. jar McHange-commons-java-0.2.12.jar

2. Define a configuration file (automatically loaded) named c3P0.properties or c3P0-config. XML path: Directly store the file in the SRC directory.

3. Create the core database connection pool object ComboPooledDataSource

 DataSource ds = new ComboPooledDataSource();
Copy the code

4. Obtain the database connection object

Connection conn = ds.getConnection();
Copy the code

5. The remaining operations are the same as the basic operations.

@WebServlet("/C3p0ServletTest1") public class C3p0ServletTest1 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); DataSource ds = new ComboPooledDataSource(); Connection conn = null; PreparedStatement pstat = null; ResultSet rs = null; Conn = ds.getConnection(); conn = ds.getConnection(); SQL = "select * from student where sex =?" ; Pstat = conn.prepareStatement(SQL); Pstat. SetString (1, "male"); //5. Execute SQL rs = pstat.executeQuery(); while (rs.next()) { String sequence = rs.getString("sequence"); String name = rs.getString("name"); String sex = rs.getString("sex"); out.println("<p>" + sequence + " " + name + " " + sex + "<p>"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { try { if(rs! =null){ rs.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } if(pstat! =null){ try { pstat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn! =null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); }}Copy the code

Druid: Database connection pool implementation technology

Druid is provided by Alibaba.

1. Import the jar package druid-1.0.9.jar.

2. Define the profile: It’s in the proporties form. Name: can be any name, can be placed in any directory (need to manually load)

3. Get the Database connection pool object: Get the DruidDataSourceFactory class

4. Obtain the connection

5. All other steps are the same

@WebServlet("/DruidServletTest1") public class DruidServletTest1 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); DataSource ds = null; Connection conn = null; PreparedStatement pstat = null; //1. Load configuration file Properties pro = new Properties(); InputStream is = DruidServletTest1.class.getClassLoader().getResourceAsStream("druid.properties"); pro.load(is); / / 2. Get the connection pool object try {ds = DruidDataSourceFactory. CreateDataSource (pro); } catch (Exception e) { e.printStackTrace(); Conn = ds.getConnection(); // Conn = ds.getConnection(); SQL = "update student set name=? where birthday=?" ; Pstat = conn.prepareStatement(SQL); Pstat.setstring (1); pstat.setString(2,"1999-10-18"); //5. Execute SQL int count = pstat.executeupdate (); If (count>0){out.print(" modify successfully!" ); }else{out.print(" Modify failed!" ); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { if(pstat! =null){ try { pstat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn! =null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); }}Copy the code

2. Define utility classes to simplify development and define a JDBCUtils class. Method: 1. Obtain the connection from the database connection pool.

2. Release resources

3. Method of obtaining the connection pool.

/** * Public class JdbcUtils {//1. DateaSource Private static DataSource ds; Static {try{//1. Properties pro = new Properties(); InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"); pro.load(is); / / 2. Get the DataSource ds = DruidDataSourceFactory. CreateDataSource (pro); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); Public static Connection getConntion() throws SQLException {return ds.getConnection(); public static Connection getConntion() throws SQLException {return ds.getConnection(); Public static void close(Statement STMT,Connection conn){/* if(STMT! =null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn! =null){// return connection try {stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } }*/ close(null,stmt,conn); } public static void close(ResultSet rs, Statement STMT, Connection conn){if(rs! =null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(stmt! =null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn! =null){// return connection try {stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); }}} public static DataSource getDataSource(){return DataSource; }}Copy the code

05.Spring JDBC

JDBC has been able to meet the most basic requirements of most users. However, when using JDBC, you must manage database resources by yourself, such as obtaining PreparedStatement, setting SQL statement parameters, and closing connections. The JdbcTemplate is Spring’s encapsulation of JDBC to make it easier to use. JdbcTemplate is part of Spring. The JdbcTemplate handles resource creation and release. He helps us avoid common mistakes, such as forgetting to always close the connection. He runs core JDBC workflows, such as Statement creation and execution, while we only need to provide SQL statements and extract results.

The JDBC utility class is intended to simplify development steps. The Spring framework provides a JdbcTemplate object to simplify JDBC development.

Procedure: 1. Import the JAR package.

2. Create a JdbcTemplate object that depends on the DataSource.

JdbcTemplate template = new JdbcTemplate(ds);
Copy the code

3. Call the JdbcTemplate method to complete the CRUD operation.

Update () : Execute DML statements (INSERT,update,delete). QueryForMap (): Query result encapsulates the result as a map collection. QueryForList (): The query result encapsulates the result set as a list. Query (): the query result is encapsulated as a JavaBean object. Generally we implement the class using BeanPropertyRowMapper. Can complete automatic encapsulation to JavaBean. New BeanPropertyRowMapper< type >(type.class) queryForObject: Query result, encapsulate the result as an object. Generally used for aggregate function queriesCopy the code

The update () exercise:

@WebServlet("/jdbcTemplateServletTest1") public class jdbcTemplateServletTest1 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); JdbcTemplate template = new JdbcTemplate(jdbCutils.getdatasource ()); / / define the SQL String SQL = "insert into student (sequence, sex, name, birthday) values (?,?,?,?,? ,? ,? ,?) "; // call method int count = template.update(SQL,"101000","男"," 三 三","2020-11-19"); out.print(count); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); }}Copy the code

QueryForMap () exercise:

@WebServlet("/jdbcTemplateServletTestl2") public class jdbcTemplateServletTestl2 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource()); String sql = "select * from student where id = ?" ; // Encapsulate only one Map<String,Object> Map =template.queryForMap(SQL,1); out.print(map); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); }}Copy the code

QueryForList () exercise:

@WebServlet("/jdbcTemplateServletTestl2") public class jdbcTemplateServletTestl2 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource()); String sql = "select * from student where id > ?" ; List<Map<String,Object>> list =template.queryForList(sql,1); for(Map<String,Object> StringObjectMap:list){ out.print("<p>"+StringObjectMap+"</p>"); } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); }}Copy the code

Convert Map to JSON format:

String sql="select * from student where id > ?" ; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql,1); Map<String, Object> map = maps.get(2); JSONObject jsonObject=new JSONObject(map); System.out.println(jsonObject.toJSONString());Copy the code

Query practice:

@WebServlet("/jdbcTemplateServletTest2") public class jdbcTemplateServletTest2 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource()); String sql = "select * from student "; List<Student> list = template.query(sql, new RowMapper<Student>() { @Override public Student mapRow(ResultSet rs, int i) throws SQLException { Student student = new Student(); int id = rs.getInt("id"); String sequence = rs.getString("sequence"); String name = rs.getString("name"); String sex = rs.getString("sex"); Date birthday = rs.getDate("birthday"); student.setId(id); student.setSequence(sequence); student.setName(name); student.setSex(sex); student.setBirthday(birthday); return student; }}); for(Student student :list){ out.print(student); } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); }}Copy the code

The problem: Although we used the Spring JDBC class and the Query exercise, we didn’t implement the simplified code as before. That’s because we implemented the RowMapper interface ourselves. In fact, the JdbcTemplate already implemented the RowMapper interface for us.

After the improvement:

@WebServlet("/JdbcTemplateServletTest3") public class JdbcTemplateServletTest3 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource()); String sql = "select * from student "; List<Student> list = template.query(sql,new BeanPropertyRowMapper<Student>(Student.class)); for(Student student:list){ out.print(list); } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); } } @WebServlet("/JdbcTemplateServletTest3") public class JdbcTemplateServletTest3 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource()); String sql = "select * from student where id =?" ; List<Student> List = template.query(SQL,new BeanPropertyRowMapper<Student>(student.class),"1"); out.print(list.get(0).getName()); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); }}Copy the code

Note that the JavaBean class, if the attribute is defined as a base type, in the query database, if the field is empty, the error will be reported, you can change the JavaBean attribute base type to reference data type

QueryForObject practice:

@WebServlet("/JdbcTemplateServletTest3")
public class JdbcTemplateServletTest3 extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource());
    
        String sql ="select count(id) from student";
        Long total = template.queryForObject(sql,Long.class);
        out.print(total);

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request,response);
    }
}
Copy the code

If you do not use the custom utility class JdbcUtils to get the connection pool, but directly get the connection pool, after multiple accesses, there will be too many connections.

    JdbcTemplate template = new 
    JdbcTemplate(JdbcUtils.getDataSource());
Copy the code

06. Login case

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <! DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <script src=".. /.. /case43/res/js/jquery.min.js"></script> <style type="text/css"> p{ color:red; } </style> </head> <body> <form class=" FM "> <div> <label> </div> <label> password :</label> <input type="password" name=" password" id=" password" > </div> Id = "BTN" value = "confirmed" > < / form > < p id = "p" > < / p > < script > $(function () {$(" # BTN "). Click (function () {var dataFm = $(".fm").serialize(); $.post( "/teaching/com/teaching/homeWork/Check1", dataFm, function(data){ var json =JSON.parse(data); if(json.success){ window.location.href= json.url; }else{$("#p").html(" wrong account or password! ") ); }}); }); }); </script> </body> </html>Copy the code

Tools:

/** * Public class JdbcUtils {//1. DateaSource Private static DataSource ds; Static {try{//1. Properties pro = new Properties(); InputStream is =JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"); pro.load(is); / / 2. Get the DataSource ds = DruidDataSourceFactory. CreateDataSource (pro); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); Public static Connection getConntion() throws SQLException {return ds.getConnection(); public static Connection getConntion() throws SQLException {return ds.getConnection(); Public static void close(Statement STMT,Connection conn){close(null, STMT,conn); } public static void close(ResultSet rs, Statement STMT, Connection conn){if(rs! =null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(stmt! =null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn! =null){// return connection try {stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); }}} public static DataSource getDataSource(){return DataSource; }}Copy the code

Processing the request:

@WebServlet("/com/teaching/homeWork/Check1") public class Check1 extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public Check1() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse * response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); String userName = request.getParameter("userName"); String passWord = request.getParameter("passWord"); JdbcTemplate template = new JdbcTemplate(JdbcUtils.getDataSource()); String SQL = "select * from student where name =? and sequence = ?" ; Student Student = null; try { student =template.queryForObject(sql, new BeanPropertyRowMapper<Student>(Student.class),userName,passWord); } catch (EmptyResultDataAccessException e) { e.printStackTrace(); } //Student student = template.queryForObject(sql, new BeanPropertyRowMapper<Student>(Student.class),userName,passWord); if(student! =null&&userName.equals(student.getName())&&passWord.equals(student.getSequence())) { HttpSession session = request.getSession(); session.setAttribute("userName",student.getName()); session.setAttribute("passWord",student.getSequence()); Cookie id = new Cookie("JSESSIONID", session.getid ()); id.setMaxAge(60*60*24*10); 10 days / / response addCookie (id); / / forwarding String url = request. GetContextPath () + "/ case631 / homeWork/HTML/success. JSP"; request.setAttribute("success",true); request.setAttribute("url",url); request.setAttribute("student", student); String url1 ="/com/teaching/homeWork/Json"; RequestDispatcher rd =request.getRequestDispatcher(url1); rd.forward(request, response); }else{Student student1 = new Student(); student1.setName("null"); student1.setSequence("null"); request.setAttribute("success",false); request.setAttribute("url","null"); request.setAttribute("student", student1); String url1 ="/com/teaching/homeWork/Json"; RequestDispatcher rd =request.getRequestDispatcher(url1); rd.forward(request, response); } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse * response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); }}Copy the code

JavaBean classes:

package com.teaching.homeWork; import java.util.Date; /** * javaBean */ public class Student { private Integer id; // To prevent database fields from being empty, define the application data type as private String sequence; private String name; private String sex; private Date birthday; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getSequence() { return sequence; } public void setSequence(String sequence) { this.sequence = sequence; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } @Override public String toString() { return "Student{" + "id=" + id + ", sequence='" + sequence + '\'' + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + '}'; }}Copy the code

This class:

@WebServlet("/com/teaching/homeWork/Json") public class Json extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public Json() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); response.setContentType("text/html; charset=utf-8"); PrintWriter out = response.getWriter(); boolean success=(boolean)request.getAttribute("success"); String url=(String)request.getAttribute("url"); Student student = (Student)request.getAttribute("student"); String json="{"+"\""+"success"+"\":"+success+","+"\""+"url"+"\":"+"\""+url+"\""+","+"\""+"userName"+"\":"+"\""+student.getName( )+"\""+","+"\""+"userName"+"\":"+"\""+student.getSequence()+"\""+"}"; System.out.print(json); out.print(json); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); }}Copy the code

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <! DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> </head> <body> <p><%=request.getSession().getAttribute("userName") %>> Welcome! </p> </body> </html>Copy the code

Exercise 1. Query the record whose ID is 1 and encapsulate it as a Map set

2. Query all records and encapsulate them as a List

3. Query all records and encapsulate them as a List of Studentt objects

4. Query the total number of records

You don’t need any special talent to succeed, just do the little things you can do well.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Database Connection Pool &Spring JDBC(JdbcTemplate)

The article directories

01. Why database connection pool

02. Implementation of database connection pool

C3P0: Database connection pool technology

Druid: Database connection pool implementation technology

05.Spring JDBC

06. Login case

Database Connection Pool &Spring JDBC(JdbcTemplate)

The article directories

01. Why database connection pool

02. Implementation of database connection pool

C3P0: Database connection pool technology

Druid: Database connection pool implementation technology

05.Spring JDBC

06. Login case

Related Posts

Linux interprocess communication pipeline

Comics: The underlying principles of the AES algorithm

Why does MySQL use B+ trees as indexes?