package com.softtech.dao.impl;

import java.sql.Connection;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.softtech.entity.EasyBuy_User;
import com.softtech.utils.JdbcUtil;

public class EasyBuy_UserDaoImpl {
    /**
     * ResultSetHandler:结果集处理器，告诉DBUtils框架最终返回的结果使用何种类型来封装
     * 常见的结果集处理器有以下几种：
     * 1.BeanHandler是处理JavaBean数据类型
     * 2.MapHandler是处理Map集合类型
     * 3.BeanListHandler是处理多条记录，每条记录使用JavaBean来封装
     * 4.MapListHandler是处理多条记录，每条记录使用Map来封装
     * 5.ArrayHandler是处理Array集合类型，导出Excel常用。
     * 6.ArrayListHandler是处理多条记录，每条记录使用Array来封装，导出Excel常用。
     * 7.ScalarHandler是处理单行单列的记录，比如校验用户登录是否成功。
     */
    
    private QueryRunner qr = new QueryRunner();
    private Connection conn = JdbcUtil.getConn();
    
    /**
     * Description:通过DBUtils添加记录。
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void insertEasyBuy_User() throws Exception {
        String sql = "INSERT INTO easybuy_user SET EU_USER_ID=?,EU_USER_NAME=?,EU_PASSWORD=?,EU_SEX=?,EU_STATUS=?";
        int flag = qr.update(conn,sql, new Object[] {"JC2","JC2","123","T","1"});
        System.out.println("Insert Record have "+flag+" Row.");
    }
    
    
    /**
     * Description:通过DBUtils-BeanHandler查询记录。
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User1() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user where EU_USER_ID=‘admin‘ order by EU_USER_ID asc";
        EasyBuy_User ebu = qr.query(conn,sql,new BeanHandler<EasyBuy_User>(EasyBuy_User.class));
        System.out.println(ebu);
    }
    
    /**
     * Description:通过DBUtils-MapHandler查询记录。
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User2() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user where EU_USER_ID=‘admin‘ order by EU_USER_ID asc";
        Map<String,Object> ebuMap = qr.query(conn,sql,new MapHandler());
        System.out.println(ebuMap);
    }
    
    /**
     * Description:通过DBUtils-BeanListHandler查询记录。
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User3() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user order by EU_USER_ID asc";
        List<EasyBuy_User> ebuList = qr.query(conn,sql,new BeanListHandler<EasyBuy_User>(EasyBuy_User.class));
        for(EasyBuy_User ebu:ebuList) {
            System.out.println(ebu);
        }
        
    }
    
    /**
     * Description:通过DBUtils-MapListHandler查询记录。
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User4() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user order by EU_USER_ID asc";
        List<Map<String,Object>> mapList = qr.query(conn,sql,new MapListHandler());
        for(Map<String,Object> map:mapList) {
            System.out.println(map);
        }
    }
    
    
    /**
     * Description:通过DBUtils-MapListHandler查询记录。
     * Author:陈杰
     * DbUtils.closeQuietly会关闭3个对象
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User5() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user order by EU_USER_ID asc";
        List<Map<String,Object>> mapList = qr.query(conn,sql,new MapListHandler());
        
        //会内部关闭3个对象(ResultSet，Statement，Connection)
        DbUtils.closeQuietly(conn);
        //打印结果
        mapList.forEach((temp)-> {
            System.out.println(temp);
        });
    }
    
    
    /**
     * Description:通过DBUtils-ArrayHandler查询单条记录。
     * Author:陈杰
     * DbUtils.closeQuietly会关闭3个对象
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User6() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user where EU_USER_ID=‘admin‘";
        Object[] obj = qr.query(conn,sql,new ArrayHandler());
        DbUtils.closeQuietly(conn);
        for(Object o:obj) {
            System.out.println(o);
        }
    }
    
    
    /**
     * Description:通过DBUtils-ArrayHandler查询多条记录。
     * Author:陈杰
     * DbUtils.closeQuietly会关闭3个对象
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User7() throws Exception {
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user";
        List<Object[]> objList = qr.query(conn,sql,new ArrayListHandler());
        DbUtils.closeQuietly(conn);
        objList.forEach((temp)-> {
            System.out.println(Arrays.deepToString(temp));
        });
    }
    
    
    /**
     * Description:通过DBUtils-MapHandler查询单记录。
     * Author:陈杰
     * DbUtils.closeQuietly会关闭3个对象
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User8() throws Exception {
        String sql = "SELECT count(*) as flag from easybuy_user where EU_USER_ID=? and eu_password=?";
        Map<String,Object> map = qr.query(conn,sql,new MapHandler(),new Object[] {"admin","admin"});
        DbUtils.closeQuietly(conn);
        Long flag = (Long) map.get("flag");
        if(flag==1) {
            System.out.println("Login successful");
        } else {
            System.out.println("Login failed");
        }
        
    }
    
    
    /**
     * Description:通过DBUtils-MapHandler查询单记录。
     * Author:陈杰
     * DbUtils.closeQuietly会关闭3个对象
     * @throws Exception
     */
    @Test
    public void queryEasyBuy_User9() throws Exception {
        String sql = "SELECT count(*) as flag from easybuy_user where EU_USER_ID=? and eu_password=?";
        //当结果集只有一行一列时，可以使用这个ScalarHandler。
        Long flag = qr.query(conn,sql,new ScalarHandler<Long>(),new Object[] {"admin","admin"});
        DbUtils.closeQuietly(conn);
        if(flag==1) {
            System.out.println("Login successful");
        } else {
            System.out.println("Login failed");
        }
        
    }
    
}