罗锐原数据分享

1.工具类DBUtil

package com.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
	private static Properties p=new Properties();
	static{
		try {
    		p.load(DBUtil.class.getResourceAsStream("/db.properties"));
        	Class.forName(p.getProperty("smartcloud.menu.lifery.driver"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    public static Connection getConnection() throws SQLException{
    	 Connection conn =null;
    	try {
        	String url=p.getProperty("smartcloud.menu.lifery.url");
        	String name=p.getProperty("smartcloud.menu.lifery.username");
        	String pwd=p.getProperty("smartcloud.menu.lifery.password");
        	conn=DriverManager.getConnection(url,name,pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
        return conn;
    }
    
    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();
			}
    	}
    }

}

2.配置文件db.properties

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

3.调用实例

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;
}