数据库JDBC包装实现

定义增删改查的接口

package cn.com.aistudynet.base;

import java.util.List;

public interface BaseService<T> {
    /**
     * 新增记录
     * @param t
     */
    void add(T t);

    /**
     * 以获取记录数
     * @param t
     * @return
     */
    Integer getCount(T t);

    /**
     * 获取列表
     * @return
     */
    List<T> getDataList();

    /**
     * 更新记录
     * @param t
     */
    boolean update(T t);    //这里修改过了原本是T update(T t); 实现为通过id替换数据库的值

    /**
     * 插入记录
     * @param t
     * @return
     */
    boolean insert(T t);   //这里修改稿过了原本是T insert(T t);  实现为插入数据返回是否成功

}

定义配置文件接口

package cn.com.aistudynet.base;

/**
 * 该接口表示数据库配置信息
 */
public interface Config {
    /**
     * 数据库加载驱动
     */
    String drive = "com.mysql.cj.jdbc.Driver";

    /**
     *数据库电脑所在的ip地址
     */
    String ip="localhost";

    /**
     * 数据库在该电脑上所占用的端口信息
     */
    String port="3306";

    /**
     * 进入的数据库
     */
    String database = "test";

    /**
     * 数据库需要配置的编码格式
     */
    String code="?characterEncoding=utf8&serverTimezone=Asia/Shanghai";

    /**
     * 登录数据库的用户
     */
    String user="root";

    /**
     * 登录数据库的密码
     */
    String password="98526";
}

 实现抽象类:

package cn.com.aistudynet.base;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.json.JSONTokener;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.sql.*;
import java.util.*;

/**
 * 数据库链接类调用该类需要自行判断从传入对象的值不能为空且传入的值不能与主键id互相冲突
 * 本类提供了getColumn()方法判断在数据库的表多少列
 * @param <T>
 */
public abstract class DbMangerBase<T> implements BaseService<T> {
    //数据库链接
    private static String url = "jdbc:mysql://" + Config.ip + ":" + Config.port + "/" + Config.database + Config.code;
    //获取数据库
    private static Connection connection = null;

    //加载数据库驱动
    static {
        try {
            Class.forName(Config.drive);
            connection = DriverManager.getConnection(url, Config.user, Config.password);
        } catch (ClassNotFoundException x) {
            System.err.println("数据库驱动加载错误...");
        } catch (SQLException v) {
            System.err.println("数据库链接失败...");
        }
    }

    //该方法用来获取一个类的实例的方法
    private T getInstance() {
        try {
            ParameterizedType superClass = (ParameterizedType) getClass().getGenericSuperclass();
            Class<T> type = (Class<T>) superClass.getActualTypeArguments()[0];
            return type.getDeclaredConstructor().newInstance();
        } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override   //增加对象到数据库  大约78秒
    public void add(T t) {
        if (null == t) {
            return;
        }
        T temp = getInstance();
        temp = t;
        //将实例映射到了map上
        //Map<String, Object> map = BeanUtil.beanToMap(temp);
        Map<String, Object> map = BeanUtil.beanToMap(temp, false, false);
        if (map.size() == 0) {
            System.err.println("对象不能为空...");
            return;
        }
        //INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
        StringJoiner key = new StringJoiner(","); //后面的参数表示每添加一次则加一个逗号最后一个不加逗号
        StringJoiner value = new StringJoiner(",");
        Boolean flag = Boolean.FALSE;
        for (Map.Entry<String, Object> k : map.entrySet()) {
            if (null == k.getValue() || Objects.equals(k.getValue(), "")) {
                continue;
            }
            flag = Boolean.TRUE;
            key.add(k.getKey());
            if (k.getValue() instanceof String) {
                value.add("\'" + k.getValue() + "\'");
            } else {
                value.add("" + k.getValue());
            }
        }
        if (!flag) {
            System.out.println("对象为空");
            return;
        }
        StringBuilder sql = new StringBuilder("INSERT INTO " + t.getClass().getSimpleName());
        sql.append("(").append(key).append(")");
        sql.append(" VALUES (").append(value).append(")");
        try {
            System.out.println(sql);
            PreparedStatement preparedStatement = connection.prepareStatement(String.valueOf(sql));
            int i = preparedStatement.executeUpdate();
            if (i <= 0) {
                System.err.println("增加失败...");
            }
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("增加sql语句出现问题...");
        }
    }


    @Override   //返回拥有与这个对象信息相同的列的数量
    public Integer getCount(T t) {  //基本稳定125毫秒
        T temp = getInstance();
        temp = t;
        String simpleName = temp.getClass().getSimpleName();//获取名字
        StringJoiner stringJoiner = new StringJoiner(" and "); //拼接条件
        Map<String, Object> map = BeanUtil.beanToMap(temp, false, false);

        for (Map.Entry<String, Object> k : map.entrySet()) {
            if (k.getValue() instanceof String) {
                stringJoiner.add(k.getKey() + "= '" + k.getValue() + "'");
            } else {
                stringJoiner.add(k.getKey() + "=" + k.getValue());
            }
        }
        int count = 0;
        try {
            String sql = "select count(1) as count  from " + simpleName + " where " + stringJoiner;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt("count");
            } else {
                System.err.println("数据库查询错误...");
            }
            preparedStatement.close();
            resultSet.close();
        } catch (SQLException e) {
            System.err.println("数据库查询错误....");
        }
        return count;
    }

    @Override   //获取数据
    public List<T> getDataList() {  //125~171毫秒左右
        T temp = getInstance();
        //这里不需要这么写！！！！！！！！
//        Map<String, Object> map = BeanUtil.beanToMap(temp, false, false);
//        //装入字段名
//        ArrayList<Object> list = new ArrayList<>();
//        for (Map.Entry<String, Object> k : map.entrySet()) {
//            list.add(k.getKey());
//        }
        ArrayList<T> list = new ArrayList<>();
        String simpleName = temp.getClass().getSimpleName();
        String sql = "select * from " + simpleName;
        try {
            PreparedStatement yby = connection.prepareStatement(sql);
            ResultSet resultSet = yby.executeQuery();
            //这里获取数据表中的数据
            HashMap data = null;
            //解析结果集
            while (resultSet.next()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                data = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    data.put(columnName, resultSet.getObject(columnName));
                }
                T instance = getInstance();
                //将查询的数据转成对象数据
                T t = BeanUtil.fillBeanWithMap(data, instance, true, false);
                list.add(t);
            }
            yby.close();
            resultSet.close();
        } catch (SQLException e) {
            System.err.println("获取数据列表失败...");
        }
        return list;
    }

    @Override  //修改 如果没有这个id则会报错
    public boolean update(T t) {    //94~141毫秒左右
        Map<String, Object> map = BeanUtil.beanToMap(t, false, false);
        StringJoiner stringJoiner = new StringJoiner(",");
         int temp=0;
         String temp1=null;
        for (Map.Entry<String, Object> stringObjectEntry : map.entrySet()) {
            if (stringObjectEntry.getValue()==null){
                System.err.println("值不能为空...");
                return false;
            }
            temp++;
            if (temp==1){
                temp1=stringObjectEntry.getKey()+"="+stringObjectEntry.getValue();
            }
            if (stringObjectEntry.getValue() instanceof String){
                stringJoiner.add(stringObjectEntry.getKey()+"= '"+stringObjectEntry.getValue()+"'");
            }else {
                stringJoiner.add(stringObjectEntry.getKey()+"="+stringObjectEntry.getValue());
            }
        }
        String simpleName = t.getClass().getSimpleName();
        String sql="update "+simpleName+" set "+stringJoiner+" WHERE "+temp1;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int i = preparedStatement.executeUpdate();
            if (i<=0){
                System.err.println("替换执行失败...");
                return false;
            }
            preparedStatement.close();
        } catch (SQLException e) {
            System.err.println("替换执行出错...");
            return false;
        }
        return true;
    }

    @Override   //插入数据  该方法执行大约78毫秒左右
    public boolean insert(T t) {
        Map<String, Object> map = BeanUtil.beanToMap(t, false, false);
        StringJoiner stringJoiner = new StringJoiner(",");
        //INSERT INTO `student`(`name`,`password`) VALUES ('小步','123456');
        for (Map.Entry<String, Object> stringObjectEntry : map.entrySet()) {
            if (stringObjectEntry.getValue() instanceof String){
                stringJoiner.add("'"+stringObjectEntry.getValue()+"'");
            }else {
                stringJoiner.add(stringObjectEntry.getValue() + "");
            }
        }
        String simpleName = t.getClass().getSimpleName();
        String sql="insert into `"+simpleName+"` values ("+stringJoiner+")";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int i = preparedStatement.executeUpdate();
            if (i<=0){
                System.err.println("插入数据库失败....");
                return false;
            }
        } catch (SQLException e) {
            System.err.println("插入数据库失败...");
            return false;
        }
        return true;
    }

    /**
     * 返回传入的对象在数据库对应的表有多少行
     * @param oop
     * @return
     */
    public Integer getColumn(T t){  //该方法约65毫秒上下
        String simpleName = t.getClass().getSimpleName();
        String sql="SELECT COUNT(1) AS `count` FROM "+simpleName;
        System.out.println(sql);
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int count = resultSet.getInt("count");
                return count;
            }
            System.err.println("sql查询行数失败...");
        } catch (SQLException e) {
            System.err.println("sql查询行数失败...");
        }
        return null;
    }

    //关闭资源
    public void close() {
        try {
            connection.close();
        } catch (SQLException e) {
            System.err.println("资源关闭失败...");
        }
    }
}