jdbc连接数据库 

  1 import java.sql.Connection;
  2 import java.sql.DriverManager;
  3 import java.sql.PreparedStatement;
  4 import java.sql.ResultSet;
  5 import java.sql.ResultSetMetaData;
  6 import java.sql.SQLException;
  7 import java.util.ArrayList;
  8 import java.util.HashMap;
  9 import java.util.List;
 10 import java.util.Map;
 11 import java.util.Set;
 12 
 13 
 14 public class SqlBean {
 15     private static String className = PropertiesReader.getInstance().getProperty("driverName");
 16     private static String url = PropertiesReader.getInstance().getProperty("url");
 17     private static String userName = PropertiesReader.getInstance().getProperty("user");
 18     private static String password = PropertiesReader.getInstance().getProperty("password");
 19     
 20     /**
 21      * Constructor
 22      */
 23     public SqlBean() {
 24     
 25     }
 26     private static Connection conn = null;
 27     private static Connection getconnection(){
 28         if(conn == null){
 29             getConn();
 30         }
 31         return conn;
 32     }
 33     /**
 34      * 获得数据连接
 35      * @return:
 36      * @Description:
 37      */
 38     private static Connection getConn() {
 39         try {
 40             Class.forName(className);
 41             conn = DriverManager.getConnection(url, userName, password);
 42         } catch (Exception e) {
 43             e.printStackTrace();
 44         }
 45         return conn;
 46     }
 47 
 48     /**
 49      * 关闭连接
 50      * @param param:
 51      * @Description:
 52      */
 53     private static void closeDB(Object... param) {
 54         if (param != null) {
 55             for (Object obj : param) {
 56                 try {
 57                     if (obj instanceof ResultSet) {
 58                         ((ResultSet) obj).close();
 59                          obj = null;
 60                     }else if (obj instanceof PreparedStatement) {
 61                         ((PreparedStatement) obj).close();
 62                          obj = null;
 63                     }else if (obj instanceof Connection) {
 64                         ((Connection) obj).close();
 65                          obj = null;
 66                     }
 67                 } catch (SQLException e) {
 68                     e.printStackTrace();
 69                 }
 70             }
 71         }
 72     }
 73 
 74     /**
 75      *  创建PreparedSatement
 76      * @param sql
 77      * @return:
 78      * @Description:
 79      */
 80     private static PreparedStatement getPreparedStatement(String sql) {
 81         PreparedStatement pst = null;
 82         try {
 83             pst =getconnection().prepareStatement(sql);
 84         } catch (SQLException e) {
 85             e.printStackTrace();
 86         } finally {
 87             //closeDB(getconnection());
 88         }
 89         return pst;
 90     }
 91     /**
 92      * 设置参数
 93      * @param pst
 94      * @param param:
 95      * @Description:
 96      */
 97     private static void setParam(PreparedStatement pst, Object... param) {
 98         if(param == null){
 99             return;
100         }
101         int length = param.length;
102         for (int i = 0; i < length; i++) {
103             try {
104                 pst.setObject(i + 1, param[i]);
105             } catch (SQLException e) {
106                 e.printStackTrace();
107             }
108         }
109     }
110 
111     /**
112      * 执行添加、删除、修改
113      * @param sql
114      * @param param
115      * @return:
116      * @Description:
117      */
118     private static int executeUpdate(String sql, Object... param) {
119         int rows = 0;
120         PreparedStatement pst = getPreparedStatement(sql);
121         setParam(pst, param);
122         try {
123             rows = pst.executeUpdate();
124         } catch (SQLException e) {
125             e.printStackTrace();
126         } finally {
127             closeDB(pst);
128         }
129         return rows;
130     }
131     /**
132      * 删除
133      * @param tableName
134      * @param condition
135      * @param param
136      * @return:
137      * @Description:
138      */
139     public static boolean delete(String tableName,String condition,Object[] param){
140         String sql = "delete from " + tableName + " where " + condition;
141         int num = executeUpdate(sql, param);
142         if(num > 0){
143             return true;
144         }
145         return false;
146     }
147     
148     /**
149      * 更新
150      * @param tableName
151      * @param content
152      * @param condition
153      * @return:
154      * @Description:
155      */
156     public static boolean update(String tableName, Map<Object,Object> content, String condition){
157         String Values = "";
158         Set<Object> keylist = content.keySet();
159         Object[] param = new Object[content.size()];
160         int i = 0;
161         for(Object key : keylist){
162             Values += key + " = ?, ";
163             param[i]  = content.get(key);
164             i++;
165         }
166         String updateValues = Values.substring(0, Values.length() - 1);
167         String sql = "update " + tableName + " set " + updateValues + " where "+ condition;
168         int num = executeUpdate(sql, null);
169         if(num > 0){
170             return true;
171         }
172         return false;
173     }
174     
175     /**
176      * 添加
177      * @param tableName
178      * @param content
179      * @return:
180      * @Description:
181      */
182     public static boolean insert(String tableName, Map<Object,Object> content){
183         String sql = "";
184         String insertFields = "", temFields = "";
185         String insertValues = "", temValues = "";
186         Object[] param = new Object[content.size()];
187         Set<Object> keyslist = content.keySet();
188         int i = 0;
189         for(Object key : keyslist){
190             temFields += key + ", ";
191             temValues +=  " ?,";
192             param[i]  = content.get(key);
193             i++;
194         }
195         insertFields = temFields.substring(0, temFields.length() - 2);
196         insertValues = temValues.substring(0, temValues.length() - 1);
197         sql += "insert into " + tableName + " (" + insertFields + ") values" + "(" + insertValues + ")";
198         int num = executeUpdate(sql,param);
199         if(num > 0){
200             return true;
201         }
202         return false;
203     }
204     
205     /**
206      * 查询
207      * @param sql
208      * @param param
209      * @return:
210      * @Description:
211      */
212     public static List<Map<Object, Object>> exeQuery(String sql,Object... param) {
213         List<Map<Object, Object>> lst = new ArrayList<Map<Object, Object>>();
214         Map<Object, Object> map = null;
215         ResultSet rs = null;
216         ResultSetMetaData rsd = null;
217         PreparedStatement pst = getPreparedStatement(sql);
218         setParam(pst, param);
219         try {
220             rs = pst.executeQuery();
221             if (rs != null) {
222                 rsd = rs.getMetaData();
223                 while (rs.next()) {
224                     int columnCount = rsd.getColumnCount();
225                     map = new HashMap<Object, Object>();
226                     for (int i = 1; i < columnCount; i++) {
227                         map.put(rsd.getColumnName(i), rs.getObject(i));
228                     }
229                     lst.add(map);
230                 }
231             }
232         } catch (SQLException e) {
233             e.printStackTrace();
234         } finally {
235             closeDB(new Object[]{rs,pst});
236         }
237         return lst;
238     }
239     /**
240      * 查询所有
241      * @param tableName
242      * @return:
243      * @Description:
244      */
245     public static List<Map<Object, Object>> selectAll(String tableName){
246         String sql = "select * from " + tableName;
247         List<Map<Object, Object>> lst = new ArrayList<Map<Object, Object>>();
248         Map<Object, Object> map = null;
249         ResultSet rs = null;
250         ResultSetMetaData rsd = null;
251         PreparedStatement pst = getPreparedStatement(sql);
252         try {
253             rs = pst.executeQuery();
254             if (rs != null) {
255                 rsd = rs.getMetaData();
256                 while (rs.next()) {
257                     int columnCount = rsd.getColumnCount();
258                     map = new HashMap<Object, Object>();
259                     for (int i = 1; i < columnCount; i++) {
260                         map.put(rsd.getColumnName(i), rs.getObject(i));
261                     }
262                     lst.add(map);
263                 }
264             }
265         } catch (SQLException e) {
266             e.printStackTrace();
267         } finally {
268             closeDB(new Object[]{rs,pst});
269         }
270         return lst;
271     }
272     
273     
274     /**
275      * 执行查询
276      * @param sql
277      * @param param
278      * @return:
279      * @Description:
280      */
281     public static List<Map<Object, Object>> select(String sql,Object... param){
282         List<Map<Object, Object>> lst = new ArrayList<Map<Object, Object>>();
283         Map<Object, Object> map = null;
284         ResultSet rs = null;
285         ResultSetMetaData rsd = null;
286         PreparedStatement pst = getPreparedStatement(sql);
287         setParam(pst, param);
288         try {
289             rs = pst.executeQuery();
290             if (rs != null) {
291                 rsd = rs.getMetaData();
292                 while (rs.next()) {
293                     int columnCount = rsd.getColumnCount();
294                     map = new HashMap<Object, Object>();
295                     for (int i = 1; i < columnCount; i++) {
296                         map.put(rsd.getColumnName(i), rs.getObject(i));
297                     }
298                     lst.add(map);
299                 }
300             }
301         } catch (SQLException e) {
302             e.printStackTrace();
303         } finally {
304             closeDB(new Object[]{rs,pst});
305         }
306         return lst;
307     }
308 }
 1 import java.io.IOException;
 2 import java.io.InputStream;
 3 import java.util.Properties;
 4 
 5 /**
 6  * @author
 7  *
 8  */
 9 public class PropertiesReader extends Properties {
10     /**
11      * 
12      */
13     private static final long serialVersionUID = 1L;
14     private InputStream input =getClass().getResourceAsStream("/com/conf/jdbc.properties");
15     private static PropertiesReader read;
16     private PropertiesReader() {
17         try {
18             this.load(input);
19             input.close();
20         } catch (IOException e) {
21             e.printStackTrace();
22         }
23     }
24     
25     public static PropertiesReader getInstance() {
26         if(read == null) {
27             return new PropertiesReader();
28         }
29         else {
30             return read;
31         }
32     }
33 }


配置文件jdbc.properties

1 driverName = com.mysql.jdbc.Driver
2 url = jdbc:mysql://218.206.179.52:3307/CMB_SPIDER?useUnicode=true&characterEncoding=utf8
3 user = yyttest
4 password =Abc123

 

posted @ 2012-06-30 14:20  绒花雪冷  阅读(210)  评论(0)  编辑  收藏  举报