warehouse-edi/src/main/java/com/uas/util/JdbcUtil.java

package com.uas.util;

import org.codehaus.jackson.map.ObjectMapper;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class JdbcUtil {
	public static Map<String,Object> ftpConfigs;
	public static Map<String,Object> dbConfigs;
	public static Connection connection;	
	
	/**
	 * 从ftpconfigs配置文件中获取ftp站点配置信息
	 * @return Map<String,Object>
	 */
	@SuppressWarnings("unchecked")
	public static Map<String,Object> getFtpConfigs(){
		if(ftpConfigs==null){
			try {
				ftpConfigs = new ObjectMapper().readValue(JdbcUtil.class.getResourceAsStream("/properties/ftpconfig.properties"), HashMap.class);
			}catch (Exception e) {
				BaseUtil.getLogger().error(e.toString());
				e.printStackTrace();
			}			
		}
		return ftpConfigs;
	}
	
	/**
	 * 从dbconfig配置文件中获取数据库配置信息
	 * @return Map<String,Object>
	 */
	@SuppressWarnings("unchecked")
	public static Map<String,Object> getDBConfigs(){
		if(dbConfigs==null){
			try {
				dbConfigs = new ObjectMapper().readValue(JdbcUtil.class.getResourceAsStream("/properties/dbconfig.properties"), HashMap.class);
			}catch (Exception e) {
				BaseUtil.getLogger().error(e.toString());
				e.printStackTrace();
			}			
		}
		return dbConfigs;
	}
	
	/**
	 * 连接到数据库
	 * @param url 地址
	 * @param user 用户名
	 * @param password 密码
	 * @return 返回一个连接
	 * @throws Exception
	 */
	public static Connection getConnect(String url,String user,String password) throws Exception{
        Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
        connection = DriverManager.getConnection(url, user, password);// 获取连接
        return connection;	       
	}
	
	/**
	 * 从dbconfig配置文件中获取数据库连接
	 * @return 返回数据库连接
	 */
	public static Connection getConnect(){
		if(connection==null){
		    try{
				InputStream dbinput = JdbcUtil.class.getResourceAsStream("/properties/dbconfig.properties");
				Properties prop = new Properties();
				prop.load(dbinput);
				connection = JdbcUtil.getConnect(prop.getProperty("url"), prop.getProperty("user"), prop.getProperty("password"));
		    }catch (Exception e){
		    	try {
					System.in.read();
				} catch (IOException e2) {
					// TODO Auto-generated catch block
					e2.printStackTrace();
				}
		    	BaseUtil.getLogger().error(e.toString());
		    	try {
		    		if(connection!=null){
		    			connection.close();
		    		}				
				} catch (SQLException e1) {
					BaseUtil.getLogger().error(e1.toString());
					e1.printStackTrace();
				}finally{
					connection = null;
				}
		        e.printStackTrace();
		    } 			
		}
		return connection;
	}
	
	/**
	 * 从dbconfig配置文件中获取数据库连接
	 * @return 返回数据库连接
	 */
	@SuppressWarnings("unchecked")
	public static Connection getConnectBySob(String sob){
		Map<String,Object> dbServMaps = getDBConfigs();
		Map<String,Object> dbServMap = (Map<String,Object>)dbServMaps.get(sob);
		//先把当前的连接关闭
		try {
			if(connection!=null){
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			connection = null;
		}
		
	    try{
			connection = JdbcUtil.getConnect(dbServMap.get("url").toString(), dbServMap.get("user").toString(), dbServMap.get("password").toString());
	    }catch (Exception e){
	    	try {
				System.in.read();
			} catch (IOException e2) {
				// TODO Auto-generated catch block
				e2.printStackTrace();
			}
	    	BaseUtil.getLogger().error(e.toString());
	    	try {
	    		if(connection!=null){
	    			connection.close();
	    		}				
			} catch (SQLException e1) {
				BaseUtil.getLogger().error(e1.toString());
				e1.printStackTrace();
			}finally{
				connection = null;
			}
	        e.printStackTrace();
	    } 
		
		return connection;
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @param pre
	 * @param result
	 * @return
	 */
	public static boolean closeCn(Connection con,PreparedStatement pre,ResultSet result){
        try{
            if (result != null) {
				result.close();
				result = null;
			}
            if (pre != null) {
				pre.close();
				pre = null;
			}
            if (con != null) {
				con.close();
			}
        }
        catch (Exception e){
        	BaseUtil.getLogger().error(e.toString());
            e.printStackTrace();
        }
		return true;
	}
	
	/**
	 * 执行多条sql语句
	 * @param connection 连接
	 * @param str 多条sqls语句
	 * @return
	 */
	@SuppressWarnings("finally")
	public static boolean executeSqls(Connection connection,List<String> str){
		Statement statement = null;
		boolean bol = true;
		try{		
			statement = connection.createStatement();		
			for(String sql:str){
				statement.addBatch(sql);
			}
			try {
				statement.setQueryTimeout(180);
				statement.executeBatch();
			} catch (SQLException e) {
				bol = false;
				BaseUtil.getLogger().error(e.toString());
				try {
					statement.close();
				} catch (SQLException e1) {
					BaseUtil.getLogger().error(e1.toString());
					e1.printStackTrace();
				}
				e.printStackTrace();
			}
			statement.close();
		}catch(Exception e){
			bol = false;
			BaseUtil.getLogger().error(e.toString());
			try {
				if(connection!=null){
					connection.rollback();
				}				
			} catch (SQLException e1) {
				BaseUtil.getLogger().error(e1.toString());
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			statement = null;
			return bol;
		}
	}
	
	@SuppressWarnings("finally")
	public static int getIdBySeq(String seq){
		connection = getConnect();
		Statement statement = null;
		int id = 0;
		if(seq!=null&!"".equals(seq)){
			try{			
				statement = connection.createStatement();		
				ResultSet rs = statement.executeQuery("select " + seq + ".nextval id from dual");
				if(rs.next()){
					id = rs.getInt("id");
				}
				rs.close();
				statement.close();
			}catch(Exception e){
				BaseUtil.getLogger().error(e.toString());
				try {
					if(connection!=null){
						connection.rollback();
					}				
				} catch (SQLException e1) {
					BaseUtil.getLogger().error(e1.toString());
					e1.printStackTrace();
				}
				e.printStackTrace();
			}finally{
				statement = null;
				return id;
			}			
		}
		return id;
	}
}