SQLHelper、DBUtil终极封装

DBUtil.java

package org.guangsoft.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.beanutils.BeanUtils;


/**
 *
 * @author guanghe
 */
public class DBUtil
{
    //定义连接资源
    private static Connection ct = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    //定义配置参数
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    //定义配置文件引入
    private static Properties pp = null;
    private static InputStream is = null;

    //读取配置参数，加载驱动
    static
    {
        try
        {
            pp = new Properties();
            is = DBUtil.class.getClassLoader().getResourceAsStream("org/guangsoft/util/db.properties");
            pp.load(is);
            driver = pp.getProperty("driver");
            url = pp.getProperty("url");
            username = pp.getProperty("username");
            password = pp.getProperty("password");
            Class.forName(driver);
        }
        catch (Exception e)
        {
            e.printStackTrace();
            System.exit(0);
        }
        finally
        {
            try
            {
                is.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            is = null;
        }
    }

    //获取连接
    public static Connection getConnection()
    {
        try
        {
            ct = DriverManager.getConnection(url, username, password);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return ct;
    }

    //执行DQL查询
    public static<T> List<T> executeQuery(String sql, Object[] parameters,Class<T> clazz)
    {
        List<T> list = new ArrayList<T>();
        try
        {
            //得到数据结果集
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            if (parameters != null)
            {
                for (int i = 0; i < parameters.length; i++)
                {
                    ps.setObject(i + 1, parameters[i]);
                }
            }
            rs = ps.executeQuery();
            
            //封装数据
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while(rs.next())
            {
                T t = clazz.newInstance();
                for(int i = 0; i < columnCount; i++)
                {
                    String columnName = metaData.getColumnName(i+1);
                    Object value = rs.getObject(columnName);
                    BeanUtils.copyProperty(t, columnName, value); 
                }
                list.add(t);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            close();
        }
        return list;
    }

    //执行DML更新
    public static int executeUpdate(String sql, Object[] parameters)
    {
        try
        {
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            if (parameters != null)
            {
                for (int i = 0; i < parameters.length; i++)
                {
                    ps.setObject(i + 1, parameters[i]);
                }
            }
            return ps.executeUpdate();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            close();
        }
        return 0;
    }
    
    //关闭所有资源连接
    public static void close()
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            rs = null;
        }
        if (ps != null)
        {
            try
            {
                ps.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            ps = null;
        }
        if (null != ct)
        {
            try
            {
                ct.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            ct = null;
        }
    }
    
}

db.properties

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/test
username = root
password =root