This is the 28th day of my participation in the August Text Challenge.More challenges in August

directory

Writing in the front

What is a JdbcTemplate?

Second, JdbcTemplate framework construction

1. Import the required JAR package

2. Configure the JDBC data source

(1) Directly configure the data source in

(2) Import external configuration files

Configure the JdbcTemplate object

Detailed explanation of persistence layer operation

1. Add, delete and modify operations

2. Batch add, delete and change operation

3. Query single row data

4, query multi-row data

5, query a single specified value

Use the named parameter JdbcTemplate

Declare the named parameter class

2. Common use of named parameters

3. Pass in the value from the SqlParameterSource object

Automatic assembly of JdbcTemplate and implement Dao

Write at the end

Writing in the front

Hello, Hello, I am gray little ape, a super bug writing program ape!

We insist on creating technology, tapping the future with our fingertips! May we make our lives smarter and our world more interesting with every keystroke!

When using Spring for business logic layer processing, have you ever wondered whether such a powerful Spring framework will make it easier to handle database-related business processing? To what extent can the Spring framework optimize the operation of a traditional JDBC database?

Today I’m going to explore a lightweight framework for JDBC database operations – JdbcTemplate. Learn the core of the Spring JDBC framework in an article.

​

What is a JdbcTemplate?

Spring’s JdbcTemplate can be seen as a small, lightweight persistence layer framework. To make JDBC operations easier, Spring defines an abstraction layer on top of the JDBC API to build a JDBC access framework.

At the heart of the Spring JDBC framework, it is designed to provide template methods for different types of JDBC operations in a way that minimizes database access while preserving as much flexibility as possible.

Now you know what a jdbcTemplate is, right? So let me talk about how this lightweight framework is used in detail.

Second, JdbcTemplate framework construction

To perform database operations using JdbcTemplate, you need to set up the relevant environment and configure it in advance. So let’s start by looking at how to configure JdbcTemplate in Spring.

1. Import the required JAR package

We know that the framework is usually built when the need to rely on the relevant Jar package to achieve. What jars are required for JdbcTemplate? I gave you a list of functions and sorted them out,

① JAR packages required by IOC containers

• Commons logging – 1.1.1. The jar
• Spring beans – 4.0.0. RELEASE. The jar
• Spring – the context – 4.0.0. RELEASE. The jar
• Spring – the core – 4.0.0. RELEASE. The jar
• Spring – expression – 4.0.0. RELEASE. The jar

② JAR package for JdbcTemplate

• Spring – JDBC – 4.0.0. RELEASE. The jar
• Spring – the orm – 4.0.0. RELEASE. The jar
• Spring – tx – 4.0.0. RELEASE. The jar

③ Database driver and data source

• C3p0-0.9.1.2. Jar
• Mysql connector – Java – 5.1.7 – bin. The jar

Above these JAR packages, including SSM development required all JAR packages I sorted out for you, download can be used.

Download the SSM framework Jar package

Now that you’ve imported all the jars you depend on, it’s time to use these resources to build the next JdbcTemplate framework,

2. Configure the JDBC data source

Since the operation is on the database, the data source must be needed. Let’s take MySQL database as an example to configure the data source. I have also told you about the assignment of beans in IOC before, so we can directly configure the data source in IOC container and connect to the specified database. Use the CombopooledDataSource class to assign values to user, password, jdbcurl, driverClass, etc. We also configure the maximum and minimum number of connections in the connection pool (of course, these two properties are also not required).

There are two ways to configure data source assignments:

One is to write the connection information directly into the tag.

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

(1) Directly configure the data source in

To use this method, just write the value of the property directly in value and write the id of the data source 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>Copy the code

(2) Import external configuration files

The second way is to introduce an external configuration file with a data source connection information, and then use the introduction of external configuration file tag data source information is introduced, using * * * * ${} expression data value assigned to the attribute, the benefits of using this method is convenient to change change information when data changes, directly in the source file can be updated, There is no need to change code in the IOC container.

This method requires that we first create a configuration file for the data source information, such as jdbcconfig.properties, but of course you can define other names, such as “xxx.properties”. ** However, the file suffix is usually “. Properties “. ** File to write data source information:

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

Introduce the external configuration file “jdbcconfig.properties” in the IOC container using the tag Context :property-placeholder.

<! - add external configuration files - > < context: the property - placeholder location = "classpath: jdbcconfig. Properties" / >Copy the code

** Note: ** where class represents a file under the classpath.

Then configure the data source in the same way under the tag in the container, but now the assignment is using the configuration data from jdbcconfig.properties obtained with ‘${}’. The code is as follows:

<! Mysql > set database configuration # $is used to read the information in the configuration file is used for spring applications - > < bean id = "dataSource" class = "boPooledDataSource com.mchange.v2.c3p0.Com" > < 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>Copy the code

Configure the JdbcTemplate object

After we have configured the data source, it is time to configure the JdbcTemplate object, which is only a JDBC operation template, so it needs to import external data sources to operate on. To do this, assign the dataSource attribute of the JdbcTemplate class in IOC.

The code is as follows:

<! - to establish a jdbcTemplate connection - > < bean id = "jdbcTemplate" class = "org. Springframework. JDBC. Core. JdbcTemplate" > < constructor - arg name="dataSource" ref="dataSource"></constructor-arg> </bean>Copy the code

JdbcTemplate is a lightweight framework that can be used to connect to a database using JdbcTemplate. The JdbcTemplate framework is a lightweight framework that can be used to connect to a database using JdbcTemplate. Database connection is normal:

public class JdbcTest { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class); @test public void Test () throws SQLException {system.out.println ("jdbc_template executes "); DataSource bean = context.getBean(DataSource.class); Connection connection = bean.getConnection(); System.out.println(" Normal method to get database connection :" + connection); } /** * get database connection from jdbcTemplate * */ @test public void test01() {system.out.println ("jdbcTemplate "); }}Copy the code

If the following information is displayed, the connection is normal:

​

Now that you’ve made sure your database connection is working, you’re getting to the core of using the JdbcTemplate. Get to the point!!

​

Detailed explanation of persistence layer operation

JdbcTemplate has a set of action functions to implement different add, delete, change, and query operations. I will use the following table “Employee” to describe how they are used:

​

1. Add, delete and modify operations

Amazingly, the add, delete, and modify operations on the JdbcTemplate are done using the same method:

JdbcTemplate.update(String, Object…)

The most common arguments to this method are:

The first argument, String, is passed in to the SQL statement to execute,

The second argument Object… Pass in the parameters needed in the SQL statement, using object… Means that there may be more than one argument behind it. This method returns a value of type int, indicating how many rows were modified. Here’s an example;

Example: Update the salary field of the record emp_id=5 to 1300.00

First, we need to write the corresponding SQL statement, which needs to pass the position of the parameter using “? “. The update method is then called to perform the modification and returns the number of rows that were modified:

*/ / @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 successful!" + update); }Copy the code

The above is a modification operation. Use the same method for delete and add operations.

2. Batch add, delete and change operation

The above is for ordinary single data add, delete, change operation, but what if there is a large number of data need to perform the same operation? Wouldn’t it be too much trouble to come one by one? Therefore, JdbcTemplate also specially provides batch add, delete and change methods to facilitate the operation of large amounts of data. Here’s how it works.

This is done by calling the following function:

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

This method returns an array of type int containing the number of rows modified each time the SQL statement is executed.

The String still represents the SQL statement to execute,

But Object[] encapsulates the parameters required for each execution of the SQL statement, while the List collection encapsulates all the parameters required for multiple executions of the SQL statement.

We use the following example to verify the operation of this method:

Example: Batch insert data into table EMPLOYEE

You first need to write the SQL statement, then write the parameters you want to pass to the list collection, and then pass the SQL statement and list collection to the batchUpdate() method.

@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[]{" ","2999"}); int[] batchUpdate = jdbcTemplate.batchUpdate(sql, batchArgs); for (int i : batchUpdate) { System.out.println(i); }}Copy the code

3. Query single row data

Now that we’ve seen how to add, delete, and modify a jdbcTemplate, how can the CRUD brothers care less about lookup? It’s coming!!

​

Querying data from the jdbcTemplate is actually quite simple, but why doesn’t it use the same operation method as the other three operations?

Select * from table where row (s) are changed; select * from table where row (s) are changed; select * from table where row (s) are changed;

First let’s look at how to query a single row of data. The function used to query a single row of data in the jdbcTemplate is:

JdbcTemplate.queryForObject(String, RowMapper, Object…)

The String argument to this method also represents the SQL statement to be searched,

** But there is a catch: ** what is RowMapper? RowMapper is a subclass of Bea****nPropertyRowMapper, and its inheritance concerns are as follows: RowMapper (); RowMapper ();

​

When using the Bean object returned by the BeanPropertyRowMapper map, the object is returned if it can be found and mapped successfully, or an error is reported if it cannot be found.

The third argument object… Again represents the query parameter passed in.

Let’s look at an example to get the idea.

Example: Query the database record where emp_id=5 and return it wrapped as a Java object.

/** * select * from database; Select * from javabean where emp_id=5; select * from javabean where emp_id=5; Otherwise, you need to map a single piece of data using queryForObject, but you need to use BeanPropertyRowMapper to map the bean object that needs to be generated 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); }Copy the code

4, query multi-row data

Instead of querying a single row, you need to query multiple rows using the following methods:

JdbcTemplate.query(String, RowMapper, Object…)

But the arguments are the same, except that this method returns an arraylist containing every item of data queried.

Here’s an example:

Salary >4000; salary>4000;

/** * select * from database; @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); }}Copy the code

5, query a single specified value

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

That’s manageable. The jdbcTemplate has a special method for returning the single value you want to query.

JdbcTemplate.queryForObject(String, Class, Object…)

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

Here’s an example:

Example: Select the maximum salary from employee.

This method obviously returns a specific value, and there is no argument, so we do not need to pass the object when passing the parameter… Type parameter.

/** * select * from table; @test public void test06() {String SQL = "SELECT MAX(salary) FROM employee"; Double quDouble = jdbcTemplate.queryForObject(sql, Double.class); System.out.println(quDouble); }Copy the code

That’s all you need to do with jdbcTemplate to implement different add, delete, change, query operations. In terms of specific parameters. Let’s take a look at executing SQL statements this way.

Use the named parameter JdbcTemplate

The next JdbcTemplate works a little differently. It uses a named parameter to represent the parameter passed in the SQL statement.

** 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 automatically looks for the parameter with the name from the passed parameter and assigns its value to the SQL statement.

The Spring has a support named parameter feature jdbcTemplate, namely NamedParameterJdbcTemplate class, in the Spring can be used by NamedParameterJdbcTemplate objects of a class with a named parameter SQL statements.

Declare the named parameter class

Use NamedParameterJdbcTemplate kind way is similar to ordinary JdbcTemplate, all need a statement in the ioc, as shown below:

<! - create a jdbcTemplate with named parameters for support - > < bean id = "namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean>Copy the code

2. Common use of named parameters

Let’s use the insert statement as an example to demonstrate the use of named parameters,

A traditional SQL statement looks like this:

INSERT INTO employee(emp_name,salary) values(? ,?)Copy the code

SQL statements with named parameters look like this;

INSERT INTO employee(emp_name,salary) values(:emp_name,:salary)
Copy the code

Here’s an example:

Example: Insert an employee record using an SQL statement with named parameters and pass in the parameter values as a Map.

/** * Experiment 7: Insert an employee record using a SQL statement with a named parameter and pass in a Map of parameter values * placeholders:? In the right order, @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 6 "); ParamMap. Put (" salary ", 998.12); int updateNum = jdbcTemplate2.update(sql, paramMap); System.out.println(updateNum); }Copy the code

** One thing to note here is: ** Whether you use plain SQL statements or SQL statements with named parameters. The order of the parameters passed in must be the same as the order of the parameters in the SQL statement, otherwise there will be a parameter call error, which must be paid attention to!

3. Pass in the value from the SqlParameterSource object

Passing in values via the SqlParameterSource object is essentially passing in parameters as Javabeans, but there are a few caveats.

Note: When using sqlParmeterSource for data loading in the database, it is important 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 of passing parameters through the SqlParameterSource object.

Example: Insert an employee record using an SQL statement with named parameters, passed in via the SqlParameterSource object.

/** * experiment 8: Repeat experiment 7, @test public void test08() {String SQL = "INSERT INTO employee(emp_name,salary) values(:emp_name,:salary)"; Employee employee = new Employee(); Employee. SetEmp_name (" nine wu "); The employee setSalary (997.7); int updateNum = jdbcTemplate2.update(sql, new BeanPropertySqlParameterSource(employee)); System.out.println(updateNum); }Copy the code

Automatic assembly of JdbcTemplate and implement Dao

** Since the JdbcTemplate class is thread-safe, ** it is possible to declare a single instance of it in the IOC container and inject that instance into all Dao instances, implementing auto-assembly of the JdbcTemplate in the Dao class. And in which to realize the method of add, delete, change and check, through the automatic assembly of jdbcTemplate can reduce the code operation in Dao, more easily realize add, delete, change and check operation.

Using this method to automatically assemble the JdbcTemplate and implement the Dao I summarized the steps for you:

1. Establish a dao class
2. The way to write it
3. Automatic assembly using packet scanning
4. Get the DAO class from the IOC container
5. A method to implement the database operation that responds therein

The following is verified by an example.

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

In the Dao class, we use the @AutoWired annotation to automatically assemble the jdbcTemplate and implement a method for adding data:

@repository public class EmployeeDao {// Insert jdbcTemplate into @autoWired jdbcTemplate; Public int saveEmployee(Employee) {String SQL = "insert into Employee(emp_name,salary)" values(? ,?) "; return jdbcTemplate.update(sql, employee.getEmp_name(),employee.getSalary()); }}Copy the code

Test using test methods:

@test public void test09() {Employee Employee = new Employee(); The employee setEmp_name (" tortoise "); The employee setSalary (888.7); int saveEmployeeNum = employeeDao.saveEmployee(employee); System.out.println(saveEmployeeNum); }Copy the code

This completes the work of automating the JdbcTemplate assembly and implementing the Dao, eliminating the need to repeatedly create the JdbcTemplate and reducing the amount of code.

Write at the end

Ding ding! Spring’s JdbcTemplate framework is now fully operational.

This ranges from plain JdbcTemplate scaffolding to implementing simple curds to complex named parameters. In this article you will learn how to use JdbcTemplate. At the same time in the learning process encountered do not understand or not the place, welcome to leave a message, we learn together!

No matter how advanced the technology is, you need to knock it out by one key. To every “creator” on the Java road!

I’m Grey Ape, and I’ll see you next time!

​

​