“This is the 22nd day of my participation in the First Challenge 2022. For details: First Challenge 2022”

This article describes how to use JDBC in Spring. In fact, using JDBC in Spring is no different from using traditional JDBC or some JDBC frameworks such as DBUtils, so using JDBC in Spring is very simple.

Getting a database connection

Create a Java project, import jar package of Spring, c3P0, database driver, and then create a table to test:

 create table user(
 	id integer primary key auto_increment,
 	name varchar(20),
 	password varchar(20));Copy the code

Next create the Spring configuration file and configure it as follows:


      
<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.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">

	<! -- Import resource file -->
	<context:property-placeholder
		location="classpath:db.properties" />

	<! C3P0 -->
	<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>

</beans>
Copy the code

Test whether the database connection can be successfully obtained:

public class SpringJDBCTest {

	private ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");

	@Test
	public void testConnection(a) throws SQLException { DataSource dataSource = ctx.getBean(DataSource.class); System.out.println(dataSource.getConnection()); }}Copy the code

Running results:

com.mchange.v2.c3p0.impl.NewProxyConnection@5d47c63f
Copy the code

The database connection was successfully obtained.

Update the data table

The traditional JDBC usage is familiar and nothing special in Spring, except that the Bean lifecycle is managed by the IOC container, and the Spring framework has a separate SET of apis for database operations (JDBCTemplate). Let’s test common database operations separately by putting the JDBCTemplate class into the container:

	<! Insert JDBCTemplate into the container -->
	<bean id="jdbcTemplate"
		class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
Copy the code

JDBCTemplate = JDBCTemplate = JDBCTemplate = JDBCTemplate = JDBCTemplate

	@Test
	public void testInsert(a) {
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		String sql = "insert into user(name,password) values(? ,?) ";
		Object[] args = {"zhangsan"."12345"};
		jdbcTemplate.update(sql,args);
	}
Copy the code

Execute code, query table result, insert successful.

mysql> select * from user;
+----+----------+----------+
| id | name     | password |
+----+----------+----------+
|  1 | zhangsan | 12345    |
+----+----------+----------+
1 row in set (0.00 sec)
Copy the code

Then test the alter table operation:

	@Test
	public void testUpdate(a) {
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		String sql = "update user set password = ? where id = ?";
		Object[] args = {"admin"."1"};
		jdbcTemplate.update(sql,args);
	}
Copy the code

Execute the code, query the table result, modify successfully.

mysql> select * from user;
+----+----------+----------+
| id | name     | password |
+----+----------+----------+
|  1 | zhangsan | admin    |
+----+----------+----------+
1 row in set (0.00 sec)
Copy the code

The delete operation is similar to the delete operation. Instead of repeating the test, let’s test the batch operation:

	@Test
	public void testBatchInsert(a) {
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		String sql = "insert into user(name,password) values(? ,?) ";
		List<Object[]> batchArgs = new ArrayList();
		batchArgs.add(new Object[]{"lisi"."123"});
		batchArgs.add(new Object[]{"wangwu"."1234"});
		batchArgs.add(new Object[]{"zhaoliu"."12345"});
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}
Copy the code

In this case, an array of objects is a record, and batch operations require a collection of generic Object arrays. Execute the code, query the table results, batch insert success.

mysql> select * from user;
+----+----------+----------+
| id | name     | password |
+----+----------+----------+
|  1 | zhangsan | admin    |
|  2 | lisi     | 123      |
|  3 | wangwu   | 1234     |
|  4 | zhaoliu  | 12345    |
+----+----------+----------+
4 rows in set (0.00 sec)
Copy the code

SQL > create table User; SQL > create table User

package com.wwj.spring.jdbc;

public class User {

	private Integer id;
	private String name;
	private String password;

	public Integer getId(a) {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName(a) {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword(a) {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString(a) {
		return "User [id=" + id + ", name=" + name + ", password=" + password + "]"; }}Copy the code

Write test code:

	@Test
	public void testGet(a) {
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		String sql = "select id,name,password from user where id = ?";
		RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
		User user = jdbcTemplate.queryForObject(sql, rowMapper,1);
		System.out.println(user);
	}
Copy the code

The running results are as follows:

User [id=1, name=zhangsan, password=admin]
Copy the code

In Spring, the results of the query are processed by RowMapper, which specifies how to map the row data of the ResultSet. Compared to traditional resultsets, RowMapper saves the repetitive operation of traversing the ResultSet, making the query simpler.

You can query multiple pieces of data in the following ways:

	@Test
	public void testGetMore(a) {
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		String sql = "select id,name,password from user where id > ?";
		RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
		List<User> userList = jdbcTemplate.query(sql, rowMapper,2);
		System.out.println(userList);
	}
Copy the code

Running results:

[User [id=3, name=wangwu, password=1234], User [id=4, name=zhaoliu, password=12345]]
Copy the code

Some students might assume that the queryForList() method is called to query multiple pieces of data. In fact, the query() method is called. Note that the API provided by the JDBCTemplate does not support cascading properties.

Get the values of the data form columns:

	@Test
	public void testSingleValue(a) {
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		String sql = "select name from user where id = ?";
		String name = jdbcTemplate.queryForObject(sql, String.class,1);
		System.out.println(name);
	}
Copy the code

The running results are as follows:

zhangsan
Copy the code

In fact, the whole process of adding, deleting, changing and checking are not any difficulty, which is not different from the database operation API before learning, so there is no special need to explain the place, through some test code should be able to understand.

Using named parameters

In classic JDBC usage, SQL parameters are placeholders “?” The problem with locating parameters is that once you change the order of the parameters, you have to change how the parameters are bound. To solve this problem, Spring gives us another option: use named parameters. Using named arguments, SQL to specified by name rather than the position, later named parameters are easier to maintain, and improve the readability of the code, named parameters in NamedParameterJdbcTemplate class only. If you want to use a named parameter, first put NamedParameterJdbcTemplate in container:

	<! - put namedParameterJdbcTemplate in containers - >
	<bean id="namedParameterJdbcTemplate"
		class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>
Copy the code

The class does not have a constructor with no arguments. A constructor of type DataSource is usually used. Here is an example of the use of named parameters by inserting data:

	@Test
	public void testNamedParameterJdbcTemplate(a) {
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) ctx.getBean("namedParameterJdbcTemplate");
		String sql = "insert into user(name,password) values(:name,:psd)";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("name"."wangweijun");
		map.put("psd"."112233");
		namedParameterJdbcTemplate.update(sql,map);
	}
Copy the code

Execute code, query table result, insert successful.

mysql> select * from user;
+----+------------+----------+
| id | name       | password |
+----+------------+----------+
|  1 | zhangsan   | admin    |
|  2 | lisi       | 123      |
|  3 | wangwu     | 1234     |
|  4 | zhaoliu    | 12345    |
|  5 | wangweijun | 112233   |
+----+------------+----------+
5 rows in set (0.00 sec)
Copy the code

While named parameters have many benefits, they also add a lot of code and are arguably more complex than the previous approach. Of course, there are simpler ways to implement it:

	@Test
	public void testSimpleNamedParameterJdbcTemplate(a) {
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) ctx.getBean("namedParameterJdbcTemplate");
		String sql = "insert into user(name,password) values(:name,:password)";
		User user = new User();
		user.setName("zhaoliu");
		user.setPassword("223344");
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(user);
		namedParameterJdbcTemplate.update(sql, paramSource);
	}
Copy the code

Execute code, query table result, insert successful.

mysql> select * from user;
+----+------------+----------+
| id | name       | password |
+----+------------+----------+
|  1 | zhangsan   | admin    |
|  2 | lisi       | 123      |
|  3 | wangwu     | 1234     |
|  4 | zhaoliu    | 12345    |
|  5 | wangweijun | 112233   |
|  6 | zhaoliu    | 223344   |
+----+------------+----------+
6 rows in set (0.00 sec)
Copy the code

This method implements automatic matching of named parameters by object, provided that the name of the named parameter is the same as the name of the property in the class. This method is more reasonable than the first method, because the object is passed from the foreground and can be processed directly through the object without any other operations.

Program source code

Github.com/blizzawang/…