Location>code7788 >text

What's New in Solon 3.0: SqlUtils

Popularity：525 ℃/2024-10-10 19:08:45

Solon 3.0 introduces a new SqlUtils for basic database operations , SqlUtils is a more primitive package for JDBC , using the style of the Utils API , extremely anti-popularity. The features are:

  • Support for transaction management
  • Support for multiple data sources
  • Streaming output support
  • Supports batch execution
  • Stored Procedure Support

I. Overview

SqlUtils is a lightweight database manipulation framework , using the Utils API style , simple and flexible , easy to read and maintain , support for writing complex SQL. for the use of complex ORM framework is not suitable , or the need to write complex SQL scenarios , you can use SqlUtils to operate the database .

II. Introducing SqlUtils

  • gradle dependencies
implementation ':solon-data-sqlutils'
  • maven dependencies
<dependency>
    <groupId></groupId>
    <artifactId>solon-data-sqlutils</artifactId>
</dependency>

III. Configuring data sources

Configure the data source (specifically refer to:Configuration and Construction of Data Sources）

:
  rock!:
    class: ""
    jdbcUrl: jdbc:mysql://localhost:3306/rock?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true
    driverClassName: 
    username: root
    password: 123456

After that, you can inject SqlUtils by data source name (with a! (the name of the data source at the end, is the default)

@Component
public class DemoService {
    @Inject //default data source name
    SqlUtils sqlUtils.
}

IV. Query operations

Check the number:

public Long findCount() throws SQLException {
    return ("select count(*) from appx where app_id = ?", id);
}

Look up the data according to the primary key:

public Appx findDataById(Integer id) throws SQLException {
    return ("select * from appx where app_id = ?", id)
                   .toBean();
}

Check the data according to the customized query conditions:

public List<Appx> findDataByGroup(Integer group_id) throws SQLException {
    return ("select * from appx where group_id = ?", group_id)
                   .toBeanList();
}

The above types of queries, where the variables in the query conditions use placeholders (SqlUtils only supports placeholders), are also relatively simple. What about complex queries? For example, the conditional statistics of the management backend, you can use the builder first:

public List<Appx> findDataStat(int group_id, String channel, int scale) throws SQLException {
    SqlBuilder builder = new SqlBuilder();
    ("select group_id, sum(amount) amount from appx ")
           .append("where group_id = ? group by group_id", group_id);

    (channel != null, " and title channel ?", channel + "%");

    if(scale > 10){
        (" and scale = ?", scale);
    }

    return ((), ())
                   .toBeanList();
}

Manage common paging queries in the backend:

public void findDataPage(int group_id, String channel) throws SQLException {
    SqlBuilder builder = new SqlBuilder()
      .append(" from appx where group_id = ?", group_id)
      .appendIf(channel != null, " and title channel ?", channel + "%");
    
    //backing up
    ();
    ("select *");
    (" limit ?,?", 10,10); //Pagination Get List
    
    //Inquiry List
    List<Appx> list = ((), ())
                              .toBeanList();
    
    //rolling back（可以复用backing up前的代码构建）
    ();
    ("select count(*)");
    
    //Total number of queries
    Long total = ((), ());
}

V. Streaming query operations

Support for fetchSize parameter

public void findDataAll(Integer group_id) throws SQLException {
    try (RowIterator iterator = ("select * from appx where group_id = ?", 100, group_id)) {
        while (()){
            Appx app = ().toBean();
            //....
        }
    }
}

VI. Insertion operations

Single-stripe insertion:

public void addData(int id) throws SQLException {
    return ("insert appx(app_id) values(?)", id); 
}

Single insert and return Key:

public void addData(int id) throws SQLException {
    return ("insert appx(app_id) values(?)", id); 
}

Batch Insert:

public void addDataBatch() throws SQLException {
    List<Object[]> argsList = new ArrayList<>();
    (new Object[]{1});
    (new Object[]{2});
    (new Object[]{3});
    (new Object[]{4});
    (new Object[]{5});
    
    ("insert appx(app_id) values(?)", argsList);
}

VI. Performing operations (updating or deleting)

Support for Transaction Control

@Tran
public void delData(int id) throws SQLException {
    ("delete from appx where app_id=?", id); 
}

@Tran
public void updateData(int id) throws SQLException {
    ("update appx set group_id=?  where app_id=?", 2, id); 
}

VII. Stored Procedure Operations

query operation

public Appx findDataById(int id) throws SQLException {
    return ("{call findDataById(?)}", id).toBean();
}

Delete operation

public int findDataById(int id) throws SQLException {
    return ("{call delDataById(?)}", id);
}

VIII. Summary

Through the above example, you can see the basic database operations can be achieved with SqlUtils , to avoid the use of complex ORM framework , cut the operation than the ORM framework is much simpler and more flexible.Utils API style is also easier to write and read.

<Previous Next>