DbUtils的使用

DbUtils的使用

今天在看一段源码的时候，看到了可以使用DbUtils来操作Jdbc

大致研究了DbUtils ，它对Jdbc的操作进行了二次封装，这样我们对数据库的操作就更加方便了，有时间的话大家一定要研究下

http://cuics-100.iteye.com/blog/841207
这篇文章解读了DbUtils的源码 ，其实源码并不是太复杂，大家可以把源码包下载下来看看

http://baike.baidu.com/view/4877026.htm
百度百科

http://commons.apache.org/dbutils/
DbUtils官网


研究了一段 使用 org.apache.commons.dbutils.handlers的代码

package dbutils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;

public class HandlersExample
{
    public static void main(String[] args)
    {
        Connection conn = null;
        String url = "jdbc:sybase:Tds:10.47.181.179:4100?language=us_english";
        String jdbcDriver = "com.sybase.jdbc3.jdbc.SybDriver";
        String user = "sa";
        String password = "";
        DbUtils.loadDriver(jdbcDriver);
        try
        {
            conn = DriverManager.getConnection(url, user, password);
            QueryRunner qr = new QueryRunner();

            // 插入一条数据
            qr.update(conn, "insert into zxinweb..guestbook(id,name) values (5,'name5')");

            // 采用ArrayHandler方式查询单条记录
            System.out.println("....ArrayHandler....");
            Object[] r1 = qr.query(conn, "select id,name  from zxinweb..guestbook where id =5", new ArrayHandler());
            for (int i = 0; i < r1.length; i++)
            {
                System.out.print(r1[i] + " ");
            }
            System.out.println("");
            // 采用ArrayListHandler方式查询 多条记录
            System.out.println("....ArrayListHandler....");
            List r2 = qr.query(conn, "select id,name  from zxinweb..guestbook", new ArrayListHandler());

            for (int i = 0; i < r2.size(); i++)
            {
                Object[] o = (Object[]) r2.get(i);
                for (int x = 0; x < o.length; x++)
                {
                    System.out.print(o[x] + " ");
                }
                System.out.println(" ");
            }

            // 采用BeanHandler方式查询 单条记录
            System.out.println("....BeanHandler....");
            Guestbook r3 = (Guestbook) qr.query(conn, "select id,name  from zxinweb..guestbook", new BeanHandler(
                    Guestbook.class));
            System.out.println(r3);

            // 采用BeanListHandler方式查询
            System.out.println("....BeanListHandler....");
            List<Guestbook> r4 = (List) qr.query(conn, "select id,name  from zxinweb..guestbook", new BeanListHandler(
                    Guestbook.class));

            for (int i = 0; i < r4.size(); i++)
            {
                Guestbook gb = r4.get(i);
                System.out.println(gb);
            }

            // 采用BeanMapHandler方式查询 查询多条
            System.out.println("....BeanMapHandler....");

            ResultSetHandler<Map<Integer, Guestbook>> h5 = new BeanMapHandler(Guestbook.class, "id");
            Map<Integer, Guestbook> r5 = qr.query(conn, "select id,name from zxinweb..guestbook", h5);
            Guestbook g5 = r5.get(1); // jane's id is 1
            Iterator<Entry<Integer, Guestbook>> it5 = r5.entrySet().iterator();
            while (it5.hasNext())
            {
                System.out.println(it5.next());
            }

            // 采用ColumnListHandler方式查询 获取单个字段多条记录
            System.out.println("....ColumnListHandler....");
            List lName = (List) qr.query(conn, "select id,name from zxinweb..guestbook", new ColumnListHandler("name"));
            for (int i = 0; i < lName.size(); i++)
            {
                String name = (String) lName.get(i);
                System.out.println(name);
            }

            // 采用KeyedHandler方式查询 单个字段多条
            System.out.println("....KeyedHandler....");
            ResultSetHandler h7 = new KeyedHandler("id");
            Map map7 = (Map) qr.query(conn, "select id, name  from zxinweb..guestbook", h7);

            Iterator<Entry<Integer, Map<String, String>>> it7 = map7.entrySet().iterator();
            while (it7.hasNext())
            {
                Entry<Integer, Map<String, String>> entry = it7.next();
                System.out.println(entry.getKey() + " " + entry.getValue().get("name"));
            }

            // 采用MapHandler方式查询 单条记录
            System.out.println("....MapHandler....");
            Map map8 = (Map) qr.query(conn, "select id,name  from zxinweb..guestbook where id =5 ", new MapHandler());

            if (map8 != null)
            {
                System.out.println(map8.get("name"));
            }

            // 采用MapListHandler方式查询
            System.out.println("....MapListHandler....");
            List results2 = (List) qr.query(conn, "select id,name  from zxinweb..guestbook", new MapListHandler());
            for (int i = 0; i < results2.size(); i++)
            {
                Map gb = (Map) results2.get(i);
                System.out.println(gb.get("id") + " " + gb.get("name"));
            }

            // 采用ScalarHandler方式查询
            System.out.println("....ScalarHandler....");
            String name = (String) qr.query(conn, "select id,name  from zxinweb..guestbook where id =5", new ScalarHandler("name"));

            System.out.println(name);

            // 删除一条数据
            qr.update(conn, "delete from  zxinweb..guestbook where id =5 ");
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            DbUtils.closeQuietly(conn);
        }
    }
}

结果

....ArrayHandler....
5 name5 
....ArrayListHandler....
1 name1  
2 name2  
5 name5  
....BeanHandler....
dbutils.Guestbook:[  id:1  name:name1]
....BeanListHandler....
dbutils.Guestbook:[  id:1  name:name1]
dbutils.Guestbook:[  id:2  name:name2]
dbutils.Guestbook:[  id:5  name:name5]
....BeanMapHandler....
1=dbutils.Guestbook:[  id:1  name:name1]
2=dbutils.Guestbook:[  id:2  name:name2]
5=dbutils.Guestbook:[  id:5  name:name5]
....ColumnListHandler....
name1
name2
name5
....KeyedHandler....
1 name1
2 name2
5 name5
....MapHandler....
name5
....MapListHandler....
1 name1
2 name2
5 name5
....ScalarHandler....
name5