基于spring-boot-starter-JDBC组件的通用增删改查

前言

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

• pom

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

• 代码
• 接口

import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author JHL
 * @version 1.0
 * @date 2023/2/17 10:08
 * @since : JDK 11
 */
public interface CommonService {

    public boolean save(String tableName, LinkedList<String> toSaveField, Object... values);

    /**
     * 通用查全部
     */
    public List<Map<String, Object>> selectAll(String tableName);

    /**
     * 指定字段条件查询
     */
    public List<Map<String, Object>> selectByFieldAndValue(String tableName, String name, Object value);

    /**
     * 按照指定字段排序并取到最后一条
     */
    public Map<String, Object> selectOrderAndGetLastOne(String tableName, String fieldName);

    /**
     * 通用更新
     */
    public boolean updateByFiledAndValue(String tableName, String setFieldName, String setFieldValue, String conditionField, String conditionValue);

    /**
     * 通用删除
     */
    public boolean del(String tableName, String name, String value);

}

• 实现类

import cn.hutool.core.util.StrUtil;
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 CommonServiceImpl implements CommonService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 通用通用保存
     */
    @Override
    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;
    }

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

    /**
     * 指定字段条件查询
     */
    @Override
    public List<Map<String, Object>> selectByFieldAndValue(String tableName, String name, Object value) {
        return jdbcTemplate.queryForList(StrUtil.format("select * from {} where {} = {};", tableName, name, value));
    }

    /**
     * 按照指定字段排序并取到最后一条
     */
    @Override
    public Map<String, Object> selectOrderAndGetLastOne(String tableName, String fieldName) {
        return jdbcTemplate.queryForMap("select * from " + tableName + " order by " + fieldName + " desc limit 1;");
    }

    /**
     * 通用更新
     */
    @Override
    public boolean updateByFiledAndValue(String tableName, String setFieldName, String setFieldValue, String conditionField, String conditionValue) {
        return jdbcTemplate.update(StrUtil.format("UPDATE {} SET {} = {} where {} = {} ;", tableName, setFieldName, setFieldValue, conditionField, conditionValue)) > 0;
    }

    /**
     * 通用删除
     */
    @Override
    public boolean del(String tableName, String name, String value) {
        return jdbcTemplate.update(StrUtil.format("DELETE FROM {} WHERE {} = {};", tableName, name, value)) > 0;
    }
}

• 使用

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>> selectAll() {
        return commonService.selectAll(TABLE_NAME);
    }

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


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

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

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