新的Java访问mysql数据库工具类的操作代码

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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
package util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;

public class JdbcUtilV2 {
    /**
     * 定义需要的变量
     */
    private static Connection connection = null;
    /**
     * 在大多情况下，我们使用的是PrepardStatement 来代替Statement
     * 这样可以防止sql注入
     */
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;

    /**
     * 连接数据库参数
     */
    private static String username = "";
    private static String password = "";
    private static String driver = "";
    private static String url = "";

    /**
     * 加载驱动，只需一次
     */
    public JdbcUtilV2(String dr, String ur,String user, String passwd) {
        try {
        	username = user;
        	password = passwd;
            driver = dr;
            url = ur;
            Class.forName(driver);
        } catch (Exception e) {
            System.err.println("连接失败，请检查连接参数");
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return 返回Connection
     */
    private static Connection getConnection() {
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
        	JOptionPane.showConfirmDialog(null , "获取连接失败,请检查网络是否连通", "系统消息", JOptionPane.CLOSED_OPTION);
            e.printStackTrace();
        }
        return connection;
    }
    
    /**
     *  查询表
     *  @return 返回查询结果的List
     */

    public ArrayList<HashMap<String, Object>> query(String sql, Object ...params){
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setPreparedStatement(params);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData(); // key name
            int columnCount = metaData.getColumnCount(); // column count
            
            ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
            while(resultSet.next()) {
            	HashMap<String, Object> rowData = new HashMap<String, Object>();
            	for(int i = 1; i <= columnCount; ++i) {
            		rowData.put(metaData.getColumnName(i), resultSet.getObject(i));
            	}
            	list.add(rowData);
            }
            return list;
        }catch (Exception e){
            System.err.println("查询失败！");
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }
    
    public boolean exists(String sql, Object ...params) {
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setPreparedStatement(params);
            resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        }catch (Exception e){
            System.err.println("查询失败！");
            e.printStackTrace();
        } finally {
            close();
        }
        return false;
    }
    /**
     * 可处理insert/delete/update语句
     * @param sql sql语句
     * @param params 占位符参数数组
     * @return 返回bool值，表示是否成功
     */
    public boolean execute(String sql, Object ...params){
        try {
            //获取连接
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //对占位符进行赋值
            setPreparedStatement(params);
            //提交sql
            preparedStatement.executeUpdate();
            return true;
        } catch (Exception e){
            System.err.println("表更新失败!");
            e.printStackTrace();
        } finally {
            close();
        }
        return false;
    }
    
    public void executeVoidProc(String sql, Object ...params){
        try {
            //获取连接
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //对sql中的占位符进行赋值
            setPreparedStatement(params);
            
            preparedStatement.execute();
        }catch (Exception e){
            System.err.println("查询失败！");
            e.printStackTrace();
        } finally {
            close();
        }
    }
    /**
     * 对sql语句中的占位符进行赋值
     * @param params 参数值
     * @throws SQLException sql异常
     */
    private void setPreparedStatement(Object ... params) throws SQLException {
        if(params != null && params.length > 0){
            for(int i = 0; i < params.length; i++){
                if("null".equals(params[i])){
                    preparedStatement.setNull(i + 1, Types.NULL);
                }else{
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
        }
    }
    
 

    /**
     * 关闭资源的函数
     */
    private void close() {
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            resultSet = null;
        }
        if(preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            preparedStatement = null;
        }
        if(connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            connection = null;
        }
    }

}