SpringBook Advanced JdbcTemplate Data Query Chapter 2

Keywords: Programming SQL Spring git JDBC

SpringBook Advanced Paper JdbcTemplate Data Query Part I This paper describes how to use JdbcTemplate to perform simple query operations. It mainly introduces the invocation postures of three methods: queryForMap, queryForList and queryForObject.

  • queryForRowSet
  • query

<!-- more -->

I. Environmental preparation

The environment is still configured with the help of the previous article, such as: Data insertion using posture in JdbcTemplate, 190407-SpringBook Advanced Paper

Or look directly at the project source code: https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate

The data we query is the result of the previous insertion, as shown below.

II. Instructions for Query Use

1. queryForRowSet

The three methods of querying are introduced in the previous section. The structure of the returned records is either map or encapsulated by RowMapper. The invocation of queryForRowSet method returns SqlRowSet object, which is a collection, that is to say, multiple records can be queried.

The use of posture is also relatively simple, as follows

public void queryForRowSet() {
    String sql = "select * from money where id > 1 limit 2";
    SqlRowSet result = jdbcTemplate.queryForRowSet(sql);
    while (result.next()) {
        MoneyPO moneyPO = new MoneyPO();
        moneyPO.setId(result.getInt("id"));
        moneyPO.setName(result.getString("name"));
        moneyPO.setMoney(result.getInt("money"));
        moneyPO.setDeleted(result.getBoolean("is_deleted"));
        moneyPO.setCreated(result.getDate("create_at").getTime());
        moneyPO.setUpdated(result.getDate("update_at").getTime());

        System.out.println("QueryForRowSet by DirectSql: " + moneyPO);
    }
}

There's not much difference in the use of posture from the previous one. Another is that sql also supports the use of placeholders, such as

// Search by placeholder
sql = "select * from money where id > ? limit ?";
result = jdbcTemplate.queryForRowSet(sql, 1, 2);
while (result.next()) {
    MoneyPO moneyPO = new MoneyPO();
    moneyPO.setId(result.getInt("id"));
    moneyPO.setName(result.getString("name"));
    moneyPO.setMoney(result.getInt("money"));
    moneyPO.setDeleted(result.getBoolean("is_deleted"));
    moneyPO.setCreated(result.getDate("create_at").getTime());
    moneyPO.setUpdated(result.getDate("update_at").getTime());

    System.out.println("QueryForRowSet by ? sql: " + moneyPO);
}

Focus on the processing of the results, we need to iterate through the data, get the value of each column record in the same way as before, can be obtained by serial number (serial number starts from 1), can also be established by column name (db column name)

2. query

For the use of query method, there are four different ways to deal with the results, which are explained one by one below.

a. Callback queryByCallBack

In this callback mode, the query method does not return the result, but it needs to pass in a callback object, which will be called automatically after the result is queried.

private void queryByCallBack() {
    String sql = "select * from money where id > 1 limit 2";
    // This is a callback mode and does not return the result; a record callback once
    jdbcTemplate.query(sql, new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet rs) throws SQLException {
            MoneyPO moneyPO = result2po(rs);
            System.out.println("queryByCallBack: " + moneyPO);
        }
    });
}

In the example code above, you can see that a ResultSet object is passed into the callback method, which simply encapsulates a method converted to PO.

private MoneyPO result2po(ResultSet result) throws SQLException {
    MoneyPO moneyPO = new MoneyPO();
    moneyPO.setId(result.getInt("id"));
    moneyPO.setName(result.getString("name"));
    moneyPO.setMoney(result.getInt("money"));
    moneyPO.setDeleted(result.getBoolean("is_deleted"));
    moneyPO.setCreated(result.getDate("create_at").getTime());
    moneyPO.setUpdated(result.getDate("update_at").getTime());
    return moneyPO;
}

In subsequent tests, you will see that two lines of data are output, that is to say

Each record in the return result executes the above callback method once, i.e. returns n pieces of data, and the above callback executes n times.

b. Result Set Extractor for batch processing

The previous callback method is mainly aimed at irrelevant return results, here is the return results, encapsulated into our expected object, and then returned.

private void queryByResultSet() {
    String sql = "select * from money where id > 1 limit 2";
    // extractData receives batch results, so it can be understood as a conversion of all results at once, which can be compared with the RowMapper approach.
    List<MoneyPO> result = jdbcTemplate.query(sql, new ResultSetExtractor<List<MoneyPO>>() {
        @Override
        public List<MoneyPO> extractData(ResultSet rs) throws SQLException, DataAccessException {
            List<MoneyPO> list = new ArrayList<>();
            while (rs.next()) {
                list.add(result2po(rs));
            }
            return list;
        }
    });

    System.out.println("queryByResultSet: " + result);
}

Pay extra attention to your use above. If you return more than one data, note that the generic parameter type is List<?>. In short, this is a batch conversion scenario for the results.

So when the extractData method calls above, you pass in multiple pieces of data, and you need to iterate through them yourself instead of using them as in the first one.

c. Result single-line processing RowMapper

Since there is batch processing ahead, of course there is a single line conversion, as follows

private void queryByRowMapper() {
    String sql = "select * from money where id > 1 limit 2";
    // If multiple pieces of data are returned, the mapRow method is invoked one by one, so it can be understood as a conversion of a single record.
    List<MoneyPO> result = jdbcTemplate.query(sql, new RowMapper<MoneyPO>() {
        @Override
        public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
            return result2po(rs);
        }
    });
    System.out.println("queryByRowMapper: " + result);
}

In practice, just remember that the RowMapper mode passes in a single record, n calls, and the ResultSetExtractor mode passes in all records, one call.

d. occupying sql

Several of the previous introductions are directly written sql, which is not recommended writing, but more common is occupied sql, which is replaced by passing parameters. This kind of use is more introduced in the previous blog article. Here is a simple demonstration.

private void queryByPlaceHolder() {
    String sql = "select * from money where id > ? limit ?";
    // The placeholder, with the actual sql parameter added at the end, can also be replaced by ResultSetExtractor
    List<MoneyPO> result = jdbcTemplate.query(sql, new RowMapper<MoneyPO>() {
        @Override
        public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
            return result2po(rs);
        }
    }, 1, 2);
    System.out.println("queryByPlaceHolder: " + result);
}

e. PreparedStatement approach

We use PreparedStatement a lot when inserting records, especially when we need to return the primary key id. It can also be used in actual queries, especially when using PreparedStatement Creator, we can set the db connection parameters of queries.

private void queryByPreparedStatement() {
    // Using Prepared Statement Creator queries, you can set connection-related parameters, such as read-only.
    List<MoneyPO> result = jdbcTemplate.query(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            con.setReadOnly(true);
            PreparedStatement statement = con.prepareStatement("select * from money where id > ? limit ?");
            // Represents ID > 1
            statement.setInt(1, 1);
            // Represents limit 2
            statement.setInt(2, 2);
            return statement;
        }
    }, new RowMapper<MoneyPO>() {
        @Override
        public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
            return result2po(rs);
        }
    });

    System.out.println("queryByPreparedStatement: " + result);
}

The above is a typical case, but it's hardly fun to actually use JdbcTemplate.

f. No data scenarios

In the previous query, if no result hits sql in a single query, an exception will be thrown. What about here?

private void queryNoRecord() {
    // What happens without a hit?
    List<MoneyPO> result = jdbcTemplate
            .query("select * from money where id > ? limit ?", new Object[]{100, 2}, new RowMapper<MoneyPO>() {
                @Override
                public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return result2po(rs);
                }
            });

    System.out.println("queryNoRecord: " + result);
}

As you can see from the subsequent output, it doesn't matter if you don't record a hit. It returns an empty set.

III. Testing & Summary

1. test

Next, test the output above.

package com.git.hui.boot.jdbc;

import com.git.hui.boot.jdbc.insert.InsertService;
import com.git.hui.boot.jdbc.query.QueryService;
import com.git.hui.boot.jdbc.query.QueryServiceV2;
import com.git.hui.boot.jdbc.update.UpdateService;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * Created by @author yihui in 11:04 19/4/4.
 */
@SpringBootApplication
public class Application {
    private QueryServiceV2 queryServiceV2;

    public Application(QueryServiceV2 queryServiceV2) {
        this.queryServiceV2 = queryServiceV2;
        queryTest2();
    }

    public void queryTest2() {
        // The third call
        queryServiceV2.queryForRowSet();
        queryServiceV2.query();
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

The output of the above execution is as follows

2. summary

This paper mainly introduces two other query postures, query ForRowSet and query.

queryForRowSet

  • Returning the SqlRowSet object requires traversal to get all the results

query

  • Provide three ways to process results
    • Callback posture without returning the result
    • Result Set Extractor for batch processing of results
    • RowMapper for single iteration of results
  • You can return >= 0 data
  • If you need to set the connection parameters of the query, use PreparedStatement Creator to create PreparedStatement processing

IV. other

Related blog

2. statement

Letters are not as good as letters. The above contents are purely family statements. Due to limited personal abilities, there are inevitably omissions and errors. If you find bug s or have better suggestions, you are welcome to criticize and correct them. Thank you very much.

Posted by danmahoney245 on Tue, 23 Apr 2019 17:48:35 -0700

Hot Keywords

©2024 Programmer Group