JDBC连接数据库
来源：互联网 发布：村上春树作品推荐知乎 编辑：程序博客网 时间：2024/05/14 10:49
import java.io.BufferedInputStream;import java.io.FileInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import java.util.TreeMap;import javax.el.ELContext;import net.sf.json.JSONArray;import net.sf.json.JSONObject;public class JdbcByPropertiesUtil { private static String filePath = "jdbc.properties"; private static JdbcByPropertiesUtil instance = null; public JdbcByPropertiesUtil() {super();}/** * 单例方式创建对象 * @return */ public static JdbcByPropertiesUtil getInstance() { if (instance == null) { synchronized (JdbcByPropertiesUtil.class) { if (instance == null) { instance = new JdbcByPropertiesUtil(); } } } return instance; } /** * 读取properties文件中 数据库连接信息 * @param filePath * add 2012-4-17 */ public Properties readPropertiesFile(){String dir = getClass().getProtectionDomain().getCodeSource().getLocation().getPath();dir = dir.substring(0,dir.indexOf("classes")+8); String realFilePath = dir+filePath; Properties pros = new Properties(); try { InputStream is = new BufferedInputStream(new FileInputStream(realFilePath)); pros.load(is); } catch (Exception e) { e.printStackTrace(); } return pros; } /** * 注册驱动 * 静态代码块 用于启动web服务器时加载驱动 */ static{ JdbcByPropertiesUtil util = new JdbcByPropertiesUtil(); Properties pros = util.readPropertiesFile(); String className = (String) pros.get("className"); try { Class.forName(className).newInstance(); } catch (Exception e) { e.printStackTrace(); } } /** * 获取数据库连接 * modify 2012-4-17 * @param con * @return */public Connection getConnection(){ Properties pros = readPropertiesFile(); String url = (String) pros.get("url"); String user = (String) pros.get("user"); String password = (String) pros.get("password"); Connection conn = null;try {conn = DriverManager.getConnection(url,user,password);} catch (Exception e) {e.printStackTrace();}return conn;} /** * 依次关闭ResultSet、Statement、Connection * 若对象不存在则创建一个空对象 * @param rs * @param st * @param pst * @param conn */ public void close(ResultSet rs,Statement st,Connection conn){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally{ if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } finally{ if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } } } public static List<Map<String, Object>> convertList(ResultSet rs) { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); try { ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); while (rs.next()) { Map<String, Object> rowData = new HashMap<String, Object>(); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if (rs != null) rs.close(); rs = null; } catch (SQLException e) { e.printStackTrace(); } } return list; } public static Map<String, Object> convertMap(ResultSet rs){ Map<String, Object> map = new TreeMap<String, Object>(); try{ ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { map.put(md.getColumnName(i), rs.getObject(i)); } } } catch (SQLException e){ e.printStackTrace(); } finally { try { if (rs != null) rs.close(); rs = null; } catch (SQLException e) { e.printStackTrace(); } } return map; } /** * 新增、修改、删除、查询记录(也可以改为有结果集ResultSet返回的查询方法) * @param sql * @throws */ public static Boolean execute(String sql){ JdbcByPropertiesUtil jbpu = getInstance(); Connection conn = null; PreparedStatement pst = null; try { conn = jbpu.getConnection(); conn.setAutoCommit(false); pst = conn.prepareStatement(sql); pst.execute(); conn.commit(); } catch (Exception e) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } return false; } finally{ //Statement st = null; ResultSet rs = null; jbpu.close(rs, pst, conn); }return true; } public static JSONObject executeAll(String [] fields,JSONArray array,String table,String type,String id ) { JSONObject json = new JSONObject(); JdbcByPropertiesUtil jbpu = getInstance(); Connection conn = null; PreparedStatement pst = null; try { conn = jbpu.getConnection(); conn.setAutoCommit(false); for (Object object : array) {JSONObject obj = (JSONObject) object;String str1 = "";String str2 = "";for (int i = 0; i < fields.length; i++) {str1 = str1 +fields[i]+",";if(obj.get(fields[i]).toString().equals("null")){str2 = str2 +obj.get(fields[i])+",";}else{str2 = str2 +"'"+obj.get(fields[i])+"'"+",";}}if(type.equals("2")){String sql2 = "delete from "+table+" where "+id+" = '"+obj.get(id)+"'";System.out.println(sql2);conn.prepareStatement(sql2).execute();}String sql = "insert into "+table+" ("+str1.substring(0,str1.length()-1)+") values ("+str2.substring(0,str2.length()-1)+")";System.out.println(sql);conn.prepareStatement(sql).execute();} conn.commit(); } catch (Exception e) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } json.put("mes",e.getMessage()); json.put("merge", false); return json; } finally{ //Statement st = null; ResultSet rs = null; jbpu.close(rs, pst, conn); } json.put("merge", true); json.put("mes", "保存成功");return json; } public static List<Map<String, Object>> executeByList(String sql){ JdbcByPropertiesUtil jbpu = getInstance(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; List<Map<String, Object>> list = null; try { conn = jbpu.getConnection(); conn.setAutoCommit(false); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); list = JdbcByPropertiesUtil.convertList(rs); } catch (Exception e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } return list; } finally{ //Statement st = null; jbpu.close(rs, pst, conn); } return list; } public static Map<String,Object> executeByChar(String sql){ JdbcByPropertiesUtil jbpu = getInstance(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; List<Map<String, Object>> list = null; Map<String,Object> map = new HashMap<String, Object>(); try { conn = jbpu.getConnection(); conn.setAutoCommit(false); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); list = JdbcByPropertiesUtil.convertList(rs); map.put("data", list); } catch (Exception e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } return map; } finally{ //Statement st = null; jbpu.close(rs, pst, conn); } return map; } public static String executeByString(String sql){ JdbcByPropertiesUtil jbpu = getInstance(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String str = ""; try { conn = jbpu.getConnection(); conn.setAutoCommit(false); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); while (rs.next()) { str=rs.getString(1); } } catch (Exception e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } return str; } finally{ //Statement st = null; jbpu.close(rs, pst, conn); } return str; } //测试方法 public static void main(String[] args) {List<Map<String, Object>> list = JdbcByPropertiesUtil.executeByList("select TRANS_month,sum(TRD_AMT) TRD_AMT,sum(NET_COMM) NET_COMM,sum(MARKET_RATE) MARKET_RATE,sum(NET_COMM_RATE) NET_COMM_RATE,sum(CUST_NUM),sum(CUST_ASSET) CUST_ASSET,sum(TRD_ASSET) TRD_ASSET,sum(NEW_CUST_NUM) NEW_CUST_NUM,sum(NEW_CUST_ASSET) NEW_CUST_ASSET,sum(TRD_CUST_NUM) TRD_CUST_NUM,sum(TRD_CUST_ASSET) TRD_CUST_ASSET,sum(FI_CUST_NUM) FI_CUST_NUM,sum(FI_CUST_ASSET) FI_CUST_ASSET,sum(HKT_CUST_NUM) HKT_CUST_NUM,sum(HKT_CUST_TRD_AMT) HKT_CUST_TRD_AMT,sum(HKT_CUST_ASSET) HKT_CUST_ASSET,sum(SO_CUST_NUM) SO_CUST_NUM,sum(SO_CUST_ASSET) SO_CUST_ASSET,sum(SO_CUST_TRD_AMT) SO_CUST_TRD_AMT,sum(IA_NUM) IA_NUM,sum(IA_CUST_NUM) IA_CUST_NUM,sum(IA_ASSET) IA_ASSET,sum(CM_NUM) CM_NUM,sum(CM_CUST_NUM) CM_CUST_NUM,sum(CM_ASSET) CM_ASSET,sum(BROKER_NUM) BROKER_NUM,sum(BROKER_CUST_NUM) BROKER_CUST_NUM,sum(BROKER_ASSET) BROKER_ASSET from RPT_BD_CHART where TRANS_month >=201501 and TRANS_month<=201512 group by TRANS_month");System.out.println(list.size());}}阅读全文
0 0
- jdbc入门、jdbc连接数据库
- jdbc-odbc连接数据库
- JDBC连接MySQL数据库
- JDBC连接MySQL数据库
- JDBC之连接数据库
- [JDBC] 如何连接数据库
- JDBC连接数据库技巧
- jdbc连接数据库
- JDBC连接常用数据库
- jdbc连接数据库
- JDBC连接SqlServer2005数据库
- jdbc连接各种数据库
- JDBC连接各种数据库
- JDBC连接数据库参考
- JDBC连接数据库
- JDBC连接数据库小结
- JDBC连接MYSQL数据库
- Jdbc 连接数据库 实例
- Java-垃圾收集器
- HDU 5761 Rower Bo
- 前端后端交互的方法
- windows版solr安装
- 不同信道带宽所对应的子载波数的解释
- JDBC连接数据库
- es6学习
- 下班后两小时，决定你将会成为怎样的人
- 《C++ Primer》读书笔记第一章
- Linux逻辑卷扩展
- Spring MVC 使用拦截器优雅地实现权限验证功能
- 智能指针和万能指针
- Tweaking
- nodejs学习笔记二 hello world
