1 packagecom.latiny.db;2
3 import java.io.*;4 import java.sql.*;5 importjava.util.ArrayList;6 importjava.util.Properties;7
8 /*
9 * 数据库操作类10 */
11
12 public classDBUtil {13
14 //定义需要的变量
15 private static String driver =null;16 private static String url =null;17 private static String user=null;18 private static String password=null;19
20 private staticConnection conn;21 //使用PreparedStatment可以防止sql注入
22 private staticPreparedStatement ps;23 private staticResultSet rs;24 private staticCallableStatement cs;25
26 //读配置文件
27 private static Properties pp=null;28 private static InputStream fis=null;29
30
31 //加载驱动,只需要执行一次
32 static
33 {34 try
35 {36 pp = newProperties();37
38 //当我们使用java web的时候,读取文件要使用类加载器
39 fis = DBUtil.class.getClassLoader().getResourceAsStream("dbinfo.properties");40
41 pp.load(fis);42 driver = pp.getProperty("DRIVER");43 url = pp.getProperty("URL");44 user = pp.getProperty("USER");45 password = pp.getProperty("PASSWORD");46
47 //1 加载驱动
48 Class.forName(driver);49
50 }51 catch(Exception e)52 {53 e.printStackTrace();54 }55 finally
56 {57 try
58 {59 fis.close();60 } catch(IOException e) {61 //TODO Auto-generated catch block
62 e.printStackTrace();63 }64 fis = null;65 }66 }67
68 /*
69 * 获取Connection连接70 */
71 public staticConnection getConn()72 {73 try
74 {75 //2 获取数据库连接
76 conn =DriverManager.getConnection(url, user, password);77 }78 catch(SQLException e)79 {80 //TODO Auto-generated catch block
81 e.printStackTrace();82 }83
84 returnconn;85 }86
87 /*
88 * 直接返回rs结果,此方法不能关闭rs,因为后面调用它的类还会用到,如果关闭则不能正常使用89 */
90 public staticResultSet queryResult(String sql, String[] parameters)91 {92 try
93 {94 conn =getConn();95 //3 创建Statement对象
96 ps =conn.prepareStatement(sql);97 //4 给问号赋值,即给sql语句的条件参数赋值如果需要的话
98 if(parameters!=null)99 {100 for(int i=1; i<=parameters.length; i++)101 {102 ps.setString(i, parameters[i-1]);103 }104 }105
106 //5 执行sql获取返回结果
107 rs =ps.executeQuery();108 }109 catch(SQLException e)110 {111 //TODO Auto-generated catch block
112 e.printStackTrace();113 }114
115 returnrs;116 }117
118 /*
119 * 将rs结果封装成ArrayList,然后可以关闭rs,节省数据库访问资源120 */
121 public staticArrayList queryResult2(String sql, String[] parameters)122 {123 ArrayList al = newArrayList();124
125 try
126 {127 //2 获取数据库连接
128 conn =getConn();129 //3 创建Statement对象
130 ps =conn.prepareStatement(sql);
132 //4 给问号赋值,即给sql语句的条件参数赋值如果需要的话
133 if(parameters!=null)134 {135 for(int i=1; i<=parameters.length; i++)136 {137 ps.setString(i, parameters[i-1]);138 }139 }140
141 //5 执行sql语句获取返回结果
142 rs =ps.executeQuery();143
144 //获取rs的结构
145 ResultSetMetaData rsmd =rs.getMetaData();146 //获取查询语句的列数
147 int column =rsmd.getColumnCount();148
149 while(rs.next())150 {151 //对象数组,存储一行数据
152 Object[] objs = newObject[column];153 for(int i=0; i
160 }161 catch(SQLException e)162 {163 //TODO Auto-generated catch block
164 e.printStackTrace();165 }166 finally
167 {168 //关闭资源
169 close(rs, ps, conn);170 }171
172 returnal;173 }174
175 //调用存储过程,带输入输出参数的
176 public staticCallableStatement callProcedure(String sql, String[] inputPara, Integer[] outputPara)177 {178
179 try
180 {181 conn =getConn();182 cs =conn.prepareCall(sql);183 for(int i=0; inputPara!=null && i
188 //给output参数赋值
189 for(int j=0; outputPara!=null && j
194 cs.execute();195
196 } catch(SQLException e) {197 //TODO Auto-generated catch block
198 e.printStackTrace();199 }200 finally
201 {202 close(rs, ps, conn);203 }204
205 returncs;206
207 }208
209 //update, insert, delete
210 public staticInteger updateData(String sql, String[] parameters)211 {212 int result = 0;213 try
214 {215 conn =getConn();216 ps =conn.prepareStatement(sql);217 if(parameters!=null)218 {219 for(int i=0; i
225 //执行executeUpdate并且返回受影响的行数
226 result =ps.executeUpdate();227
228 }229 catch(Exception e)230 {231 e.printStackTrace();232 }233 finally
234 {235 close(rs, ps, conn);236 }237
238 returnresult;239 }240
241 //关闭对应的数据库连接资源
242 public static voidclose(ResultSet rs1, PreparedStatement ps1, Connection conn1)243 {244
245 try
246 {247 if(rs1!=null)248 {249 rs1.close();250 }251 if(ps1!=null)252 {253 ps1.close();254 }255 if(conn1!=null)256 {257 conn1.close();258 }259
260 } catch(SQLException e) {261 //TODO Auto-generated catch block
262 e.printStackTrace();263 }264
265 }266 }