javaEE jdbc获取数据1（封装到对象中）

1、jdbc获取数据1（封装到对象中）

package aTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

import com.ImpStr;
/**
 * 通用sql使用
 */
public class ImpSql4 {
    private static String clssName = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@*******:1521:orcl";
    private static String user = "*****";
    private static String password = "12345678";
    
    
    /**
     * 获取查询数据, 返回ArrayList<HashMap<String colName, String Str>> ，数据类型是object
     * @param sql 传入sql语句
     * @return
     * @throws Exception
     */
    public static synchronized ArrayList<DataStr> getStrMapList(final String sql){
        ArrayList<DataStr> reslutList = new ArrayList<DataStr>();
        Connection Conn = null;
        Statement Stmt = null;
//        System.out.println("\n/*****开始执行时间------------>" + CTools.getCurrentTimeString() + "************/");
        try {
          Class.forName(clssName);//加入oracle的驱动，“”里面是驱动的路径
          Conn = DriverManager.getConnection(url,user,password);
//           Conn = CDBManager.getConn();
            Stmt = Conn.createStatement();
            ResultSet rs = Stmt.executeQuery(sql);
            ResultSetMetaData RsMeta = rs.getMetaData();              
            int numberOfColumns = RsMeta.getColumnCount();//字段个数
            reslutList = getDataStr(rs, numberOfColumns);
        } catch (Exception e) {
            System.out.println("ImpSql.doSqlReturnMap(sql):sql------->" + sql);
            e.printStackTrace();
        } finally {
            if (Stmt != null) { try {Stmt.close();} catch (Exception e) {};}
            if (Conn != null) { try {Conn.close();} catch (Exception e) {};}
            
//            System.out.println("/*****结束时间------------>"    + CTools.getCurrentTimeString() + "************/");
        }
        return reslutList;
    }
    
    private static ArrayList<DataStr> getDataStr(ResultSet rs, int numberOfColumns) {
    	ArrayList<DataStr> reslutList = new ArrayList<DataStr>();
    	try{
    		int rownum = 1;
//    		HashMap<String, String> mapObj = new HashMap<String, String>();
//            for (int i = 1; i <= numberOfColumns; i++) {
              String columnType = RsMeta.getColumnTypeName(i);//get coltype ; like "DATE"
//                String colName = RsMeta.getColumnName(i);
//                Object colVObject = rs.getObject(i);
//                String colValue = ImpStr.getStrFromObject(colVObject);
//                mapObj.put(colName, colValue);
//            }
//            reslutList.add(mapObj);
            
    		while (rs.next()) {
            	DataStr datas = new DataStr();
            	ArrayList strsList = new ArrayList();
            	for (int i = 1; i <= numberOfColumns; i++) {
                    Object colVObject = rs.getObject(i);
                    String colValue = ImpStr.getStrFromObject(colVObject);
                    strsList.add(colValue);
                }
                datas.setRownum(rownum);
                datas.setStrsList(strsList);
                reslutList.add(datas);
                rownum++;
            }
    	} catch (Exception e) {
    		e.printStackTrace();
    	}    	
		return reslutList;
	}
    
    

	public static void main(String[] args)
    {
		String sql = "select * from t_sys_user where rownum<20";    	
    	ArrayList<DataStr> reslutLis = getStrMapList(sql);
    	for (int i = 0; i < reslutLis.size(); i++){
    		DataStr data = reslutLis.get(i);
        	ArrayList strs = data.getStrsList();
        	System.out.println("data.getRownum()=="+data.getRownum() + "  strs[i]=="+strs.get(0).toString());
    	}
    	
    	
    }
}

package aTest;

import java.util.ArrayList;

/**
 * 获取数据中存储的对象
 * 初始设置为35个参数，也就是最多就是35个列的值
 */
public class DataStr {

	private  ArrayList strsList;
	
	private  int rownum;
	private  String str1;
	private  String str2;
	private  String str3;
	private  String str4;
	private  String str5;
	private  String str6;
	private  String str7;
	private  String str8;
	private  String str9;
	private  String str10;
	private  String str11;
	private  String str12;
	private  String str13;
	private  String str14;
	private  String str15;
	private  String str16;
	private  String str17;
	private  String str18;
	private  String str19;
	private  String str20;
	private  String str21;
	private  String str22;
	private  String str23;
	private  String str24;
	private  String str25;
	private  String str26;
	private  String str27;
	private  String str28;
	private  String str29;
	private  String str30;

	
	
	private  void init() {
		// TODO Auto-generated method stub
		try{
			this.str1 = this.strsList.get(0).toString();
			this.str2 = this.strsList.get(1).toString();
			this.str3 = this.strsList.get(2).toString();
			this.str4 = this.strsList.get(3).toString();
			this.str5 = this.strsList.get(4).toString();
			this.str6 = this.strsList.get(5).toString();
			this.str7 = this.strsList.get(6).toString();
			this.str8 = this.strsList.get(7).toString();
			this.str9 = this.strsList.get(8).toString();
			this.str10 = this.strsList.get(9).toString();
			this.str11 = this.strsList.get(10).toString();
			this.str12 = this.strsList.get(11).toString();
			this.str13 = this.strsList.get(12).toString();
			this.str14 = this.strsList.get(13).toString();
			this.str15 = this.strsList.get(14).toString();
			this.str16 = this.strsList.get(15).toString();
			this.str17 = this.strsList.get(16).toString();
			this.str18 = this.strsList.get(17).toString();
			this.str19 = this.strsList.get(18).toString();
			this.str20 = this.strsList.get(19).toString();
			this.str21 = this.strsList.get(20).toString();
			this.str22 = this.strsList.get(21).toString();
			this.str23 = this.strsList.get(22).toString();
			this.str24 = this.strsList.get(23).toString();
			this.str25 = this.strsList.get(24).toString();
			this.str26 = this.strsList.get(25).toString();
			this.str27 = this.strsList.get(26).toString();
			this.str28 = this.strsList.get(27).toString();
			this.str29 = this.strsList.get(28).toString();
			this.str30 = this.strsList.get(29).toString();
		} catch (Exception e) {
//    		e.printStackTrace();
		}
		
	}
	
	
	public  ArrayList getStrsList() {
		return strsList;
	}


	public  void setStrsList(ArrayList strsList) {
		this.strsList = strsList;
		init();
	}


	public  int getRownum() {
		return rownum;
	}


	public  String getStr1() {
		return str1;
	}


	public  String getStr2() {
		return str2;
	}


	public  String getStr3() {
		return str3;
	}


	public  String getStr4() {
		return str4;
	}


	public  String getStr5() {
		return str5;
	}


	public  String getStr6() {
		return str6;
	}


	public  String getStr7() {
		return str7;
	}


	public  String getStr8() {
		return str8;
	}


	public  String getStr9() {
		return str9;
	}


	public  String getStr10() {
		return str10;
	}


	public  String getStr11() {
		return str11;
	}


	public  String getStr12() {
		return str12;
	}


	public  String getStr13() {
		return str13;
	}


	public  String getStr14() {
		return str14;
	}


	public  String getStr15() {
		return str15;
	}


	public  String getStr16() {
		return str16;
	}


	public  String getStr17() {
		return str17;
	}


	public  String getStr18() {
		return str18;
	}


	public  String getStr19() {
		return str19;
	}


	public  String getStr20() {
		return str20;
	}


	public  String getStr21() {
		return str21;
	}


	public  String getStr22() {
		return str22;
	}


	public  String getStr23() {
		return str23;
	}


	public  String getStr24() {
		return str24;
	}


	public  String getStr25() {
		return str25;
	}


	public  String getStr26() {
		return str26;
	}


	public  String getStr27() {
		return str27;
	}


	public  String getStr28() {
		return str28;
	}


	public  String getStr29() {
		return str29;
	}


	public  String getStr30() {
		return str30;
	}


	public  void setRownum(int rownum) {
		this.rownum = rownum;
	}


	
}

2、封装成hashmap

package aTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.ImpStr;
/**
 * 通用sql使用
 */
public class ImpSql2 {
    private static String clssName = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@**********:1521:orcl";
    private static String user = "*****";
    private static String password = "12345678";
    
    
    /**
     * 获取查询数据, 返回ArrayList<HashMap<String colName, String Str>> ，数据类型是object
     * @param sql 传入sql语句
     * @return
     * @throws Exception
     */
    public static synchronized ArrayList<HashMap<String, String>> getStrMapList(final String sql){
        ArrayList<HashMap<String, String>> reslutList = new ArrayList<HashMap<String,String>>();
        
        Connection Conn = null;
        Statement Stmt = null;
//        System.out.println("\n/*****开始执行时间------------>" + CTools.getCurrentTimeString() + "************/");
        try {
          Class.forName(clssName);//加入oracle的驱动，“”里面是驱动的路径
          Conn = DriverManager.getConnection(url,user,password);
//            Conn = CDBManager.getConn();
            Stmt = Conn.createStatement();
            ResultSet rs = Stmt.executeQuery(sql);
            ResultSetMetaData RsMeta = rs.getMetaData();              
            int numberOfColumns = RsMeta.getColumnCount();//字段个数
            while (rs.next()) {
                HashMap<String, String> mapObj = new HashMap<String, String>();
                for (int i = 1; i <= numberOfColumns; i++) {
//                  String columnType = RsMeta.getColumnTypeName(i);//get coltype ; like "DATE"
                    String colName = RsMeta.getColumnName(i);
                    Object colVObject = rs.getObject(i);
                    String colValue = ImpStr.getStrFromObject(colVObject);
                    mapObj.put(colName, colValue);
                }
                reslutList.add(mapObj);
            }
        } catch (Exception e) {
            System.out.println("ImpSql.doSqlReturnMap(sql):sql------->" + sql);
//            throw new CDealException("执行数据失败。", e);
            e.printStackTrace();
        } finally {
            if (Stmt != null) { try {Stmt.close();} catch (Exception e) {};}
            if (Conn != null) { try {Conn.close();} catch (Exception e) {};}
            
//            System.out.println("/*****结束时间------------>"    + CTools.getCurrentTimeString() + "************/");
        }
        return reslutList;
    }
    
    public static void main(String[] args)
    {
    	String sql = "select sysdate from dual";
    	ArrayList<HashMap<String, String>> reslutLis = getStrMapList(sql);
    	System.out.print(reslutLis.size());
    }
}