07 Data Access - How to Use JdbcTemplate to Access the Relational Databases #

In Lesson 06, we discussed the details of the JDBC specification, which is the most widely used data access standard in the Java domain. Currently, mainstream data access frameworks are built on top of the JDBC specification.

Because JDBC is a low-level specification, there are many ways to implement relational data access using the JDBC specification (differing in the degree of encapsulation of the JDBC specification). In Spring, the JdbcTemplate template class is provided to simplify the usage of the JDBC specification. Today, we will discuss this template class.

Data Model and Repository Layer Design #

Before introducing the JdbcTemplate template class, let’s go back to the SpringCSS case and provide some groundwork for the content of this lesson by presenting the data model used in the order-service.

We know that an order often involves one or more goods, so in this case, we mainly demonstrate database design and implementation techniques using a one-to-many relationship. To simplify the description, we have simplified the specific business fields. The definition of the Order class is shown in the following code:

public class Order {

    private Long id; // Order ID

    private String orderNumber; // Order number

    private String deliveryAddress; // Delivery address

    private List<Goods> goodsList; // Goods list

    // Getter/setter omitted

}

The Goods class, representing a product, is defined as follows:

public class Goods {

    private Long id; // Product ID

    private String goodsCode; // Product code

    private String goodsName; // Product name

    private Double price; // Product price

    // Getter/setter omitted

}

From the above code, it is not difficult to see that an order can contain multiple goods. Therefore, when designing the relational database table, we will first create an intermediate table to store the one-to-many relationship between Order and Goods. In this course, we use MySQL as the relational database, and the corresponding database schema definition is shown in the following code:

DROP TABLE IF EXISTS `order`;

DROP TABLE IF EXISTS `goods`;

DROP TABLE IF EXISTS `order_goods`;

 

create table `order` (

    `id` bigint(20) NOT NULL AUTO_INCREMENT,

    `order_number` varchar(50) not null,

    `delivery_address` varchar(100) not null,

  `create_time` timestamp not null DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)

);

 

create table `goods` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `goods_code` varchar(50) not null,

  `goods_name` varchar(50) not null,

  `goods_price` double not null,

  `create_time` timestamp not null DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)

);

 

create table `order_goods` (

    `order_id` bigint(20) not null,

    `goods_id` bigint(20) not null,

    foreign key(`order_id`) references `order`(`id`),

    foreign key(`goods_id`) references `goods`(`id`)

);

Based on the above data model, we will complete the design and implementation of the Repository layer component in the order-server. First, we need to design an OrderRepository interface, which serves as an abstraction for accessing the database, as shown in the following code:

public interface OrderRepository {

    Order addOrder(Order order);

    Order getOrderById(Long orderId);

    Order getOrderDetailByOrderNumber(String orderNumber);

}

This interface is very simple, and the methods are self-explanatory. However, please note that the OrderRepository here does not inherit any parent interfaces and is entirely a custom, independent Repository.

Based on the interface definition in the OrderRepository above, we will create a series of implementation classes.

• OrderRawJdbcRepository: Uses raw JDBC for database access
• OrderJdbcRepository: Uses JdbcTemplate for database access
• OrderJpaRepository: Uses Spring Data JPA for database access

The OrderJpaRepository in the above implementation classes will be discussed in detail in Lesson 10, “ORM Integration: How to Use Spring Data JPA to Access Relational Databases”. The OrderRawJdbcRepository is the most basic and not the focus of this course, so in Lesson 07, we will only focus on the implementation process of the getOrderById method in the OrderRepository, which can be considered as a review and extension of Lesson 06.

The implementation methods in the OrderRawJdbcRepository class are shown in the following code:

@Repository("orderRawJdbcRepository")
public class OrderRawJdbcRepository implements OrderRepository {

    @Autowired
    private DataSource dataSource;

    @Override
    public Order getOrderById(Long orderId) {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement("select id, order_number, delivery_address from `order` where id=?");
            statement.setLong(1, orderId);
            resultSet = statement.executeQuery();

            Order order = null;
            if (resultSet.next()) {
                order = new Order(resultSet.getLong("id"), resultSet.getString("order_number"),
                        resultSet.getString("delivery_address"));

@Override

public Order getOrderDetailByOrderNumber(String orderNumber) {

        Order order = jdbcTemplate.queryForObject("select id, order_number, delivery_address from `order` where order_number=?",

                this::mapRowToOrder, orderNumber);


        //获取订单中的商品信息

        List<Product> products = jdbcTemplate.query("select * from product where order_id=?", this::mapRowToProduct, order.getId());


        order.setProducts(products);


        return order;

}

这里我们通过queryForObject方法获取了订单的基础信息，然后通过query方法获取了订单所包含的所有商品信息。需要注意的是，我们在查询订单商品信息时，通过this::mapRowToProduct将查询结果映射为Product对象。

与此同时，我们还需要编写一个mapRowToProduct方法，用于将查询结果映射为Product对象。代码如下所示：

private Product mapRowToProduct(ResultSet rs, int rowNum) throws SQLException {

        return new Product(rs.getLong("id"), rs.getString("name"), rs.getDouble("price"));

}

        ```java
        @Service
        public class OrderService {
        
            @Autowired
            private OrderDao orderDao;
        
            @Transactional
            public Order createOrder(Order order) {
                orderDao.addOrderDetailWithJdbcTemplate(order);
                return order;
            }
        
            ...
        }
        ```
        
        ```java
        @RestController
        public class OrderController {
        
            @Autowired
            private OrderService orderService;
        
            @PostMapping("/orders")
            public Order createOrder(@RequestBody Order order) {
                return orderService.createOrder(order);
            }
        
            ...
        }
        ```
        
        在 Service 类中，我们注入了 OrderDao 对象，并在 createOrder 方法内部调用了 OrderDao 的 addOrderDetailWithJdbcTemplate 方法来完成 Order 的插入操作。同时，这里使用了 @Transactional 注解来实现方法级别的事务控制，当出现异常时可以进行回滚操作。
        
        在 Controller 类中，我们注入了 OrderService 对象，并在 createOrder 方法上使用 @PostMapping 注解来指定 RESTful 接口中的 HTTP POST 请求。请求的 body 中会传递一个 Order 对象，然后我们调用 orderService.createOrder 方法来将 Order 对象插入到数据库中，并返回插入后的 Order 对象。
@Service

public class OrderService {



    @Autowired

    @Qualifier("orderJdbcRepository")

    private OrderRepository orderRepository;

    public Order addOrder(Order order) {

        return orderRepository.addOrder(order);

    } 

}



@RestController

@RequestMapping(value="orders")

public class OrderController {



    @RequestMapping(value = "", method = RequestMethod.POST)

    public Order addOrder(@RequestBody Order order) {

        Order result = orderService.addOrder(order);

     return result;

    }

}

Both of these classes directly encapsulate and call the methods in the orderJdbcRepository, making the operation very simple. Then, we open Postman and enter the following content in the request body:

{

    "orderNumber" : "Order10002",

    "deliveryAddress" : "test_address2",

    "goods": [

        {

            "id": 1,

            "goodsCode": "GoodsCode1",

            "goodsName": "GoodsName1",

            "price": 100.0

        }

    ]

}

After sending a POST request to the endpoint http://localhost:8081/orders using Postman, we find that the data in both the order and order_goods tables have been successfully inserted.

Simplifying the Data Insertion Process using SimpleJdbcInsert #

Although we can accomplish correct data insertion through the update method of JdbcTemplate, we can’t help but notice that the implementation process is quite complex, especially when it comes to handling auto-increment primary keys, causing the code to become bloated. Is there a simpler way to implement this?

The answer is yes; Spring Boot provides a utility class called SimpleJdbcInsert specifically for data insertion scenarios. SimpleJdbcInsert is essentially a wrapper on top of JdbcTemplate, providing a set of overloaded execute, executeAndReturnKey, and executeBatch methods to simplify the data insertion operation.

Usually, we can initialize SimpleJdbcInsert in the constructor of the repository implementation class. The following code demonstrates this:

private JdbcTemplate jdbcTemplate;

private SimpleJdbcInsert orderInserter;

private SimpleJdbcInsert orderGoodsInserter;

 

public OrderJdbcRepository(JdbcTemplate jdbcTemplate) {

        this.jdbcTemplate = jdbcTemplate;

        this.orderInserter = new SimpleJdbcInsert(jdbcTemplate).withTableName("`order`").usingGeneratedKeyColumns("id");

        this.orderGoodsInserter = new SimpleJdbcInsert(jdbcTemplate).withTableName("order_goods");

}

As you can see, we first inject a JdbcTemplate object, and then based on JdbcTemplate, we initialize two SimpleJdbcInsert objects orderInserter and orderGoodsInserter for the order and order_goods tables, respectively. In orderInserter, we also use the usingGeneratedKeyColumns method to set the auto-increment primary key column.

With SimpleJdbcInsert, inserting an Order object becomes very simple. The implementation is as follows:

private Long saveOrderWithSimpleJdbcInsert(Order order) {

        Map<String, Object> values = new HashMap<String, Object>();

        values.put("order_number", order.getOrderNumber());

        values.put("delivery_address", order.getDeliveryAddress());

 

        Long orderId = orderInserter.executeAndReturnKey(values).longValue();

        return orderId;

}

Here, we create a Map object, and then set the fields to be added as key-value pairs. We can directly return the auto-increment primary key while inserting data using the executeAndReturnKey method of SimpleJdbcInsert. Similarly, inserting data into the order_goods table only requires a few lines of code, as shown below:

private void saveGoodsToOrderWithSimpleJdbcInsert(Goods goods, long orderId) {

        Map<String, Object> values = new HashMap<>();

        values.put("order_id", orderId);

        values.put("goods_id", goods.getId());

        orderGoodsInserter.execute(values);

}

Here, we use the execute method provided by SimpleJdbcInsert. We can combine these methods to refactor the addOrderDetailWithJdbcTemplate method and obtain the addOrderDetailWithSimpleJdbcInsert method as shown below:

private Order addOrderDetailWithSimpleJdbcInsert(Order order) {

        //Insert basic information of Order

        Long orderId = saveOrderWithSimpleJdbcInsert(order);

 

        order.setId(orderId);

        //Insert the relationship between Order and Goods

        List<Goods> goodsList = order.getGoods();

        for (Goods goods : goodsList) {

            saveGoodsToOrderWithSimpleJdbcInsert(goods, orderId);

        }

 

        return order;

}

You can refer to the detailed code listing in the course’s sample code for reference, and you can also try out the refactored code using Postman.

Summary and Preview #

The JdbcTemplate template class is a powerful tool for implementing data access based on the JDBC specification. It encapsulates common CRUD operations and provides a large set of simplified APIs. Today, we provided implementation solutions based on JdbcTemplate for both query and insert operations. Especially for insert scenarios, we introduced SimpleJdbcInsert built on top of JdbcTemplate to simplify this operation.