Java Code Examples for org.springframework.jdbc.core.JdbcTemplate#query()

@Test
public void test_设多个i分流() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi0, ds1:ri0, ds2:ri1, ds3:ri1"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds0", "select") || MockDataSource.hasTrace("", "ds1", "select"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds2", "select") || MockDataSource.hasTrace("", "ds3", "select"));
}
 

@Deprecated
public byte[] getMessage(long id) {
    String customSelect = "select LONG_MESSAGE from COP_AUDIT_TRAIL_EVENT where SEQ_ID = ? ";

    ResultSetExtractor<byte[]> rse = new ResultSetExtractor<byte[]>() {

        @Override
        public byte[] extractData(ResultSet rs) throws SQLException,
                DataAccessException {
            rs.next();
            return convertToArray(rs.getBinaryStream("LONG_MESSAGE"));
        }

    };

    JdbcTemplate jdbcTemplate = getJdbcTemplate();
    return (jdbcTemplate != null) ? jdbcTemplate.query(customSelect, rse, new Object[] { id }) : null;
}
 

public String getMessageString(long id) {
    if (messagePostProcessor == null) {
        throw new NullPointerException("Message Post Processor is not set. use byte[] getMessage(long id) method or set Message Post Processor");
    }

    String customSelect = "select LONG_MESSAGE from COP_AUDIT_TRAIL_EVENT where SEQ_ID = ? ";
    ResultSetExtractor<String> rse = new ResultSetExtractor<String>() {

        @Override
        public String extractData(ResultSet rs) throws SQLException,
                DataAccessException {
            rs.next();
            Clob message = rs.getClob("LONG_MESSAGE");

            if ((int) message.length() == 0) {
                return null;
            }

            return messagePostProcessor.deserialize(message.getSubString(1, (int) message.length()));
        }
    };

    JdbcTemplate jdbcTemplate = getJdbcTemplate();

    return (jdbcTemplate != null) ? jdbcTemplate.query(customSelect, rse, new Object[] { id }) : null;
}
 

@Test
public void test_1个ds设多个i() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi0, ds1:ri0i1, ds2:ri1, ds3:r, ds4:ri3"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds0", "select") || MockDataSource.hasTrace("", "ds1", "select"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds1", "select") || MockDataSource.hasTrace("", "ds2", "select"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(3);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds3", "select") || MockDataSource.hasTrace("", "ds4", "select"));
}
 

@SuppressWarnings({ "rawtypes", "unchecked" })
@Override
public List query(String sql, Object[] args, RowMapper rowMapper, Integer maxRows) throws PersistenceException {
	List list = new ArrayList();
	try {
		DataSource dataSource = (DataSource) Context.get().getBean("DataSource");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		if (maxRows != null)
			jdbcTemplate.setMaxRows(maxRows);
		if (args != null)
			list = jdbcTemplate.query(insertTopClause(sql, maxRows), args, rowMapper);
		else
			list = jdbcTemplate.query(insertTopClause(sql, maxRows), rowMapper);
		return list;
	} catch (Throwable e) {
		throw new PersistenceException(e);
	}

}
 

protected List<Recipient> getRecipientList(int companyID, String statement, Object[] parameters, boolean useUnsharpRecipientQuery) throws SQLException {
	try (Connection connection = getDataSource().getConnection()) {
		try {
			// TODO: IGNORE_BOUNCELOAD_COMPANY_ID is a bad hack for CONRAD-371!!!
			if (useUnsharpRecipientQuery) {
				setRuleOptimizerMode(connection, true);
			}
			
			final SingleConnectionDataSource scds = new SingleConnectionDataSource(connection, true);
			final JdbcTemplate template = new JdbcTemplate(scds);
			RecipientRowMapper rowMapper = new RecipientRowMapper(recipientFactory, companyID);
			
			return template.query(statement, parameters, rowMapper);
			
		} finally {
			// TODO: IGNORE_BOUNCELOAD_COMPANY_ID is a bad hack for CONRAD-371!!!
			if (useUnsharpRecipientQuery) {
				setRuleOptimizerMode(connection, false);
			}
		}
	}
}
 

@Test
public void test_设多个i分流() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi0, ds1:ri0, ds2:ri1, ds3:ri1"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds0", "select") || MockDataSource.hasTrace("", "ds1", "select"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds2", "select") || MockDataSource.hasTrace("", "ds3", "select"));
}
 

/**
 * !!! Only supports MySQL
 */
@SuppressWarnings("unchecked")
public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern) {
    try {
        if (StringUtils.isEmpty(schemaPattern)) {
            return jdbcTemplate.query("show databases", new SingleColumnRowMapper(String.class));
        }
        return jdbcTemplate.query("show databases like ?",
                new Object[]{schemaPattern},
                new SingleColumnRowMapper(String.class));
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return new ArrayList<String>();
    }
}
 

/**
 * 取分页
 * 
 * @param jt
 *            jdbcTemplate
 * @param sqlCountRows
 *            查询总数的SQL
 * @param sqlFetchRows
 *            查询数据的sql
 * @param args
 *            查询参数
 * @param pageNo
 *            页数
 * @param pageSize
 *            每页大小
 * @param rowMapper
 * @return
 */
public Page<E> fetchPage(final JdbcTemplate jt, final String sqlCountRows, final String sqlFetchRows,
        final Object args[], final int pageNo, final int pageSize, final ParameterizedRowMapper<E> rowMapper) {
    if (pageSize == 0) {
        return null;
    }

    // 查询当前记录总数
    final int rowCount = jt.queryForInt(sqlCountRows, args);

    // 计算页数
    int pageCount = rowCount / pageSize;
    if (rowCount > pageSize * pageCount) {
        pageCount++;
    }

    // 创建Page对象
    final Page<E> page = new Page<E>();
    page.setPageNumber(pageNo);
    page.setPagesAvailable(pageCount);
    page.setTotalCount(rowCount);

    if (pageNo > pageCount)
        return null;
    // 取单页数据，计算起始位置
    final int startRow = (pageNo - 1) * pageSize;
    // TODO 在数据量很大时， limit效率很低
    final String selectSQL = sqlFetchRows + " limit " + startRow + "," + pageSize;
    jt.query(selectSQL, args, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final List<E> pageItems = page.getPageItems();
            int currentRow = 0;
            while (rs.next()) {
                pageItems.add(rowMapper.mapRow(rs, currentRow++));
            }
            return page;
        }
    });
    return page;
}
 

@SuppressWarnings("unchecked")
@Override
      public T call() throws Exception {
      	JdbcTemplate jdbcTemplate = createJdbcTemplate(shard.getTargetDataSource(), ShardJdbcTemplate.this);
          String interceptedSql = shardDataSource.getSqlInterceptor().intercept(sql, shard.getTableContext());

          return (T) jdbcTemplate.query(interceptedSql, args, extractor);
      }
 

/**
 * Transfer the resources from the source file system handler to the
 * destination.
 */
public void convertStorage() {
    log.info("Start converting storage....");
    setupDataSource();
    if (sourceFileSystemHandler == null) {
        throw new IllegalStateException("The source FileSystemHandler must be set!");
    }
    if (destinationFileSystemHandler == null) {
        throw new IllegalStateException("The destination FileSystemHandler must be set!");
    }
    final AtomicInteger counter = new AtomicInteger(0);
    // read content_resource records that have null file path
    JdbcTemplate template = new JdbcTemplate(dataSource);
    template.query(contentSql, new RowCallbackHandler() {
        public void processRow(ResultSet resultSet) throws SQLException {
            counter.incrementAndGet();
            String id = resultSet.getString(1);
            String path = resultSet.getString(2);
            try {
                InputStream input = sourceFileSystemHandler.getInputStream(id, sourceBodyPath, path);
                if (input != null) {
                    destinationFileSystemHandler.saveInputStream(id, destinationBodyPath, path, input);
                }
                if (deleteFromSource) {
                    sourceFileSystemHandler.delete(id, sourceBodyPath, path);
                }
            } catch (IOException e) {
                if (ignoreMissing) {
                    log.info("Missing file: " + id);
                } else {
                    log.error("Failed to read or write resources from or to the FileSystemHandlers", e);
                    throw new SQLException("Failed to read or write resources from or to the FileSystemHandlers", e);
                }
            }
        }
    });
    log.info("Converted " + counter.get() + " records....");
    log.info("Finished converting storage....");
}
 

/**
 * 以流方式获得blog,image等大数据
 * 
 * @param id
 *            字段主键
 * @param tableName
 *            表名
 * @param ColumnName
 *            字段名
 * @param jdbcTemplate
 */
public static String getBlob(String id, String tableName, final String columnName, JdbcTemplate jdbcTemplate) {
	String ls_sql = "select " + columnName + " from " + tableName + " where id='" + id + "'";

	// 查询并获得输入流
	jdbcTemplate.query(ls_sql, new RowCallbackHandler() {
		
		public void processRow(ResultSet rs) throws SQLException {
			inStream = rs.getBinaryStream(columnName);
		}
	});

	// 读取流数据并转换成16进制字符串
	if (inStream != null) {
		StringBuffer readInBuffer = new StringBuffer();
		readInBuffer.append("0x");
		byte[] b = new byte[4096];
		try {

			for (; (inStream.read(b)) != -1;) {
				readInBuffer.append(byte2HexStr(b));
			}
		} catch (IOException e) {

			e.printStackTrace();
		}
		String ls_return = readInBuffer.toString().trim();
		if ("0x".equals(ls_return)) {
			ls_return = ls_return + "00";
		}
		return ls_return;
	} else {
		return "0x00";
	}
}
 

public <T> T query(String sql, ResultSetExtractor<T> extractor, Object...args) throws DataAccessException {
    T result = null;
    for (JdbcTemplate jdbcTemplate : jdbcTemplates.values()) {
        result = jdbcTemplate.query(sql, extractor, args);
        if (result != null) {
            break;
        }
    }
    return result;
}
 

public List<QueryVersion> findAll() {
    String sql = "SELECT * FROM query_versions";

    jdbcTemplate = new JdbcTemplate(dataSource);
    List<QueryVersion> queryVersions =
            jdbcTemplate.query(sql, new QueryVersionMapper());

    return queryVersions;
}
 

private <T> List<T> doSelectWithTemplate(JdbcTemplate jdbcTemplate, String sql, RowMapper<T> rowMapper, Object... params) {
    logSQL(sql,params);
    try {
        long t = System.currentTimeMillis();
        List<T> result = jdbcTemplate.query(sql, params, rowMapper);
        if (logger.isDebugEnabled())
            logger.debug("Fetched result: " + result.size() + '/' + (System.currentTimeMillis() - t) + " rows/ms");
        return result;
    } catch (RuntimeException ex) {
        logger.error("Failed to execute query: " + sql, ex);
        throw ex;
    }
}
 

public List<DatabaseConnection> findAll() {
    String sql = "SELECT * FROM database_connections";

    jdbcTemplate = new JdbcTemplate(dataSource);
    List<DatabaseConnection> databaseConnections =
            jdbcTemplate.query(sql, new DatabaseConnectionMapper());

    return databaseConnections;
}
 

@Test
public void test_设单个i不加数字等同于没设() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi, ds1:ri, ds2:ri, ds3:ri"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds1", "select 1 from dual"));
}
 

public List<QueryVersion> findPreviousVersionsByQueryId(Integer id) {
    String sql = "SELECT * FROM query_versions WHERE query_id = ? AND is_current_version = FALSE ORDER BY ID DESC";

    jdbcTemplate = new JdbcTemplate(dataSource);
    List<QueryVersion> queryVersions =
            jdbcTemplate.query(sql, new Object[] { id }, new QueryVersionMapper());

    return queryVersions;
}
 

/**
 * {@link JdbcTemplate#query(String, ResultSetExtractor)}
 */
@Override
public <T> T query(JdbcTemplate jdbcTemplate, String sql, ResultSetExtractor<T> resultSetExtractor)
{
    return jdbcTemplate.query(sql, resultSetExtractor);
}
 

private static final List<TargetGroupData> listTargetGroups(final int companyId, final DataSource dataSource) {
	final String sql = "SELECT target_id, target_representation FROM dyn_target_tbl WHERE company_id = ? AND (locked IS NULL OR locked = 0) AND (eql IS NULL OR eql = '') AND target_representation IS NOT NULL";
	
	final JdbcTemplate template = new JdbcTemplate(dataSource);

	return template.query(sql, new TargetGroupDataRowMapper(), companyId);
}