封装jdbc、DBUtil

package com.cmos.util;

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

/**
 * 数据库连接工具类
 * 
 * @author Wx
 *
 */
public class JDBCUtil {
    private static Properties ps = new Properties();
    static {
        // 读入配置文件
        InputStream is = JDBCUtil.class.getResourceAsStream("jdbc.properties");
        try {
            ps.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 建立连接方法
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        Connection conn = null;
        if (conn == null) {
            //加载驱动
            Class.forName(ps.getProperty("driver"));
            conn = DriverManager.getConnection(ps.getProperty("url"), ps.getProperty("username"),
                    ps.getProperty("password"));
            return conn;
        } else {
            return conn;
        }
    }

    /**
     * 关闭连接
     * @param conn
     * @param pre
     * @param rs
     */
    public static void close(Connection conn, PreparedStatement pre, ResultSet rs) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e2) {
                // TODO Auto-generated catch block
                e2.printStackTrace();
            }
        if (pre != null)
            try {
                pre.close();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

    }
    /**
     * 关闭连接方法
     * @param conn
     * @param pre
     */
    public static void close(Connection conn, PreparedStatement pre) {
        if (pre != null)
            try {
                pre.close();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

    }

}

 

JDBC-util 封装了jdbc并封装dbutil方法，查询直接返回List<map<String,Object>>，将一大堆代码放在一块是真的不好看

代码如下：

　　

　　

　　

DButil

package com.cmos.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {
	private static Connection conn = null;
	private static PreparedStatement pstmt = null;
	private static ResultSet rs = null;
	
	/**
	 * 增删改操作
	 * @param sql
	 * @param paramsValue
	 * @return
	 */
	public static int execute(String sql, Object[] paramsValue) {
		int result = 0;
		try {
			// 1. 数据库连接
			conn = JDBCUtil.getConnection();
			// 2. 获取PreparedStatement
			pstmt = conn.prepareStatement(sql);
			// 3. 得到参数元数据个数
			int count = pstmt.getParameterMetaData().getParameterCount();
			// 4. 利用参数元数据给SQL语句的占位符需要的参数赋值
			if (paramsValue != null && paramsValue.length > 0) {
				for (int i = 0; i < count; i++) {
					// 循环结束，可以给SQL语句完整赋值
					pstmt.setObject(i + 1, paramsValue[i]);
				}
			}
			// 5. 执行
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(conn, pstmt);
		}
		return result;
	}

	/**
	 * 查询操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String, Object>> executeQuery(String sql, Object[] params) {
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			// 设置参数
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			rs = pstmt.executeQuery();
			// 得到ResultSetMetaData对象，这个对象包括列的信息(列的名称和类型等等)
			ResultSetMetaData metaData = rs.getMetaData();
			// 得到结果集中列的个数
			int columnCount = metaData.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					// 得到指定的列名的别名
					String name = metaData.getColumnLabel(i);
					Object value = rs.getObject(i);
					map.put(name, value);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, pstmt, rs);
		}
		return list;
	}

}