wangshuo'blog

wangshuo'blog

java | jdbc 通用的数据库访问层设计

Published on
Last updated on

java | 通用的数据库访问层设计

下面给出了一个基础，相对通用的数据库访问层代码，简单修改一下可以通用

创建表

1
2
3
4
5
6
7
8
CREATE TABLE `goods` (
     `id` int NOT NULL AUTO_INCREMENT COMMENT '主键 id',
     `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '商品名称',
     `color` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '颜色',
     `price` int DEFAULT NULL COMMENT '价格',
     `number` int DEFAULT NULL COMMENT '数量',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据库配置文件

jdbc.properties

1
2
3
4
jdbc.driver   = com.mysql.cj.jdbc.Driver
jdbc.url      = jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
jdbc.username = root
jdbc.password = MhS9PbWUbxwrerpo

代码

JDBCUtil 工具类

主要作用是读取从文件中读取数据库配置，和提供一个静态方法获取数据连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;


/**
 * @author wangshuo
 * @date
 */
public class JDBCUtil {
    public static String DRIVER;
    public static String URL;
    public static String USER;
    public static String PASSWORD;
    public static InputStream inputStream;

    static {
        // 获取数据库默认的配置
        Properties properties = new Properties();
        try {
            inputStream = JDBCUtil.class.getClassLoader().getResource("jdbc.properties").openStream();
            properties.load(inputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }
        DRIVER= properties.getProperty("jdbc.driver");
        URL = properties.getProperty("jdbc.url");
        USER = properties.getProperty("jdbc.username");
        PASSWORD =properties.getProperty("jdbc.password");
    }


    /**
     * 获取数据库的连接
     * @return
     */
    public static Connection getConnect(){
        Connection conn = null;
        //1.加载驱动程序
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2. 获得数据库连接
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //3.操作数据库，实现增删改查

        return conn;
    }

}

GoodsDao

提供增删改查的相关方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
@Slf4j
public class GoodsDao<T> {

    private Connection connection = JDBCUtil.getConnect();

    /**
     * 表名称
     */
    private final String tableName = "goods";

    /**
     * 字段列表
     */
    private final String columnList = "id, name, color, price, number";


    private volatile static GoodsDao goodsDao;

    public GoodsDao() {

    }



    /**
     * 更新数据库连接
     */
    public void updateConnection(){
        connection = JDBCUtil.getConnect();
    }


    /**
     * 向数据库中插入信息
     * @param entity
     */
    public int insert(Goods entity){
        final String sql = String.format("INSERT INTO  %s(name, color, price, number) VALUES('%s', '%s', %d, %d);",
                tableName,
                 entity.getName(), entity.getColor(), entity.getPrice(), entity.getNumber());
        Statement stmt = null;
        log.info(sql);
        int len = -1;
        try {
            stmt = connection.createStatement();
            len =  stmt.executeUpdate(sql);
            //执行
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return len;
    }

    public int insertBatch(List<Goods> entities){
        int len = 0;
        for(Goods entity: entities){
            len += this.insert(entity);
        }
        return len;
    }

    /**
     * 删除信息 通过主键 id
     * @param id
     */
    public void deleteById(T id){
        final String sql = String.format("DELETE FROM %s WHERE id = %d",tableName, id);
        log.info(sql);
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            stmt.executeUpdate(sql);
            //执行
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    /**
     * 更新信息
     * @param entity
     */
    public void updateById(Goods entity){
        final String sql = String.format(
                "UPDATE %s SET name = '%s', color='%s', price= %d, number= %d WHERE id = %d;",
                tableName,
                entity.getName(), entity.getColor(), entity.getPrice(), entity.getNumber(), entity.getId());
        log.info(sql);
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            stmt.executeUpdate(sql);
            //执行
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public List<Goods> findAll() {
        final String sql = "SELECT " + columnList + " FROM "+tableName+";";
        return executeQuery(sql);
    }



    /**
     * 执行相关的查询，返回对应的列表
     * @param sql
     * @return
     */
    private List<Goods> executeQuery(String sql){
        log.info(sql);
        List<Goods> entities = null;
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            entities = generateResult(rs);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return entities;
    }


    /**
     * 遍历结果集，生成列表
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<Goods> generateResult(ResultSet rs) throws SQLException {
        List<Goods> entities = new ArrayList<>();
        while(rs.next()){

            Goods entity = new Goods(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getString(3),
                    rs.getInt(4),
                    rs.getInt(5)
            );
            entities.add(entity);
        }
        return entities;
    }
}