1 包含分页的JDBC工具类
2 包含分页的JDBC工具类
3 package com.shxt.tool;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.ParameterMetaData;
7 import java.sql.PreparedStatement;
8 import java.sql.ResultSet;
9 import java.sql.ResultSetMetaData;
10 import java.sql.SQLException;
11 import java.sql.Statement;
12 import java.util.ArrayList;
13 import java.util.HashMap;
14 import java.util.Map;
15 /**
16 * @Author：何云龙
17 * @Version:JDBC封装1.1 2012-11-29 下午06:38:55
18 * @Description:jdbc的封装
19 */
20 public class DBUtil {
21 private String url = "jdbc:mysql://localhost:3306/sduentdb";
22 private String userName = "root";
23 private String passWord = "root";
24 private Connection conn = null;
25 private Statement st = null;
26 private PreparedStatement ps = null;
27 private ResultSet rs=null;
28
29
30
31
32
33
34
35
36 // 加载驱动，只加载一次即可
37 static {
38 try {
39 // System.out.println("加载驱动正在进行");
40 Class.forName("com.mysql.jdbc.Driver");
41 } catch (ClassNotFoundException e) {
42 System.out.println("加载驱动遇到异常");
43 e.printStackTrace();
44 }
45 }
46 public Connection getConnection() {
47 // 创建连接
48 try {
49 conn = DriverManager.getConnection(url, userName, passWord);
50 return conn;
51 } catch (SQLException e) {
52 System.out.println("创建连接出现异常！！");
53 e.printStackTrace();
54 }
55 return null;
56 }
57 public int update(String sql) {
58 // row是指受影响的行数
59 int row = -1;
60 try {
61 // 当前连接如果是空或者被关闭，需要重新创建一个连接
62 if (conn == null || conn.isClosed()) {
63 conn = getConnection();
64 }
65 st = conn.createStatement();
66 row = st.executeUpdate(sql);
67 } catch (SQLException e) {
68 e.printStackTrace();
69 }finally{
70
71 //关闭资源
72 release();
73 }
74 return row;
75 }
76 public int update(String sql, Object[] obj) {
77 int row = -1;
78 // 当前连接如果是空或者被关闭，需要重新创建一个连接
79 try {
80 if (conn == null || conn.isClosed()) {
81 conn = getConnection();
82 }
83 ps = conn.prepareStatement(sql);
84 // 参数结构数据对象
85 ParameterMetaData pmd = ps.getParameterMetaData();
86 int varCount = pmd.getParameterCount();
87 // 给sql语句中的问号？附上值
88 for (int i = 0; i < varCount; i++) {
89 ps.setObject(i + 1, obj[i]);
90 }
91 row = ps.executeUpdate();
92 } catch (SQLException e) {
93 e.printStackTrace();
94 }finally{
95
96 //关闭资源
97 release();
98 }
99 return row;
100 }
101 public ArrayList<Map<String, Object>> queryToList(String sql) {
102 ArrayList<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
103 // 当前连接如果是空或者被关闭，需要重新创建一个连接
104 try {
105 if (conn == null || conn.isClosed()) {
106 conn = getConnection();
107 }
108 st = conn.createStatement();
109 rs = st.executeQuery(sql);
110 ResultSetMetaData rsmd = rs.getMetaData();
111 int col = rsmd.getColumnCount();
112
113 while (rs.next()) {
114 Map<String, Object> map = new HashMap<String, Object>();
115 for (int i = 1; i <= col; i++) {
116 map.put(rsmd.getColumnName(i),
117 rs.getObject(rsmd.getColumnName(i)));
118 }
119 list.add(map);
120
121 }
122 // System.out.println(list);
123 return list;
124
125 } catch (Exception e) {
126 e.printStackTrace();
127 }finally{
128
129 //关闭资源
130 release();
131
132 }
133 return null;
134 }
135
136
137 public ArrayList<Map<String, Object>> queryToList(String sql,String[] str) {
138 ArrayList<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
139 // 当前连接如果是空或者被关闭，需要重新创建一个连接
140 try {
141 if (conn == null || conn.isClosed()) {
142 conn = getConnection();
143 }
144 ps = conn.prepareStatement(sql);
145 // 参数结构数据对象
146 ParameterMetaData pmd = ps.getParameterMetaData();
147 int varCount = pmd.getParameterCount();
148 // 给sql语句中的问号？附上值
149 for (int i = 0; i < varCount; i++) {
150 ps.setString(i + 1, str[i]);
151 }
152
153 rs = ps.executeQuery();
154 ResultSetMetaData rsmd = rs.getMetaData();
155 int col = rsmd.getColumnCount();
156 while (rs.next()) {
157 Map<String, Object> map = new HashMap<String, Object>();
158 for (int i = 1; i <= col; i++) {
159 map.put(rsmd.getColumnName(i),
160 rs.getObject(rsmd.getColumnName(i)));
161 }
162 list.add(map);
163
164 }
165 return list;
166 } catch (Exception e) {
167 e.printStackTrace();
168 }finally{
169
170 //关闭资源
171 release();
172 }
173 return null;
174 }
175
176
177 private int pageSize;//页容量
178 private int rowsCount;//总记录数
179 private int start;//开始位置
180 private int end;//结束位置
181 private int pageNow;//当前页
182 public static int pageCount;//总页数
183
184
185 public ArrayList<Map<String, Object>> getPage(int pageSize,int pageNow,String sql){
186 rowsCount=queryToList(sql).size();//获取到总记录数
187 pageCount=rowsCount%pageSize==0?rowsCount/pageSize:(rowsCount/pageSize+1);//获取到总页数
188 start=pageNow*pageSize-pageSize;//开始位置
189
190 String sqlPage="select * from ("+sql+") as t limit "+start+" , "+pageSize;
191
192 ArrayList<Map<String, Object>> list=queryToList(sqlPage);
193
194 return list;
195
196 }
197
198
199
200
201
202
203
204
205
206 //关闭资源 释放资源
207 public void release(){
208
209 try {
210
211 if(rs!=null){
212 rs.close();
213 }
214 } catch (SQLException e) {
215 e.printStackTrace();
216 }
217
218 try {
219 if(st!=null){
220 st.close();
221 }
222 } catch (SQLException e) {
223 e.printStackTrace();
224 }
225 try {
226 if(ps!=null){
227 ps.close();
228 }
229 } catch (SQLException e) {
230 e.printStackTrace();
231 }
232 try {
233 if(conn!=null){
234 conn.close();
235 }
236 } catch (SQLException e) {
237 e.printStackTrace();
238 }
239
240
241
242
243 }
244
245
246 }
