Solon 3.0 新特性：SqlUtils

带刺的坐椅發表於2024-10-10
SQL

Solon 3.0 引入了新的 SqlUtils 用於資料庫基礎操作，SqlUtils 是對 JDBC 較為原始的封裝，極為反樸歸真。 特性有：

  • 支援事務管理
  • 支援多資料來源
  • 支援流式輸出
  • 支援批次執行
  • 支援儲存過程

一、概述

SqlUtils 是一個輕量的資料庫操作框架，簡單靈活，易於閱讀和維護，支援編寫複雜的SQL。對於不適合使用複雜的 ORM 框架，或者需要編寫複雜的 SQL 的場景，可以使用 SqlUtils 來運算元據庫。

SqlUtils 總體上分為查詢操作（query 開發）和更新操作（update 開頭）。分別對應 JDBC 的 Statement:executeQuery() 和 Statement:executeUpdate()。

二、引入 SqlUtils

  • gradle 依賴
implementation 'org.noear:solon-data-sqlutils'
  • maven 依賴
<dependency>
    <groupId>org.noear</groupId>
    <artifactId>solon-data-sqlutils</artifactId>
</dependency>

三、配置資料來源

配置資料來源（具體參考：《資料來源的配置與構建》）

solon.dataSources:
  rock!:
    class: "com.zaxxer.hikari.HikariDataSource"
    jdbcUrl: jdbc:mysql://localhost:3306/rock?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true
    driverClassName: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456

之後就可以按資料來源名注入 SqlUtils 了（帶 ! 結尾的資料來源名，為預設）

@Component
public class DemoService {
    @Inject //預設資料來源名
    SqlUtils sqlUtils;
}

四、查詢操作

查數量：

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

按照主鍵查資料：

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

按照自定義查詢條件查資料：

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

以上幾種查詢方式，都是一行程式碼就解決的。複雜的查詢怎麼辦？比如管理後臺的條件統計，可以先使用構建器：

public List<Appx> findDataStat(int group_id, String channel, int scale) throws SQLException {
    SqlBuilder sqlSpec = new SqlBuilder();
    sqlSpec.append("select group_id, sum(amount) amount from appx ")
           .append("where group_id = ? ", group_id)
           .appendIf(channel != null, "and channel like ? ", channel + "%");

    //可以分離控制
    if(scale > 10){
        sqlSpec.append("and scale = ? ", scale);
    }
    
    sqlSpec.append("group by group_id ");

    return sqlUtils.sql(sqlSpec).queryRowList().toBeanList(Appx.class);
}

管理後臺常見的分頁查詢：

public Page<Appx> findDataPage(int group_id, String channel) throws SQLException {
    SqlBuilder sqlSpec = new SqlBuilder()
      .append("from appx  where group_id = ? ", group_id)
      .appendIf(channel != null, "and channel like ? ", channel + "%");
    
    //備份
    sqlSpec.backup();
    sqlSpec.insert("select * ");
    sqlSpec.append("limit ?,? ", 10,10); //分頁獲取列表
    
    //查詢列表
    List<Appx> list = sqlUtils.sql(sqlSpec).queryRowList().toBeanList(Appx.class);
    
    //回滾（可以複用備份前的程式碼構建）
    sqlSpec.restore();
    sqlSpec.insert("select count(*) ");
    
    //查詢總數
    Long total = sqlUtils.sql(sqlSpec).queryValue();
    
    return new Page(list, total);
}

構建器支援 ?... 集合佔位符查詢：

public List<Appx> findDataList() throws SQLException {
    SqlBuilder sqlSpec = new SqlBuilder()
        .append("select * from appx  where app_id in (?...) ", Arrays.asList(1,2,3,4));
    
    //查詢列表
    List<Appx> list = sqlUtils.sql(sqlSpec).queryRowList().toBeanList(Appx.class);
}

五、流式查詢操作

支援 fetchSize 引數

public void findDataAll(Integer group_id) throws SQLException {
    SqlBuilder sqlSpec = new SqlBuilder()
        .append("select * from appx where group_id = ?", group_id);
    
    try (RowIterator iterator = sqlUtils.sql(sqlSpec).queryRowIterator(100)) {
        while (iterator.hasNext()){
            Appx app = iterator.next().toBean(Appx.class);
            //....
        }
    }
}

六、插入操作

單條插入：

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

單條插入並返回主鍵：

public Long addData(int id) throws SQLException {
    return sqlUtils.sql("insert appx(app_id) values(?)", id).updateReturnKey();
}

批次插入：

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

六、更新操作（更新或刪除）

支援事務控制

@Tran
public void updateData(int id, Appx app) throws SQLException {
    SqlBuilder sqlSpec = new SqlBuilder();
    sqlSpec.append("update appx set ")
    sqlSpec.append("  group_id=?, ", app.group_id)
    sqlSpec.append("  name=?, ", app.name)
    sqlSpec.append("  channel=?, ", app.channel)
    sqlSpec.append("  amount=? ", app.amount)
    sqlSpec.append("where app_id=? ", id)
    
    sqlUtils.sql(sqlSpec).queryRow();
}

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

七、儲存過程操作

查詢操作

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

刪除操作

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

八、總結

透過上述的示例，可以看到基本的資料庫操作都可以用 SqlUtils 實現，可以避免了複雜的ORM框架的使用。

相關文章