一、背景

由于多租户本来就有动态数据源，所以新建一个数据库然后搜集里面的数据

二、配置

helpai:
  url: jdbc:mysql://localhost:3306/farm?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
  username: root
  password: 123456
  driver-class-name: com.mysql.cj.jdbc.Driver

 二、工具类

package jnpf.helpailog.service.impl;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import jnpf.model.helpailog.po.FtbHelpAiChatLog;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.UUID;

@Component
public class AiUtil {

    @Value("${helpai.url}")
    private String url;

    @Value("${helpai.username}")
    private String username;

    @Value("${helpai.password}")
    private String password;
    private HikariDataSource dataSource;

    @PostConstruct
    public void init() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        dataSource = new HikariDataSource(config);
    }

    // Create a new user
    public void insertFtbHelpAiChatLog(FtbHelpAiChatLog log) {

        String sql = "INSERT INTO ftb_help_ai_chat_log (F_Id, F_UserId, F_Question, F_Answer, F_SessionId, F_TenantId, F_CreatorTime) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, log.getId() != null ? log.getId() : UUID.randomUUID().toString());
            pstmt.setString(2, log.getUserId());
            pstmt.setString(3, log.getQuestion());
            pstmt.setString(4, log.getAnswer());
            pstmt.setString(5, log.getSessionId());
            pstmt.setString(6, log.getTenantId());
            pstmt.setTimestamp(7, new java.sql.Timestamp(log.getCreatorTime().getTime()));
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 三、调用

  @Autowired
    AiUtil aiUtil;   
aiUtil.insertFtbHelpAiChatLog(entity);

 

 

 

优化版本

1、新建一个接口

import java.util.Map;

public interface GenericEntity {
    String getTableName();
    Map<String, Object> getFieldValues();
}

 2、实体类

import lombok.Data;

import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

@Data
public class FtbHelpAiChatLog1 implements GenericEntity {
    private String id;
    private String userId;
    private String question;
    private String answer;
    private String sessionId;
    private String tenantId;
    private java.util.Date creatorTime;

    // Getters and Setters

    @Override
    public String getTableName() {
        return "ftb_help_ai_chat_log";
    }

    @Override
    public Map<String, Object> getFieldValues() {
        Map<String, Object> fieldValues = new HashMap<>();
        fieldValues.put("F_Id", id != null ? id : UUID.randomUUID().toString());
        fieldValues.put("F_UserId", userId);
        fieldValues.put("F_Question", question);
        fieldValues.put("F_Answer", answer);
        fieldValues.put("F_SessionId", sessionId);
        fieldValues.put("F_TenantId", tenantId);
        fieldValues.put("F_CreatorTime", new java.sql.Timestamp(creatorTime.getTime()));
        return fieldValues;
    }
}

 3、工具类

package jnpf.helpailog.config;


import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import java.util.UUID;

@Component
public class AiUtil {

    private static final Logger logger = LoggerFactory.getLogger(AiUtil.class);

    @Value("${helpai.url}")
    private String url;

    @Value("${helpai.username}")
    private String username;

    @Value("${helpai.password}")
    private String password;
    private HikariDataSource dataSource;

    @PostConstruct
    public void init() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        dataSource = new HikariDataSource(config);
    }

    public void insertEntity(GenericEntity entity) {
        String tableName = entity.getTableName();
        Map<String, Object> fieldValues = entity.getFieldValues();

        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(tableName).append(" (");
        StringBuilder valuesBuilder = new StringBuilder("VALUES (");

        int index = 0;
        for (String fieldName : fieldValues.keySet()) {
            if (index > 0) {
                sqlBuilder.append(", ");
                valuesBuilder.append(", ");
            }
            sqlBuilder.append(fieldName);
            valuesBuilder.append("?");
            index++;
        }

        sqlBuilder.append(") ").append(valuesBuilder).append(")");

        String sql = sqlBuilder.toString();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            setPreparedStatementParameters(pstmt, fieldValues.values().toArray());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            logger.error("Failed to insert entity", e);
            throw new RuntimeException("Failed to insert entity", e);
        }
    }

    private void setPreparedStatementParameters(PreparedStatement pstmt, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            if (params[i] instanceof String) {
                pstmt.setString(i + 1, (String) params[i]);
            } else if (params[i] instanceof java.sql.Timestamp) {
                pstmt.setTimestamp(i + 1, (java.sql.Timestamp) params[i]);
            } else if (params[i] instanceof UUID) {
                pstmt.setString(i + 1, params[i].toString());
            } else {
                throw new IllegalArgumentException("Unsupported parameter type: " + params[i].getClass().getName());
            }
        }
    }
}