DBUtilsの公式例

11851 ワード
DbUtils
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: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:1行のレコードを取得し、接続する
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には3つのフィールドがあります
 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();
        }
    }
}
Android Appインストール失敗エラーまとめ
ActionBarタイトルとメニューのテキストスタイルのカスタマイズ