DBUtil.java
1 package org.guangsoft.util; 2 3 import java.io.InputStream; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.sql.ResultSetMetaData; 9 import java.util.ArrayList; 10 import java.util.List; 11 import java.util.Properties; 12 13 import org.apache.commons.beanutils.BeanUtils; 14 15 16 /** 17 * 18 * @author guanghe 19 */ 20 public class DBUtil 21 { 22 //定义连接资源 23 private static Connection ct = null; 24 private static PreparedStatement ps = null; 25 private static ResultSet rs = null; 26 27 //定义配置参数 28 private static String driver = null; 29 private static String url = null; 30 private static String username = null; 31 private static String password = null; 32 33 //定义配置文件引入 34 private static Properties pp = null; 35 private static InputStream is = null; 36 37 //读取配置参数，加载驱动 38 static 39 { 40 try 41 { 42 pp = new Properties(); 43 is = DBUtil.class.getClassLoader().getResourceAsStream("org/guangsoft/util/db.properties"); 44 pp.load(is); 45 driver = pp.getProperty("driver"); 46 url = pp.getProperty("url"); 47 username = pp.getProperty("username"); 48 password = pp.getProperty("password"); 49 Class.forName(driver); 50 } 51 catch (Exception e) 52 { 53 e.printStackTrace(); 54 System.exit(0); 55 } 56 finally 57 { 58 try 59 { 60 is.close(); 61 } 62 catch (Exception e) 63 { 64 e.printStackTrace(); 65 } 66 is = null; 67 } 68 } 69 70 //获取连接 71 public static Connection getConnection() 72 { 73 try 74 { 75 ct = DriverManager.getConnection(url, username, password); 76 } 77 catch (Exception e) 78 { 79 e.printStackTrace(); 80 } 81 return ct; 82 } 83 84 //执行DQL查询 85 public static List executeQuery(String sql, Object[] parameters,Class clazz) 86 { 87 List list = new ArrayList (); 88 try 89 { 90 //得到数据结果集 91 ct = getConnection(); 92 ps = ct.prepareStatement(sql); 93 if (parameters != null) 94 { 95 for (int i = 0; i < parameters.length; i++) 96 { 97 ps.setObject(i + 1, parameters[i]); 98 } 99 }100 rs = ps.executeQuery();101 102 //封装数据103 ResultSetMetaData metaData = rs.getMetaData();104 int columnCount = metaData.getColumnCount();105 while(rs.next())106 {107 T t = clazz.newInstance();108 for(int i = 0; i < columnCount; i++)109 {110 String columnName = metaData.getColumnName(i+1);111 Object value = rs.getObject(columnName);112 BeanUtils.copyProperty(t, columnName, value); 113 }114 list.add(t);115 }116 }117 catch(Exception e)118 {119 e.printStackTrace();120 }121 finally122 {123 close();124 }125 return list;126 }127 128 //执行DML更新129 public static int executeUpdate(String sql, Object[] parameters)130 {131 try132 {133 ct = getConnection();134 ps = ct.prepareStatement(sql);135 if (parameters != null)136 {137 for (int i = 0; i < parameters.length; i++)138 {139 ps.setObject(i + 1, parameters[i]);140 }141 }142 return ps.executeUpdate();143 }144 catch (Exception e)145 {146 e.printStackTrace();147 }148 finally149 {150 close();151 }152 return 0;153 }154 155 //关闭所有资源连接156 public static void close()157 {158 if (rs != null)159 {160 try161 {162 rs.close();163 }164 catch (Exception e)165 {166 e.printStackTrace();167 }168 rs = null;169 }170 if (ps != null)171 {172 try173 {174 ps.close();175 }176 catch (Exception e)177 {178 e.printStackTrace();179 }180 ps = null;181 }182 if (null != ct)183 {184 try185 {186 ct.close();187 }188 catch (Exception e)189 {190 e.printStackTrace();191 }192 ct = null;193 }194 }195 196 }
db.properties
1 driver = com.mysql.jdbc.Driver2 url = jdbc:mysql://localhost:3306/test3 username = root4 password =root