罗锐原数据分享

1.需要JAR包commons-dbcp、commons-pool

2.链接池工具类DBUti

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class DBUti {
	private static BasicDataSource pool;
    static{
        try {
            Properties p = new Properties();
            p.load(DBUtil.class.getResourceAsStream("/db.properties"));
            pool = new BasicDataSource();
            pool.setUsername(p.getProperty("smartcloud.menu.lifery.username"));
            pool.setPassword(p.getProperty("smartcloud.menu.lifery.password"));
            pool.setDriverClassName(p.getProperty("smartcloud.menu.lifery.driver"));
            pool.setUrl(p.getProperty("smartcloud.menu.lifery.url"));
            pool.setMaxActive(Integer.parseInt(p.getProperty("smartcloud.maxActive")));
            pool.setInitialSize(Integer.parseInt(p.getProperty("smartcloud.initialSize")));
            pool.setMaxIdle(Integer.parseInt(p.getProperty("smartcloud.maxIdle")));
            pool.setMaxWait(Integer.parseInt(p.getProperty("smartcloud.maxWait")));
            pool.setTimeBetweenEvictionRunsMillis(Long.parseLong(p.getProperty("smartcloud.timeBetweenEvictionRunsMillis")));
            pool.setMinEvictableIdleTimeMillis(Long.parseLong(p.getProperty("smartcloud.minEvictableIdleTimeMillis")));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //返回DataSource--池
    public static DataSource getDataSource(){
        return pool;
    }

    public static Connection getConnection() throws SQLException{
        Connection con =pool.getConnection();
        return con;
    }
    
    public static void close(PreparedStatement ps,ResultSet rs,Connection conn){
    	if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
    	}
    	if(rs!=null){
    		try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
    	}
    	if(ps!=null){
    		try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
    	}
    }

}

3.配置文件db.properties

#连接池配置
smartcloud.initialSize=1
smartcloud.maxActive=10
smartcloud.maxIdle=3
smartcloud.maxWait=10000
smartcloud.timeBetweenEvictionRunsMillis=20000
smartcloud.minEvictableIdleTimeMillis=28000

#menu数据库连接
smartcloud.menu.driver=com.mysql.jdbc.Driver
smartcloud.menu.url=jdbc:mysql://127.0.0.1:3505/scc_burstable?useUnicode=true&characterEncoding=utf8
smartcloud.menu.username=root
smartcloud.menu.password=123456

#liferay数据库连接
smartcloud.menu.lifery.driver=com.mysql.jdbc.Driver
smartcloud.menu.lifery.url=jdbc:mysql://127.0.0.1:3307/liferay_uat?useUnicode=true&characterEncoding=utf8
smartcloud.menu.lifery.username=root
smartcloud.menu.lifery.password=123456

4.调用实例

public Map<String,String> getGidInfo(String emailAddress) throws Exception{
	String sql="SELECT a.userId,a.screenName,c.name orgName,a.emailAddress,IFNULL(d.data_,'') gid,a.password_,a.createDate ,f.companyType FROM user_ a,users_orgs b ,organization_ c,expandovalue d,ExpandoColumn e ,(SELECT a.userId,a.screenName,c.name orgName,a.emailAddress,IFNULL(d.data_,'') companyType,a.password_,a.createDate FROM user_ a,users_orgs b ,organization_ c,expandovalue d,ExpandoColumn e WHERE a.userId=b.userId AND b.organizationId=c.organizationId AND c.organizationId=d.classPK AND d.columnId=e.columnId AND e.name='Company Type'  AND a.companyId=c.companyId) f WHERE a.userId=b.userId AND b.organizationId=c.organizationId AND c.organizationId=d.classPK AND d.columnId=e.columnId AND e.name='Company Gid' AND a.companyId=c.companyId AND a.userId=f.userId AND a.screenName=f.screenName AND c.name=f.orgName AND a.emailAddress=f.emailAddress AND a.password_=f.password_ AND a.createDate=f.createDate AND a.emailAddress=?";
	PreparedStatement ps=null;
	ResultSet rs=null;
	Connection conn=null;
	Map<String,String> map=new HashMap<String,String>();
	try {
		conn=DBUtil.getConnection();
		ps=conn.prepareStatement(sql);
		ps.setString(1, emailAddress);
		rs=ps.executeQuery();
		if(rs.next()){
			map.put("gid", rs.getString("gid"));
			map.put("emailAddress", rs.getString("emailAddress"));
			map.put("companyType", rs.getString("companyType"));
			map.put("userID", rs.getString("userID"));
		}
	} catch (Exception e) {
		throw e;
	}finally{
		DBUtil.close(ps, rs, conn);
	}
	return map;
}