SpringBook Advanced Paper JdbcTemplate Data Query Part I

Keywords: Programming SQL Spring Java github

The previous article describes how to use JdbcTemplate to insert data, and then to enter the most common query in real business. Because the posture of the query is too much, the content is split. This article mainly introduces several basic postures.

  • queryForMap
  • queryForList
  • queryForObject

<!-- 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. queryForMap

queryForMap, commonly used to query single data, and then fill the fields queried in db into the map, with key as column name and value as value.

a. Basic Posture

The basic usage posture is to write the complete sql directly and execute it.

String sql = "select * from money where id=1";
Map<String, Object> map = jdbcTemplate.queryForMap(sql);
System.out.println("QueryForMap by direct sql ans: " + map);

The advantage of this usage is simplicity and intuition; but there is a very lethal disadvantage if you provide an interface for

public Map<String, Object> query(String condition) {
  String sql = "select * from money where name=" + condition;
  return jdbcTemplate.queryForMap(sql);
}

Look directly at the above code, will you find the problem???

Experienced buddies may find sql injection problems at once. If the input parameter is'gray blog'or 1 = 1 order by ID desc limit 1, does the output match our expectations?

b. Placeholder substitution

It is precisely because the direct spelling of sql may lead to sql injection only, so the preferred way of writing is through placeholder + parameterization.

// Search with placeholder substitution
sql = "select * from money where id=?";
map = jdbcTemplate.queryForMap(sql, new Object[]{1});
System.out.println("QueryForMap by ? ans: " + map);

// Specify the parameter type to fill the placeholder in sql
sql = "select * from money where id =?";
map = jdbcTemplate.queryForMap(sql, 1);
System.out.println("QueryForMap by ? ans: " + map);

As can be seen from the above example, the use of placeholders is very simple. Say Hello (?) instead of specific values, and then pass on the parameters.

There are two kinds of postures: one is to pass in an Object [] array; the other is to pass in an indefinite length parameter of java; the two placeholder substitutions are in order, that is, if you have a value to replace more than one placeholder, you will have to bleed many times.

Such as:

sql = "select * from money where (name=? and id=?) or (name=? and id=?)";
map = jdbcTemplate.queryForMap(sql, "Ash blog", 1, "Ash blog", 2);

c. Undetectable case s

One of the things you have to pay attention to when using queryForMap is that if you can't find the data, an exception will be thrown, so additional processing is needed for this scenario.

// No data available
try {
    sql = "select * from money where id =?";
    map = jdbcTemplate.queryForMap(sql, 100);
    System.out.println("QueryForMap by ? ans: " + map);
} catch (EmptyResultDataAccessException e) {
    e.printStackTrace();
}

2. queryForList

The former is mainly aimed at a single query. If there are multiple query scenarios, you may need to use queryForList. Its use posture is not different from the above.

a. Basic Posture

The basic usage posture is, of course, to write sql directly and execute it.

System.out.println("============ query for List! ==============");
String sql =
        "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, unix_timestamp(update_at) as updated from money limit 3;";

// Returns List < Map < String, Object > type data by default, and returns an empty collection if none of the data exists.
List<Map<String, Object>> res = jdbcTemplate.queryForList(sql);
System.out.println("basicQueryForList: " + res);

Note that the result returned is List < Map < String, Object). If none of the items hit, an empty set will be returned, unlike Query ForMap throwing exceptions.

b. Placeholder substitution

Queries directly using sql, as before, may have injection problems, of course, the preferred use of placeholder to refer to the way

String sql2 = "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, " +
        "unix_timestamp(update_at) as updated from money where id=? or name=?;";
res = jdbcTemplate.queryForList(sql2, 2, "Ash 2");
System.out.println("queryForList by template: " + res);

3. queryForObject

If it's a simple query, it's enough to use the above two directly, but for those who have used mybatis and Hibernate, it's really a bit painful to return Map < String, Object> every time. For a database like mysql, the structure of the table is basically unchanged, and it can be associated with POJO. For business developers, of course, it's easier and more intuitive to operate specific POJO than Map. More

Here's how to use queryForObject to achieve our goals

a. Original use posture

First of all, we will introduce the most primitive posture using RowMapper to demonstrate.

The first step is to define the corresponding POJO class

@Data
public static class MoneyPO implements Serializable {
    private static final long serialVersionUID = -5423883314375017670L;
    private Integer id;
    private String name;
    private Integer money;
    private boolean isDeleted;
    private Long created;
    private Long updated;
}

Then use posture.

// sql + specify return type access
// The advantage of using this sql is that it is convenient to use reflection method to realize PO assignment.
String sql =
        "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, unix_timestamp(update_at) as updated from money limit 1;";
// Note that the subscript begins with 1
MoneyPO moneyPO = jdbcTemplate.queryForObject(sql, new RowMapper<MoneyPO>() {
    @Override
    public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
        MoneyPO po = new MoneyPO();
        po.setId(rs.getInt(1));
        po.setName(rs.getString(2));
        po.setMoney(rs.getInt(3));
        po.setDeleted(rs.getBoolean(4));
        po.setCreated(rs.getLong(5));
        po.setUpdated(rs.getLong(6));
        return po;
    }
});
System.out.println("queryFroObject by RowMapper: " + moneyPO);

From the point of view of using posture, RowMapper is a callback after sql execution to realize the result encapsulation. It is important to note that ResultSet encapsulates the complete return result, which can be specified by subscript. The subscript is from 1 instead of 0, which is our common one. We need to pay more attention to it.

This subscript starts from 1 and feels a little bit sore and easy to remember, so the preferred method is to get data directly by listing.

// Using columnName directly to get the corresponding value, we can consider using reflection to assign value, reducing getter/setter.
moneyPO = jdbcTemplate.queryForObject(sql, new RowMapper<MoneyPO>() {
    @Override
    public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
        MoneyPO po = new MoneyPO();
        po.setId(rs.getInt("id"));
        po.setName(rs.getString("name"));
        po.setMoney(rs.getInt("money"));
        po.setDeleted(rs.getBoolean("isDeleted"));
        po.setCreated(rs.getLong("created"));
        po.setUpdated(rs.getLong("updated"));
        return po;
    }
});
System.out.println("queryFroObject by RowMapper: " + moneyPO);

b. Advanced use

When the column name returned by sql and the attribute name of POJO can match perfectly, the above writing is very redundant and troublesome. I need a more elegant and concise gesture. The best way is to pass in the POJO type directly and automatically convert it.

If you want this effect, all you need is the following: Bean Property Row Mapper

// A simpler way is to assign attributes directly through BeanProperty RowMapper, provided that the column names returned by sql match correctly
moneyPO = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(MoneyPO.class));
System.out.println("queryForObject by BeanPropertyRowMapper: " + moneyPO);

c. Error-prone Posture

When you look at the interface provided by JdbcTemplate, you can see the following interface

@Override
public <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args) throws DataAccessException {
  return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
}

It's natural to think that the type of POJO that is directly introduced into it can get the expected results?

String sql =
                "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, unix_timestamp(update_at) as updated from money limit 1;";
try {
    MoneyPO po = jdbcTemplate.queryForObject(sql, MoneyPO.class);
    System.out.println("queryForObject by requireType return: " + po);
} catch (Exception e) {
    e.printStackTrace();
}

Execute the above code and throw an exception

As can be seen from the above source code, the above posture is suitable for the scenario where sql only returns a list of data, that is, the following case

// Let's start testing org. spring framework. jdbc. core. JdbcTemplate. queryForObject (java. lang. String, java. lang. Class < T >, java. lang. Object...)
// According to the test, this type can only be the basic type.
String sql2 = "select id from money where id=?";
Integer res = jdbcTemplate.queryForObject(sql2, Integer.class, 1);
System.out.println("queryForObject by requireId return: " + res);

4. test

All the above code can be viewed: https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate/src/main/java/com/git/hui/boot/jdbc/query/QueryService.java

Simple inheritance calls to all of the above methods

@SpringBootApplication
public class Application {
    private QueryService queryService;

    public Application(QueryService queryService) {
        this.queryService = queryService;

        queryTest();
    }
    public void queryTest() {
        queryService.queryForMap();
        queryService.queryForObject();
        queryService.queryForList();
    }

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

The output is as follows

Summary of III.

This blog article mainly introduces the simple use posture of JdbcTemplate query, mainly the invocation of queryForMap, queryForList, queryForObject three methods.

1. Based on the number of results returned

Single record query

  • queryForMap: Returns a record, and the returned result is stuffed into Map < String, Object >, with key as the column name of the fixed String corresponding query, and value as the actual value.
  • queryForObject: The same data is returned, but the difference is that you can use RowMapper to convert the returned result to the corresponding POJO.

It is important to note that the query above must have a record returned, if not found, then throw an exception.

Batch query

  • queryForList: One query >= 0 data, return type List < Map < String, Object >

2. According to sql type

There are two ways of sql reference

  • One is to write complete sql statements, just like our normal sql queries; the problem is that there is a risk of injection.
  • Secondly, the placeholder (?) is used, and the actual value is passed in by parameter.

IV. other

0. project

1. 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 thallium6 on Tue, 23 Apr 2019 20:09:35 -0700

Hot Keywords

©2025 Programmer Group