文档章节

DBUtils官方例子

小黑小别姬
 小黑小别姬
发布于 2012/03/12 01:45
字数 962
阅读 2430
收藏 16
sql
CREATE TABLE [dbo].[Person] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[userName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[age] [int] NULL 
) ON [PRIMARY]
GO

---
1:取得一行记录.DataSource
package com.x.test;


import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import javax.sql.DataSource;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;


public class TEST01_getFirstRow
{

    public static void main(String[] args) throws SQLException
    {
        TEST01_getFirstRow t = new TEST01_getFirstRow();
        t.test();
    }


    private void test() throws SQLException
    {
        // Create a ResultSetHandler implementation to convert the
        // first row into an Object[].
        ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>()
        {
            public Object[] handle(ResultSet rs) throws SQLException
            {
                if (!rs.next())
                {
                    return null;
                }

                ResultSetMetaData meta = rs.getMetaData();
                int cols = meta.getColumnCount();
                Object[] result = new Object[cols];

                for (int i = 0; i < cols; i++)
                {
                    result[i] = rs.getObject(i + 1);
                }

                return result;
            }
        };

        // Create a QueryRunner that will use connections from
        // the given DataSource
        JtdsDataSource dataSource = new JtdsDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("DBtest");
        dataSource.setUser("sa");
        dataSource.setPassword("");
        
        
        QueryRunner run = new QueryRunner(dataSource);

        // Execute the query and get the results back from the handler
        Object[] result = run.query("SELECT * FROM Person WHERE username!=?", h,  "John Doe");

        System.out.println("over");
    }

}




2:取得一行记录,Connection

package com.x.test;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import javax.sql.DataSource;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

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


public class TEST02_getFirstRow2
{

    public static void main(String[] args) throws SQLException
    {
        TEST02_getFirstRow2 t = new TEST02_getFirstRow2();
        t.test();
    }


    private void test() throws SQLException
    {
        // Create a ResultSetHandler implementation to convert the
        // first row into an Object[].
        ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>()
        {
            public Object[] handle(ResultSet rs) throws SQLException
            {
                if (!rs.next())
                {
                    return null;
                }

                ResultSetMetaData meta = rs.getMetaData();
                int cols = meta.getColumnCount();
                Object[] result = new Object[cols];

                for (int i = 0; i < cols; i++)
                {
                    result[i] = rs.getObject(i + 1);
                }

                return result;
            }
        };

        // Create a QueryRunner that will use connections from
        // the given DataSource
        JtdsDataSource dataSource = new JtdsDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("DBtest");
        dataSource.setUser("sa");
        dataSource.setPassword("");

        Connection conn = DriverManager.getConnection(
                "jdbc:jtds:sqlserver://localhost:1433/DBtest;", "sa", "");

        QueryRunner run = new QueryRunner();

        try
        {
            Object[] result = run.query(conn,
                    "SELECT * FROM Person WHERE userName!=?", h, "John Doe");
            // do something with the result
            System.out.println("over");
        }
        finally
        {
            // Use this helper method so we don't have to check for null
            DbUtils.close(conn);
        }

        System.out.println("over");
    }

}



3:添加/更新

package com.x.test;

import java.sql.SQLException;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

import org.apache.commons.dbutils.QueryRunner;

public class TEST03_insertData
{

    /**
     * @param args
     */
    public static void main(String[] args)
    {
        TEST03_insertData insert=new TEST03_insertData();
        insert.test();
    }

    private void test()
    {
        JtdsDataSource  dataSource=new JtdsDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("DBtest");
        dataSource.setUser("sa");
        dataSource.setPassword("");
        
        QueryRunner run = new QueryRunner( dataSource );
        try
        {
            // Execute the SQL update statement and return the number of
            // inserts that were made
            int inserts = run.update( "INSERT INTO Person (userName,age) VALUES (?,?)",
                                      "zhanghongjie", 122 );
            // The line before uses varargs and autoboxing to simplify the code

            // Now it's time to rise to the occation...
            int updates = run.update( "UPDATE Person SET age=? WHERE username=?",
                                      2, "zhanghongjie" );
            // So does the line above
            
            System.out.println("over");
        }
        catch(SQLException sqle) {
            // Handle it
        }
    }

}


4:异步执行更新

package com.x.test;


import java.sql.SQLException;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.FutureTask;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

import org.apache.commons.dbutils.AsyncQueryRunner;


public class TEST04_asyncQueryRunner
{

    public static void main(String[] args)
    {
        TEST04_asyncQueryRunner t = new TEST04_asyncQueryRunner();
        t.test();
    }


    private void test()
    {
        JtdsDataSource dataSource = new JtdsDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("DBtest");
        dataSource.setUser("sa");
        dataSource.setPassword("");

        // ExecutorCompletionService<Integer> executor = new
        // ExecutorCompletionService<Integer>(
        // Executors.newCachedThreadPool());
        
        AsyncQueryRunner asyncRun = new AsyncQueryRunner(dataSource, Executors
                .newCachedThreadPool());

        try
        {
            
            for (int i = 0; i < 10; i++)
            {
                System.out.println("over1");
            }
            
            
            // Create a Callable for the update call
            Future futures = asyncRun.update(
                    "UPDATE Person SET age=? WHERE username=?", 120,
                    "zhanghongjie");
            
            for (int i = 0; i < 10; i++)
            {
                System.out.println("over2");
            }
            System.out.println(futures.get());//如果有必要取得返回结果
            
            for (int i = 0; i < 10; i++)
            {
                System.out.println("over3");
            }
            // Submit the Callable to the executor
            // executor.submit(callable);
            
            
        }
        catch (Exception sqle)
        {
            // Handle it
        }
         

        // Sometime later (or in another thread)
//        try
//        {
//            // Get the result of the update
//            Integer updates = executor.take().get();
//            System.out.println("over");
//        }
//        catch (Exception ie)
//        {
//            // Handle it
//        }
    }

}

这个官方的例子错了。

5:动态映射类取得一条记录

注意:Person.java 有三个字段
 private String USERNAME;//跟数据库中字段名称不用要求大小写一样
 private String age;//数据库类型和java类型没有关系
 private int age1;//多出来字段会被赋予基本类型的默认值
package com.x.test;


import java.sql.SQLException;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import com.x.vo.Person;


public class TEST05_getByclz
{
    public static void main(String[] args)
    {
        TEST05_getByclz t = new TEST05_getByclz();
        t.test();
    }


    private void test()
    {

        JtdsDataSource dataSource = new JtdsDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("DBtest");
        dataSource.setUser("sa");
        dataSource.setPassword("");

        QueryRunner run = new QueryRunner(dataSource);

        // Use the BeanHandler implementation to convert the first
        // ResultSet row into a Person JavaBean.
        ResultSetHandler<Person> h = new BeanHandler<Person>(Person.class);

        // Execute the SQL statement with one replacement parameter and
        // return the results in a new Person object generated by the
        // BeanHandler.
        try
        {
            Person p = run.query("SELECT * FROM Person WHERE id=?", h, "1");
         
            System.out.println(p);
            
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}


6:动态映射类取得List

package com.x.test;


import java.sql.SQLException;
import java.util.List;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.x.vo.Person;


public class TEST06_getListByclz
{
    public static void main(String[] args)
    {
        TEST06_getListByclz t = new TEST06_getListByclz();
        t.test();
    }


    private void test()
    {

        JtdsDataSource dataSource = new JtdsDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("DBtest");
        dataSource.setUser("sa");
        dataSource.setPassword("");

        QueryRunner run = new QueryRunner(dataSource);

        // Use the BeanHandler implementation to convert the first
        // ResultSet row into a Person JavaBean.
        ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class);
        
        // Execute the SQL statement with one replacement parameter and
        // return the results in a new Person object generated by the
        // BeanHandler.
        try
        {
            List<Person> p = run.query("SELECT * FROM Person  ", h);
            System.out.println(p);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}



© 著作权归作者所有

小黑小别姬
粉丝 13
博文 3
码字总数 1185
作品 0
海淀
程序员
私信 提问
加载中

评论(1)

于恒利
于恒利
支持下
Apache Commons DbUtils 1.6 发布

Apache Commons DbUtils 1.6 发布,主要改进内容包括: ArrayHandler should return an empty array when handle has no rows Fixes DBUTILS-110. Order of columns not retained in BasicR......

oschina
2014/07/22
7.3K
15
Apache Commons DbUtils 1.5 发布

DbUtils 可是难得更新一次啊,刚发布的 1.5 版本改进记录包括: Bug [DBUTILS-73] - .BasicRowProcessor.CaseInsensitiveHashMap uses default Locale for toLowerCase [DBUTILS-77] - "dro......

oschina
2012/07/22
4.5K
15
DBUtils 用户指南

1 简介 DBUtils是一套Python数据库连接池包,并允许对非线程安全的数据库接口进行线程安全包装。DBUtils来自Webware for Python。 DBUtils提供两种外部接口: PersistentDB :提供线程专用的...

红薯
2008/11/28
1K
2
Apache Commons-DbUtils 1.4 发布

dbutils 1.4 发布了,dbutils 并不是一个严格意义上的 ORM 框架,它提供了一些Jdbc的操作封装来简化数据查询和记录读取操作。本站使用的就是 dbutils 这个工具包来进行数据库存取。 新版本主...

红薯
2011/10/25
3.5K
0
Commons DbUtils

1 概述 Commons DBUtils类库是小型的设计于易于使用JDBC的类集合。JDBC资源清理是平凡的,容易出错,以至于这些类从你的代码中抽象出清理代码,剩下你最初真正想要使用JDBC做的代码:查询和更...

Leech
2015/07/21
430
0

没有更多内容

加载失败,请刷新页面

加载更多

关于AsyncTask的onPostExcute方法是否会在Activity重建过程中调用的问题

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接:https://blog.csdn.net/XG1057415595/article/details/86774575 假设下面一种情况...

shzwork
今天
6
0
object 类中有哪些方法?

getClass(): 获取运行时类的对象 equals():判断其他对象是否与此对象相等 hashcode():返回该对象的哈希码值 toString():返回该对象的字符串表示 clone(): 创建并返此对象的一个副本 wait...

happywe
今天
6
0
Docker容器实战(七) - 容器中进程视野下的文件系统

前两文中,讲了Linux容器最基础的两种技术 Namespace 作用是“隔离”,它让应用进程只能看到该Namespace内的“世界” Cgroups 作用是“限制”,它给这个“世界”围上了一圈看不见的墙 这么一...

JavaEdge
今天
8
0
文件访问和共享的方法介绍

在上一篇文章中,你了解到文件有三个不同的权限集。拥有该文件的用户有一个集合,拥有该文件的组的成员有一个集合,然后最终一个集合适用于其他所有人。在长列表(ls -l)中这些权限使用符号...

老孟的Linux私房菜
今天
7
0
面试套路题目

作者:抱紧超越小姐姐 链接:https://www.nowcoder.com/discuss/309292?type=3 来源:牛客网 面试时候的潜台词 抱紧超越小姐姐 编辑于 2019-10-15 16:14:56APP内打开赞 3 | 收藏 4 | 回复24 ...

MtrS
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部