前言

配合嵌入式数据库使用，比如H2，sqlite嵌入式关系型数据，很香！

• pom

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

• 代码

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 通用DAO
 *
 * @author JHL
 * @version 1.0
 * @date 2023/2/16 19:09
 * @since : JDK 11
 */
@Service
public class CommonService {
    private static final Logger LOG = LoggerFactory.getLogger(CommonService.class);
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 通用查询
     */
    public boolean save(String tableName, LinkedList<String> toSaveField, Object... values) {
        StringBuilder sb = new StringBuilder("INSERT INTO ");
        sb.append(tableName);
        sb.append(" (");
        for (int i = 0; i < toSaveField.size(); i++) {
            if (i == toSaveField.size() - 1) {
                sb.append(toSaveField.get(i));
            } else {
                sb.append(toSaveField.get(i) + ",");
            }
        }
        sb.append(") VALUES (");
        for (int i = 0; i < toSaveField.size(); i++) {
            if (i == toSaveField.size() - 1) {
                sb.append("?");
            } else {
                sb.append("?,");
            }
        }
        sb.append(");");
        return jdbcTemplate.update(sb.toString(), values) > 0;
    }

    /**
     * 通用查询
     */
    public List<Map<String, Object>> select(String tableName) {
        return jdbcTemplate.queryForList("select * from " + tableName + ";");
    }

    /**
     * 查最后一条
     */
    public Map<String, Object> selectLastOne(String tableName, String pkField) {
        return jdbcTemplate.queryForMap("select * from " + tableName + " order by " + pkField + " desc limit 1;");
    }

    /**
     * 通用更新
     */
    public boolean updateByFiledAndValue(String tableName, long dataId, String fieldName, String fieldValue) {
        StringBuilder sb = new StringBuilder("UPDATE " + tableName + " SET ");
        sb.append(fieldName + " = " + "'" + fieldValue + "' ");
        sb.append(" WHERE id = " + dataId + ";");
        return jdbcTemplate.update(sb.toString()) > 0;
    }

    /**
     * 通用删除
     */
    public boolean del(String tableName, long dataId) {
        return jdbcTemplate.update("DELETE FROM " + tableName + " WHERE id = " + dataId + ";") > 0;
    }
}

• 使用示例

import com.manage.service.CommonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author JHL
 * @version 1.0
 * @date 2023/2/16 18:58
 * @since : JDK 11
 */
@Service
public class DeviceService {
    private static final String TABLE_NAME = "camera";
    private static final String PK_FIELD = "id";
    private static final LinkedList<String> ALL_FIELD = new LinkedList<>();

    static {
        ALL_FIELD.add("ip");
        ALL_FIELD.add("port");
        ALL_FIELD.add("username");
        ALL_FIELD.add("pwd");
        ALL_FIELD.add("remark");
        ALL_FIELD.add("status");
    }

    @Autowired
    private CommonService commonService;

    public boolean save(String ip, String port, String username, String pwd, String remark, String status) {
        return commonService.save(TABLE_NAME, ALL_FIELD, ip, port, username, pwd, remark, status);
    }

    public List<Map<String, Object>> select() {
        return commonService.select(TABLE_NAME);
    }

    public Map<String, Object> selectLastOne() {
        return commonService.selectLastOne(TABLE_NAME, PK_FIELD);
    }

    public boolean updateByFiledAndValue(long dataId, String fieldName, String fieldValue) {
        return commonService.updateByFiledAndValue(TABLE_NAME, dataId, fieldName, fieldValue);
    }

    public boolean del(long dataId) {
        return commonService.del(TABLE_NAME, dataId);
    }
}