JOINした際に、ResultSet で、同一名の項目を扱うには...
* 項目に対して、「AS」を付けた方がいい。
SELECT w.id AS workerId, w.name AS workername, ...
* めんどいかもしれないけど、以下のURLにもあるが、それ以外に区別できない。
http://atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=23406&forum=12
【ダメな例】以下のように「*」を使うと...
"SELECT * FROM worker AS w"
+ " INNER JOIN company AS c ON w.companyid = c.id"
+ " WHERE (w.sex = ?) IS NOT FALSE order by w.id"
ResultSetMetaData で項目名を出力した際に、以下のように区別でなくなる
id
name
sex
updatedate
companyid
id -- ★同じ名前★
name -- ★同じ名前★
updatedate -- ★同じ名前★
注意
* 「*」だけじゃなくても、「w.id」のような指定でも上記と同じ結果になる。
【ダメな例２】
SELECT w.id, w.name, ...
使用するテーブル
worker
CREATE TABLE worker
(
id character(8) NOT NULL,
name character varying(100),
sex character(1),
companyid character(8),
updatedate timestamp without time zone,
CONSTRAINT worker_pkey PRIMARY KEY (id)
)
INSERT INTO worker(
id, name, sex, companyid, updatedate)
VALUES
('X0000001', 'Mike', 'm', 'C0000001', current_date),
('X0000002', 'Tom', 'm', 'C0000002', current_date),
('X0000003', 'Kevin', 'm', 'C0000003', current_date),
('X0000004', 'Naomi', 'f', 'C0000002', current_date),
('X0000005', 'Meg', 'f', 'C0000003', current_date),
('X0000006', 'Ken', 'm', 'C0000004', current_date);
company
CREATE TABLE company
(
id character(8) NOT NULL,
name character varying(100),
updatedate timestamp without time zone,
CONSTRAINT company_pkey PRIMARY KEY (id)
)
INSERT INTO company(
id, name, updatedate)
VALUES
('C0000001', 'Mitsuboshi', current_date),
('C0000002', 'Hanabishi', current_date),
('C0000003', 'SQNY', current_date),
('C0000004', 'Naiwa', current_date);
サンプル
SamplePreparedStatementForJoinTables.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SamplePreparedStatementForJoinTables {
public static void main(String[] args) {
try {
System.out.println("Start");
SamplePreparedStatementForJoinTables.dbAccess();
} catch (Exception e) {
e.printStackTrace();
} finally {
System.out.println("Finished");
}
}
private static void dbAccess() throws Exception {
try {
Class.forName("org.postgresql.Driver");
try (Connection connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/Sample", "user", "password")) {
String query = "SELECT w.id AS workerId, w.name AS workername,"
+ " w.sex AS sex, c.id AS companyId, c.name AS companyname,"
+ " w.updatedate AS workerupdatedate, c.updatedate AS companyupdatedate"
+ " FROM worker AS w"
+ " INNER JOIN company AS c ON w.companyid = c.id"
+ " WHERE (w.sex = ?) IS NOT FALSE order by w.id";
List<Map<String, Object>> results =
SamplePreparedStatementForJoinTables.executeQuery(connection, query, "f");
// Output results
for (Map<String, Object> rows : results) {
for (Map.Entry<String, Object> row : rows.entrySet()) {
System.out.println(row.getKey() + " : " + row.getValue());
}
}
} catch (Exception ex) {
throw ex;
}
} catch (Exception ex) {
throw ex;
}
}
private static List<Map<String, Object>> executeQuery(Connection connection,
String query, Object... parameters) throws Exception {
List<Map<String, Object>> returnValues = new ArrayList<Map<String, Object>>();
try (PreparedStatement statement = connection.prepareStatement(query)) {
// Set Parameters
if (parameters != null) {
int index = 1;
for (Object parameter : parameters) {
statement.setObject(index, parameter);
}
}
// Run SQL
try (ResultSet resultSet = statement.executeQuery();) {
// For Debug
System.out.println("--------------------------");
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
System.out.println(metaData.getColumnName(i));
}
System.out.println("--------------------------");
// Set Result
while (resultSet.next()) {
Map<String, Object> rows = new HashMap<String, Object>();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
String key = (String) metaData.getColumnName(i);
rows.put(key, resultSet.getObject(key));
}
returnValues.add(rows);
}
} catch (Exception ex) {
throw ex;
}
} catch (Exception ex) {
throw ex;
}
return returnValues;
}
}
補足
* ResultSetからフィールド名を取得するには...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
System.out.println(metaData.getColumnName(i));
}
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
