是否可以在不为每个列调用getInt(...)getString(...)的情况下检索整行?
我有多个线程,每个线程都需要将结果写入某些线程安全的集合。
我希望能够直接将行写入此集合,并在此之后解析该集合的成员并根据列类型检索值。
是否可以在不为每个列调用getInt(...)getString(...)的情况下检索整行?
我有多个线程,每个线程都需要将结果写入某些线程安全的集合。
我希望能够直接将行写入此集合,并在此之后解析该集合的成员并根据列类型检索值。
class Row
{
public Map <Object,Class> row;
public static Map <String, Class> TYPE;
static
{
TYPE = new HashMap<String, Class>();
TYPE.put("INTEGER", Integer.class);
TYPE.put("TINYINT", Byte.class);
TYPE.put("SMALLINT", Short.class);
TYPE.put("BIGINT", Long.class);
TYPE.put("REAL", Float.class);
TYPE.put("FLOAT", Double.class);
TYPE.put("DOUBLE", Double.class);
TYPE.put("DECIMAL", BigDecimal.class);
TYPE.put("NUMERIC", BigDecimal.class);
TYPE.put("BOOLEAN", Boolean.class);
TYPE.put("CHAR", String.class);
TYPE.put("VARCHAR", String.class);
TYPE.put("LONGVARCHAR", String.class);
TYPE.put("DATE", Date.class);
TYPE.put("TIME", Time.class);
TYPE.put("TIMESTAMP", Timestamp.class);
// ...
}
public Row ()
{
row = new HashMap<Object,Class>();
}
public <T> void add (T data)
{
row.put(data, data.getClass());
}
public void add (Object data, String sqlType)
{
add((Row.TYPE.get(sqlType)) data);
}
public static void formTable (ResultSet rs, ArrayList<Row> table)
{
if (rs == null) return;
ResultSetMetaData rsmd = rs.getMetaData();
int NumOfCol = rsmd.getColumnCount();
while (rs.next())
{
row = new Row ();
for(int i = 1; i <= NumOfCol; i++)
{
row.add(rs.getObject(i), rsmd.getColumnTypeName(i));
}
table.add(row);
}
}
}
List<Row> table = new ArrayList<Row>();
Row row = null;
ResultSet rs = st.executeQuery("SELECT * FROM table_name");
Row.formTable(rs, table);
然后,您可以检索字段并将它们转换为相应的数据类型:
for (Row row : table)
{
for (Object data : row.row.getKeySet())
{
System.out.print(" > " + ((row.row.get(data) data));
}
System.out.println();
}
以列表形式表示的行:
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
* @author Adam Dziedzic
*
*/
public class Row {
public List<Entry<Object, Class>> row;
public static Map<String, Class> TYPE;
static {
TYPE = new HashMap<String, Class>();
TYPE.put("INTEGER", Integer.class);
TYPE.put("TINYINT", Byte.class);
TYPE.put("SMALLINT", Short.class);
TYPE.put("BIGINT", Long.class);
TYPE.put("REAL", Float.class);
TYPE.put("FLOAT", Double.class);
TYPE.put("DOUBLE", Double.class);
TYPE.put("DECIMAL", BigDecimal.class);
TYPE.put("NUMERIC", BigDecimal.class);
TYPE.put("BOOLEAN", Boolean.class);
TYPE.put("CHAR", String.class);
TYPE.put("VARCHAR", String.class);
TYPE.put("LONGVARCHAR", String.class);
TYPE.put("DATE", Date.class);
TYPE.put("TIME", Time.class);
TYPE.put("TIMESTAMP", Timestamp.class);
TYPE.put("SERIAL",Integer.class);
// ...
}
public Row() {
row = new ArrayList<Entry<Object, Class>>();
}
public <T> void add(T data) {
row.add(new AbstractMap.SimpleImmutableEntry<Object,Class>(data, data.getClass()));
}
public void add(Object data, String sqlType) {
Class castType = Row.TYPE.get(sqlType.toUpperCase());
try {
this.add(castType.cast(data));
} catch (NullPointerException e) {
e.printStackTrace();
Logger lgr = Logger.getLogger(Row.class.getName());
lgr.log(Level.SEVERE, e.getMessage()+" Add the type "+sqlType+" to the TYPE hash map in the Row class.", e);
throw e;
}
}
public static void formTable(ResultSet rs, List<Row> table)
throws SQLException {
if (rs == null)
return;
ResultSetMetaData rsmd;
try {
rsmd = rs.getMetaData();
int NumOfCol = rsmd.getColumnCount();
while (rs.next()) {
Row current_row = new Row();
for (int i = 1; i <= NumOfCol; i++) {
current_row.add(rs.getObject(i), rsmd.getColumnTypeName(i));
}
table.add(current_row);
}
} catch (SQLException e) {
throw e;
}
}
}
使用方法:
List<Row> table = new ArrayList<Row>();
ResultSet rs = st.executeQuery("SELECT * FROM table_name");
Row.formTable(rs, table);
for (Row row : table)
{
for (Entry<Object, Class> col: row.row)
{
System.out.print(" > " + ((col.getValue()).cast(col.getKey())));
}
System.out.println();
}
以下是一个示例,使用查询来计算查询的大小,然后将处理拆分为多个线程。
我使用MySQL,因此这些查询是针对该数据库引擎编写的。您需要根据自己的数据库引擎更改查询。
public static void main(String[] args) throws Exception {
final int count;
try (final Connection conn = DATA_SOURCE.getConnection()) {
final String countQuery = "SELECT COUNT(*) FROM my_table";
try (final PreparedStatement ps = conn.prepareStatement(countQuery);
final ResultSet resultSet = ps.executeQuery()) {
resultSet.next();
count = resultSet.getInt(1);
}
}
final int chunksize = 1000;
final Queue<SqlResult> results = new ConcurrentLinkedQueue<>();
final ExecutorService es = Executors.newFixedThreadPool(10);
for (int end = 0; end < count; end += chunksize) {
es.execute(new ResultReader(count, end, DATA_SOURCE, results));
}
}
private static class ResultReader implements Runnable {
private final int start;
private final int size;
private final DataSource dataSource;
private final Queue<SqlResult> results;
public ResultReader(int start, int size, DataSource dataSource, Queue<SqlResult> results) {
this.start = start;
this.size = size;
this.dataSource = dataSource;
this.results = results;
}
@Override
public void run() {
try (final Connection connection = dataSource.getConnection()) {
final String query = "SELECT id, something, somethingElse FROM my_table LIMIT ?, ?";
try (final PreparedStatement ps = connection.prepareStatement(query)) {
ps.setInt(1, start);
ps.setInt(2, size);
try (final ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
final SqlResult sqlResult = new SqlResult();
sqlResult.setId(rs.getInt("id"));
sqlResult.setSomething(rs.getString("something"));
sqlResult.setSomethingElse(rs.getString("somethingElse"));
results.add(sqlResult);
}
}
}
} catch (SQLException ex) {
Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
private static class SqlResult {
private int id;
private String something;
private String somethingElse;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSomething() {
return something;
}
public void setSomething(String something) {
this.something = something;
}
public String getSomethingElse() {
return somethingElse;
}
public void setSomethingElse(String somethingElse) {
this.somethingElse = somethingElse;
}
}
DataSource
实现了连接池。每个工作线程都使用 LIMIT
运行查询,并将结果处理成 SqlResult
对象,然后将它们添加到并发的 Queue
中。我使用了adam.cajf的答案,非常有用,但是我需要添加一行代码来添加函数,否则在我的特定数据集中会出现错误。
if (data != null) {
不行。
根据Java文档,没有一种方法可以让您在不指定列标签或索引的情况下检索结果。
https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html