Apache DbUtils使用

　　Apache组织的DbUtils工具简单封装了JDBC，在不影响性能的前提下，简化了jdbc编码的工作量。
　　更新函数：int update(String sql, Object[] params)。
　　查询函数：Object query(String sql, ResultSetHandler rsh, Object[] params)
　　具体用法如下：
　　当然，第一步要先添加jar包，Download Apache Commons DbUtils，还是老样子（下载编译好的二进制文件包解压后将commons-dbutils-1.6.jar复制到 WebRoot–>WEB-INF–>lib文件夹中），查询示例如下：

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

        String sql = "select * from test";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);

            //commons-dbutils工具
            QueryRunner qr = new QueryRunner();
            //使用MapListHandle存放数据
            MapListHandler mapListHandler = new MapListHandler();
            List list = (List) qr.query(conn, sql, mapListHandler);
            for (int i = 0; i < list.size(); i++)
            {
                Map map = (Map) list.get(i);
                System.out.println(map.get("user"));
                System.out.println(map.get("pwd"));
                out.print(map.get("user"));
                out.print(map.get("pwd"));
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally
        {
            try
            {
                if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e)
            {
                // TODO: handle exception
                e.printStackTrace();
            }
        }

　　更新示例如下：

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

        String sql = "insert into test(user,pwd) values(?,?)";

        Connection conn = null;

        try
        {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);

            String param[] = {"test4","test4"};
            //commons-dbutils工具
            QueryRunner qr = new QueryRunner();
            int result = qr.update(conn, sql, param);
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally
        {
            try
            {
                if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e)
            {
                // TODO: handle exception
                e.printStackTrace();
            }
        }

　　而使用tomcat内置数据库连接池时，更新示例如下：

        String sql = "insert into test(user,pwd) values(?,?)";

        try
        {
            Context context = new InitialContext();
            DataSource ds = (DataSource) context.lookup("java:/comp/env/jdbc/mysql");

            String param[] = {"test5","test5"};
            //commons-dbutils工具
            QueryRunner qr = new QueryRunner(ds);
            int result = qr.update(sql, param);
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

查询示例类似，只需更改为：

        String sql = "select * from test";

        try
        {
            Context context = new InitialContext();
            DataSource ds = (DataSource) context.lookup("java:/comp/env/jdbc/mysql");

            //commons-dbutils工具
            QueryRunner qr = new QueryRunner(ds);
            //使用MapListHandle存放数据
            MapListHandler mapListHandler = new MapListHandler();
            List list = (List) qr.query(sql, mapListHandler);
            for (int i = 0; i < list.size(); i++)
            {
                Map map = (Map) list.get(i);
                System.out.println(map.get("user"));
                System.out.println(map.get("pwd"));
                out.print(map.get("user"));
                out.print(map.get("pwd"));
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

参考资料

[1] javaweb学习总结(四十一)——Apache的DBUtils框架学习(http://www.cnblogs.com/xdp-gacl/p/4007225.html)