Liver! Spring JDBC persistence layer framework "family bucket" tutorial!

Create technology with persistence and tap the future with your fingertips! May every time we hit the keyboard, we can make life smarter and the world more interesting!

When using Spring for business logic layer processing, have you ever thought about whether such a powerful Spring framework has more convenient operation for database related business processing? To what extent can the Spring framework optimize the operation of traditional JDBC databases?

Today, I'm going to explore a lightweight framework for JDBC database operation - JdbcTemplate. Teach you an article to master the core of Spring JDBC framework.

I. what is a JdbcTemplate?

Spring's JdbcTemplate can be regarded as a small lightweight persistence layer framework. In order to make JDBC operation more convenient, spring defines an abstraction layer on the JDBC API to establish a JDBC access framework.

As the core of Spring JDBC framework, it is designed to provide template methods for different types of JDBC operations, so that in this way, the workload of database access can be minimized on the premise of preserving flexibility as much as possible.

Now you should know more about what a JDBC template is? Then I'll talk to you in detail about how this lightweight framework is used.

2, JdbcTemplate framework construction

For database related operations using JdbcTemplate, you need to build the relevant environment configuration in advance. Let's talk about how to configure the JdbcTemplate in spring.

1. Import the required jar package

We know that we usually need to rely on the relevant jar package to build the framework. What jar packages does the JdbcTemplate need? I listed and sorted them out according to their functions,

① JAR package required by IOC container

commons-logging-1.1.1.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar

② JAR package required by JdbcTemplate

spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar

③ Database drivers and data sources

c3p0-0.9.1.2.jar
mysql-connector-java-5.1.7-bin.jar

The above jar packages, including all jar packages required for SSM development, are sorted out for you and can be downloaded for use.

SSM framework Jar package download

Now that all dependent jar packages have been imported, the next step is to use these resources to build the next JdbcTemplate framework,

2. Configuring JDBC data sources

Since it is an operation on the database, it must need a data source. We take MySQL database as an example to configure the data source. I also told you about the assignment of bean s in IOC before. Therefore, we can directly configure the data source in the IOC container and connect to the specified database. Here, we need to use the CombopooledDataSource class and give it to user Assign values to password, JDBC URL, driverclass and other attributes. At the same time, we configure the maximum number of connections and the minimum number of connections in the connection pool (of course, these two properties can also be configured without configuration).

There are actually two ways to configure the assignment of data sources to attributes:

One is to write the connection information directly in the < bean > < / bean > tag.

The second method is to write the connection information of the data source in a separate file, and then introduce the external configuration file. Here I will introduce both methods:

(1) . directly configure the data source in < bean > < / bean >

Using this method, you only need to write the value of the attribute directly in value, and write the id of the data source at the same time. The code is as follows:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="user" value="root"/>
    <property name="password" value="admin"/>
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/jdbc_template"/>
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    <property name="minPoolSize" value="5"/>
    <property name="maxPoolSize" value="20"/>
</bean>

(2) . import external configuration file

The second method is to import the external configuration file with data source connection information, then import the data source information by using the label of the imported external configuration file, and then assign the data value to the attribute by using the ${} expression. The advantage of this method is that it is convenient to change the change information when the data source is changed, and it can be updated directly in the data source file, There is no need to change the code in the IOC container.

This method requires us to first create a configuration file of data source information, such as JDBC config properties, of course, you can also define other names, such as "xxx.properties". However, it is generally necessary to use ". properties" as the file suffix. Write data source information to file:

jdbc.user=root
jdbc.password=ADMIN
jdbc.jdbcurl=jdbc:mysql://localhost:3306/jdbc_template
jdbc.driverClass=com.mysql.jdbc.Driver

Use the label context: Property placeholder in the IOC container to introduce the external configuration file "JDBC config. Properties".

<!-- Add external profile -->
<context:property-placeholder location="classpath:jdbcconfig.properties"/>

Note: the class here represents the file under the classpath.

Then configure the data source under the < bean > < / bean > tag in the container in the same way, but now the assignment is obtained by using "${}" JDBC config Configuration data in properties. The code is as follows:

<!-- Set database configuration
 Note here:
$Is used to read the information in the configuration file
#Is used for spring applications
-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="user" value="${jdbc.user}"></property>
    <property name="password" value="${jdbc.password}"></property>
    <property name="jdbcUrl" value="${jdbc.jdbcurl}"></property>
    <property name="driverClass" value="${jdbc.driverClass}"></property>
</bean>

3. Configure the JdbcTemplate object

After configuring the data source, we will configure the JdbcTemplate object. Since the JdbcTemplate object is only a JDBC operation template, it needs to introduce external data sources to operate. The specific operation is to assign a data source to the dataSource attribute of the JdbcTemplate class in the IOC.

The code is as follows:

<!-- Build a jdbcTemplate connect -->
<bean id="jdbcTemplate"  class="org.springframework.jdbc.core.JdbcTemplate">
	<constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
</bean>

Until now, the "lightweight" framework of JdbcTemplate has been built and configured. Then, you can normally use JdbcTemplate to perform relevant operations in the database. Let's write a test statement to test whether the database connection is normal under the conditions of ordinary connection and connection with JdbcTemplate:

public class JdbcTest {

	ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
	JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);
		
//	Get the database connection through the common method
	@Test
	public void test() throws SQLException {
		System.out.println("jdbc_template implement");
		DataSource bean = context.getBean(DataSource.class);
		Connection connection = bean.getConnection();
		System.out.println("Common method to get database connection:" + connection);
		
	}
	         
	/**
	 * Get database connection through JDBC template
	 * Experiment: test data source
	 * */
	@Test
	public void test01() {
		System.out.println("jdbcTemplate To get the database connection:" + jdbcTemplate);
	}

}

The following information appears during operation, indicating that the connection is normal:

After confirming that the database connection is normal, you can now reach the core part of the JdbcTemplate. Click the blackboard! To the point!!!

3, Detailed explanation of persistence layer operation

JdbcTemplate has special operation functions to realize different operations of adding, deleting, modifying and querying. Next, I will introduce their specific use to you through the following data table "employee table":

1. Addition, deletion and modification

Miraculously, the same method is used to add, delete and modify the JdbcTemplate, that is:

JdbcTemplate.update(String, Object...)

Two parameters are most commonly used in this method:

The first parameter String passes in the SQL statement to be executed,

Second parameter object Pass in the parameters required in the sql statement. Use object It means that there may be more than one parameter. This method will have a return value of type int, indicating how many rows of data have been modified. Let me demonstrate it to you through an example;

Example: EMP_ The salary field of the record with id = 5 is updated to 1300.00

First, we need to write out the corresponding sql statement, and use "? Where the parameters need to be passed in the statement Indicates that the update method is invoked to implement the modification operation and the number of rows modified is returned.

/** Modify the data in the data table of the database
 * Will EMP_ The salary field of the record with id = 5 is updated to 1300.00*/
// @Test
public void test02() {
    String sql = "UPDATE employee SET salary=? WHERE emp_id=?";
    int update = jdbcTemplate.update(sql, 1300.00, 5);
    System.out.println("Update succeeded!" + update);
}

The above is a modification operation. You can use the same method for delete and add operations.

2. Batch addition, deletion and modification

The above is the operation of adding, deleting and modifying a common single piece of data, but what if a large amount of data needs to perform the same operation? Isn't it too troublesome to come one by one? Therefore, in view of this situation, JdbcTemplate also specially provides batch addition, deletion and modification methods to facilitate our operation of a large amount of data. The specific use is as follows.

This is achieved by calling the following functions:

JdbcTemplate.batchUpdate(String, List<Object[]>)

This method will return an array of int type, which stores the number of rows modified each time the sql statement is executed.

The String still represents the sql statement to be executed,

However, Object [] encapsulates the parameters required for each execution of the SQL statement, while the List set encapsulates all the parameters when the SQL statement is executed multiple times.

We verify the operation of this method through the following example:

Example: batch insert data into the employee table

First, write the sql statement, then write the parameters to be passed into the list set, and then pass the sql statement and list set into the batchUpdate() method.

	/**
	 * Batch insert data
	 * */
	@Test
	public void test03() {
		String sql = "INSERT INTO employee(emp_name,salary) VALUES(?,?)";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		batchArgs.add(new Object[]{"Zhang San","999"});
		batchArgs.add(new Object[]{"Li Si","1999"});
		batchArgs.add(new Object[]{"Wang Wu","2999"});		
		int[] batchUpdate = jdbcTemplate.batchUpdate(sql, batchArgs);
		for (int i : batchUpdate) {
			System.out.println(i);
		}
	}

3. Query single line data

Above, we have learned how to add, delete and modify in the JDBC template. How can the CRUD brothers ignore the important operation of finding? No, it's coming!!!

Querying data in JDBC template is actually very simple, but why doesn't it use the same operation method with the other three operations?

In fact, the reason is very simple. The addition, deletion and modification operation will modify the data table and return the number of modified rows of int type, while the query operation will not modify the data table and return other types of query results at the same time!

First, let's look at how to query single line data. The functions used to query single row data in JDBC template are:

JdbcTemplate.queryForObject(String, RowMapper<Department>, Object...)

String in the parameters of this method also represents the sql statement to be searched,

But there is a hole to note: what is the parameter rowmapper < Department > passed in the middle? In fact, the value here is to pass the type of bean object to be returned. However, in real use, we do not map the bean object to be returned through rowmapper < Department >, but through its subclass BeanPropertyRowMapper. Their inheritance concerns are as follows:

When mapping the returned bean object using BeanPropertyRowMapper, if the object can be found and the mapping is successful, it will return. If it cannot be found, an error will be reported.

The third parameter object Or represents the passed in query parameters.

Let's take a look at such an example.

Example: query EMP_ The database record with id = 5 is encapsulated and returned as a Java object.

/**
	 * Query a single piece of data in the database
	 * Experiment 4: query EMP_ The database record with id = 5 is encapsulated and returned as a Java object
	 * The fields in the created javabean should be the same as the field names in the data table, otherwise they need to be mapped
	 * queryForObject is used to query a single piece of data, but BeanPropertyRowMapper needs to be used in the middle to map the bean object to be generated
	 * 		An error will be reported when it cannot be found
	 * 
	 * */
	@Test
	public void test04() {
		String sql = "SELECT * FROM employee WHERE emp_id=?";
		Employee employee = null;
		try {
			employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Employee>(Employee.class),5);
		} catch (Exception e) {
			// TODO: handle exception
		}
		System.out.println(employee);
		
	}

4. Query multi row data

Different from querying single line data, the methods used to query multi line data are:

JdbcTemplate.query(String, RowMapper<Department>, Object...)

But the parameters passed are the same. The only difference is that the method returns an array list, which contains each piece of data queried.

For example:

Example: query database records with salary > 4000, encapsulate them as a List set, and return them.

	/**
	 * Query multiple pieces of data in the database
	 * Experiment 5: query database records with salary > 4000, encapsulate them as List sets and return them
	 * */
	@Test
	public void test05() {
		String sql  = "SELECT * FROM employee WHERE salary>?";
		List<Employee> employees = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Employee>(Employee.class),4000);
		for (Employee employee : employees) {
			System.out.println(employee);
		}
	}

5. Query a single specified value

Now we know how to query a single piece of data and how to query multiple pieces of data, but these data return rows of data. What if we only want to get the data of a certain row?

That's easy. JDBC template has a special method to return a single value that needs to be queried.

JdbcTemplate.queryForObject(String, Class, Object...)

One return value in this method is class, which indicates the type of data to be returned, such as int or double. At the same time, the method returns the queried value.

For example:

Example: query the largest salary in the employee table.

Obviously, this method returns a specific value without parameters, so we don't need to pass the following object when passing parameters Type parameter.

	/**
	 * Query the data in the data table, but only return a value
	 * Experiment 6: query the maximum salary
	 * */
	@Test
	public void test06() {
		String sql = "SELECT MAX(salary) FROM employee";
		Double quDouble = jdbcTemplate.queryForObject(sql, Double.class);
		System.out.println(quDouble);
	}

The above is all the methods to use JDBC template to realize different operations of adding, deleting, modifying and querying. However, there is another way to operate JDBC template, that is, to use "? In sql statements Expressed by specific parameters. Next, let's introduce how to execute sql statements in this way.

4, JdbcTemplate using named parameters

The operation mode of the JdbcTemplate to be introduced next is a little different from the above. Here, a named parameter is used to represent the parameters to be passed in the sql statement. What is a named parameter?

Named parameter: refers to a parameter with a name. The parameter is no longer a placeholder, but a variable name

Syntax format: parameter name

After using the named parameter, spring will automatically find the parameter with the corresponding name from the passed in parameters and assign its value to the sql statement.

Spring has a JDBC template that supports the function of named parameters, that is, the NamedParameterJdbcTemplate class. In spring, SQL statements with named parameters can be used through the objects of the NamedParameterJdbcTemplate class.

1. Declare a named parameter class

The method of using NamedParameterJdbcTemplate class is similar to that of ordinary JdbcTemplate. Both need to be declared in ioc, as shown below:

<!-- Create a with named parameter support jdbcTemplate -->
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
</bean>

2. Common use of named parameters

Let's take the insertion statement as an example to demonstrate the use of named parameters,

Traditional sql statements are as follows:

INSERT INTO employee(emp_name,salary) values(?,?)

sql statements that use named parameters are like this;

INSERT INTO employee(emp_name,salary) values(:emp_name,:salary)

For example:

Example: insert an employee record using an SQL statement with named parameters, and pass in the parameter value in the form of Map.

	/**
	 * Experiment 7: insert an employee record using SQL statement with named parameters, and pass in the parameter value in the form of Map
	 * Placeholder query parameter:? The order of parameters must not be wrong. You must pay attention when passing parameters
	 * */
	@Test
	public void test07() {
		String sql = "INSERT INTO employee(emp_name,salary) values(:emp_name,:salary)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("emp_name", "Zhao Liu");
		paramMap.put("salary", 998.12);
		int updateNum = jdbcTemplate2.update(sql, paramMap);
		System.out.println(updateNum);
	}

One thing to note here is: whether you use ordinary sql statements or sql statements with named parameters. The order of the parameters passed in should be consistent with the order of the parameters in the sql statement, otherwise the parameter call error will occur. This must be noted!

3. Pass in a value through the SqlParameterSource object

Passing in a value through the SqlParameterSource object actually means that the parameter needs to be passed in the form of a java bean, but there is something to pay attention to.

Note: when using sqlParmeterSource to load data in the database, be sure to note that the parameter name after values corresponds to the parameter name in the bean

Otherwise, the following error will be reported:

No value supplied for the SQL parameter 'emp_Name': Invalid property 'emp_Name' of bean class [com.spring.beans.Employee]: Bean property 'emp_Name' is not readable or has an invalid getter method:

The following is an example to illustrate the parameter passed in through the SqlParameterSource object.

Example: insert an employee record using SQL statement with named parameters, and pass in parameters through SqlParameterSource object.

/**
 * Experiment 8: repeat Experiment 7 and pass in the parameter value in the form of SqlParameterSource
 * */
	@Test
	public void test08() {
		String sql = "INSERT INTO employee(emp_name,salary) values(:emp_name,:salary)";
		Employee employee = new Employee();
		employee.setEmp_name("Wu Jiu");
		employee.setSalary(997.7);
		int updateNum = jdbcTemplate2.update(sql, new BeanPropertySqlParameterSource(employee));
		System.out.println(updateNum);
	}

5, Automatically assemble the JdbcTemplate and implement Dao

Because the jdbctemplate class is thread safe, you can declare its single instance in the IOC container, inject this instance into all Dao instances, and implement automatic assembly of the jdbctemplate in the Dao class. The method of adding, deleting, modifying and querying is implemented. Through the automatically assembled JDBC template, the code operation can be reduced in Dao, and the operation of adding, deleting, modifying and querying can be realized more easily.

The steps of automatically assembling JdbcTemplate and realizing Dao through this method are summarized as follows:

Create dao class
How to write it
Automatic assembly using package scanning
Get dao class from IOC container
Method for implementing database operation in which response is made

The following is verified by an example.

Example: create BookDao, automatically assemble the JdbcTemplate object, and implement an add operation.

In Dao class, we use @ Autowired annotation to automatically assemble JDBC template and implement a data addition method:

@Repository
public class EmployeeDao {
	//	Inject JDBC template automatically
	@Autowired
	JdbcTemplate jdbcTemplate;
	/**
	 * Save data to Datasheet
	 * */
	public int saveEmployee(Employee employee) {
		String sql = "insert into employee(emp_name,salary) values(?,?)";
		return jdbcTemplate.update(sql, employee.getEmp_name(),employee.getSalary());
	}	
}

Test with test method:

	/**
	 * Experiment 9: create BookDao and automatically assemble the JdbcTemplate object
	 * */
	@Test
	public void test09() {
		Employee employee = new Employee();
		employee.setEmp_name("bastard");
		employee.setSalary(888.7);
		int saveEmployeeNum = employeeDao.saveEmployee(employee);
		System.out.println(saveEmployeeNum);
	}

In this way, the work of automatically assembling the jdbctemplate and implementing Dao is completed, which avoids the repeated creation of JDBC template and reduces the amount of code.

6, Write at the end

Ding Ding! So far, I've explained all the operation and use of Spring's JdbcTemplate framework,

This includes building a common JdbcTemplate, implementing simple CURD, and then complex named parameters. I hope you can master the tutorial of using JdbcTemplate through this article. At the same time, in the process of learning, there are places that we don't understand or can't understand. Welcome to leave a message and let's learn together!

No matter how advanced the technology is, it needs to be knocked out one key at a time. Struggle! To every "creator" running on the Java Road!

I'm the grey ape. I'll see you next time!

Topics: Java JavaEE Spring SSM JdbcTemplate

Programmer Think

Liver! Spring JDBC persistence layer framework "family bucket" tutorial!

Write in front

I. what is a JdbcTemplate?

2, JdbcTemplate framework construction

1. Import the required jar package

2. Configuring JDBC data sources

(1) . directly configure the data source in < bean > < / bean >

(2) . import external configuration file

3. Configure the JdbcTemplate object

3, Detailed explanation of persistence layer operation

1. Addition, deletion and modification

2. Batch addition, deletion and modification

3. Query single line data

4. Query multi row data

5. Query a single specified value

4, JdbcTemplate using named parameters

1. Declare a named parameter class

2. Common use of named parameters

3. Pass in a value through the SqlParameterSource object

5, Automatically assemble the JdbcTemplate and implement Dao

6, Write at the end

Hot Topics