常用操作示例

查询

in 语句查询示例

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

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

import org.junit.Test;

import com.litongjava.jfinal.plugin.activerecord.Db;
import com.litongjava.jfinal.plugin.activerecord.Record;
import com.litongjava.open.chat.config.DbConfig;
import com.litongjava.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(", "));
    System.out.println(ids);

    String sql = "SELECT * FROM professor WHERE id IN (" + ids + ")";

    List<Record> professors = Db.find(sql, teacherIds.toArray());
    System.out.println(professors.size());
  }
}

从 record 中获取记录

// Generate the IN clause using cityId from records
String cityIds = records.stream().map(record -> record.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<Record> 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 值

Record updateRecord = new Record();
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";
Record removeRecordFilter = new Record();
removeRecordFilter.set("note_id", noteId);
Db.delete(ENoteTableNames.ENOTE_NOTE_TAG, removeRecordFilter);