hooyantsing's Blog

第47次课程

字数统计: 1k阅读时长: 5 min
2019/07/26

源辰74班

第47次课程

07.26.2019

内容

Dao封装[廖彦]

**Class DBHelper

**

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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
package dao.demo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class DBhelper {
    
    private static String url;
    private static String username;
    private static String password;
    
    //驱动加载
    static {
        try {
            Properties p = new Properties();
            //读取配置文件,转成输入流
            InputStream in = DBhelper.class.getClassLoader().getResourceAsStream("db.properties");
            p.load(in);
            String driver = p.getProperty("username");
            url = p.getProperty("url");
            username = p.getProperty("username");
            password = p.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    

    public static void main(String[] args) {
        String sql = "insert into dept values(?,?,?)";
        update(sql,11,"财务","衡阳");
    }
    
    /**
     * ...可变参数数组
     */
    //增删改
    public static int update(String sql,Object...paramArray) {
        Connection conn = null;
        try {
            conn = openConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0;i<paramArray.length;i++) {
                ps.setObject(i+1, paramArray[i]);
            }
            return ps.executeUpdate();
        } catch(Exception e){
            //异常转型
            throw new RuntimeException(e);
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    //连接
    public static Connection openConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    //查找
    public static List<Map<String,Object>> query(String sql,Object...paramArray) {
        Connection conn = null;
        try {
            conn = openConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0;i<paramArray.length;i++) {
                ps.setObject(i+1, paramArray[i]);
            }
            ResultSet rs = ps.executeQuery();
            //获取元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String,Object>> ret = new ArrayList<>();
            while(rs.next()) {
                Map<String,Object> row = new LinkedHashMap<>();
                for(int i=1;i<rsmd.getColumnCount();i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    row.put(columnName, columnValue);
                }
                ret.add(row);
            }
            return ret;
        } catch(Exception e){
            //异常转型
            throw new RuntimeException(e);
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    /**
     * 查询一条记录(例如根据主键查询记录)
     */
    public static Map<String,Object> queryOne (String sql,Object...paramArray){
        Connection conn = null;
        try {
            conn = openConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0;i<paramArray.length;i++) {
                ps.setObject(i+1, paramArray[i]);
            }
            ResultSet rs = ps.executeQuery();
            //获取元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            if(rs.next()) {
                Map<String,Object> row = new LinkedHashMap<>();
                for(int i=1;i<rsmd.getColumnCount();i++) {
                    //获取列名
                    String columnName = rsmd.getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    row.put(columnName, columnValue);
                }
                return row;
            }
            return null;
        } catch(Exception e){
            //异常转型
            throw new RuntimeException(e);
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    //分页查询
    public static List<Map<String,Object>> queryPage (
            String sql,int page,int rows,Object...paramArray){
        int startrow = (page - 1) * rows;
        int endrow = page * rows;
        sql = "select t.*\n" +
              " from ( select t.*,rownum rn from ( "+sql+") t where rownum <= ?) t\n" +
              " where t.rn > ?";    
        //生成新的查询数组
        Object[] newParamArray = new Object[paramArray.length + 2];
        System.arraycopy(paramArray, 0, newParamArray, 0, paramArray.length);
        newParamArray[newParamArray.length - 2] = endrow;
        newParamArray[newParamArray.length - 1] = startrow;

        return query(sql,newParamArray);
    }
    
    /**
     * 从查询结果中获取一个值,返回第一个行第一列的值
     */
    public static Object getValue(String sql,Object...paramArray) {
        Map<String,Object> row = queryOne(sql,paramArray);
        if(row==null) {
            return null;
        }else {
            for(Map.Entry<String, Object> e:row.entrySet()) {
                return e.getValue();
            }
        }
        return null;
    }
}

Class DBHelperTest

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
package dao.demo;

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

import org.junit.Assert;
import org.junit.Test;

public class DBHelperTest {
    
    @Test
    public void testQueryPage() {
        String sql = "select * from emp";
        List<Map<String,Object>> ret = DBhelper.queryPage(sql, 2, 5);
        Assert.assertEquals("BLAKE", ret.get(0).get("ENAME"));
        Assert.assertEquals("TURNER", ret.get(4).get("ENAME"));
    }
    
    @Test
    public void testQuery() {
        //查询所有表记录
        String sql = "select * from dept";
        List<Map<String,Object>> ret = DBhelper.query(sql);
        Assert.assertEquals(true,ret.size()>0);
        //根据deptno查询表记录
        sql = "select * from dept where deptno = ?";
        ret = DBhelper.query(sql, 30);
        Assert.assertEquals("SALES", ret.get(0).get("DNAME"));
    }
    @Test
    public void testUpdate() {
        //测试添加
        String sql = "insert into dept values(?,?,?)";
        int cnt = DBhelper.update(sql, 12,"财务","衡阳");
        Assert.assertEquals(1, cnt);
        //测试修改
        sql = "update dept set dname = ? where deptno = ?";
        cnt = DBhelper.update(sql, "技术",12);
        Assert.assertEquals(1, cnt);
        //测试删除
        sql = "delete dept where deptno = ?";
        cnt = DBhelper.update(sql, 12);
        Assert.assertEquals(1, cnt);
    }
}
CATALOG