org.apache.commons.dbutils.handlers.BeanListHandler Java Examples

/**
 * 将查询结果集转换成Bean列表返回。
 * 
 * @param ds JDBC连接池
 */
public void queryBeanList(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<List<Student>> handler = new BeanListHandler<Student>(Student.class);
    List<Student> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    if (null == result) {
        return;
    }
    for (Student student : result) {
        System.out.println(student);
    }
}
 

@Override
public List<User> searchUsers(final String key, final String... excludeIds) {
    return process(new Handler<List<User>>() {
        @Override
        public List<User> handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder _excludeIds_ = new StringBuilder("\'\',");
            if (excludeIds != null && excludeIds.length > 0) {
                for (String id : excludeIds) {
                    _excludeIds_.append("\'");
                    _excludeIds_.append(id);
                    _excludeIds_.append("\'");
                    _excludeIds_.append(",");
                }
            }
            _excludeIds_ = _excludeIds_.delete(_excludeIds_.length() - 1, _excludeIds_.length());
            StringBuilder sql = new StringBuilder("select id,email,nickname from user where  id not in(" + _excludeIds_ + ") and instr(nickname , ?)>0 order by length(nickname) asc limit 5");
            return qr.query(connection, sql.toString(), new BeanListHandler<>(User.class), key);
        }
    });
}
 

@Override
public List<Project> getProjects(final Pagination pagination) {
    return process(new Handler<List<Project>>() {
        @Override
        public List<Project> handle(Connection connection, QueryRunner qr) throws SQLException {
            String status = (String) pagination.getParams().get("status");
            if (status == null) {
                status = "VALID";
            }
            StringBuilder sql = new StringBuilder("select DISTINCT p.*,u.nickname userName,pu.editable,pu.commonlyUsed from ").append(TableNames.PROJECT)
                    .append(" p left join user u on u.id = p.userId ")
                    .append(" left join project_user pu on pu.projectId = p.id ")
                    .append("  where ( pu.userId=?) and p.status=?")
                    .append(" order by createTime desc");
            return qr.query(connection, sql.toString(), new BeanListHandler<>(Project.class), pagination.getParams().get("userId"), status);
        }
    });
}
 

@Override
public List<String> getDocIdsByParentId(final String parentId) {
    return process(new Handler<List<String>>() {
        @Override
        public List<String> handle(Connection connection, QueryRunner qr) throws SQLException {
            List<Doc> docs = qr.query(connection, "select id from " + TableNames.DOC + " where parentId=?", new BeanListHandler<>(Doc.class), parentId);
            List<String> ids = new ArrayList<String>();
            if (docs != null) {
                for (Doc temp : docs) {
                    ids.add(temp.getId());
                }
            }
            return ids;
        }
    });
}
 

@Override
public LogList findByLimitAndFields(int page, int limit, int timeOrder, List<Field> list) throws TxLoggerException {
    if (Objects.isNull(dbHelper)) {
        throw new TxLoggerException("系统日志被禁用");
    }
    StringBuilder countSql = new StringBuilder("select count(*) from t_logger where 1=1 and ");
    StringBuilder sql = new StringBuilder("select * from t_logger where 1=1 and ");
    List<String> values = whereSqlAppender(sql, list);
    whereSqlAppender(countSql, list);
    Object[] params = values.toArray(new Object[0]);
    long total = dbHelper.query(countSql.toString(), new ScalarHandler<>(), params);
    if (total < (page - 1) * limit) {
        page = 1;
    }
    sql.append(timeOrderSql(timeOrder)).append(" limit ").append((page - 1) * limit).append(", ").append(limit);
    List<TxLog> txLogs = dbHelper.query(sql.toString(), new BeanListHandler<>(TxLog.class, processor), params);

    LogList logList = new LogList();
    logList.setTotal(total);
    logList.setTxLogs(txLogs);
    return logList;
}
 

@SuppressWarnings("unchecked")
protected BaseDao() {
	queryRunner = new QueryRunner();
	Type superClass = getClass().getGenericSuperclass();
	if (superClass instanceof ParameterizedType) {
		ParameterizedType parameterizedType = (ParameterizedType) superClass;
		Type[] typeArgs = parameterizedType.getActualTypeArguments();
		if (typeArgs != null && typeArgs.length > 0) {
			if (typeArgs[0] instanceof Class) {
				clazz = (Class<T>) typeArgs[0];
			}
		}
	}
	beanHandler = new BeanHandler<>(clazz);
	beanListHandler = new BeanListHandler<>(clazz);
}
 

@Override
public <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {
    List<T> result;
    try {
        Map<String, String> columnMap = EntityHelper.getColumnMap(entityClass);
        if (MapUtil.isNotEmpty(columnMap)) {
            result = queryRunner.query(sql, new BeanListHandler<T>(entityClass, new BasicRowProcessor(new BeanProcessor(columnMap))), params);
        } else {
            result = queryRunner.query(sql, new BeanListHandler<T>(entityClass), params);
        }
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

@Override
public <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {
    List<T> result;
    try {
        Map<String, String> columnMap = EntityHelper.getColumnMap(entityClass);
        if (MapUtil.isNotEmpty(columnMap)) {
            result = queryRunner.query(sql, new BeanListHandler<T>(entityClass, new BasicRowProcessor(new BeanProcessor(columnMap))), params);
        } else {
            result = queryRunner.query(sql, new BeanListHandler<T>(entityClass), params);
        }
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

/**
 * 
 * @Title:       querySql   
 * @Description: TODO(带可变参数查询,返回执行结果)   
 * @param:       @param clazz 转换的对象实例
 * @param:       @param sql 查询sql语句
 * @param:       @param para 查询参数
 * @param:       @return
 * @param:       @throws SQLException      
 * @return:      List      
 * @throws
 */
public <T> List querySql(T clazz, String sql, Object... para) throws SQLException {
	logger.debug("查询Sql: {}, 查询参数: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	List<T> result = new ArrayList<T>();
	try {
		conn = getConnection();
		// 下划线分隔的表字段名转换为实体bean驼峰命名属性
		BeanProcessor bean = new GenerousBeanProcessor();
		RowProcessor processor = new BasicRowProcessor(bean);
		result = (List<T>) runner.query(conn, sql, new BeanListHandler((Class) clazz, processor), para);
	} catch (SQLException e) {
		logger.error("查询出错，异常信息: {}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return result;
}
 

public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);

		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<List<Employee>> resultListHandler = new BeanListHandler<Employee>(
				Employee.class);

		try {
			List<Employee> empList = run.query(conn, "SELECT * FROM employee",
					resultListHandler);
			System.out.println(empList);
		} finally {
			DbUtils.close(conn);
		}

	}
 

public static void main(String[] args) {
	Connection conn = null;

	final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
	final String driver = "org.h2.Driver";
	final String usr = "sa";
	final String pwd = "";

	List<User> users = null;

	try {
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		QueryRunner query = new QueryRunner();
		users = (List<User>) query.query(conn, "select * from user",
				new BeanListHandler<User>(User.class));
		for (int i = 0; i < users.size(); i++) {
			User bean = users.get(i);
			System.out.println("User Objects::  " + bean.getUserId() + "\t"
					+ bean.getFirstName() + "\t" + bean.getLastName()
					+ "\t" + bean.getEmailId());
		}
	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
 

public static void main(String[] args) {
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
	final String driver = "org.h2.Driver";
	final String usr = "sa";
	final String pwd = "";

	List<User> users = null;
	try {
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT * FROM user");
		BeanListHandler<User> listHandler = new BeanListHandler<User>(
				User.class);
		users = listHandler.handle(rs);

		for (User user : users) {
			System.out.println("User Object:: " + user.getUserId() + "\t"
					+ user.getFirstName() + "\t" + user.getEmailId());
		}

	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
 

@Test
public void test() throws SQLException {
    Connection connect = JdbcUtils.getConnect();
    String sql ="select name a, status s from project limit 10";
    QueryRunner qr = new QueryRunner();
    List<TestBean> query = qr.query(connect, sql, new BeanListHandler<>(TestBean.class));
    System.out.println(query);
}
 

@Override
public List<Doc> searchDocs(final String text, final String projectId) {
    return process(new Handler<List<Doc>>() {
        @Override
        public List<Doc> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection, "select id,name from " + TableNames.DOC + " where projectId=? and (instr(name,?)>0  or instr(content,?)>0) order by sort asc ,createTime desc ", new BeanListHandler<>(Doc.class), projectId, text, text);
        }
    });
}
 

@Override
public List<DocHistory> getDocHistorys(final String docId) {
    return process(new Handler<List<DocHistory>>() {
        @Override
        public List<DocHistory> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection, "select h.*,u.nickname userName from " + TableNames.DOC_HISTORY + " h\n" +
                    "left join " + TableNames.USER + " u on u.id = h.userId \n" +
                    "where h.docId=?\n" +
                    "order by createTime desc limit 20 ", new BeanListHandler<>(DocHistory.class), docId);
        }
    });
}
 

@Override
public List<Attach> getAttachsByRelatedId(final String relatedId) {
    return process(new Handler<List<Attach>>() {
        @Override
        public List<Attach> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection, "select * from " + TableNames.ATTACH + " where relatedId=? order by sort asc ,createtime asc", new BeanListHandler<>(Attach.class), relatedId);
        }
    });
}
 

@Override
public List<ProjectLog> getProjectLogs(final Pagination pagination) {
    return process(new Handler<List<ProjectLog>>() {
        @Override
        public List<ProjectLog> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection, "select pl.*,u.nickname,u.avatar from " + TableNames.PROJECT_LOG + " pl left join user u on u.id = pl.userId where pl.projectId=? order by pl.createTime desc limit ?,?", new BeanListHandler<>(ProjectLog.class), pagination.getParams().get("projectId"), pagination.getStart(), pagination.getLimit());
        }
    });
}
 

@Override
public List<Share> getSharesByProjectId(final String projectId) {
    return process(new Handler<List<Share>>() {
        @Override
        public List<Share> handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder sql = new StringBuilder();
            sql.append("select s.*,u.nickname username from share s\n");
            sql.append("left join user u on u.id = s.userid\n");
            sql.append("where s.projectId = ?");
            return qr.query(connection, sql.toString(), new BeanListHandler<>(Share.class), projectId);
        }
    });
}
 

@Override
public List<Doc> getDocsByParentId(final String projectId, final String parentId) {
    return process(new Handler<List<Doc>>() {
        @Override
        public List<Doc> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection,"select id,name from doc where projectId=? and parentId=? order by sort asc",new BeanListHandler<>(Doc.class),projectId,parentId);
        }
    });
}
 

@Override
public List<Employee> handle(ResultSet rs) throws SQLException {
    List<Employee> employees = super.handle(rs);

    QueryRunner runner = new QueryRunner();
    BeanListHandler<Email> handler = new BeanListHandler<>(Email.class);
    String query = "SELECT * FROM email WHERE employeeid = ?";
    for (Employee employee : employees) {
        List<Email> emails = runner.query(connection, query, handler, employee.getId());
        employee.setEmails(emails);
    }
    return employees;
}
 

@Override
public List<Doc> getDocsByProjectId(final String projectId, final boolean full) {

    return process(new Handler<List<Doc>>() {
        @Override
        public List<Doc> handle(Connection connection, QueryRunner qr) throws SQLException {
            //不查询content
            String sqlstr = "select id,name,sort,type,parentId,projectId" + (full ? ",content" : "") + " from ";
            StringBuilder sql = new StringBuilder(sqlstr).append(TableNames.DOC).append(" where projectId=? order by sort asc");
            return qr.query(connection, sql.toString(), new BeanListHandler<>(Doc.class), projectId);
        }
    });
}
 

@Override
public List<Doc> getDocsByProjectId(final String projectId) {
    return process(new Handler<List<Doc>>() {
        @Override
        public List<Doc> handle(Connection connection, QueryRunner qr) throws SQLException {
            //不查询content
            StringBuilder sql = new StringBuilder("select id,name,sort,type,parentId,projectId from ").append(TableNames.DOC).append(" where projectId=? order by sort asc");
            return qr.query(connection, sql.toString(), new BeanListHandler<>(Doc.class), projectId);
        }
    });
}
 

@Test
public void givenResultHandler_whenExecutingQuery_thenEmployeeList() throws SQLException {
    BeanListHandler<Employee> beanListHandler = new BeanListHandler<>(Employee.class);

    QueryRunner runner = new QueryRunner();
    List<Employee> employeeList = runner.query(connection, "SELECT * FROM employee", beanListHandler);

    assertEquals(employeeList.size(), 5);
    assertEquals(employeeList.get(0).getFirstName(), "John");
    assertEquals(employeeList.get(4).getFirstName(), "Christian");
}
 

@Override
public List<User> getUsersByProjectId(final String projectId) {
    return process(new Handler<List<User>>() {
        @Override
        public List<User> handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder sql = new StringBuilder("select u.id,u.nickname,u.avatar,u.email,pu.editable from user u left join project_user pu on pu.userId=u.id where pu.projectId=?");
            return qr.query(connection, sql.toString(), new BeanListHandler<>(User.class), projectId);
        }
    });
}
 

@Override
public List<Team> getTeams(final String userId) {
    return process(new Handler<List<Team>>() {
        @Override
        public List<Team> handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder sql = new StringBuilder("select t.* from ")
                    .append(TableNames.TEAM)
                    .append(" t left join team_user tu on tu.teamId=t.id ")
                    .append(" where tu.userId=? or t.userId=?")
                    .append(" order by tu.createTime desc,t.createTime desc ");

            return qr.query(connection, sql.toString(), new BeanListHandler<>(Team.class), userId, userId);
        }
    });
}
 

public List findAllAccount() {
    try {
        return runner.query("select * from count ", new BeanListHandler<Account>(Account.class));
    } catch (Exception e) {
        throw new RuntimeException();
    }
}
 

@Override
	public List<T> queryForList(String sql, Object... args) {
		Connection connection = null;
		
		try {
//			connection = ConnectionContext.getInstance().get();
			connection = JDBCUtils.getConnection();
			return queryRunner.query(connection, sql, new BeanListHandler<>(clazz), args);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.release(connection);
		}
		return null;
	}
 

@SuppressWarnings("deprecation")
public Media queryMediaById(int id) throws Exception {
	super.init();
	//��̬����SQL���
	String strSQL = "select * from tb_media where id = ?";
	Media media = qRunner.query(conn, strSQL,new Object[]{id},new BeanListHandler<Media>(Media.class)).get(0);
	DbUtils.closeQuietly(conn); 
	return media;
}
 

@SuppressWarnings("deprecation")
public List<Media> queryALlMedia(int firstResult,
		int maxResult) throws Exception {
	super.init();
	//��̬����SQL���
	String strSQL = "select * from tb_media limit ?,?";
	List<Media> lstMedia = (List<Media>)qRunner.query(conn, strSQL,new Object[]{firstResult,maxResult},new BeanListHandler<Media>(Media.class));
	DbUtils.closeQuietly(conn); 
	return lstMedia;
}
 

public List<UserModel> getAllUser() {
    List<UserModel> result = null;
    try {
        result = db.query(GET_ALL_USER, new BeanListHandler<>(UserModel.class), nodeId);
    } catch (SQLException e) {
        logger.error("更新用户异常", e);
    }
    return result;
}