Spring JdbcTemplate

I wrote an article yesterday Why don't many people want to use hibernate? Blog, with the majority of ape friends to discuss the advantages and disadvantages of hibernate and the way to connect to the database, go home to study, found that Spring JdbcTemplate is very useful, made a summary, share to you.

Disclaimer: This article is just a little humble opinion of the editor. Don't spray if you don't like it.

If you want speed, you will not reach it. If you want speed, you will reach it!

1, hibernate vs. Spring JdbcTemplate

Hibernate mass processing massive data is not desirable in terms of performance, which wastes a lot of memory. In terms of its mechanism, hibernate is to query the qualified data, put it into memory, and then operate it. The operation is as fierce as a tiger, but the performance is not ideal.

When Spring JdbcTemplate and Hibernate handle simple query operations, their efficiency is basically the same, even hibernate's efficiency is slightly higher. But when performing batch operations and tedious operations, hibernate's efficiency can reach 80% of Spring JdbcTemplate. But hibernate can greatly improve the efficiency of development, such as paging and other complex development can be directly completed, so it makes up for the lack of efficiency.

Spring jdbctemplate is used to pursue execution efficiency and hibernate is used to pursue development efficiency. If there are many batch operations, spring jdbctemplate is recommended.

2, Advantages of Spring JdbcTemplate over hibernate

In the process of development, I have more contacts with hibernate and mybatis. Both persistence layer frameworks don't feel good. Hibernate won't talk about it Why don't many people want to use hibernate?

Mybatis is a lightweight encapsulation of the persistence layer. In mybatis, if you need to perform an operation, you need to define mapper first, then mapper.xml. In mapper.xml, we need to complete the model mapping and write the interface related sql. The author thinks that there are many repetitive works. If you can write sql directly in a java class and perform simple object operations at the same time, then the program will not only have some advantages of hibernate, but also some advantages of mybatis. Isn't it very fragrant? Spring JdbcTemplate came into being.

3, Introduction to Spring JdbcTemplate

Spring encapsulates the operation of database deeply on jdbc. With spring's injection function, DataSource can be registered in JdbcTemplate.

The JdbcTemplate is located atMedium. Its fully qualified name is org.springframework.jdbc.core.JdbcTemplate. To use the JdbcTemplate, you need anotherThis includes transaction and exception control.

4, Methods provided by JdbcTemplate

1. execute method

It can be used to execute any SQL statement, and is generally used to execute DDL statements.

2. update method and batchUpdate method

The update method is used to execute new, modified, deleted statements; the batchUpdate method is used to execute batch processing related statements.

3. query method and queryForXXX method

Used to execute query related statements.

4. call method

Used to execute stored procedure and function related statements.

5, Case study

1. Create a new property profile under src

jdbc.user=root
jdbc.password=123456
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc\:mysql\:///test

We usually put the configuration information of the database in a single file, which is also convenient for later maintenance.

2. Configure the Spring configuration file applicationContext.xml

<context:property-placeholder location="classpath:db.properties"/>
<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="driverClass" value="${jdbc.driverClass}"></property>
    <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
</bean>

The first line of code: used to read the data in the db.properties file.

The second line of code: used to configure a data source. Here, the data implementation class comes from a property class in C3P0. The value of the property is from db.properties.

Line 9: configure a JdbcTemplate instance and inject a dataSource data source.

3. update() method

(1) Insert data through update

//Start IoC container
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
//Get JdbcTemplate instance in IoC container
JdbcTemplate jdbcTemplate=(JdbcTemplate) ctx.getBean("jdbcTemplate");
String sql="insert into user (name,deptid) values (?,?)";
int count= jdbcTemplate.update(sql, new Object[]{"caoyc",3});
System.out.println(count);

Here is the update method. The second parameter can be a variable parameter. As you can see in the database, the data is inserted correctly

(2) Modify data through update

String sql="update user set name=?,deptid=? where id=?";
jdbcTemplate.update(sql,new Object[]{"zhh",5,51});

(3) Delete data through update

String sql="delete from user where id=?";
jdbcTemplate.update(sql,51);

4. batchUpdate() bulk insert, update and delete methods

Batch insertion

String sql="insert into user (name,deptid) values (?,?)";

List<Object[]> batchArgs=new ArrayList<Object[]>();
batchArgs.add(new Object[]{"caoyc",6});
batchArgs.add(new Object[]{"zhh",8});
batchArgs.add(new Object[]{"cjx",8});

jdbcTemplate.batchUpdate(sql, batchArgs);

The second parameter of the batchUpdate method is a List collection whose element is an Object [] array type.

5. Read data from database to entity object

(1) Define a User entity class first

package com.proc;

public class User {
    private Integer id;
    private String name;
    private Integer deptid;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getDeptid() {
        return deptid;
    }
    public void setDeptid(Integer deptid) {
        this.deptid = deptid;
    }

    public String toString() {
        return "User [id=" + id + ", name=" + name + ", deptid=" + deptid + "]";
    }
}

(2) Read single object

String sql="select id,name,deptid from user where id=?";
RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
User user= jdbcTemplate.queryForObject(sql, rowMapper,52);
System.out.println(user);

Output result

User [id=52, name=caoyc, deptid=6]

Be careful:

Using BeanPropertyMapper requires that the columns and entity attributes queried from sql data need to correspond one by one. If the column name and attribute name are inconsistent in the data, you need to use as to get another alias in the sql statement.
Cannot get Association object using JdbcTemplate object

(3) Read multiple objects

String sql="select id,name,deptid from user";

RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
List<User> users= jdbcTemplate.query(sql, rowMapper);
for (User user : users) {
    System.out.println(user);
}

Output result

...

User [id=49, name=Name 49, deptid=5]
User [id=50, name=Name 50, deptid=8]
User [id=52, name=caoyc, deptid=6]
User [id=53, name=zhh, deptid=8]
User [id=54, name=cjx, deptid=8]

...

(4) Get a record, a column or count, avg, sum and other functions to return unique values

String sql="select count(*) from user";
int count= jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(count);

6, Application in actual development

1,UserDao.java

package com.proc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public User get(int id){
        String sql="select id,name,deptid from user where id=?";
        RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
        return jdbcTemplate.queryForObject(sql, rowMapper,id);
    }
}

2. XML configuration

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

<context:component-scan base-package="com.proc"></context:component-scan>
<context:property-placeholder location="classpath:db.properties"/>
<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="driverClass" value="${jdbc.driverClass}"></property>
    <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
</bean>
</beans>

3. Code test

UserDao userDao=(UserDao) ctx.getBean("userDao");
System.out.println(userDao.get(53));

Recommend Bo Wen

Java framework (SSH, SSM, Springboot)

Keywords: Programming SQL JDBC Hibernate Spring

Added by tooNight on Tue, 14 Jan 2020 11:05:31 +0200

Programming VIP

Spring JdbcTemplate

Popular Keywords