import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
     * 查询实体
     */

    public static <T> T queryEntity(Class<T> entityClass, String sql , Object... params) {
        T entity;
        try {
            Connection connection = getConnection();
            entity = QUERY_RUNNER.query(connection,sql, new BeanHandler<T>(entityClass), params);
        }catch (SQLException e){
            LOGGER.error("query entity failure", e);
            throw  new RuntimeException(e);
        }
//        finally {
//            closeConnection();
//        }
        return  entity;
    }
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
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<Employee> resultHandler = new BeanHandler<Employee>(
				Employee.class);

		try {
			Employee emp = run.query(conn,
					"SELECT * FROM employee WHERE employeename=?",
					resultHandler, "Jose");
			System.out.println(emp.getEmployeeId());
		} finally {
			DbUtils.close(conn);
		}

	}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
@Override
public FrenteManzana findFrenteManzana(String point, Server server)
		throws Exception {
	String sql = "select nomvial as vialidad, tipovial as tipoVialidad, nomref1 as entrevialidad1, nomref2 as entrevialidad2,"
			+ " tipovr1 as tipoEntrevialidad1, tipovr2 as tipoEntrevialidad2, nombre_loc as localidad, cve_loc as cveloc,"
			+ " nombre_mun as municipio, cve_mun as cvemun, nombre_ent as entidad, nomasen as asentamiento, cp as codigoPostal,"
			+ " ST_AsText(ST_Line_Interpolate_Point(ST_LineMerge(the_geom), ST_Line_Locate_Point(ST_LineMerge(the_geom), ST_GeomFromText(?, 900913)))) as punto,"
			+ " ST_Distance(ST_GeomFromText(?, 900913), the_geom) as distance"
			+ " from localidadesmzn.frentesdemanzana_mgn"
			+ " where ST_DWithin(ST_GeomFromText(?, 900913), the_geom, 40)"
			+ " order by distance" + " limit 1";
	Connection conn = ConnectionManager.getConnectionW(server);
	ResultSetHandler<FrenteManzana> rsh = new BeanHandler<FrenteManzana>(
			FrenteManzana.class);
	QueryRunner qr = new QueryRunner();
	FrenteManzana fm = null;
	try {
		fm = qr.query(conn, sql, rsh, point, point, point);
	} finally {
		ConnectionManager.closeConnection(conn);
	}
	return fm;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
@Override
public NumeroExterior findNumeroExterior(String point, Server server)
		throws Exception {
	String sql = "select numextnum as numeroExterior, numextalf as numeroExteriorAlf, numextant as numeroAnterior,"
			+ " nomvial as vialidad, tipovial as tipoVialidad, nomref1 as entrevialidad1, nomref2 as entrevialidad2,"
			+ " tipovr1 as tipoEntrevialidad1, tipovr2 as tipoEntrevialidad2, nombre_loc as localidad, cve_loc as cveloc,"
			+ " nombre_mun as municipio, cve_mun as cvemun, nombre_ent as entidad, nomasen as asentamiento, cp as codigoPostal,"
			+ " ST_Distance(ST_GeomFromText(?, 900913), the_geom) as distance,"
			+ " ST_AsText(the_geom) as punto"
			+ " from localidadesmzn.numeroexteriorurbano"
			+ " where ST_DWithin(ST_GeomFromText(?, 900913), the_geom, 40)"
			+ " order by distance" + " limit 1";
	Connection conn = ConnectionManager.getConnectionW(server);
	ResultSetHandler<NumeroExterior> rsh = new BeanHandler<NumeroExterior>(
			NumeroExterior.class);
	QueryRunner qr = new QueryRunner();
	NumeroExterior ne = null;
	try {
		ne = qr.query(conn, sql, rsh, point, point);
	} finally {
		ConnectionManager.closeConnection(conn);
	}
	return ne;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
private Integer getTotales(Connection conn) throws SQLException {
    QueryRunner qr = new QueryRunner();
    String sqlWhere = DenueWhereCreator.getWhere(areasGeo, estratos, actividades, optParams);
    dto.setFiltro(sqlWhere.replaceAll("'", "''"));
    String sql = "select count(*) as total from "+DenueTable.getEsquema()+"."+DenueTable.getTabla()+" ";
    ParamConsultaTotal total = null;
    if (sqlWhere != null && sqlWhere.length() > 0) {
        sql = sql + " where " + sqlWhere;
        ResultSetHandler rsh = new BeanHandler(ParamConsultaTotal.class);
        total = (ParamConsultaTotal) qr.query(conn, sql, rsh);
    } else {
        total = new ParamConsultaTotal();
        total.setTotal(4374600);
    }
    return total.getTotal();
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
public void getFailedAttempts(final FinishedLensQuery query) {
  if (query != null) {
    String handle = query.getHandle();
    ResultSetHandler<List<FailedAttempt>> rsh = new BeanHandler<List<FailedAttempt>>(null) {
      @Override
      public List<FailedAttempt> handle(ResultSet rs) throws SQLException {
        List<FailedAttempt> attempts = Lists.newArrayList();
        while (rs.next()) {
          FailedAttempt attempt = new FailedAttempt(rs.getString(3), rs.getDouble(4), rs.getString(5),
            rs.getString(6), rs.getLong(7), rs.getLong(8));
          attempts.add(attempt);
        }
        return attempts;
      }
    };
    String sql = "select * from failed_attempts where handle=? order by attempt_number";
    QueryRunner runner = new QueryRunner(ds);
    try {
      query.setFailedAttempts(runner.query(sql, rsh, handle));
    } catch (SQLException e) {
      log.error("SQL exception while executing query.", e);
    }
  }
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 查询对应的实体，返回单条记录
 */
public <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
    T result;
    try {
    	result = queryRunner.query(sql, new BeanHandler<T>(entityClass), params);
    } catch (SQLException e) {
        logger.error("查询出错！");
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 查询客户实体
 * 
 * @param entityClass
 * @param sql
 * @param params
 * @return
 */
public static <T> T queryEntity(Class<T> entityClass, String sql,
		Object... params) {
	T entity;
	try {
		Connection conn = getConnection();
		entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(
				entityClass), params);
	} catch (SQLException e) {
		LOGGER.error("query entity   failure", e);
		throw new RuntimeException(e);
	}
	return entity;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 根据其真实姓名查找相应的教师职工信息
 */

@Override
public Faculty findByUsername(String fUsername) {
	try{
		Connection con = JdbcUtils.getConnection();
		String sql = "select * from tb_faculty where fUserName = ?";
		Faculty result = runner.query(con, sql, new BeanHandler<Faculty>(Faculty.class),fUsername);
		return result;
	}catch(Exception e){
		e.printStackTrace();
	}
	return null;

}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 根据教师工id查找其对应的信息
 */
@Override
public Faculty findByID(String fID) {
	try{
		Connection con = JdbcUtils.getConnection();
		String sql = "select * from tb_faculty where fID = ?";
		Faculty result = runner.query(con, sql, new BeanHandler<Faculty>(Faculty.class),fID);
		System.out.println(result.toString());
		return result;
	}catch(Exception e){
		e.printStackTrace();
	}
	return null;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 根据id查找相应的学生记录
 */
@Override
public Student findByID(String sID) {
	try{
		Connection con = JdbcUtils.getConnection();
		String sql = "select * from tb_student where sID = ?";
		Student result = runner.query(con, sql, new BeanHandler<Student>(Student.class),sID);
		System.out.println(result.toString());
		return result;
	}catch(Exception e){
		e.printStackTrace();
	}
	return null;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 根据照片路径查找相应的学生记录
 */

@Override
public String findPhotoByID(String sID) {
	try{
		Connection con = JdbcUtils.getConnection();
		String sql = "select * from tb_student where sID = ?";
		Student student = runner.query(con, sql, new BeanHandler<Student>(Student.class),sID);
		return student.getsPhotoPath();
	}catch(Exception e){
		e.printStackTrace();
	}
	return null;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
    T entity;
    try {
        Connection conn = getConnection();
        entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
    } catch (SQLException e) {
        LOGGER.error("query entity failure", e);
        throw new RuntimeException(e);
    }

    return entity;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
public static <T> T queryBean(QueryRunner runner, Class<T> cls, Map<String, String> map, String sql, Object... params) {
    T result;
    try {
        if (MapUtil.isNotEmpty(map)) {
            result = runner.query(sql, new BeanHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
        } else {
            result = runner.query(sql, new BeanHandler<T>(cls), params);
        }
        printSQL(sql);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    return result;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
@Override
public T query(String sql, Object... args) {
	Connection connection = null;

	try {
		connection = ConnectionContext.getInstance().get();
		return queryRunner.query(connection, sql, new BeanHandler<T>(clazz),args);
	} catch (Exception e) {
		e.printStackTrace();
	} 
	return null;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
/**
 * 查询实体
 */
public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params){
    T entity;
    try{
        Connection conn = getConnection();
        entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
    } catch (SQLException e){
        LOGGER.error("query entity failure", e);
        throw new RuntimeException(e);
    }
    return entity;
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
private CurrentBonusTime findCurrentBonusTime(UpdateBonusTime.Request request) throws SQLException {
    return getQueryRunner().query(
            "SELECT id, bonusTimeMax, " + getBonusTimeColumnName(request) + " AS bonusTime FROM BonusTime WHERE playerId=? AND mapId=?;",
            new BeanHandler<>(CurrentBonusTime.class),
            request.playerId,
            request.mapId);
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
@Override
public BlackMarketOffer findLatestOffer() {
    try {
        return getQueryRunner().query("SELECT id, cardId, cardType, expirationDate FROM BlackMarketOffer ORDER BY expirationDate DESC LIMIT 1;",
                new BeanHandler<>(BlackMarketOffer.class));
    } catch (SQLException e) {
        throw new GatewayError("Failed to find latest black market offer.", e);
    }
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
@Override
public VersionInfo findVersionInfo(String store) {
    try {
        return getQueryRunner().query("SELECT store, version, url, details FROM VersionInfo WHERE store=?;",
                new BeanHandler<>(VersionInfo.class),
                store);
    } catch (SQLException e) {
        throw new GatewayError("Failed to find version info for store.", e);
    }
}
 

import org.apache.commons.dbutils.handlers.BeanHandler; //导入依赖的package包/类
@Override
public Card findCard(long cardId, int cardType) {
    try {
        BeanHandler<? extends Card> beanHandler = new BeanHandler<>(getCardClass(cardType));
        return getQueryRunner().query(FIND_CARD_QUERY + getTableName(cardType) + " WHERE id=?;",
                beanHandler, cardId);
    } catch (SQLException e) {
        throw new GatewayError("Failed to find card.", e);
    }
}