封装java访问数据库的代码

[/code]package com.yc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yc.sun.Log;

public class DBHleper {
	private static Connection con=null;
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	//加载驱动
	static {


			try {
				Class.forName(Mypro.getInstance().getProperty("sql.driver"));
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

	}

	//获得连接
	public Connection getConnection(){
		try {
			con=DriverManager.getConnection(Mypro.getInstance().getProperty("sql.url"),
					Mypro.getInstance().getProperty("sql.username"),Mypro.getInstance().getProperty("sql.password"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;

	}
	//关闭的方法
	public void close(Connection con,PreparedStatement pstmt,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {				
				Log.log.info(toString());
			}
		}
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				Log.log.info(toString());
			}
		}
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				Log.log.info(toString());
			}
		}





	}


	//设置？的参数
	public void setValues(PreparedStatement pstmt,List<String> params){
		if(pstmt!=null&&params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				try {
					pstmt.setString(i+1, params.get(i));
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}


	//更新的方法
	public int update(String sql,List<String> params){
		int r=0;
		getConnection();
		try {
			pstmt=con.prepareStatement(sql);
			this.setValues(pstmt, params);
			r=pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close(con, pstmt, rs);
		}
		return r;

	}
	//求只有一个值的聚合函数的值
	public String uniqueResult(String sql,List<String> params){

		String result="";
		try {
			con=this.getConnection();
			pstmt=con.prepareStatement(sql);
			this.setValues(pstmt, params);
			rs=pstmt.executeQuery();
			if(rs.next()){
				result=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close(con, pstmt, rs);
		}
		return result;

	}

	//求多个聚合的值
	public List<String> Results(String sql,List<String>params){
		List<String> result=new ArrayList<String>();

		try {
			con=this.getConnection();
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			int mdc=rs.getMetaData().getColumnCount();
			//System.out.println(mdc);
			while(rs.next()){
				for(int i=0;i<mdc;i++){
				result.add(rs.getString(i+1));
				//i++;
				}
			}
		} catch (SQLException e) {
			Log.log.error(e.getMessage());
		}
		return result;
	}

/**
	 * 查询单表的数据，返回一个List类型的集合
	 * 用到的技术：反射，泛型
	 * @param <T>泛型，即你要得到的集合中存的对象的类型
	 * @param sql ：查询语句，可以包含？
	 * @param params ：?所对应的参数值集合
	 * @param c ：泛型类型所对应的反射对象 Product =》 Class.forname(XXX.XXX)
	 * @return	:返回存储了对象的集合  如果查询product表则返回List<Product>
	 */

	public <T> List<T> findSignalTables(String sql ,List<String>params,Class<T> c){
		List<T> list=new ArrayList<T>();
		con=this.getCon();			//获取连接
		try {
			pstmt=con.prepareStatement(sql);  //语句对象
			this.setValues(pstmt, params);	//设置占位符
			ResultSet rs=pstmt.executeQuery();
			//获取方法名
			Method[] ms=c.getMethods();

			//获取数据中表的列名
			ResultSetMetaData rsmd=rs.getMetaData();
			String[] columnames=new String[rsmd.getColumnCount()];
			for(int i=0 ;i<columnames.length;i++){
				columnames[i]=rsmd.getColumnName(i+1);
			}
			while(rs.next()){
				T t=(T)c.newInstance();
				for(Method m:ms){
					String mname=m.getName();
					for(int i=0;i<columnames.length;i++){
						String cname=columnames[i];
						if(mname.equalsIgnoreCase("set"+cname)){
							String ctypename=rs.getObject(columnames[i]).getClass().getName();//得到列的数据类型
						//	System.out.println(ctypename);
							//进行数据匹配
							if("java.lang.Integer".equals(ctypename)){
								m.invoke(t, rs.getInt(columnames[i]));
							}else if("java.lang.String".equals(ctypename)){
								m.invoke(t, rs.getString(columnames[i]));
							}else if("java.math.BigDecimal".equals(ctypename)){
								m.invoke(t, rs.getDouble(columnames[i]));
							}//如果还有其他的数据类型直接在这里加代码
							//m.invoke(t, rs.getString(columnames[i]));
						}
					}

				}
				list.add(t);
			}
		} catch (SecurityException e) {
			LogUtil.log.error(e.getMessage());
		} catch (IllegalArgumentException e) {
			LogUtil.log.error(e.getMessage());
		} catch (SQLException e) {
			LogUtil.log.error(e.getMessage());
		} catch (InstantiationException e) {
			LogUtil.log.error(e.getMessage());
		} catch (IllegalAccessException e) {
			LogUtil.log.error(e.getMessage());
		} catch (InvocationTargetException e) {
			LogUtil.log.error(e.getMessage());
		}
		return list;

	}
	/**
	 * 查询多表的数据,先将数据存在一个map中，在将这个map存到list中
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 */
	public List<String> findMutilTable(String sql,List<String> params) throws SQLException{
		Map<String,String> map=new HashMap<String,String>();
		//List<Map<String,String>> list=new ArrayList<Map<String,String>>();
		List<String> list=new ArrayList<String>();
		con=this.getCon();
		pstmt=con.prepareStatement(sql);
		this.setValues(pstmt, params);
		ResultSet rs=pstmt.executeQuery();
		ResultSetMetaData rsmd=rs.getMetaData();
		String[] columname=new String[rsmd.getColumnCount()];
		for(int i=0;i<columname.length;i++){
		columname[i]=rsmd.getColumnName(i+1);
		//System.out.println(columname[i]);
		}
		while(rs.next()){

			for(int i=0;i<columname.length;i++){
				map.put(columname[i], rs.getString(i+1));
				String e=map.get(columname[i]);
				list.add(e);

			}
		}
		return list;
	}









}