JDBC based

When a Java application uses the JDBC interface to access a relational database, the following steps are required:

Create a global DataSource instance that represents the database connection pool.
Within the method that needs to read and write to the database, follow these steps to access the database:
Get the Connection instance from the global DataSource instance;
Create a PreparedStatement instance from the Connection instance.
Execute the SQL statement. If it is a query, the ResultSet is read through a ResultSet, and if it is a modification, the int result is obtained.

The key to writing JDBC code correctly is to use try… Finally releases resources, and code involving transactions needs to commit or roll back transactions correctly.

Using JDBC in Spring, we first create and manage a DataSource instance through the IoC container. Then, Spring provides a JdbcTemplate that lets us easily manipulate JDBC, so normally we instantiate a JdbcTemplate. As the name implies, this class mainly uses the Template pattern.

HSQLDB is a relational database written in Java that can run in either in-memory or file mode. It has only a JAR package of its own, which is ideal for demonstrating or testing code. MySql is used in this article

Create Maven project spring-data-jdbc and import the following dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.2. 0.RELEASE</version>
    </dependency>
    <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.015.</version>
        </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.2. 0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>javax.annotation</groupId>
        <artifactId>javax.annotation-api</artifactId>
        <version>1.32.</version>
    </dependency>
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>3.42.</version>
    </dependency>
    <dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>2.5. 0</version>
    </dependency>
</dependencies>
Copy the code

In AppConfig, we need to create the following required beans:

@Configuration
@ComponentScan
@PropertySource("jdbc.properties")
public class AppConfig {

    @Value("${jdbc.url}")
    String jdbcUrl;

    @Value("${jdbc.username}")
    String jdbcUsername;

    @Value("${jdbc.password}")
    String jdbcPassword;

    @Bean
    DataSource createDataSource(a) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(jdbcUsername);
        config.setPassword(jdbcPassword);
        config.addDataSourceProperty("autoCommit"."true");
        config.addDataSourceProperty("connectionTimeout"."5");
        config.addDataSourceProperty("idleTimeout"."60");
        return new HikariDataSource(config);
    }

    @Bean
    JdbcTemplate createJdbcTemplate(@Autowired DataSource dataSource) {
        return newJdbcTemplate(dataSource); }}Copy the code

In the above configuration:

through@PropertySource("jdbc.properties")Read the database configuration file;
through@Value("${jdbc.url}")Inject configurations related to configuration files;
Create a DataSource instance whose actual type isHikariDataSource, which requires the injected configuration;
Create a JdbcTemplate instance that requires injectionDataSource, this is done with method arguments.

Finally, write a configuration file jdbc.properties for HSQLDB:

Url = JDBC: HSQLDB :file: testDB # HSQLDB default username sa, password empty string: Jdbc.username =sa jdbc.password= # mysql config # Database file name: testdb: jdbc.driverclass = com.mysql.jdbc.driver jdbc.url= JDBC :mysql://localhost:3306/springjdbc? characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false

jdbc.user=root
jdbc.password=1234
Copy the code

You can initialize the database table using the tools that come with HSQLDB. Here we write a Bean that automatically creates a Users table when the Spring container starts:

@Component
public class DatabaseInitializer {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @PostConstruct
    public void init(a) {
        jdbcTemplate.update("CREATE TABLE IF NOT EXISTS users (" //
                + "id BIGINT IDENTITY NOT NULL PRIMARY KEY, " //
                + "email VARCHAR(100) NOT NULL, " //
                + "password VARCHAR(100) NOT NULL, " //
                + "name VARCHAR(100) NOT NULL, " //
                + "UNIQUE (email))"); }}Copy the code

All preparations are now complete. We just need to inject the JdbcTemplate into the Bean that needs to access the database:

@Component
public class UserService {
    @AutowiredJdbcTemplate jdbcTemplate; . }Copy the code

JdbcTemplate usage

The JdbcTemplate provided by Spring follows the Template pattern and provides a set of callback tool methods designed to avoid tedious try… Catch statement.

Let’s use a concrete example to illustrate the use of JdbcTemplate.

First let’s look at the T execute(ConnectionCallback

action) method, which provides a Jdbc Connection for us to use:

public User getUserById(long id) {
    // Notice that ConnectionCallback is passed in:
    return jdbcTemplate.execute((Connection conn) -> {
        // You can use the conn instance directly, do not release it, JdbcTemplate will be automatically released after the callback ends:
        // If you manually create a PreparedStatement or ResultSet internally, you must use try(...). Release:
        try (var ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
            ps.setObject(1, id);
            try (var rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new User( // new User object:
                            rs.getLong("id"), // id
                            rs.getString("email"), // email
                            rs.getString("password"), // password
                            rs.getString("name")); // name
                }
                throw new RuntimeException("user not found by id."); }}}); }Copy the code

That is, the above callback method allows you to get a Connection and then do anything based on that Connection.

We then look at T execute (String SQL, PreparedStatementCallback < ‘T’ > action) usage:

public User getUserByName(String name) {
    / / need to SQL statements, and PreparedStatementCallback:
    return jdbcTemplate.execute("SELECT * FROM users WHERE name = ?", (PreparedStatement ps) -> {
        // The PreparedStatement instance is already created by the JdbcTemplate and released automatically after the callback:
        ps.setObject(1, name);
        try (var rs = ps.executeQuery()) {
            if (rs.next()) {
                return new User( // new User object:
                        rs.getLong("id"), // id
                        rs.getString("email"), // email
                        rs.getString("password"), // password
                        rs.getString("name")); // name
            }
            throw new RuntimeException("user not found by id."); }}); }Copy the code

T queryForObject(String SQL, Object[] args, RowMapper<‘T’> RowMapper)

public User getUserByEmail(String email) {
    // Pass in SQL, parameters, and RowMapper instances:
    return jdbcTemplate.queryForObject("SELECT * FROM users WHERE email = ?".new Object[] { email },
            (ResultSet rs, int rowNum) -> {
                // Map the current direction of a ResultSet to a JavaBean:
                return new User( // new User object:
                        rs.getLong("id"), // id
                        rs.getString("email"), // email
                        rs.getString("password"), // password
                        rs.getString("name")); // name
            });
}
Copy the code

RowMapper definition:

@FunctionalInterface
public interface RowMapper<T> {
    @Nullable
    T mapRow(ResultSet var1, int var2) throws SQLException;
}
Copy the code

The (ResultSet RS, int rowNum) -> {} statement in the getUserByEmail method implements an object from the RowMapper class

In the queryForObject() method, when SQL and SQL parameters are passed in, the JdbcTemplate automatically creates a PreparedStatement, automatically executes the query, and returns a ResultSet. All the RowMapper we provide needs to do is map the current direction of a ResultSet into a JavaBean and return it. Throughout the process, Connection, PreparedStatement, and ResultSet use does not require manual management.

RowMapper does not necessarily return Javabeans; in fact, it can return any Java object. For example, using a SELECT COUNT(*) query, we can return Long:

public long getUsers(a) {
    return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users".null, (ResultSet rs, int rowNum) -> {
        SELECT COUNT(*) from (SELECT COUNT(*));
        return rs.getLong(1);
    });
}
Copy the code

If we expect to return multiple rows instead of one, we can use the query() method:

public List<User> getUsers(int pageIndex) {
    int limit = 100;
    int offset = limit * (pageIndex - 1);
    return jdbcTemplate.query("SELECT * FROM users LIMIT ? OFFSET ?".new Object[] { limit, offset },
            new BeanPropertyRowMapper<>(User.class));
}
Copy the code

The arguments passed in to the query() method above are still SQL, SQL parameters, and RowMapper instances. Here we use the BeanPropertyRowMapper provided by Spring directly. If the database table structure happens to match the JavaBean property name, then BeanPropertyRowMapper can directly convert a row of records to JavaBean by column name.

If, instead of a query, we are doing insert, update, and delete operations, we need to use the update() method:

public void updateUser(User user) {
    // Pass in SQL, SQL argument, return number of updated rows:
    if (1! = jdbcTemplate.update("UPDATE user SET name = ? WHERE id=?", user.getName(), user.getId())) {
        throw new RuntimeException("User not found by id"); }}Copy the code

Only one INSERT operation is special, and that is if a column is auto-increment (such as auto-increment of a primary key), in general, we need to get the auto-increment of the INSERT. The JdbcTemplate provides a KeyHolder to simplify this operation:

public User register(String email, String password, String name) {
    // Create a KeyHolder:
    KeyHolder holder = new GeneratedKeyHolder();
    if (1! = jdbcTemplate.update(/ / parameter 1: PreparedStatementCreator
        (conn) -> {
            // When creating a PreparedStatement, you must specify RETURN_GENERATED_KEYS:
            var ps = conn.prepareStatement("INSERT INTO users(email,password,name) VALUES(? ,? ,?) ",
                    Statement.RETURN_GENERATED_KEYS);
            ps.setObject(1, email);
            ps.setObject(2, password);
            ps.setObject(3, name);
            return ps;
        },
        2: / / parameter KeyHolder
        holder)
    ) {
        throw new RuntimeException("Insert failed.");
    }
    // Get the returned increment from KeyHolder:
    return new User(holder.getKey().longValue(), email, password, name);
}
Copy the code

JdbcTemplate also has a number of overloaded methods that we won’t cover here. It is important to note that the JdbcTemplate is simply a wrapper around JDBC operations. Its purpose is to minimize manual writing of try(Resource) {… } code, for queries, mainly through RowMapper implementation of JDBC result set to Java object conversion.

Let’s summarize the use of JdbcTemplate:

Query () and queryForObject() are preferred for simple queries because only SQL statements, parameters, and RowMapper are required;
For update operations, update() is preferred because only SQL statements and parameters are supplied;
Any complex operation can eventually be implemented through execute(ConnectionCallback), because you can do any JDBC operation with a Connection.

In fact, we still use queries the most. It is convenient to use BeanPropertyRowMapper directly when designing table structures that correspond to JavaBean properties one by one. What if the table structure is inconsistent with the JavaBean? You’ll need to rewrite the query slightly to keep the result set structure consistent with javabeans.

For example, if the column name of the table is office_address and the JavaBean property is workAddress, you need to specify the alias and rewrite the query as follows:

SELECT id, email, office_address AS workAddress, name FROM users WHERE email = ?
Copy the code

Basic use of RowMapper

Those of you who have used native JDBC should know that all records queried from the database are stored in the ResultSet ResultSet, and we need to retrieve and set the data in the ResultSet piece by piece to a specific entity class so that the entity class can be used in subsequent applications. The problem, however, is that it’s too much work to do this every time, and shouldn’t Spring provide some functionality to do it for us? The answer, of course, is RowMapper, the subject of this article.

There are currently two major RowMapper implementations in Spring JDBC, and using them should suffice for most scenarios.

SingleColumnRowMapper

The name gives us a general idea of what RowMapper should be used when a query returns a single column of data. Let’s look at the code:

@Override
    public String getStudentNameById(String id) {
        String sql = "select name from test_student where id = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[]{id},
                new SingleColumnRowMapper<>(String.class));
    }

    @Override
    public List<String> getStudentNamesByGrade(Integer grade) {
        String sql = "select name from test_student where grade = ?";
        return this.jdbcTemplate.query(sql, new Object[]{grade},
                new SingleColumnRowMapper<>(String.class));
    }
Copy the code

Testing:

@Test
    public void getStudentNameById(a){
        String name = studentService.getStudentNameById("3");
        assertEquals("Li Qingzhao", name);
    }

    @Test
    public void getStudentNamesByGrade(a){
        List<String> names = studentService.getStudentNamesByGrade(1);
        assertTrue(2 == names.size());
    }
Copy the code

BeanPropertyRowMapper

Use RowMapper when a query database returns multi-column data and you need to map the multi-column data to a specific entity class. Here’s how to use RowMapper:

@Override
    public Student getStudentByName2(String name) {
        String sql = "select name, gender from test_student where name = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[]{name},
                new BeanPropertyRowMapper<>(Student.class));
    }

    @Override
    public List<Student> getStudentsByName2(String name) {
        String sql = "select name, gender from test_student where name = ?";
        return this.jdbcTemplate.query(sql, new Object[]{name},
                new BeanPropertyRowMapper<>(Student.class));
Copy the code

This way of use has a premise, that is, database SQL to check the data column name and the entity class attribute name is the same, of course, the number and order can be inconsistent. For example, if the name column in the SQL database is name, the name in the corresponding entity class must be name instead of studentName or other names.

Define your own RowMapper

Of course, you can define and use your own Row Mapper if the column name in your SQL query is different from the attribute name of the entity class, or if you want to assemble the entity class according to your own rules.

public class StudentRowMapper implements RowMapper<Student> {

    @Override
    public Student mapRow(ResultSet rs, int i) throws SQLException {
        Student student = new Student();
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setEmail(rs.getString("email"));
        returnstudent; }}@Override
    public Student getStudentByName3(String name) {
        String sql = "select name, gender, email from test_student where name = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[]{name}, new StudentRowMapper());
    }

    @Override
    public List<Student> getStudentsByName3(String name) {
        String sql = "select name, gender, email from test_student where name = ?";
        return this.jdbcTemplate.query(sql, new Object[]{name}, new StudentRowMapper());
    }
Copy the code

summary

Spring providesJdbcTemplateTo simplify JDBC operations;
useJdbcTemplateAccording to the need to choose advanced methods;
Guaranteed can be used for any JDBC operationExecute (ConnectionCallback) method.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Spring JdbcTemplate: Spring JdbcTemplate: Spring JdbcTemplate

JDBC based

JdbcTemplate usage

Basic use of RowMapper

Define your own RowMapper

summary

Spring JdbcTemplate: Spring JdbcTemplate: Spring JdbcTemplate

JDBC based

JdbcTemplate usage

Basic use of RowMapper

Define your own RowMapper

summary

Related Posts

A behavior-responsibility chain model

Principle and implementation of WebRTC audio and video synchronization

Mysql [1]