JDBC数据库操作封装(PreparedStatement)

之前有用过老师给的实现好的JDBC封装类，今天自己看着书按自己思路实现了一遍，并将Statement改为了PreparedStatement。

代码

import com.sun.tools.javac.util.List;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;

/**
 * Created by shiyi on 16/9/18.
 */
public class DBUtil {

    private static DBUtil dbUtil = null;
    private String dbDriver;
    private String dbUrl;
    private String dbUser;
    private String dbPass;
    private Connection conn;
    private PreparedStatement stmt;
    private ResultSet rst;

    private DBUtil(){}

    public static DBUtil getInstance() {
        if(dbUtil == null) {
            dbUtil = new DBUtil();
            dbUtil.openConnection();
        }
        return dbUtil;
    }

    //从配置文件获取信息
    private void loadConnProperties() {
        ResourceBundle rb = ResourceBundle.getBundle("database");
        dbDriver = rb.getString("driver");
        dbUrl = rb.getString("url");
        dbUser = rb.getString("user");
        dbPass = rb.getString("pass");
    }

    //获取数据库连接对象
    private void openConnection() {
        loadConnProperties();

        try {
            //加载driver接口,完成注册
            Class.forName(dbDriver);
            conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //执行sql命令
    public int execCommand(String sql)
    {
        int flag = 0;
        try {
            stmt = conn.prepareStatement(sql);
            flag = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    //插入新纪录,并获取标识列的值
    public int execUpdate(String sql, Object[] data) {

        int flag = 0;

        try {
            if(conn == null)
                throw new Exception("Database not connected!");

            stmt = conn.prepareStatement(sql);
            for(int i=0; i<data.length; i++)
            {
                stmt.setObject(i+1, data[i]);
            }
            flag = stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return flag;
    }

    //查询并返回结果集
    public ResultSet execQuery(String sql, Object[] data)
    {
        try {
            stmt = conn.prepareStatement(sql);
            for(int i=0; i<data.length; i++)
            {
                stmt.setObject(i+1, data[i]);
            }
            rst = stmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rst;
    }

    //查询并以链表形式返回结果
    public ArrayList<Object> execQueryList(String sql, Object[] data)
    {
        int colCount = 0;
        ResultSetMetaData rstmd = null;

        rst = execQuery(sql, data);

        try {
            rstmd = rst.getMetaData();
            colCount = rstmd.getColumnCount();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        ArrayList<Object> list = new ArrayList<Object>();
        try {
            while(rst.next())
            {
                Map<String, Object> map = new HashMap<String, Object>();
                for(int i=1; i<=colCount; i++)
                {
                    /*
                        getColumnLabel获取用于打印输出和显示的指定列的建议标
                        getColumnName获取指定列的名称
                        select的结果集返回的是Label
                        所以此处只能用Label而不能用Name
                    */
                    map.put(rstmd.getColumnLabel(i), rst.getObject(i));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                rst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public static void main(String argc[])
    {
        DBUtil db = DBUtil.getInstance();
        Object[] data = {};
        db.execCommand("create table temp(id int, name char(100))");
        db.execUpdate("insert into temp values(?, ?)", new Object[]{1, "aaa"});
        db.execUpdate("insert into temp values(?, ?)", new Object[]{2, "bbb"});
        db.execUpdate("insert into temp values(?, ?)", new Object[]{3, "ccc"});
        db.execUpdate("insert into temp values(?, ?)", new Object[]{4, "ddd"});

        ArrayList<Object> list = db.execQueryList("select * from temp where id > ? and id < ?", new Object[]{1, 4});
        for(Object it : list)
        {
            Map<String, Object> map = (Map<String, Object>) it;
            System.out.println(map.get("id") + " -- " + map.get("name"));
        }
    }
}

测试结果

2 – bbb
3 – ccc

注意点

1. 在从ResultSetMetaDate获取列名时，要使用getColumnLabel，而不能用getColumnName:

原因在于：
getColumnLabel获取用于打印输出和显示的指定列的建议标
getColumnName获取指定列的名称
而select的结果集返回的是Label
所以此处只能用Label而不能用Name

2. 在使用ResultSet之前不能关闭Statement，否则会出现SqlException：

java.sql.SQLException: Operation not allowed after ResultSet closed