常用操作示例

查询

in 语句查询示例

in 查询

• 查询的表是 professor,查询的字段是 id
• 使用 java 的 stream 语法拼接

package nexus.io.rmp;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import org.junit.Test;

import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;
import nexus.io.open.chat.config.DbConfig;
import nexus.io.tio.utils.environment.EnvUtils;

public class TestQueryWithIn {

  @Test
  public void test() {
    EnvUtils.load();
    new DbConfig().config();
    List<Integer> teacherIds = new ArrayList<>();
    // 添加的teacherIds到列表中
    teacherIds.add(1138444);
    teacherIds.add(2720210);
    teacherIds.add(1970588);

    if (teacherIds.isEmpty()) {
      // 如果teacherIds列表为空，则不执行查询或处理其他逻辑
      return;
    }

    String ids = teacherIds.stream().map(id -> "?").collect(Collectors.joining(", "));

    String sql = "SELECT * FROM professor WHERE id IN (" + ids + ")";
    System.out.println(sql);
    System.out.println(Arrays.toString(teacherIds.toArray()));
    List<Row> professors = Db.find(sql, teacherIds.toArray());
    System.out.println(professors.size());
  }
}

生成的 sql 语句和参数

SELECT * FROM professor WHERE id IN (?, ?, ?)
[1138444, 2720210, 1970588]

从 row 中获取记录

// Generate the IN clause using cityId from records
String cityIds = records.stream().map(row -> row.getStr("id")).collect(Collectors.joining(", "));
// Create the SQL query with the IN clause
String sql = "SELECT city_hot_business_district FROM lrb_city WHERE city_id IN (" + cityIds + ")";

使用 findIn 方法

@Test
public void findIn() {
  TioBootTest.runWith(DbConfig.class);
  List<Row> records = Db.findIn(TableNames.max_kb_task, "id", 434902001417166848L,434902001417166849L);
  List<Kv> kvs = RecordUtils.recordsToKv(records, false);
  System.out.println(JsonUtils.toJson(kvs));
}

updateById

根据 id 值更新 ENOTE_USER_DEVICES 表中的 mac_address 值

Row updateRecord = new Row();
updateRecord.set("id", id);
updateRecord.set("mac_address", macAddresses);
boolean update = Db.update(ENoteTableNames.ENOTE_USER_DEVICES, updateRecord);

删除指定字段

删除 ENOTE_NOTE_TAG 表中 note_id 字段为 '0000000' 的记录

String noteId = "0000000";
Row removeRecordFilter = new Row();
removeRecordFilter.set("note_id", noteId);
Db.delete(ENoteTableNames.ENOTE_NOTE_TAG, removeRecordFilter);