20160410javaweb之JDBC---DBUtils框架

破玉發表於2016-04-10
JavaWebJDBC框架

DBUtils

1.DbUtils
工具類

2.QueryRunner -- 兩行程式碼搞定增刪改查

(1)QueryRunner() --需要控制事務時,使用這組方法
int update(Connection conn, String sql)
Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
int update(Connection conn, String sql, Object... params)
Execute an SQL INSERT, UPDATE, or DELETE query.
int update(Connection conn, String sql, Object param)
Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.

<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh)
Execute an SQL SELECT query without any replacement parameters.
<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
Execute an SQL SELECT query with replacement parameters.

(2)QueryRunner(DataSource ds) --不需要控制事務用這組方法
int update(String sql)
Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters.
int update(String sql, Object... params)
Executes the given INSERT, UPDATE, or DELETE SQL statement.
int update(String sql, Object param)
Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter.

<T> T query(String sql, ResultSetHandler<T> rsh)
Executes the given SELECT SQL without any replacement parameters.
<T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
Executes the given SELECT SQL query and returns a result object.

package com.dzq.dbutils;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DbUtilsUpdate {
   
    @Test
    public void add() throws SQLException{
        QueryRunner runner=new QueryRunner(new ComboPooledDataSource());
        runner.update("update account set money=? where name=?",888,"a");
    }
}
package com.dzq.dbutils;

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

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

import com.dzq.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtilsQuery {
   @Test
   public void find() throws SQLException{
       QueryRunner runner=new QueryRunner(new ComboPooledDataSource());
       String sql="select * from account where money>?";
     List<Account> list=  runner.query(sql, new ResultSetHandler<List<Account>>() {

        @Override
        public List<Account> handle(ResultSet rs) throws SQLException {
            List <Account> list =new ArrayList<Account>();
            while (rs.next()) {
                
                Account acc=new Account();
                acc.setId(rs.getInt("id"));
                acc.setName(rs.getString("name"));
                acc.setMoney(rs.getDouble("money"));
                list.add(acc);
            }
            return list;
        }
       
    },500);
     System.out.println(list);
   }
}

 

 

3.ResultSetHandler 實現類

ArrayHandler:把結果集中的第一行資料轉成物件陣列。
ArrayListHandler：把結果集中的每一行資料都轉成一個物件陣列，再存放到List中。
!!!!BeanHandler：將結果集中的第一行資料封裝到一個對應的JavaBean例項中。
!!!!BeanListHandler：將結果集中的每一行資料都封裝到一個對應的JavaBean例項中，存放到List裡。
MapHandler：將結果集中的第一行資料封裝到一個Map裡，key是列名，value就是對應的值。
MapListHandler：將結果集中的每一行資料都封裝到一個Map裡，然後再存放到List
ColumnListHandler：將結果集中某一列的資料存放到List中。
KeyedHandler(name)：將結果集中的每一行資料都封裝到一個Map裡(List<Map>)，再把這些map再存到一個map裡，其key為指定的列。
!!!!!ScalarHandler:獲取結果集中第一行資料指定列的值,常用來進行單值查詢

示例程式碼：

package com.itheima.dbutils;

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.itheima.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class RSHanlderDemo {
    //ScalarHandler:獲取結果集中第一行資料指定列的值,常用來進行單值查詢
    @Test
    public void tes9() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        Long count = (Long)runner.query("select count(*) from account",new ScalarHandler());
        System.out.println(count);
    }
    
    //KeyedHandler(name)：將結果集中的每一行資料都封裝到一個Map裡(List<Map>)，再把這些map再存到一個map裡，其key為指定的列。
    @Test
    public void tes8() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
         Map<Object, Map<String, Object>> map = runner.query("select * from account where money>?", new KeyedHandler("id"),500);
        System.out.println(map);
    }
    //ColumnListHandler：將結果集中某一列的資料存放到List中。
    @Test
    public void tes7() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        List<Object>list = runner.query("select * from account where money>?", new ColumnListHandler(3),500);
        System.out.println(list);
    }
    //MapListHandler：將結果集中的每一行資料都封裝到一個Map裡，然後再存放到List
    @Test
    public void tes6() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        List<Map<String, Object>> list = runner.query("select * from account where money>?", new MapListHandler(),500);
        System.out.println(list);
    }
    
    //MapHandler：將結果集中的第一行資料封裝到一個Map裡，key是列名，value就是對應的值。
    @Test
    public void tes5() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
         Map<String, Object> map = runner.query("select * from account where money>?", new MapHandler(),500);
        System.out.println(map);
    }
    
    //BeanListHandler：將結果集中的每一行資料都封裝到一個對應的JavaBean例項中，存放到List裡。
    @Test
    public void tes4() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        List<Account>list = runner.query("select * from account where money>?", new BeanListHandler<Account>(Account.class),500);
        System.out.println(list);
    }
    
    //BeanHandler：將結果集中的第一行資料封裝到一個對應的JavaBean例項中。
    @Test
    public void tes3() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        Account acc = runner.query("select * from account where money>?", new BeanHandler<Account>(Account.class),500);
        System.out.println(acc);
    }
    //ArrayListHandler：把結果集中的每一行資料都轉成一個物件陣列，再存放到List中。
    @Test
    public void tes2() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        List<Object[]> list = runner.query("select * from account where money>?", new ArrayListHandler(),500);
        System.out.println(list);
    }
    
    //ArrayHandler:把結果集中的第一行資料轉成物件陣列。
    @Test
    public void test1() throws SQLException{
        QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
        Object[] objs = runner.query("select * from account where money>?", new ArrayHandler(),500);
        System.out.println(objs);
    }
}

 

相關文章