Java BeanPropertyRowMapper類代碼示例

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<SysConfigEntity> findAll(String domainId) {
    RowMapper<SysConfigEntity> rowMapper = new BeanPropertyRowMapper<SysConfigEntity>(SysConfigEntity.class);
    List<SysConfigEntity> list = jdbcTemplate.query(batchSqlText.getSql("sys_rdbms_181"), rowMapper);
    List<SysConfigEntity> list2 = jdbcTemplate.query(batchSqlText.getSql("sys_rdbms_187"), rowMapper, domainId);
    Map<String, SysConfigEntity> map = new HashMap<>();

    for (int i = 0; i < list2.size(); i++) {
        map.put(list2.get(i).getConfigId(), list2.get(i));
    }

    for (int i = 0; i < list.size(); i++) {
        String cid = list.get(i).getConfigId();
        if (map.containsKey(cid)) {
            list.get(i).setConfigValue(map.get(cid).getConfigValue());
        }
    }
    return list;
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
/**
 * Return all status changes of issues of given project and last known issue values : due date, resolution,
 * reporter,... in type {@link IssueDetails}.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @param pkey
 *            the project 'pkey'.
 * @param authoring
 *            When <code>true</code> authors are fetched for changes.
 * @param timing
 *            When <code>true</code> time spent data is fetched.
 * @return status changes of all issues of given project.
 */
public List<JiraChangeItem> getChanges(final DataSource dataSource, final int jira, final String pkey, final boolean authoring,
		final boolean timing) {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final RowMapper<JiraChangeItem> rowMapper = new BeanPropertyRowMapper<>(JiraChangeItem.class);

	// First, get all created issues (first change)
	final List<JiraChangeItem> changes = getChanges(dataSource, jira, pkey, JiraChangeItem.class, timing, false);

	// Then add all status changes
	changes.addAll(jdbcTemplate.query(
			"SELECT i.ID AS id, cgi.OLDVALUE AS fromStatus, cgi.NEWVALUE AS toStatus, cg.CREATED AS created"
					+ (authoring ? ", cg.AUTHOR as author" : "")
					+ " FROM changeitem cgi INNER JOIN changegroup AS cg ON (cgi.groupid = cg.ID) INNER JOIN jiraissue AS i ON (cg.issueid = i.ID)"
					+ " WHERE cgi.FIELD = ? AND cgi.OLDVALUE IS NOT NULL AND cgi.NEWVALUE IS NOT NULL AND cg.CREATED IS NOT NULL AND i.PROJECT = ?",
			rowMapper, "status", jira));

	/*
	 * Then sort the result by "created" date. The previous SQL query did not used since order had to be applied to
	 * the whole collection. In addition, the result set of the previous query "should already been ordered since
	 * the natural order in this table is chronological.
	 */
	changes.sort(Comparator.comparing(IssueDetails::getCreated));
	return changes;
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
/**
 * 通過where條件查找一條記錄
 * 查找姓名為1年齡大於23的記錄  selectOneWhere("name=? and age>?", "wang",23)
 *
 * @param sqlCondition name=:1 and age=:2
 * @param values       "wang",23
 */
public E selectOneWhere(String sqlCondition, Object... values) {
    //sql
    String sql = "SELECT * FROM " + tableName + " WHERE " + sqlCondition;

    List<E> dataList = getSqlFactory().createSQL().useSql(sql)
            .varParameter(values)
            .queryList(new BeanPropertyRowMapper<>(entityClass));

    if (dataList.isEmpty()) {
        return null;
    } else if (dataList.size() == 1) {
        return dataList.get(0);
    } else {
        log.error(tableName + "#findOneWhere()返回多條數據");
        throw new RuntimeException(tableName + "#findOneWhere()返回多條數據");
    }
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<TArticle> findRandomRecommendArticleList(final int count) {
    List<Object> params = new ArrayList<Object>();
    StringBuffer sql = new StringBuffer();
    // 為了提升性能，沒有用hibernate，寫了nactiveSQL
    sql.append(" SELECT * ");
    sql.append(" FROM  ( ");
    sql.append("    SELECT DISTINCT 1 + floor(RAND() * (select max(articleno) from t_article) ) AS articleno ");
    sql.append("    FROM   (select     @num:[email protected]+1  from   t_chapter,   (select @num:=0) aa  LIMIT 50 ) g ");
    sql.append("    ) r ");
    sql.append(" JOIN   t_article USING (articleno) ");
    sql.append(" where  deleteflag = false ");
    sql.append(" AND lastupdate is not null  ");
    sql.append(" AND lastchapterno is not null ");
    sql.append("LIMIT  ?");
    params.add(count);

    return this.yiduJdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper<TArticle>(
            TArticle.class));
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<BookcaseDTO> findWithArticleInfo(BookcaseSearchBean searchBean) {
    // 初期SQL做成
    StringBuffer sql = new StringBuffer();
    sql.append("Select tb.*,ta.lastchapterno,ta.lastchapter,ta.chapters,ta.size,ta.fullflag,ta.lastupdate "
            + " ,ta.imgflag ,ta.pinyin  "
            + "      FROM t_bookcase tb                                                                     "
            + "      LEFT JOIN t_article ta ON tb.articleno = ta.articleno                                  "
            + "WHERE tb.userno= ");
    sql.append(searchBean.getUserno());
    // 添加排序信息
    if (searchBean.getPagination() != null) {
        sql.append(searchBean.getPagination().getSortInfo());
    } else {
        sql.append("ORDER BY ta.lastupdate DESC");
    }
    return yiduJdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<BookcaseDTO>(BookcaseDTO.class));
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<SubscribeDTO> findAllData(SubscribeSearchBean searchBean) {
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT ts.subscribeno, ta.* , tu.userno,tu.loginid,tu.email FROM t_subscribe ts ");
    sql.append(" join t_article ta on ts.articleno = ta.articleno ");
    sql.append(" join t_user tu on tu.userno = ts.userno ");
    sql.append(" where ta.deleteflag = false and  tu.deleteflag = false ");
    if (Utils.isDefined(searchBean.getUserno())) {
        sql.append(" AND ts.userno =  " + searchBean.getUserno());
    }

    if (Utils.isDefined(searchBean.getDateRange())) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.sss");
        sql.append(" AND ta.lastupdate >= '" + sdf.format(searchBean.getDateRange().getMinimum()) + "'");
        sql.append(" AND ta.lastupdate < '" + sdf.format(searchBean.getDateRange().getMaximum()) + "'");
    }
    sql.append(" order by ts.articleno ");

    List<Object> params = new ArrayList<Object>();
    List<SubscribeDTO> subscribeList = this.yiduJdbcTemplate.query(sql.toString(), params.toArray(),
            new BeanPropertyRowMapper<SubscribeDTO>(SubscribeDTO.class));
    return subscribeList;
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Test
public void testSpringXaTx() throws Exception {
    DataSource ds = wrap(createHsqlDataSource());
    JdbcTemplate jdbc = new JdbcTemplate(ds);
    TransactionTemplate tx = new TransactionTemplate(ptm);

    jdbc.execute(DROP_USER);
    jdbc.execute(CREATE_TABLE_USER);

    tx.execute(ts -> jdbc.update(INSERT_INTO_USER, 1, "user1"));
    User user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
    assertEquals(new User(1, "user1"), user);
    tx.execute(ts -> jdbc.update(DELETE_FROM_USER_BY_ID, 1));

    tx.execute(ts -> {
        int nb = jdbc.update(INSERT_INTO_USER, 1, "user1");
        ts.setRollbackOnly();
        return nb;
    });
    try {
        user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
        fail("Expected a EmptyResultDataAccessException");
    } catch (EmptyResultDataAccessException e) {
        // expected
    }
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Test
public void testSpringLocalTx() throws Exception {
    DataSource ds = wrap(createHsqlDataSource());
    JdbcTemplate jdbc = new JdbcTemplate(ds);
    TransactionTemplate tx = new TransactionTemplate(new DataSourceTransactionManager(ds));

    jdbc.execute(DROP_USER);
    jdbc.execute(CREATE_TABLE_USER);

    tx.execute(ts -> jdbc.update(INSERT_INTO_USER, 1, "user1"));
    User user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
    assertEquals(new User(1, "user1"), user);
    tx.execute(ts -> jdbc.update(DELETE_FROM_USER_BY_ID, 1));

    tx.execute(ts -> {
        int nb = jdbc.update(INSERT_INTO_USER, 1, "user1");
        ts.setRollbackOnly();
        return nb;
    });
    try {
        user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
        fail("Expected a EmptyResultDataAccessException");
    } catch (EmptyResultDataAccessException e) {
        // expected
    }
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Test
public void testSpringXaTx() throws Exception {
    DataSource ds = wrap(createH2DataSource());
    JdbcTemplate jdbc = new JdbcTemplate(ds);
    TransactionTemplate tx = new TransactionTemplate(ptm);

    jdbc.execute(DROP_USER);
    jdbc.execute(CREATE_TABLE_USER);

    tx.execute(ts -> jdbc.update(INSERT_INTO_USER, 1, "user1"));
    User user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
    assertEquals(new User(1, "user1"), user);
    tx.execute(ts -> jdbc.update(DELETE_FROM_USER_BY_ID, 1));

    tx.execute(ts -> {
        int nb = jdbc.update(INSERT_INTO_USER, 1, "user1");
        ts.setRollbackOnly();
        return nb;
    });
    try {
        user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
        fail("Expected a EmptyResultDataAccessException");
    } catch (EmptyResultDataAccessException e) {
        // expected
    }
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Test
public void testSpringLocalTx() throws Exception {
    DataSource ds = wrap(createH2DataSource());
    JdbcTemplate jdbc = new JdbcTemplate(ds);
    TransactionTemplate tx = new TransactionTemplate(new DataSourceTransactionManager(ds));

    jdbc.execute(DROP_USER);
    jdbc.execute(CREATE_TABLE_USER);

    tx.execute(ts -> jdbc.update(INSERT_INTO_USER, 1, "user1"));
    User user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
    assertEquals(new User(1, "user1"), user);
    tx.execute(ts -> jdbc.update(DELETE_FROM_USER_BY_ID, 1));

    tx.execute(ts -> {
        int nb = jdbc.update(INSERT_INTO_USER, 1, "user1");
        ts.setRollbackOnly();
        return nb;
    });
    try {
        user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
        fail("Expected a EmptyResultDataAccessException");
    } catch (EmptyResultDataAccessException e) {
        // expected
    }
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<TArticle> findRandomRecommendArticleList(final int count) {
    List<Object> params = new ArrayList<Object>();
    StringBuffer sql = new StringBuffer();
    // 為了提升性能，沒有用hibernate，寫了nactiveSQL
    sql.append(" SELECT * ");
    sql.append(" FROM  ( ");
    sql.append("    SELECT DISTINCT 1 + floor(random() * (select max(articleno) from t_article) )::integer AS articleno ");
    sql.append("    FROM   generate_series(1, 50) g ");
    sql.append("    ) r ");
    sql.append(" JOIN   t_article USING (articleno) ");
    sql.append(" where  deleteflag = false ");
    sql.append(" AND lastupdate is not null  ");
    sql.append(" AND lastchapterno is not null ");
    sql.append("LIMIT  ?");
    params.add(count);

    return this.yiduJdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper<TArticle>(
            TArticle.class));
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<TArticle> findRelativeArticleList(List<String> keys, String sortCol, boolean isAsc, int limitNum) {

    List<Object> params = new ArrayList<Object>();
    params.addAll(keys);
    params.add(sortCol);

    String cond = "";
    boolean isFirst = true;
    for (int i = 0; i < keys.size(); i++) {
        if (isFirst) {
            cond += " ?  % articlename ";
            isFirst = false;
        } else {
            cond += (" OR ?  % articlename ");
        }
    }
    String sql = "SELECT * FROM t_article where " + cond + "  order by ?  " + (isAsc ? "ASC" : "DESC") + " limit "
            + limitNum;
    return this.yiduJdbcTemplate.query(sql, params.toArray(), new BeanPropertyRowMapper<TArticle>(TArticle.class));
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
private Map<String, ZuulRoute> locateRoutesFromDB(){
    Map<String, ZuulRoute> routes = new LinkedHashMap<>();
    List<ZuulRouteVO> results = jdbcTemplate.query("select * from gateway_api_define where enabled = true ",new BeanPropertyRowMapper<>(ZuulRouteVO.class));
    for (ZuulRouteVO result : results) {
        if(org.apache.commons.lang3.StringUtils.isBlank(result.getPath()) /*|| org.apache.commons.lang3.StringUtils.isBlank(result.getUrl())*/ ){
            continue;
        }
        ZuulRoute zuulRoute = new ZuulRoute();
        try {
            org.springframework.beans.BeanUtils.copyProperties(result,zuulRoute);
        } catch (Exception e) {
            logger.error("=============load zuul route info from db with error==============",e);
        }
       // zuulRoute.setUrl(null);
       // zuulRoute.setRetryable(null);
        routes.put(zuulRoute.getPath(),zuulRoute);
    }
    return routes;
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
@Override
public List<BatchGroupStatusEntity> findAll(String batchId, String asOfDate) {
    RowMapper<BatchGroupStatusEntity> rowMapper = new BeanPropertyRowMapper<>(BatchGroupStatusEntity.class);
    List<BatchGroupStatusEntity> list = jdbcTemplate.query(batchSqlText.getSql("sys_rdbms_201"), rowMapper, batchId, asOfDate);
    for (BatchGroupStatusEntity bh : list) {

        Integer totalCnt = getTotalJobs(batchId, bh.getSuiteKey(), asOfDate);

        Integer completeCnt = getCompleteJobs(batchId, bh.getSuiteKey(), asOfDate);

        bh.setTotalJobsCnt(totalCnt);

        bh.setCompleteJobsCnt(completeCnt);
    }
    return list;
}
 

import org.springframework.jdbc.core.BeanPropertyRowMapper; //導入依賴的package包/類
/**
 * Return ordered custom fields by the given identifiers
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param customFields
 *            the expected custom fields identifiers.
 * @param project
 *            Jira project identifier. Required to filter custom field agains contexts.
 * @return ordered custom fields by their identifier.
 */
public Map<Integer, CustomFieldEditor> getCustomFieldsById(final DataSource dataSource, final Set<Integer> customFields, final int project) {
	if (customFields.isEmpty()) {
		// No custom field, we save an useless query
		return new HashMap<>();
	}

	// Get map as list
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final RowMapper<CustomFieldEditor> rowMapper = new BeanPropertyRowMapper<>(CustomFieldEditor.class);
	final List<CustomFieldEditor> resultList = jdbcTemplate
			.query("SELECT ID AS id, TRIM(cfname) AS name, DESCRIPTION AS description, CUSTOMFIELDTYPEKEY AS fieldType FROM customfield WHERE ID IN ("
					+ newIn(customFields) + ") ORDER BY id", rowMapper, customFields.toArray());

	// Make a Map of valid values for single/multi select values field
	final Map<Integer, CustomFieldEditor> result = new LinkedHashMap<>();
	addListToMapIdentifier(dataSource, resultList, result, project);
	return result;
}