Introduction to methods of JDBC database in Spring

This article mainly introduces the construction of the basic JDBC framework based on Spring; the addition, deletion, modification and query of JDBC based on Spring; reading the data in the configuration file, etc., which has a very good reference value. Let’s take a look at it with the editor

Database connection pool

For a simple database application, since the access to the database is not very frequent, you can simply When you need to access the database, you create a new connection and close it when you are done. This will not bring any performance overhead. But for a complex database application, the situation is completely different. Frequent establishment and closing of connections will greatly reduce the performance of the system, because the use of connections has become a bottleneck of system performance.

By establishing a database connection pool and a set of connection usage management policies, the effect of connection reuse can be achieved, so that a database connection can be safely and efficiently reused, avoiding the need for database The overhead of frequent connection establishment and closing.

The basic principle of the database connection pool is to maintain a certain number of database connections in the internal object pool and expose the database connection acquisition and return methods to the outside world. For example: external users can obtain the connection through the getConnection method, and then return the connection through the releaseConnection method after use. Note that the connection is not closed at this time, but is recycled by the connection pool manager and prepared for the next use.

Benefits brought by database connection pool technology:

1. Resource reuse

Due to Database connections are reused, avoiding a large amount of performance overhead caused by frequent creation and release of links. On the basis of reducing system consumption, on the other hand, it also improves the stability of the system operating environment (reduces memory fragmentation and database temporary processing/number of threads)

2. Faster system Response speed

During the initialization process of the database connection pool, several database connection pools are often created and placed in the pool for backup. At this point, the initialization of the connection has been completed. For business request processing, existing available connections are directly used to avoid the time overhead of database connection initialization and release process, thereby reducing the overall system response time

3. Unified connection management to avoid database connection leakage

In a relatively complete database connection pool implementation, forced recovery can be based on the preset connection occupation timeout setting. The occupied connection thus avoids resource leakage that may occur in regular database connection operations.

There are currently many database connection pool products, mainly including:

1, dbcp

dbcp is a database connection pool produced by DataBase Connection PoolApache and recommended by the Spring development team. It is actively developed and is an extremely widely used database connection pool product. However, from the Internet

# 2, C3P0

## Hibernate Development Group recommended database connection pool, it implements the binding of data sources and JNDI

3. Proxool

Proxool has a good reputation and no negative comments (for example, dbcp is not used by Hibernate because Hibernate thinks it has too many bugs. Recommended) is also the database connection pool recommended by the Hibernate development team, but there are not many users and the development is not active enough. This connection pool provides the connection pool monitoring function, which is convenient and easy to use, making it easy to detect connection pool leaks

Building the JDBC basic framework based on Spring

Let’s talk about it first Use Spring to implement JDBC. The database connection pool uses DBCP recommended by the Spring development team. DBCP requires three jar packages. Download them first:

1. commons-dbcp-1.4.jar. It is available on the official website. Click I downloaded

2, commons.pool-1.6.jar, which is available on the official website. Click here to download

3, commons.collections4-4.0.jar, which is available on the official website.

After downloading these three jar packages, please import them into your own project (note that MySql packages are not forgotten to import). Although both dbcp and pool have dbcp2 and pool2 versions, they are available on the Apache official website. It can be downloaded, but the version download addresses of dbcp1 and pool1 are provided here. One reason is that both dbcp2 and pool2 can only run in JDK1.7 and above, while dbcp1 and pool1 can run in JDK1.6. Run, considering that the default JRE that comes with MyEclipse10 is version 1.6, so download and use dbcp1 and pool1 here. If you want dbcp2 and pool2, you can download them from the Apache official website. However, please note that dbcp2 must be paired with pool2, and dbcp1 must be paired with pool1. They cannot be used interchangeably.

JDBC, I have written an article before. The database creation and entity classes are all used in the original article. Here I just move the native JDBC to Spring JDBC. Let’s take a look at the most basic writing method. Add more functions, the student management category is:

public class StudentManager
{
 private JdbcTemplate jdbcTemplate;

 private static StudentManager instance = new StudentManager();

 public static StudentManager getInstance()
 {
 return instance;
 }

 public JdbcTemplate getJdbcTemplate()
 {
 return jdbcTemplate;
 }
 public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
 {
 this.jdbcTemplate = jdbcTemplate;
 }
}

Spring's XML configuration file is named jdbc.xml, and jdbc.xml is written as:

<?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:context="http://www.springframework.org/schema/context"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
 http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
 http://www.springframework.org/schema/context
 http://www.springframework.org/schema/context/spring-context-4.2.xsd">

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
 <!-- 驱动包名 -->
 <property name="driverClassName" value="com.mysql.jdbc.Driver" />
 <!-- 数据库地址 -->
 <property name="url" value="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8;" />
 <!-- 用户名 -->
 <property name="username" value="root" />
 <!-- 密码 -->
 <property name="password" value="root" />
 <!-- 最大连接数量 -->
 <property name="maxActive" value="150" />
 <!-- 最小空闲连接 -->
 <property name="minIdle" value="5" />
 <!-- 最大空闲连接 -->
 <property name="maxIdle" value="20" />
 <!-- 初始化连接数量 -->
 <property name="initialSize" value="30" />
 <!-- 连接被泄露时是否打印 -->
 <property name="logAbandoned" value="true" />
 <!-- 是否自动回收超时连接 -->
 <property name="removeAbandoned" value="true" />
 <!-- 超时等待时间(以秒为单位) -->
 <property name="removeAbandonedTimeout" value="10" />
 </bean>

 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
 <property name="dataSource" ref="dataSource" />
 </bean>
 <bean id="studentManager" class="com.xrq.jdbc.StudentManager" factory-method="getInstance"> 
 <property name="jdbcTemplate" ref="jdbcTemplate" />
 </bean>
</beans>

Mainfunction is :

public static void main(String[] args)
{ 
 ApplicationContext ac = 
  new ClassPathXmlApplicationContext("jdbc.xml");
 System.out.println(StudentManager.getInstance());
 System.out.println(StudentManager.getInstance().getJdbcTemplate());
}

There is no problem in running. I get the reference address of StudentManager and the reference address of attributejdbcTemplate in StudentManager, indicating that the entire connection and injection are fine.

JDBCTemple is the most basic JDBC template in Spring, which uses JDBC and simple indexparametersqueryto provide simple access to the database. In addition to JDBCTemplate, Spring also provides two classes: NamedParameterJdbcTemplate and SimpleJdbcTemplate. The former can bind values ​​to named parameters in SQL when executing queries instead of using indexes. The latter takes advantage of Java 5 features such as autoboxing, generics and Variable parameters List to simplify the use of JDBC templates. Which one to use depends on personal preference. JdbcTemplate is used here, so add JdbcTemplate to the student management class.

In addition:

1. dbcp provides many parameters for user configuration. The meaning of each parameter is written in the .xml in the form of comments. More specific requirements To know the meaning of each parameter, you can check it online

2. Pay attention to the attribute url of dbcp. The url refers to the database connection address. If you encounter special characters, you need to transfer Meaning, so the "&" here becomes "&", otherwise an error will be reported

JDBC addition, deletion and modification query based on Spring

Above Part of the basic framework of Spring JDBC is built. Let's take a look at how the Java code implements CRUD. In this process, jdbc.xml does not need to be changed.

1. Add a student information, the code is:

// 添加学生信息
public boolean addStudent(Student student)
{
 try
 {
 jdbcTemplate.update("insert into student values(null,?,?,?)", 
  new Object[]{student.getStudentName(), student.getStudentAge(), student.getStudentPhone()},
  new int[]{Types.VARCHAR, Types.INTEGER, Types.VARCHAR});
 return true;
 }
 catch (Exception e)
 {
 return false;
 }
}

2. Delete the specified student information based on Id, the code is:

// 根据Id删除单个学生信息
public boolean deleteStudent(int id)
{
 try
 {
 jdbcTemplate.update("delete from student where studentId = ?", new Object[]{id}, new int[]{Types.INTEGER});
 return true;
 }
 catch (Exception e)
 {
 return false;
 }
}

3. Update student information based on Id, the code is:

// 根据Id更新指定学生信息
public boolean updateStudent(int Id, Student student)
{
 try
 {
 jdbcTemplate.update("update student set studentName = ?, studentAge = ?, studentPhone = ? where studentId = ?", 
  new Object[]{student.getStudentName(), student.getStudentAge(), student.getStudentPhone(), Id},
  new int[]{Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.INTEGER});
 return true;
 }
 catch (Exception e)
 {
 return false;
 }
}

4. Query student information based on Id, the code is:

// 根据学生Id查询单个学生信息
public Student getStudent(int id)
{
 try
 {
 return (Student)jdbcTemplate.queryForObject("select * from student where studentId = ?", 
  new Object[]{id}, new int[]{Types.INTEGER}, new RowMapper<Student>(){
  public Student mapRow(ResultSet rs, int arg1) throws SQLException
  {
  Student student = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4));
  return student;
  }
 });
 }
 // 根据Id查询学生信息抛异常, 不管什么原因, 认为查询不到该学生信息, 返回null
 catch (DataAccessException e)
 {
 return null;
 }
}

5. Query all student information, the code is:

// 查询所有学生信息
public List<Student> getStudents()
{
 List<Map<String, Object>> resultList = jdbcTemplate.queryForList("select * from student");
 List<Student> studentList = null;
 if (resultList != null && !resultList.isEmpty())
 {
 studentList = new ArrayList<Student>();
 Map<String, Object> map = null;
 for (int i = 0; i < resultList.size(); i++)
 {
  map = resultList.get(i);
  Student student = new Student(
  (Integer)map.get("studentId"), (String)map.get("studentName"),
  (Integer)map.get("studentAge"), (String)map.get("studentPhone")
  );
  studentList.add(student);
  }
 }
 return studentList;
}

This is a simple CRUD operation. With these 5 as the basis, the rest can be expanded on the basis of these 5. I will not continue to write in detail, but I will talk about a few points to note:

1. From personal experience, except for the last query, it is recommended to add try...catch... blocks to all others, because it will be thrown when the operation fails. Exception , if you catch it, you will know that the operation failed. Otherwise, the program will be terminated, and you will not know whether the operation was successful or failed.

2. It is not recommended to add information or update information. Each field to be operated is used as a formal parameter. It is recommended that the formal parameter be a Student entity class. This is more in line with the design principles of Object-oriented. Secondly, the fields in the formal parameter list Too many methods can easily lead to errors

3. Update and query methods. If there are placeholders?, it is recommended to choose the

overloaded method with parameter type and specify the fields of each placeholder. Type, just like what I wrote in the code above

Finally, what I’m talking about here is the

basic use of jdbcTemplate. There are many methods in jdbcTemplate, so I won’t go into details one by one. Try it yourself, or check out the Spring API documentation.

Read the data in the configuration file

Before we configured some properties of the database connection in db.properties, which is convenient for modification, but here it is It is hard-coded in jdbc.xml, so we need to think of a way to read the configuration from db.properties. Context helps developers achieve this. Take a look at how to write jdbc.xml:

<?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:context="http://www.springframework.org/schema/context"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
 http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
 http://www.springframework.org/schema/context
 http://www.springframework.org/schema/context/spring-context-4.2.xsd">

 <context:property-placeholder location="classpath:db.properties"/>

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
 <!-- 驱动包名 -->
 <property name="driverClassName" value="${mysqlpackage}" />
 <!-- 数据库地址 -->
 <property name="url" value="${mysqlurl}" />
 <!-- 用户名 -->
 <property name="username" value="${mysqlname}" />
 <!-- 密码 -->
 <property name="password" value="${mysqlpassword}" />
 <!-- 最大连接数量 -->
 <property name="maxActive" value="150" />
 <!-- 最小空闲连接 -->
 <property name="minIdle" value="5" />
 <!-- 最大空闲连接 -->
 <property name="maxIdle" value="20" />
 <!-- 初始化连接数量 -->
 <property name="initialSize" value="30" />
 <!-- 连接被泄露时是否打印 -->
 <property name="logAbandoned" value="true" />
 <!-- 是否自动回收超时连接 -->
 <property name="removeAbandoned" value="true" />
 <!-- 超时等待时间(以秒为单位) -->
 <property name="removeAbandonedTimeout" value="10" />
 </bean>

 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
 <property name="dataSource" ref="dataSource" />
 </bean>
 <bean id="studentManager" class="com.xrq.jdbc.StudentManager" factory-method="getInstance"> 
 <property name="jdbcTemplate" ref="jdbcTemplate" />
 </bean>
</beans>

【Related recommendations】

1.

Java Free Video Tutorial

2.

JAVA Tutorial Manual

3.

Comprehensive analysis of Java annotations

The above is the detailed content of Introduction to methods of JDBC database in Spring. For more information, please follow other related articles on the PHP Chinese website!