DBUtils框架
2020-04-20 11:51 JaysonChan 阅读(157) 评论(0) 编辑 收藏 举报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"); } } }