常用结果集 scalarHandler,beanListHandler,BeanHander ,columnlistHandler,BeanMapHandler,ArrayHandler

package com.oracle.dao;

import com.oracle.dbutile.DbUtils;

import com.oracle.entity.Muser;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DbDao {

    private QueryRunner queryRunner;
    private  Connection conn;
    @Before
    //单元测试 两个注解 before 和 after   每一个测试方法 先执行Before 自己的方法 @after的代码
    public  void before(){
        queryRunner=new QueryRunner();
       conn= DbUtils.getConn();

    }
//ScalarHandler<Long>
    @Test
    public  void jdbc(){

        String sql="insert into muser values(null,?,?,?,?,?,?)";
        try {

           long pk= queryRunner.insert(conn,sql,new ScalarHandler<Long>(),"admin","123456",1,11,"188888","10089");
            //int count=queryRunner.update(conn,sql,"admin","111",1,23,"10086","helloworld");

           System.out.println(pk);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DbUtils.closeConn(conn,null,null);
        }

    }

  @Test
  public void query()
  {

      try {
          List<Muser> list= queryRunner.query(conn,"select * from  muser",new BeanListHandler<Muser>(Muser.class));
          Iterator i=list.iterator();
          while(i.hasNext())
          {
              System.out.println(i.next());
          }

      } catch (SQLException e) {
          e.printStackTrace();
      }
  }

//注入
    @Test
    public void dsql(){

            QueryRunner queryRunner=new QueryRunner();
            Connection conn= DbUtils.getConn();
            String muname="admin";
            String mupwd="' or '1'='1";
            try {//select * from muser where muname='admin' and mupwd='' or '1'='1'
                Muser user = queryRunner.query(conn, "select * from muser where muname=? and mupwd=?", new BeanHandler<Muser>(Muser.class),muname,mupwd);
                System.out.println(user);
            }catch (Exception e)
            {
                e.printStackTrace();
            }
            finally {
                DbUtils.closeConn(conn,null,null);
            }
        }








  //
  @Test
    public void querone(){
      try {

         Muser user=  queryRunner.query(conn,"select *from muser where muname=? and mupwd=?",new BeanHandler<Muser>(Muser.class),"admin","123456");
         if(user==null){
             System.out.println("登录失败");
         }else {
             System.out.println("登录成功");
         }

      } catch (SQLException e) {
          e.printStackTrace();
      }
      finally {
          DbUtils.closeConn(conn,null,null);
      }
  }
 @Test
    public void queryColumnList(){
        String sql="select muname from muser";
     try {
         List<String> unameList=queryRunner.query(conn,sql,new ColumnListHandler<String>());
     Iterator i=unameList.iterator();
     while (i.hasNext()){
         System.out.println(i.next());
     }
     } catch (SQLException e) {
         e.printStackTrace();
     }

 }

@Test
    public void beanMap(){
    try {
       Map<Integer,Muser> beanMap= queryRunner.query(conn,"select *from muser",new BeanMapHandler<Integer,Muser>(Muser.class));
    Set<Integer> keySet=beanMap.keySet();
   Iterator<Integer> i=keySet.iterator();
    while (i.hasNext()){
        int key=i.next();
        System.out.println(key);
        System.out.println(beanMap.get(key));

    }
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

@Test
    public void queryArray(){
    String sql="select media.*,r.regionname from media as media left join region as r on media.regionid=r.regionid";
    try {
        Object[] objList=queryRunner.query(conn,sql,new ArrayHandler());
        for (Object column:objList){
            System.out.print(column);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }


}

@Test
    public void queryall(){
    try {
      List<Muser> list  =queryRunner.query(conn,"select *from muser",new BeanListHandler<Muser>(Muser.class));
    Iterator i= list.iterator();
    while (i.hasNext()){
        System.out.println(i.next());
    }
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

}