This article is for the blogger to study notes, the content comes from the Internet, if there is infringement, please contact delete.

Personal Note: github.com/dbses/TechN…

01 | JDBC access to relational database specification

As a unified standard, the JDBC specification has a complete architecture, as shown in the following figure:

What are the core programming objects in the JDBC specification?

For daily development, the core programming objects in the JDBC specification include DriverManger, DataSource, Connection, Statement, and ResultSet.

• DriverManager

JDBC’s DriverManager is responsible for loading various drivers.

public interface Driver {
    // Get the database connection
    Connection connect(String url, java.util.Properties info) throws SQLException;
}
Copy the code

For the Driver interface, different database vendors provide their own implementation schemes. For example, the Driver implementation class in MySQL looks like this:

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    // Register the Driver through DriverManager
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }
    …
}
Copy the code

• DataSource

DataSource is an intermediate layer that has been promoted as an alternative to DriverManager and is the preferred method of retrieving database connections.

The DataSource interface is defined as follows:

public interface DataSource  extends CommonDataSource.Wrapper {
 
    Connection getConnection(a) throws SQLException;
 
    Connection getConnection(String username, String password) throws SQLException;
}
Copy the code

The CommonDataSource interface is the root interface of the JDBC DataSource definition. In addition to the DataSource interface, the CommonDataSource interface has two other subinterfaces, as shown in the following figure:

DataSource is the official basic interface for obtaining Connection, XADataSource is used to obtain Connection in distributed transaction environment. The ConnectionPoolDataSource is the interface that gets the Connection from the ConnectionPool.

• Connection

Connection represents a database Connection that completes communication with the database.

All SQL execution takes place in a specific Connection environment, and it provides a set of overloaded methods for creating statements and preparedStatements, respectively. Connection, on the other hand, also involves transaction-related operations.

public interface Connection extends Wrapper.AutoCloseable {
    / / create the Statement
 	  Statement createStatement(a) throws SQLException;
    / / create a PreparedStatement
    PreparedStatement prepareStatement(String sql) throws SQLException;
    / / submit
    void commit(a) throws SQLException;
    / / rollback
    void rollback(a) throws SQLException;
    // Close the connection
    void close(a) throws SQLException;
}
Copy the code

• Statement/PreparedStatement

There are two types of STATEMENTS in the JDBC specification: plain statements and PreparedStatements that support pre-compilation.

The so-called precompilation means that the database compiler compiles SQL statements in advance, and then caches the precompiled results in the database. The next time you execute SQL statements, you can replace parameters and directly use the compiled statements, thus greatly improving the execution efficiency of SQL.

Of course, this kind of precompilation also requires some cost. Therefore, in daily development, if only one read or write operation is performed on the database, the Statement object is suitable for processing. When multiple executions of SQL statements are involved, a PreparedStatement can be used.

• ResultSet

Once we have executed an SQL Statement through a Statement or PreparedStatement and obtained a ResultSet object, we can use a number of utility methods defined in that object to obtain the value of the SQL execution result, as shown in the following code:

public interface ResultSet extends Wrapper.AutoCloseable {
    // Get the next result
    boolean next(a) throws SQLException;
    // Get the result value of a certain type
    Value getXXX(int columnIndex) throws SQLException; ... }Copy the code

How do I access a database using the JDBC specification?

// Create pooled data sources
PooledDataSource dataSource = new PooledDataSource ();
// Set the MySQL Driver
dataSource.setDriver ("com.mysql.jdbc.Driver");
// Set the database URL, user name, and password
dataSource.setUrl ("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");
// Get the connection
Connection connection = dataSource.getConnection();
 
// Execute the query
PreparedStatement statement = connection.prepareStatement ("select * from user");
// Obtain the query result and process it
ResultSet resultSet = statement.executeQuery();
while(the resultSet. Next ()) {... }// Close the resource
statement.close();
resultSet.close();
connection.close();
Copy the code

02 | use JdbcTemplate access to relational database

Order data model

The Order class is defined as follows:

public class Order{

    private Long id; / / order Id
    private String orderNumber; // Order no
    private String deliveryAddress; // Logistics address
    private List<Goods> goodsList;  // List of items
    // the getter/setter is omitted
}
Copy the code

The database Schema definition for Order is shown in the following code:

DROP TABLE IF EXISTS `order`;
 
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`)
);
Copy the code

Use JdbcTemplate to implement the query

First we need to introduce a dependency on it:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
Copy the code

First, design an OrderRepository interface to abstract the entry to the database, as shown in the following code:

public interface OrderRepository {
    Order getOrderById(Long orderId);
}
Copy the code

Build an OrderJdbcRepository class and implement the OrderRepository interface as follows:

@Repository("orderJdbcRepository")
public class OrderJdbcRepository implements OrderRepository {
 
    private JdbcTemplate jdbcTemplate;
 
    @Autowired
    public OrderJdbcRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
  
    @Override
    public Order getOrderById(Long orderId) {
        Order order = jdbcTemplate.queryForObject(
          "select id, order_number, delivery_address from `order` where id=?".this::mapRowToOrder, 
          orderId
        );
        return order;
    }
  
    private Order mapRowToOrder(ResultSet rs, int rowNum) throws SQLException {
        return new Order(
            rs.getLong("id"),
            rs.getString("order_number"),
            rs.getString("delivery_address")); }}Copy the code

Insert using JdbcTemplate

public Long saveOrderWithJdbcTemplate(Order order) {
 
    PreparedStatementCreator psc = new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement(
                "insert into `order` (order_number, delivery_address) values (? ,?) ",
                Statement.RETURN_GENERATED_KEYS
            );
            ps.setString(1, order.getOrderNumber());
            ps.setString(2, order.getDeliveryAddress());
            returnps; }}; KeyHolder keyHolder =new GeneratedKeyHolder();
    jdbcTemplate.update(psc, keyHolder);
  
    return keyHolder.getKey().longValue();
}
Copy the code

Statement.RETURN_GENERATED_KEYS is set during the creation of the PreparedStatement to return the auto-increment primary key. A GeneratedKeyHolder object is then built to hold the returned increment primary key.

Simplify data insertion using SimpleJdbcInsert

Spring Boot provides a SimpleJdbcInsert utility class specifically for data insertion scenarios. SimpleJdbcInsert is essentially a wrapper on top of JdbcTemplate.

Initialize SimpleJdbcInsert with the following code:

private SimpleJdbcInsert orderInserter;
 
public OrderJdbcRepository(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
    this.orderInserter = new SimpleJdbcInsert(jdbcTemplate)
      .withTableName("`order`")
      .usingGeneratedKeyColumns("id");
    this.orderGoodsInserter = new SimpleJdbcInsert(jdbcTemplate).withTableName("order_goods");
}
Copy the code

Insert Order object 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;
}
Copy the code

03 | JdbcTemplate data access principle

JdbcTemplate is based on the template method pattern and callback mechanism, addressing the complexity of native JDBC.

JdbcTemplate source code parsing

JdbcTemplate’s execute(StatementCallback Action) method looks like this:

public <T> T execute(StatementCallback<T> action) throws DataAccessException {
    Assert.notNull(action, "Callback object must not be null");

    Connection con = DataSourceUtils.getConnection(obtainDataSource());
    Statement stmt = null;
    try {
        stmt = con.createStatement();
        applyStatementSettings(stmt);
        T result = action.doInStatement(stmt);
        handleWarnings(stmt);
        return result;
    } catch (SQLException ex) {
        String sql = getSql(action);
        JdbcUtils.closeStatement(stmt);
        stmt = null;
        DataSourceUtils.releaseConnection(con, getDataSource());
        con = null;
        throw translateException("StatementCallback", sql, ex);
    } finally{ JdbcUtils.closeStatement(stmt); DataSourceUtils.releaseConnection(con, getDataSource()); }}Copy the code

Is it necessary to duplicate catch and finally code?

The StatementCallback callback interface is defined as follows:

public interface StatementCallback<T> {
 
    T doInStatement(Statement stmt) throws SQLException, DataAccessException;
}
Copy the code

In JdbcTemplate, there is another execute(final String SQL) method that uses exactly the execute(StatementCallback Action) method as follows:

class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
    @Override
    @Nullable
    public Object doInStatement(Statement stmt) throws SQLException {
        stmt.execute(sql);
        return null;
    }
    @Override
    public String getSql(a) {
        returnsql; }}public void execute(final String sql) throws DataAccessException {
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL statement [" + sql + "]");
    }

    execute(new ExecuteStatementCallback());
}
Copy the code

JdbcTemplate is based on JDBC native API, the template method and callback mechanism together, provides us with a concise and highly extensible implementation scheme, worth our analysis and application.

04 | Spring Data how to unified abstract Data access process?

Spring Data is an open source framework for Data access in the Spring family. Its abstraction of Data access process is mainly reflected in two aspects: (1) It provides a Repository interface definition and implementation; ② Implement a variety of query support, let’s take a look at each.

Repository interface and implementation

The Repository interface is the highest-level abstraction for Data access in Spring Data and is defined as follows:

public interface Repository<T.ID> {}Copy the code

In Spring Data, there are a number of subinterfaces and implementation classes for the Repository interface:

The save method of SimpleJpaRepository is shown below:

private finalJpaEntityInformation<T, ? > entityInformation;private final EntityManager em;
 
@Transactional
public <S extends T> S save(S entity) {
    if (entityInformation.isNew(entity)) {
        em.persist(entity);
        return entity;
    } else {
        returnem.merge(entity); }}Copy the code

The above save method relies on the EntityManager in the JPA specification.

Diversified query support

• @ Query annotation

This annotation is located in the org. Springframework. Data. Jpa. Repository package, as shown below:

package org.springframework.data.jpa.repository;
 
public @interface Query {
    String value(a) default "";
    String countQuery(a) default "";
    String countProjection(a) default "";
    boolean nativeQuery(a) default false;
    String name(a) default "";
    String countName(a) default "";
}
Copy the code

A typical example of a Query using the @query annotation is as follows:

public interface AccountRepository extends JpaRepository<Account.Long> {
    @Query("select a from Account a where a.userName = ? 1)" 
    Account findByUserName(String userName);
}
Copy the code

Since we are using JpaRepository, this SQL-like syntax is actually a JPA Query Language, also known as JPQL (Java Persistence Query Language).

The only difference between JPQL and native SQL is that JPQL FROM statements are followed by objects, whereas in native SQL statements they correspond to tables.

If the @Query annotation’s nativeQuery is set to true, then the value attribute needs to specify the specific native SQL statement.

• Method name derived query

By using query fields and parameters directly in method names, Spring Data can automatically identify the corresponding query criteria and assemble the corresponding query statements.

To implement derived queries using method names, we need to constrain the method names defined in Repository. First, we need to specify some query keywords. Common keywords are shown in the following table:

Next, you need to specify query fields and some restrictive conditions, such as “firstName” and “lastName.”

If we specify both the @Query annotation and the method name-derived Query in a Repository, which one will Spring Data execute?

In Spring Data, you can define a query policy, as shown in the following code:

public interface QueryLookupStrategy {
 
    public static enum Key {
        CREATE, USE_DECLARED_QUERY, CREATE_IF_NOT_FOUND;

        public static Key create(String xml) {
            if(! StringUtils.hasText(xml)) {return null;
            }
            return valueOf(xml.toUpperCase(Locale.US).replace("-"."_")); }}}Copy the code

The CREATE policy refers to the creation of queries based on method names, that is, method name derived queries.

USE_DECLARED_QUERY refers to the declaration mode, which uses the @query annotation.

CREATE_IF_NOT_FOUND looks for the @query annotation first, and if it doesn’t, it looks for a Query that matches the method name.

• QueryByExample mechanism

What if there are too many fields in the query criteria?

QueryByExample can be translated as QueryByExample and is a user-friendly query technique. It allows us to create queries on the fly without having to write query methods that contain field names.

QueryByExample includes Probe, ExampleMatcher, and Example.

First, we need to inherit the QueryByExampleExecutor interface from the definition of the OrderJpaRepository interface, as shown in the following code:

@Repository("orderJpaRepository")
public interface OrderJpaRepository extends JpaRepository<JpaOrder.Long>, QueryByExampleExecutor<JpaOrder> {}Copy the code

Then, we implement in JpaOrderService getOrderByOrderNumberByExample method as shown in the following code:

public JpaOrder getOrderByOrderNumberByExample(String orderNumber) {
    JpaOrder order = new JpaOrder();
    order.setOrderNumber(orderNumber);
 
    ExampleMatcher matcher = ExampleMatcher
      .matching()
      .withIgnoreCase()
      .withMatcher("orderNumber", GenericPropertyMatchers.exact())
      .withIncludeNullValues();
 
    Example<JpaOrder> example = Example.of(order, matcher);
 
    return orderJpaRepository.findOne(example).orElse(new JpaOrder());
}
Copy the code

• Specification mechanism

What if we want to query an entity, but the query criteria given are not fixed?

This is done by dynamically building the corresponding query, which can be implemented in the Spring Data JPA through the JpaSpecificationExecutor interface.

OrderJpaRepository, which inherits from JpaSpecificationExecutor, is defined as follows:

@Repository("orderJpaRepository")
public interface OrderJpaRepository 
  extends JpaRepository<JpaOrder.Long>, JpaSpecificationExecutor<JpaOrder> {}Copy the code

For the JpaSpecificationExecutor interface, the Specification interface is used behind it:

public interface Specification {
    Predicate toPredicate( Root
       
         root, CriteriaQuery
         query, CriteriaBuilder criteriaBuilder )
       ;
}
Copy the code

The Root object represents the Root object being queried, from which we can obtain the attributes of the entity. CriteriaQuery represents a top-level query object that is used to implement custom queries. CriteriaBuilder is used to construct query conditions.

The refactored getOrderByOrderNumberBySpecification method as shown in the following code:

public JpaOrder getOrderByOrderNumberBySpecification(String orderNumber) {
    JpaOrder order = new JpaOrder();
    order.setOrderNumber(orderNumber);

    @SuppressWarnings("serial")
    Specification<JpaOrder> spec = new Specification<JpaOrder>() {
        @Override
        public Predicate toPredicate(Root
       
         root, CriteriaQuery
         query, CriteriaBuilder cb)
        {
            Path<Object> orderNumberPath = root.get("orderNumber");

            Predicate predicate = cb.equal(orderNumberPath, orderNumber);
            returnpredicate; }};return orderJpaRepository.findOne(spec).orElse(new JpaOrder());     
}
Copy the code

We first obtain the “orderNumber” attribute from the root object, and then compare this attribute with the orderNumber parameter passed in using the cb.equal method to implement the query condition construction process.

05 | using Spring Data JPA to access a relational database

JPA stands for JPA Persistence API (Java Persistence API), which is an Object Relational Mapping (ORM) technology.

Introduce Spring Data JPA

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
Copy the code

Defining entity Classes

There are two main domain objects in the order-service, order and Goods. The two domain objects are named JpaOrder and JpaGoods, respectively.

JpaGoods defines the following code:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
 
@Entity
@Table(name="goods")
public class JpaGoods {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;    
    private String goodsCode;
    private String goodsName;
    private Float price;    
    / / omit getter/setter
}
Copy the code

JpaOrder defines the following code:

@Entity
@Table(name="`order`")
public class JpaOrder implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String orderNumber;
    private String deliveryAddress;
 
    @ManyToMany(targetEntity=JpaGoods.class)
    @JoinTable( name = "order_goods", joinColumns = @JoinColumn(name = "order_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "goods_id", referencedColumnName = "id") )
    private List<JpaGoods> goods = new ArrayList<>();
 
    / / omit getter/setter
}
Copy the code

The @joinTable annotation is used to specify the order_GOODS intermediate table, and the joinColumns and inverseJoinColumns annotations specify the column names in the intermediate table and the foreign key names in the two main tables, respectively.

Define the Repository

OrderJpaRepository is defined as follows:

@Repository("orderJpaRepository")
public interface OrderJpaRepository extends JpaRepository<JpaOrder.Long> {}Copy the code

OrderJpaRepository actually already has basic CRUD capabilities to access the database.