ResultSetMetaData.isNullable(i) and outer joined columns
| От | Thor Michael Støre |
|---|---|
| Тема | ResultSetMetaData.isNullable(i) and outer joined columns |
| Дата | |
| Msg-id | 63DBC81F-2AB0-4C02-AC08-2B05C31FFCA6@gmail.com обсуждение исходный текст |
| Ответы |
Re: ResultSetMetaData.isNullable(i) and outer joined columns
(Craig Ringer <ringerc@ringerc.id.au>)
|
| Список | pgsql-jdbc |
Hello,
Although the spec doesn't explicitly distinguish between result set and source table only the former seems sensible to me -- what I expect the isNullable return value to reflect is whether an invocation of the ResultSet.getXXX(i) methods always returns an object (or sensible primitive), if they may return null or if that's unknown.
Test case:
package tmdbctest;
import java.sql.*;
/*
CREATE TABLE employee (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE,
salary NUMERIC,
hire_date TIMESTAMP,
fire_date TIMESTAMP,
picture BYTEA
)
WITHOUT OIDS;
INSERT INTO employee ( name, salary, hire_date, fire_date ) VALUES
( 'Foo Johnson', 50, '2007-02-05 09:00:00', null ),
( 'Bar Mannson', 60, '2007-08-02 09:00:00', null ),
( 'Bruce Woolloomooloo', 70, '2007-08-15 08:00:00', '2008-06-09 15:00:00' ),
( 'Janus Larsen', 65, '2006-08-15 08:30:00', '2008-03-14 16:30:00' );
CREATE TABLE worklog (
id SERIAL PRIMARY KEY,
employee_id INT4 REFERENCES employee(id) DEFERRABLE NOT NULL,
workday DATE NOT NULL,
hours_worked NUMERIC NOT NULL
)
WITHOUT OIDS;
INSERT INTO worklog ( employee_id, workday, hours_worked ) VALUES
( 1, '2007-10-15', 5 ),
( 1, '2007-10-16', 6 ),
( 1, '2007-10-17', 7 ),
( 1, '2007-10-18', 8 ),
( 2, '2007-10-16', 2 ),
( 2, '2007-10-17', 7 ),
( 2, '2007-10-18', 3 ),
( 2, '2007-10-19', 5 );
*/
public class Testcase {
static final String SQL_QUERY =
"SELECT employee.id, name, salary, worklog.id AS worklog_id, workday, hours_worked\n"+
" FROM employee\n"+
" LEFT JOIN worklog ON ( employee.id = worklog.employee_id )";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(
"jdbc:postgresql://localhost/postgres?user=tmdbc_sample&password=tmdbc2008");
}
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
PreparedStatement stmnt = conn.prepareStatement(SQL_QUERY);
ResultSetMetaData rsmd = stmnt.getMetaData();
int columnCount = rsmd.getColumnCount();
System.out.println( "Metadata:" );
for( int i = 1; i <= columnCount; i++ ){
System.out.println( rsmd.getColumnLabel(i) +": "+ parseNullable( rsmd.isNullable(i) ) );
}
ResultSet rs = stmnt.executeQuery();
System.out.println( "Data:" );
for( int i = 1; i <= columnCount; i++ ){
if( i != 1 )
System.out.print( "," );
System.out.print( rsmd.getColumnLabel(i) );
}
System.out.println();
while( rs.next() ){
for( int i = 1; i <= columnCount; i++ ){
if( i != 1 )
System.out.print( "," );
System.out.print( rs.getString(i) );
}
System.out.println();
}
}
public static String parseNullable(int i){
switch(i){
case ResultSetMetaData.columnNoNulls: return "NoNulls";
case ResultSetMetaData.columnNullable: return "Nullable";
case ResultSetMetaData.columnNullableUnknown: return "NullableUnknown";
default:
throw new IllegalArgumentException();
}
}
}
Output:
Metadata:
id: NoNulls
name: NoNulls
salary: Nullable
worklog_id: NoNulls
workday: NoNulls
hours_worked: NoNulls
Data:
id,name,salary,worklog_id,workday,hours_worked
1,Foo Johnson,50.00,1,2007-10-15,5
1,Foo Johnson,50.00,2,2007-10-16,6
1,Foo Johnson,50.00,3,2007-10-17,7
1,Foo Johnson,50.00,4,2007-10-18,8
2,Bar Mannson,60.00,5,2007-10-16,2
2,Bar Mannson,60.00,6,2007-10-17,7
2,Bar Mannson,60.00,7,2007-10-18,3
2,Bar Mannson,60.00,8,2007-10-19,5
4,Janus Larsen,65.00,null,null,null
3,Bruce Woolloomooloo,70.00,null,null,null
Tested with postgresql-9.1-903.jdbc4
Thanks,
Thor Michael Støre
В списке pgsql-jdbc по дате отправления: