java 查询封装树状结构 java封装数据库

数据库连接是件很麻烦的事，特别是封装数据库，让我们java编程不再有烦恼

代码如下：

import java.sql.Connection;

public class JDBC {
	private final String drive="com.mysql.jdbc.Driver";
	private final String url="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8";
	private final String name="root";
	private final String password="123456";
	private Connection con=null;
	
	public JDBC(){//构造方法加载数据库驱动		
		try {
			Class.forName(drive).newInstance();
		} catch (Exception e) {		
			System.out.println("数据库加载失败！");			
		}		
	}	
	public boolean creatConnection(){//创建数据库连接
		
		try {
			con=DriverManager.getConnection(url,name,password);
			con.setAutoCommit(true);
		} catch (SQLException e) {
			
		}
		return true;
	}
	public boolean executeUpdate(String sql){//对数据表的增加，修改和删除的操作
		if(con==null){
			creatConnection();		
		}
		try{
			Statement s=con.createStatement();
			int i=s.executeUpdate(sql);
			System.out.println("操作成功，所影响的记录数为："+String.valueOf(i));
			return true;
		}catch(Exception e){
			return false;
		}
		
	}	
	
	
	
		
	public ResultSet executeQuery(String sql){//数据库的查询操作
		ResultSet rs;
		try{
			if(con==null){
			creatConnection();		
			}
			Statement s=con.createStatement();
			 rs=s.executeQuery(sql);		
		 	return rs;
		}catch(Exception e){
			return null;		
		}	
			
	}


	public void closeConnection(){//关闭数据库连接
		if(con==null){		
			try {
				con.close();
			}catch (SQLException e) {
			}		
		}
	}	
	
basedao封装

import java.lang.reflect.Field;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 基础DAO类
 * @author LYF
 */
public class BaseDao {
	
	/**
	 * 连接数据库的URL
	 */
	private static String url;

	/**
	 * 登录数据库用户名
	 */
	private static String user;

	/**
	 * 登录数据库密码
	 */
	private static String password;

	/**
	 * 所有线程共享的线程容器
	 */
	private static ThreadLocal<Map<String, Object>> threadLocal = null;

	//静态块中实例化线程容器并装载数据库驱动
	static {
		init();
	}
	
	private static void init(){
		threadLocal = new ThreadLocal<Map<String, Object>>();
		try {
			Properties pro=new Properties();
			pro.load(ClassLoader.getSystemResource("db.properties").openStream());
			user=pro.getProperty("user");
			password=pro.getProperty("password");
			url=pro.getProperty("url");
			Class.forName(pro.getProperty("driver"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取连接对象Connection
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		//从线程中取出保存数据
		Map<String, Object> threadMap = threadLocal.get();
		if (null == threadMap) {
			threadMap = new HashMap<String, Object>();
			threadLocal.set(threadMap);
		}
		Connection conn = (Connection) threadMap.get("conn");
		if (null == conn || conn.isClosed()) {
			conn = DriverManager.getConnection(url, user, password);
			threadMap.put("conn", conn);
		}
		return conn;
	}

	/**
	 * 泛型方法(执行查询DQL)
	 * @param <T>
	 * @param cla
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> executeQuery(Class<T> cla, String sql, Object... params)
			throws SQLException {
		//获取连接对象
		Connection conn = this.getConnection();
		//创建预编译语句对象
		PreparedStatement pstat = conn.prepareStatement(sql);
		// 将产生的语句对象放置到线程中
		PreparedStatement oldpstat = (PreparedStatement) threadLocal.get().put("pstat", pstat);
		//如果线程中有旧的语句对象则关闭它
		if (null != oldpstat)oldpstat.close();
		// 设置预编译占位符参数(参数从1开始)
		for (int i = 0; i < params.length; i++) {
			pstat.setObject(i + 1, params[i]);
		}
		
		List<T> list = new ArrayList<T>();
		//执行查询返回结果集
		ResultSet res = pstat.executeQuery();
		//将结果集放置到线程中
		ResultSet oldRes = (ResultSet) threadLocal.get().put("res", res);
		//如果线程中有旧的结果集则关闭它
		if (null != oldRes)oldRes.close();
		
		//获取结果集元数据
		ResultSetMetaData rsmd=res.getMetaData();
		//返回结果集中字段数量(列的数量)
		int colNum=rsmd.getColumnCount();
		try {
			T t = null;
			while (res.next()) {//遍历结果集中的所有记录
				t = cla.newInstance();//每循环一次生成一个实体对象
				for(int i=0;i<colNum;i++){//循环记录中的每个字段
					//取出字段的名称
					String fieldName=rsmd.getColumnLabel(i+1);
					//取出字段的值
					Object object=res.getObject(fieldName);
					//获取实体类的字段
					Field field=cla.getDeclaredField(fieldName);
					//打开private权限的字段访问权限
					field.setAccessible(true);
					//设置实体类对象字段属性值
					field.set(t, object);
				}
				list.add(t);//将实体对象添加的返回列表中
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 执行增删改(DML)
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeUpdate(String sql,Object... params) throws SQLException{
		Connection conn = this.getConnection();
		PreparedStatement pstat = conn.prepareStatement(sql);
		PreparedStatement oldpstat = (PreparedStatement) threadLocal.get().put("pstat", pstat);
		if (null != oldpstat)oldpstat.close();
		for (int i = 0; i < params.length; i++) {
			pstat.setObject(i + 1, params[i]);
		}
		//执行DML返回影响行数
		return pstat.executeUpdate();
	}
	
	/**
	 * 执行增加(insert)并且返回主键
	 * @param sql
	 * @param params
	 * @return
	 */
	public int[] executeInsert(String sql,Object... params) throws SQLException{
		Connection conn = this.getConnection();
		PreparedStatement pstat = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
		PreparedStatement oldpstat = (PreparedStatement) threadLocal.get().put("pstat", pstat);
		if (null != oldpstat)oldpstat.close();
		for (int i = 0; i < params.length; i++) {
			pstat.setObject(i + 1, params[i]);
		}
		//返回影响行数(就是插入的记录数量)
		int count=pstat.executeUpdate();
		//定义保存数据库生成的主键的数组
		int[] keys=new int[count];
		//获取主键结果集
		ResultSet res=pstat.getGeneratedKeys();
		//将结果集放置到当前线程中
		ResultSet oldRes = (ResultSet) threadLocal.get().put("res", res);
		if(null!=oldRes)oldRes.close();
		//遍历结果集,将结果集合中的所有主键取出来放到数组中
		for(int i=0;res.next();i++){
			keys[i]=res.getInt(1);
		}
		return keys;
	}
	
	/**
	 * 关闭所有的连接(从线程中取出所有JDBC对象并关闭它们)
	 * @throws SQLException
	 */
	public void closeAll() throws SQLException{
		Map<String,Object> map=threadLocal.get();
		Connection conn=(Connection)map.get("conn");
		PreparedStatement pstat=(PreparedStatement)map.get("pstat");
		ResultSet res=(ResultSet)map.get("res");
		if(null!=res)res.close();
		if(null!=pstat)pstat.close();
		if(null!=conn)conn.close();
	}
}