Java Code Examples for org.springframework.jdbc.core.RowMapper

/**
 * Create a ReturnResultSetParameter/SqlOutParameter depending on the support provided
 * by the JDBC driver used for the database in use.
 * @param parameterName the name of the parameter (also used as the name of the List returned in the output)
 * @param rowMapper a RowMapper implementation used to map the data returned in the result set
 * @return the appropriate SqlParameter
 */
public SqlParameter createReturnResultSetParameter(String parameterName, RowMapper<?> rowMapper) {
	CallMetaDataProvider provider = obtainMetaDataProvider();
	if (provider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (provider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, provider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException(
					"Return of a ResultSet from a stored procedure is not supported");
		}
	}
}
 

@Test
public void testQueryForObjectWithParamMapAndRowMapper() throws Exception {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt(1)).willReturn(22);

	MapSqlParameterSource params = new MapSqlParameterSource();
	params.addValue("id", 3);
	Object o = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id",
			params, new RowMapper<Object>() {
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getInt(1);
		}
	});

	assertTrue("Correct result type", o instanceof Integer);
	verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?");
	verify(preparedStatement).setObject(1, 3);
}
 

public List<BranchLog> findWaitNotifyErrorLog(int commit_type) {
    return jdbcTemplate.query("select * from dts_branch_error_log where is_notify<>1 order by resource_ip",
        new Object[] {commit_type}, new RowMapper<BranchLog>() {

            @Override
            public BranchLog mapRow(ResultSet rs, int rowNum) throws SQLException {
                BranchLog log = new BranchLog();
                log.setTransId(rs.getLong("trans_id"));
                log.setState(rs.getInt("state"));
                log.setResourceIp(rs.getString("resource_ip"));
                log.setResourceInfo(rs.getString("resource_info"));
                log.setBranchId(rs.getLong("branch_id"));
                log.setGmtCreated(rs.getTimestamp("gmt_created"));
                log.setGmtModified(rs.getTimestamp("gmt_modified"));
                return log;
            }
        });
}
 

public List<GlobalLog> getGlobalLogs() {
    return jdbcTemplate.query("select * from dts_global_record", new Object[] {}, new RowMapper<GlobalLog>() {

        @Override
        public GlobalLog mapRow(ResultSet rs, int rowNum) throws SQLException {
            GlobalLog log = new GlobalLog();
            log.setTransId(rs.getLong("trans_id"));
            log.setState(rs.getInt("state"));
            log.setGmtCreated(rs.getTimestamp("gmt_created"));
            log.setGmtModified(rs.getTimestamp("gmt_modified"));
            log.setClientInfo(rs.getString("client_info"));
            log.setClientIp(rs.getString("client_ip"));
            return log;
        }
    });
}
 

@Override
public List<ExportPredef> getAllByCompany(@VelocityCheck int companyId, Collection<Integer> disabledMailingListIds) {
	if (CollectionUtils.isEmpty(disabledMailingListIds)) {
		return getAllByCompany(companyId);
	}

	String sqlGetAll = "SELECT * FROM export_predef_tbl " +
			"WHERE deleted = 0 AND company_id = ? " +
			"AND mailinglist_id NOT IN (" + StringUtils.join(disabledMailingListIds, ',') + ")";

	List<ExportPredef> profiles = new ArrayList<>();
	RowMapper<ExportPredef> rowMapper = new ExportPredefRowMapper(isOracleDB());

	query(logger, sqlGetAll, rs -> {
		if (validateMailingListIds(rs.getString("mailinglists"), disabledMailingListIds)) {
			profiles.add(rowMapper.mapRow(rs, 0));
		}
	}, companyId);

	return profiles;
}
 

private List<CompareStatRow> getResultsFromTempTable(int tempTableID, final Locale locale) throws Exception {
	String query = "SELECT category, category_index, value, rate, mailing_id, mailing_name, targetgroup_id, targetgroup, " +
               "targetgroup_index FROM " + getTempTableName(tempTableID) + " ORDER BY category_index ";
	return selectEmbedded(logger, query, new RowMapper<CompareStatRow>() {
		@Override
		public CompareStatRow mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			CompareStatRow row = new CompareStatRow();
			row.setCategory(resultSet.getString("category"));
               row.setTargetGroupName(resultSet.getString("targetgroup"));
               row.setTargetShortName(createTargetNameShort(row.getTargetGroupName()));
			row.setCategoryindex(resultSet.getInt("category_index"));
               row.setTargetGroupIndex(resultSet.getInt("targetgroup_index"));
               row.setTargetGroupId(resultSet.getInt("targetgroup_id"));
			row.setCount(resultSet.getInt("value"));
               row.setRate(resultSet.getDouble("rate"));
			row.setMailingId(resultSet.getInt("mailing_id"));
               String name = resultSet.getString("mailing_name");
               row.setMailingNameFull(name);
               row.setMailingName(name);
			return row;
		}
	});
}
 

@Override
public List<ActionOperationUpdateCustomerParameters> getByTrackingPointId(int trackingPointId) {
	return select(logger,
		"SELECT ao.action_operation_id, company_id, column_name, update_type, update_value, trackpoint_id, company_id FROM actop_update_customer_tbl uu INNER JOIN actop_tbl ao ON uu.action_operation_id = ao.action_operation_id WHERE trackpoint_id = ?",
		new RowMapper<ActionOperationUpdateCustomerParameters>() {
			@Override
			public ActionOperationUpdateCustomerParameters mapRow(ResultSet row, int rowNum) throws SQLException {
				ActionOperationUpdateCustomerParameters operation = new ActionOperationUpdateCustomerParameters();
				operation.setId(row.getInt("action_operation_id"));
				operation.setCompanyId(row.getInt("company_id"));
				operation.setColumnName(row.getString("column_name"));
				operation.setUpdateType(row.getInt("update_type"));
				operation.setUpdateValue(row.getString("update_value"));
				Number tpId = row.getInt("trackpoint_id");
				operation.setTrackingPointId(tpId == null ? 0 : tpId.intValue());
				operation.setUseTrack(tpId != null);
				return operation;
			}
		}, trackingPointId);
}
 

@Override
protected void processGetOperation(ActionOperationSubscribeCustomerParameters operation) {
	Map<String, Object> row = selectObject(logger, "select double_check, key_column, double_opt_in from actop_subscribe_customer_tbl where action_operation_id=?", new RowMapper<Map<String, Object>>() {
		@Override
		public Map<String, Object> mapRow(ResultSet resultSet, int rowIndex) throws SQLException {
			Map<String, Object> map = new HashMap<>(3);
			map.put("double_check", resultSet.getBoolean("double_check"));
			map.put("key_column", resultSet.getString("key_column"));
			map.put("double_opt_in", resultSet.getBoolean("double_opt_in"));
			return map;
		}
	}, operation.getId());
	operation.setDoubleCheck((Boolean) row.get("double_check"));
	operation.setKeyColumn((String) row.get("key_column"));
	operation.setDoubleOptIn((Boolean) row.get("double_opt_in"));
}
 

/**
 * Create a ReturnResultSetParameter/SqlOutParameter depending on the support provided
 * by the JDBC driver used for the database in use.
 * @param parameterName the name of the parameter (also used as the name of the List returned in the output)
 * @param rowMapper a RowMapper implementation used to map the data returned in the result set
 * @return the appropriate SqlParameter
 */
public SqlParameter createReturnResultSetParameter(String parameterName, RowMapper<?> rowMapper) {
	CallMetaDataProvider provider = obtainMetaDataProvider();
	if (provider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (provider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, provider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException(
					"Return of a ResultSet from a stored procedure is not supported");
		}
	}
}
 

@Test
public void testQueryForObjectWithParamMapAndRowMapper() throws Exception {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt(1)).willReturn(22);

	MapSqlParameterSource params = new MapSqlParameterSource();
	params.addValue("id", 3);
	Object o = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id",
			params, new RowMapper<Object>() {
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getInt(1);
		}
	});

	assertTrue("Correct result type", o instanceof Integer);
	verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?");
	verify(preparedStatement).setObject(1, 3);
}
 

@Override
public List<PropertiesEntry> selectByRef(String key, final Reference ref) {
    return jdbcTemplate.query(SELECT_BY_REF_SQL,
                              new Object[]{key, ref.getRefGroup(), ref.getRefProfile(), ref.getRefDataId()},
                              new RowMapper<PropertiesEntry>() {
                                  @Override
                                  public PropertiesEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
                                      return new PropertiesEntry(rs.getString("key"),
                                                                 ref.getGroup(),
                                                                 ref.getProfile(),
                                                                 ref.getAlias(),
                                                                 rs.getLong("version"),
                                                                 rs.getString("value"));
                                  }
                              });
}
 

@GetMapping("/_useSpringJdbc_rowMapper")
public PagingResult list_useSpringJdbc_rowMapper(
        @RequestParam(name = "pageNo", required = false) Integer pageNo,
        @RequestParam(name = "pageSize", required = false) Integer pageSize,
        @RequestParam(name = "sort", required = false) String sort,
        @RequestParam(name = "count", required = false) boolean count,
        @RequestParam(name = "useLastPageIfPageOut", required = false) boolean useLastPageIfPageOut,
        @RequestParam(name = "testSubquery", required = false, defaultValue = "false") boolean testSubquery) {
    PagingRequest request = SqlPaginations.preparePagination(pageNo == null ? 1 : pageNo, pageSize == null ? -1 : pageSize, sort);
    request.setCount(count);
    request.setUseLastPageIfPageOut(useLastPageIfPageOut);
    if (testSubquery) {
        request.subqueryPaging(true);
    }
    StringBuilder sqlBuilder = testSubquery ? new StringBuilder("select * from ([PAGING_START]select ID, NAME, AGE from USER where 1=1 and age > 10[PAGING_END]) n where name like 'zhangsan%' ") : new StringBuilder("select ID, NAME, AGE from USER where 1=1 and age > 10");
    List<User> users = jdbcTemplate.query(sqlBuilder.toString(), new RowMapper<User>() {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User u = new User();
            u.setId(rs.getString("ID"));
            u.setName(rs.getString("NAME"));
            u.setAge(rs.getInt("AGE"));
            return u;
        }
    });
    String json = JSONBuilderProvider.simplest().toJson(users);
    System.out.println(json);
    return request.getResult();
}
 

@GetMapping("/_useSpringJdbcNamedTemplate")
public PagingResult list_useSpringJdbcNamedTemplate(
        @RequestParam(name = "pageNo", required = false) Integer pageNo,
        @RequestParam(name = "pageSize", required = false) Integer pageSize,
        @RequestParam(name = "sort", required = false) String sort,
        @RequestParam(name = "testSubquery", required = false, defaultValue = "false") boolean testSubquery) {
    PagingRequest request = SqlPaginations.preparePagination(pageNo == null ? 1 : pageNo, pageSize == null ? -1 : pageSize, sort);
    if (testSubquery) {
        request.subqueryPaging(true);
    }
    StringBuilder sqlBuilder = testSubquery ? new StringBuilder("select * from ([PAGING_START]select ID, NAME, AGE from USER where 1=1 and age > :age [PAGING_END]) n where name like CONCAT(:name,'%') ") : new StringBuilder("select ID, NAME, AGE from USER where 1=1 and age > :age");

    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("age", 10);
    paramMap.put("name", "zhangsan");

    List<User> users = namedJdbcTemplate.query(sqlBuilder.toString(), paramMap, new RowMapper<User>() {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User u = new User();
            u.setId(rs.getString("ID"));
            u.setName(rs.getString("NAME"));
            u.setAge(rs.getInt("AGE"));
            return u;
        }
    });
    String json = JSONBuilderProvider.simplest().toJson(users);
    System.out.println(json);
    return request.getResult();
}
 

@Override
@SuppressWarnings("unchecked")
protected RowMapper<T> newRowMapper(@Nullable Object[] parameters, @Nullable Map<?, ?> context) {
	if (this.rowMapper != null) {
		return this.rowMapper;
	}
	else {
		Assert.state(this.rowMapperClass != null, "No RowMapper set");
		return BeanUtils.instantiateClass(this.rowMapperClass);
	}
}
 

@Override
@Nullable
public <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
		throws DataAccessException {

	List<T> results = getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rowMapper);
	return DataAccessUtils.nullableSingleResult(results);
}
 

@Override
@Nullable
public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T>rowMapper)
		throws DataAccessException {

	return queryForObject(sql, new MapSqlParameterSource(paramMap), rowMapper);
}
 

/**
 * Add a {@link org.springframework.jdbc.core.RowMapper} for the specified parameter or column.
 * @param parameterName name of parameter or column
 * @param rowMapper the RowMapper implementation to use
 */
public void addDeclaredRowMapper(String parameterName, RowMapper<?> rowMapper) {
	this.declaredRowMappers.put(parameterName, rowMapper);
	if (logger.isDebugEnabled()) {
		logger.debug("Added row mapper for [" + getProcedureName() + "]: " + parameterName);
	}
}
 

public List<BranchLog> getBranchLogs() {
    return jdbcTemplate.query("select * from dts_branch_record", new Object[] {}, new RowMapper<BranchLog>() {

        @Override
        public BranchLog mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rowToObject(rs);
        }
    });
}
 

public List<BranchLog> getBranchLogs(long transId) {
    return jdbcTemplate.query("select * from dts_branch_record where trans_id = ?", new Object[] {transId},
        new RowMapper<BranchLog>() {

            @Override
            public BranchLog mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rowToObject(rs);
            }
        });
}
 

public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
    List<T> results = Lists.newArrayList();
    for (JdbcTemplate jdbcTemplate : jdbcTemplates.values()) {
        results.addAll(jdbcTemplate.query(sql, rowMapper, args));
    }

    return results;
}
 

@Override
protected void processGetOperation(ActionOperationGetCustomerParameters operation) {
	Map<String, Object> row = selectObject(logger, "select load_always from actop_get_customer_tbl where action_operation_id=?", new RowMapper<Map<String, Object>>() {
		@Override
		public Map<String, Object> mapRow(ResultSet resultSet, int rowIndex) throws SQLException {
			Map<String, Object> map = new HashMap<>(1);
			map.put("load_always", resultSet.getBoolean("load_always"));
			return map;
		}
	}, operation.getId());
	operation.setLoadAlways((Boolean) row.get("load_always"));
}
 

private <T> List<T> selectPage(String group, String profile,List<StatusType> statusTypes, int start, int pageSize, String format, RowMapper<T> mapper) {
    if (statusTypes == null || statusTypes.isEmpty()) {
        return Lists.newArrayList();
    }

    String sql = String.format(format, SQLUtil.generateQuestionMarks(statusTypes.size()));
    List<Object> params = buildSelectParamsPage(group, profile,start, pageSize, statusTypes);
    return jdbcTemplate.query(sql, mapper, params.toArray());
}
 

@Override
public List<Map<String, InputStream>> findImageByBookId(Long bookId) {

    List<Map<String, InputStream>> result = jdbcTemplate.query(
            "select id, book_id, filename, blob_image from book_image where book_id = ?",
            new Object[]{bookId},
            new RowMapper<Map<String, InputStream>>() {
                public Map<String, InputStream> mapRow(ResultSet rs, int i) throws SQLException {

                    String fileName = rs.getString("filename");
                    InputStream blob_image_stream = lobHandler.getBlobAsBinaryStream(rs, "blob_image");

                    // byte array
                    //Map<String, Object> results = new HashMap<>();
                    //byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "blob_image");
                    //results.put("BLOB", blobBytes);

                    Map<String, InputStream> results = new HashMap<>();
                    results.put(fileName, blob_image_stream);

                    return results;

                }
            });

    return result;
}
 

/**
 * Creates a new {@link OneToManyResultSetExtractor} from the given
 * {@link RowMapper}s and {@link ExpectedResults}.
 *
 * @param rootMapper      {@link RowMapper} to map the root entity, must not be
 *                        {@literal null}.
 * @param childMapper     {@link RowMapper} to map the root entities, must not
 *                        be {@literal null}.
 * @param expectedResults
 */
public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper,
        ExpectedResults expectedResults) {

    Assert.notNull(rootMapper, "Root RowMapper must not be null!");
    Assert.notNull(childMapper, "Child RowMapper must not be null!");

    this.childMapper = childMapper;
    this.rootMapper = rootMapper;
    this.expectedResults = expectedResults == null ? ExpectedResults.ANY : expectedResults;
}
 

/**
 * 通用根据主键查询单条记录
 *
 * @param pk 主键
 * @return 单条记录
 */
public T findOneById(P pk) {
	String tableName = getTableName();
	String sql = StrUtil.format("SELECT * FROM {table} where id = ?", Dict.create().set("table", tableName));
	RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(clazz);
	log.debug("【执行SQL】SQL：{}", sql);
	log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
	return jdbcTemplate.queryForObject(sql, new Object[]{pk}, rowMapper);
}
 

@Test
public void shouldBeEmbeddedDatasource() {
	assertNotNull(dataSource);
	
	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	List<String> results = jdbcTemplate.query("SELECT t.id,t.name From Things t", new RowMapper<String>() {
		@Override
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getLong("id") + ":" + rs.getString("name");
		}
	});
	assertEquals(1, results.size());
	assertEquals("1:A", results.get(0));
}
 

@Test
public void shouldBeEmbeddedDatasource() {
	assertNotNull(dataSource);
	
	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	List<String> results = jdbcTemplate.query("SELECT t.id,t.name From Things t", new RowMapper<String>() {
		@Override
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getLong("id") + ":" + rs.getString("name");
		}
	});
	assertEquals(1, results.size());
	assertEquals("1:A", results.get(0));
}
 

public <T> Map<DbEnv, List<T>> queryOutDbEnv(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
    Map<DbEnv, List<T>> results = Maps.newHashMapWithExpectedSize(jdbcTemplates.size());
    for (Map.Entry<DbEnv, JdbcTemplate> entry : jdbcTemplates.entrySet()) {
        List<T> lst = entry.getValue().query(sql, rowMapper, args);
        results.put(entry.getKey(), lst);
    }

    return results;
}
 

@Override
@SuppressWarnings("unchecked")
protected RowMapper<T> newRowMapper(@Nullable Object[] parameters, @Nullable Map<?, ?> context) {
	if (this.rowMapper != null) {
		return this.rowMapper;
	}
	else {
		Assert.state(this.rowMapperClass != null, "No RowMapper set");
		return BeanUtils.instantiateClass(this.rowMapperClass);
	}
}
 

@Override
@Nullable
public <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
		throws DataAccessException {

	List<T> results = getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rowMapper);
	return DataAccessUtils.nullableSingleResult(results);
}