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

The following examples show how to use org.apache.commons.dbutils.handlers.BeanListHandler. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: DbUtilsDemo.java    From JavaTutorial with Apache License 2.0 6 votes vote down vote up
/**
 * 将查询结果集转换成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);
    }
}
 
Example #2
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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);
        }
    });
}
 
Example #3
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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);
        }
    });
}
 
Example #4
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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;
        }
    });
}
 
Example #5
Source File: MysqlLoggerHelper.java    From tx-lcn with Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example #6
Source File: BaseDao.java    From Summer with Apache License 2.0 6 votes vote down vote up
@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);
}
 
Example #7
Source File: DefaultDataAccessor.java    From smart-framework with Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example #8
Source File: DefaultDataAccessor.java    From smart-framework with Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example #9
Source File: DbConnect.java    From mcg-helper with Apache License 2.0 6 votes vote down vote up
/**
 * 
 * @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;
}
 
Example #10
Source File: BeanListHandlerExample.java    From maven-framework-project with MIT License 6 votes vote down vote up
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);
		}

	}
 
Example #11
Source File: DbUtilsBeanListHandler.java    From maven-framework-project with MIT License 5 votes vote down vote up
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);
	}
}
 
Example #12
Source File: DbUtilsTest1.java    From maven-framework-project with MIT License 5 votes vote down vote up
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);
	}
}
 
Example #13
Source File: TestObjectArray.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
}
 
Example #14
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #15
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #16
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #17
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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());
        }
    });
}
 
Example #18
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #19
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #20
Source File: EmployeeHandler.java    From tutorials with MIT License 5 votes vote down vote up
@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;
}
 
Example #21
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #22
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #23
Source File: DbUtilsUnitTest.java    From tutorials with MIT License 5 votes vote down vote up
@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");
}
 
Example #24
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #25
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #26
Source File: AccountDaoImpl.java    From java_study with Apache License 2.0 5 votes vote down vote up
public List findAllAccount() {
    try {
        return runner.query("select * from count ", new BeanListHandler<Account>(Account.class));
    } catch (Exception e) {
        throw new RuntimeException();
    }
}
 
Example #27
Source File: BaseDAO.java    From EStore with MIT License 5 votes vote down vote up
@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;
	}
 
Example #28
Source File: MediaDaoImpl.java    From MediaPlayer with GNU General Public License v2.0 5 votes vote down vote up
@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;
}
 
Example #29
Source File: MediaDaoImpl.java    From MediaPlayer with GNU General Public License v2.0 5 votes vote down vote up
@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;
}
 
Example #30
Source File: V2rayDao.java    From ssrpanel-v2ray with GNU General Public License v3.0 5 votes vote down vote up
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;
}