JDBC连接数据库

平底锅3号
于 2017-09-05 09:12:05 发布
阅读量193
点赞数
分类专栏： java 文章标签： JDBC
本文链接：https://blog.csdn.net/guzengpan/article/details/77847347
版权
java 专栏收录该内容
4 篇文章 0 订阅
订阅专栏
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());
	}
}