public ArrayList<airschedule> getAirschedule() {
connect();
ArrayList<airschedule> list = new ArrayList<airschedule>();
try {
pstmt = con.prepareStatement(SQL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
airschedule s = new airschedule();
s.setSchedule_id(rs.getInt("schedule_id"));
s.setDeparture_date(rs.getDate("departure_date"));
s.setArrival_date(rs.getDate("arrival_date"));
s.setDeparture_time(rs.getTime("departure_time"));
s.setArrival_time(rs.getTime("arrival_time"));
s.setAirline_name(rs.getString("airline_name"));
result++;
list.add(s);
}
this.setResult(result);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return list;
}
public static void fillTable(String aQuery, JTable aTable, Connector aConnector)
throws SQLException {
((DefaultTableModel) aTable.getModel()).setRowCount(0);
((DefaultTableModel) aTable.getModel()).setColumnCount(0);
ResultSet results = aConnector.sendQuery(aQuery);
ResultSetMetaData metadata = results.getMetaData();
cols = metadata.getColumnCount();
rowData = new Object[cols];
colNames = new String[cols];
for (int i = 0; i < cols; i++) {
colNames[i] = metadata.getColumnName(i + 1);
}
for (int i = 0; i < cols; i++) {
((DefaultTableModel) aTable.getModel()).addColumn(colNames[i]);
}
while (results.next()) {
for (int i = 0; i < cols; i++) {
if (metadata.getColumnName(i + 1).equalsIgnoreCase("DatePosted")) {
rowData[i] = StrVal.SqlStringToString(results.getDate(i + 1).toString());
rowData[i] += " || ";
rowData[i] += results.getTime(i + 1).toString();
} else rowData[i] = results.getString(i + 1);
}
((DefaultTableModel) aTable.getModel()).addRow(rowData);
}
}
/**
* Zeige die Daten aller Spiele an, die am ersten Spieltag aller drei Ligen nach 17 Uhr begonnen
* haben.
*
* @param db Hostadresse der Datenbank
* @param user Benutzername für Datenbank
* @param password Passwort für Datenbank
* @throws SQLException Exception für Datenbankzugriffe benötigt
* @throws ClassNotFoundException Exception für Postgres-Verbindung benötigt
* @see #connect(String, String, String)
* @see #disconnect()
*/
public static void select3(String db, String user, String password)
throws SQLException, ClassNotFoundException {
connect(db, user, password);
System.out.println("Spiele am ersten Spieltag nach 17 Uhr:");
ResultSet rs =
stmt.executeQuery("SELECT * FROM bl.spiel WHERE spieltag = 1 AND uhrzeit > '17:00:00';");
while (rs.next()) {
int spiel_id = rs.getInt("spiel_id");
int spieltag = rs.getInt("spieltag");
Date datum = rs.getDate("datum");
Time uhrzeit = rs.getTime("uhrzeit");
int heim = rs.getInt("heim");
int gast = rs.getInt("gast");
int tore_heim = rs.getInt("tore_heim");
int tore_gast = rs.getInt("tore_gast");
System.out.print("ID: " + spiel_id + "; \t");
System.out.print("Spieltag: " + spieltag + ";\t");
System.out.print("Datum: " + datum + ";\t");
System.out.print("Uhrzeit: " + uhrzeit + ";\t");
System.out.print("Heim: " + heim + ";\t");
System.out.print("Gast: " + gast + ";\t");
System.out.print("Tore Heim: " + tore_heim + ";\t");
System.out.print("Tore Gast: " + tore_gast + ";");
System.out.println();
}
rs.close();
System.out.println();
disconnect();
}
public static void getProfile() {
String url = "jdbc:mysql://" + var.db_host + "/" + var.db_name;
String login = var.db_username;
String passwd = var.db_psswd;
Connection cn = null;
Statement stmt = null;
String query = "SELECT * FROM profiles WHERE Name='" + getCurrentProfile() + "'";
try {
Class.forName("com.mysql.jdbc.Driver");
cn = DriverManager.getConnection(url, login, passwd);
stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
Status.Name = rs.getString("Name");
Status.Sunrise = rs.getTime("Sunrise");
Status.Sunset = rs.getTime("Sunset");
Status.Interval = rs.getTime("Interval");
Status.Working_Time = rs.getTime("Working_Time");
Status.Tank_Capacity = rs.getDouble("Tank_Capacity");
Status.Pump_Flow = rs.getDouble("Pump_Flow");
Status.Watering_Hour = rs.getTime("Watering_Hour");
Status.Water_Amount = rs.getDouble("Water_Amount");
Status.Temperature = rs.getDouble("Temperature");
Status.Humidity = rs.getDouble("Humidity");
Status.Water_Days[0] = rs.getInt("Monday") == 1;
Status.Water_Days[1] = rs.getInt("Tuesday") == 1;
Status.Water_Days[2] = rs.getInt("Wednesday") == 1;
Status.Water_Days[3] = rs.getInt("Thursday") == 1;
Status.Water_Days[4] = rs.getInt("Friday") == 1;
Status.Water_Days[5] = rs.getInt("Saturday") == 1;
Status.Water_Days[6] = rs.getInt("Sunday") == 1;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Nullable
@Override
public OffsetTime getValue(ResultSet rs, int startIndex) throws SQLException {
Time time = rs.getTime(startIndex, utc());
return time != null
? OffsetTime.ofInstant(Instant.ofEpochMilli(time.getTime()), ZoneOffset.UTC)
: null;
}
@SuppressWarnings("deprecation")
@Test
public void time() throws SQLException {
connectWithJulianDayModeActivated();
Time d1 = new Time(System.currentTimeMillis());
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
prep.setTime(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1.getHours(), rs.getTime(1).getHours());
assertEquals(d1.getMinutes(), rs.getTime(1).getMinutes());
assertEquals(d1.getSeconds(), rs.getTime(1).getSeconds());
}
@Test
public void testAjoutCreneau() throws SQLException, IOException, ClassNotFoundException {
Connection connection = BddConnecteur.getConnection();
BddUtilisateur.ajout("*****@*****.**", "labri");
BddLabo.ajout(1, "Labri");
BddAtelier.ajoutAtelier(
1,
"A la poursuite d'ennemis invisibles",
"Sciences de la vie ",
"Campus Carreire (Hôpital Pellegrin)",
"Labo MFP",
"",
"",
"",
"");
// Insertion
int idAtelier = 1;
String jour = "2015-12-23";
String heure = "13:00";
int capacite = 20;
BddCreneau.ajoutCreneau(1, jour, heure, capacite);
// Vérification
String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
int count = 0;
Date jourRecup;
Time heureRecup;
int capaciteRecup = 0;
while (rs.next()) {
jourRecup = rs.getDate("jour");
heureRecup = rs.getTime("heure");
capaciteRecup = rs.getInt("capacite");
count++;
}
assertEquals(count, 1);
// assertEquals(jourRecup, jour);
// assertEquals(heureRecup, heure);
assertEquals(capaciteRecup, capacite);
// Fermeture
rs.close();
statement.close();
connection.close();
}
/**
* Gets a value from a given column in a JDBC result set.
*
* @param i Ordinal of column (1-based, per JDBC)
*/
private Object value(int i) throws SQLException {
// MySQL returns timestamps shifted into local time. Using
// getTimestamp(int, Calendar) with a UTC calendar should prevent this,
// but does not. So we shift explicitly.
switch (types[i]) {
case Types.TIMESTAMP:
return shift(resultSet.getTimestamp(i + 1));
case Types.TIME:
return shift(resultSet.getTime(i + 1));
case Types.DATE:
return shift(resultSet.getDate(i + 1));
}
return primitives[i].jdbcGet(resultSet, i + 1);
}
/**
* Bietet die Möglichkeit ein eigenes Query einzugeben und ausführen zu lassen. Die
* Postgres-Syntax muss eingehalten werden.<br>
* Nutzt Standard-Query bei leerer Eingabe.<br>
* Führt zu Fehlern bei falscher Eingabe und Abbruch.
*
* @param db Hostadresse der Datenbank
* @param user Benutzername für Datenbank
* @param password Passwort für Datenbank
* @throws SQLException Exception für Datenbankzugriffe benötigt
* @throws ClassNotFoundException Exception für Postgres-Verbindung benötigt
* @see #connect(String, String, String)
* @see #disconnect()
*/
public static void select_e(String db, String user, String password)
throws SQLException, ClassNotFoundException {
connect(db, user, password);
System.out.println("Query nach Benutzereingabe:");
String query =
JOptionPane.showInputDialog("Bitte hier Query eingeben: \n" + "(Postgres-Syntax benutzen)");
if (query.isEmpty()) {
System.out.println("Da muss schon was kommen!\nZ.B.: SELECT * FROM bl.liga;");
query = "SELECT * FROM bl.liga;";
}
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
String out = "";
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
if (i > 1) {
out = out + ",\t";
}
rsmd.getColumnType(i);
int type = rsmd.getColumnType(i);
if (type == Types.VARCHAR) {
out = out + rs.getString(i);
} else if (type == Types.INTEGER) {
out = out + rs.getInt(i);
} else if (type == Types.DATE) {
out = out + rs.getDate(i);
} else if (type == Types.TIME) {
out = out + rs.getTime(i);
}
}
System.out.print(out);
System.out.println();
}
rs.close();
System.out.println();
disconnect();
}
private static String getColumnValue(ResultSet rs, int colType, int colIndex)
throws SQLException, IOException {
String value = "";
switch (colType) {
case Types.BIT:
Object bit = rs.getObject(colIndex);
if (bit != null) {
value = String.valueOf(bit);
}
break;
case Types.BOOLEAN:
boolean b = rs.getBoolean(colIndex);
if (!rs.wasNull()) {
value = Boolean.valueOf(b).toString();
}
break;
case Types.CLOB:
Clob c = rs.getClob(colIndex);
if (c != null) {
value = read(c);
}
break;
case Types.BIGINT:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.REAL:
case Types.NUMERIC:
BigDecimal bd = rs.getBigDecimal(colIndex);
if (bd != null) {
value = "" + bd.doubleValue();
}
break;
case Types.INTEGER:
case Types.TINYINT:
case Types.SMALLINT:
int intValue = rs.getInt(colIndex);
if (!rs.wasNull()) {
value = "" + intValue;
}
break;
case Types.JAVA_OBJECT:
Object obj = rs.getObject(colIndex);
if (obj != null) {
value = String.valueOf(obj);
}
break;
case Types.DATE:
java.sql.Date date = rs.getDate(colIndex);
if (date != null) {
value = DATE_FORMATTER.format(date);
;
}
break;
case Types.TIME:
Time t = rs.getTime(colIndex);
if (t != null) {
value = t.toString();
}
break;
case Types.TIMESTAMP:
Timestamp tstamp = rs.getTimestamp(colIndex);
if (tstamp != null) {
value = TIMESTAMP_FORMATTER.format(tstamp);
}
break;
case Types.LONGVARCHAR:
case Types.VARCHAR:
case Types.CHAR:
value = rs.getString(colIndex);
break;
default:
value = "";
}
if (value == null) {
value = "";
}
return value;
}
public static Object parseType(ResultSet result, Integer i, int type)
throws SQLException, IOException, ParseException {
logger.trace("i={} type={}", i, type);
switch (type) {
/**
* The JDBC types CHAR, VARCHAR, and LONGVARCHAR are closely related. CHAR represents a
* small, fixed-length character string, VARCHAR represents a small, variable-length
* character string, and LONGVARCHAR represents a large, variable-length character string.
*/
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
{
return result.getString(i);
}
case Types.NCHAR:
case Types.NVARCHAR:
case Types.LONGNVARCHAR:
{
return result.getNString(i);
}
/**
* The JDBC types BINARY, VARBINARY, and LONGVARBINARY are closely related. BINARY
* represents a small, fixed-length binary value, VARBINARY represents a small,
* variable-length binary value, and LONGVARBINARY represents a large, variable-length
* binary value
*/
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
{
byte[] b = result.getBytes(i);
return b;
}
/**
* The JDBC type ARRAY represents the SQL3 type ARRAY.
*
* <p>An ARRAY value is mapped to an instance of the Array interface in the Java programming
* language. If a driver follows the standard implementation, an Array object logically
* points to an ARRAY value on the server rather than containing the elements of the ARRAY
* object, which can greatly increase efficiency. The Array interface contains methods for
* materializing the elements of the ARRAY object on the client in the form of either an
* array or a ResultSet object.
*/
case Types.ARRAY:
{
Array arr = result.getArray(i);
return arr == null ? null : arr.getArray();
}
/**
* The JDBC type BIGINT represents a 64-bit signed integer value between
* -9223372036854775808 and 9223372036854775807.
*
* <p>The corresponding SQL type BIGINT is a nonstandard extension to SQL. In practice the
* SQL BIGINT type is not yet currently implemented by any of the major databases, and we
* recommend that its use be avoided in code that is intended to be portable.
*
* <p>The recommended Java mapping for the BIGINT type is as a Java long.
*/
case Types.BIGINT:
{
Object o = result.getLong(i);
return result.wasNull() ? null : o;
}
/**
* The JDBC type BIT represents a single bit value that can be zero or one.
*
* <p>SQL-92 defines an SQL BIT type. However, unlike the JDBC BIT type, this SQL-92 BIT
* type can be used as a parameterized type to define a fixed-length binary string.
* Fortunately, SQL-92 also permits the use of the simple non-parameterized BIT type to
* represent a single binary digit, and this usage corresponds to the JDBC BIT type.
* Unfortunately, the SQL-92 BIT type is only required in "full" SQL-92 and is currently
* supported by only a subset of the major databases. Portable code may therefore prefer to
* use the JDBC SMALLINT type, which is widely supported.
*/
case Types.BIT:
{
try {
Object o = result.getInt(i);
return result.wasNull() ? null : o;
} catch (Exception e) {
String exceptionClassName = e.getClass().getName();
// postgresql can not handle boolean, it will throw PSQLException, something like "Bad
// value for type int : t"
if ("org.postgresql.util.PSQLException".equals(exceptionClassName)) {
return "t".equals(result.getString(i));
}
throw new IOException(e);
}
}
/**
* The JDBC type BOOLEAN, which is new in the JDBC 3.0 API, maps to a boolean in the Java
* programming language. It provides a representation of true and false, and therefore is a
* better match than the JDBC type BIT, which is either 1 or 0.
*/
case Types.BOOLEAN:
{
return result.getBoolean(i);
}
/**
* The JDBC type BLOB represents an SQL3 BLOB (Binary Large Object).
*
* <p>A JDBC BLOB value is mapped to an instance of the Blob interface in the Java
* programming language. If a driver follows the standard implementation, a Blob object
* logically points to the BLOB value on the server rather than containing its binary data,
* greatly improving efficiency. The Blob interface provides methods for materializing the
* BLOB data on the client when that is desired.
*/
case Types.BLOB:
{
Blob blob = result.getBlob(i);
if (blob != null) {
long n = blob.length();
if (n > Integer.MAX_VALUE) {
throw new IOException("can't process blob larger than Integer.MAX_VALUE");
}
byte[] tab = blob.getBytes(1, (int) n);
blob.free();
return tab;
}
break;
}
/**
* The JDBC type CLOB represents the SQL3 type CLOB (Character Large Object).
*
* <p>A JDBC CLOB value is mapped to an instance of the Clob interface in the Java
* programming language. If a driver follows the standard implementation, a Clob object
* logically points to the CLOB value on the server rather than containing its character
* data, greatly improving efficiency. Two of the methods on the Clob interface materialize
* the data of a CLOB object on the client.
*/
case Types.CLOB:
{
Clob clob = result.getClob(i);
if (clob != null) {
long n = clob.length();
if (n > Integer.MAX_VALUE) {
throw new IOException("can't process clob larger than Integer.MAX_VALUE");
}
String str = clob.getSubString(1, (int) n);
clob.free();
return str;
}
break;
}
case Types.NCLOB:
{
NClob nclob = result.getNClob(i);
if (nclob != null) {
long n = nclob.length();
if (n > Integer.MAX_VALUE) {
throw new IOException("can't process nclob larger than Integer.MAX_VALUE");
}
String str = nclob.getSubString(1, (int) n);
nclob.free();
return str;
}
break;
}
/**
* The JDBC type DATALINK, new in the JDBC 3.0 API, is a column value that references a file
* that is outside of a data source but is managed by the data source. It maps to the Java
* type java.net.URL and provides a way to manage external files. For instance, if the data
* source is a DBMS, the concurrency controls it enforces on its own data can be applied to
* the external file as well.
*
* <p>A DATALINK value is retrieved from a ResultSet object with the ResultSet methods
* getURL or getObject. If the Java platform does not support the type of URL returned by
* getURL or getObject, a DATALINK value can be retrieved as a String object with the method
* getString.
*
* <p>java.net.URL values are stored in a database using the method setURL. If the Java
* platform does not support the type of URL being set, the method setString can be used
* instead.
*/
case Types.DATALINK:
{
return result.getURL(i);
}
/**
* The JDBC DATE type represents a date consisting of day, month, and year. The
* corresponding SQL DATE type is defined in SQL-92, but it is implemented by only a subset
* of the major databases. Some databases offer alternative SQL types that support similar
* semantics.
*/
case Types.DATE:
{
try {
Date d = result.getDate(i, calendar);
return d != null ? formatDate(d.getTime()) : null;
} catch (SQLException e) {
return null;
}
}
case Types.TIME:
{
try {
Time t = result.getTime(i, calendar);
return t != null ? formatDate(t.getTime()) : null;
} catch (SQLException e) {
return null;
}
}
case Types.TIMESTAMP:
{
try {
Timestamp t = result.getTimestamp(i, calendar);
return t != null ? formatDate(t.getTime()) : null;
} catch (SQLException e) {
// java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column ... to
// TIMESTAMP.
return null;
}
}
/**
* The JDBC types DECIMAL and NUMERIC are very similar. They both represent fixed-precision
* decimal values.
*
* <p>The corresponding SQL types DECIMAL and NUMERIC are defined in SQL-92 and are very
* widely implemented. These SQL types take precision and scale parameters. The precision is
* the total number of decimal digits supported, and the scale is the number of decimal
* digits after the decimal point. For most DBMSs, the scale is less than or equal to the
* precision. So for example, the value "12.345" has a precision of 5 and a scale of 3, and
* the value ".11" has a precision of 2 and a scale of 2. JDBC requires that all DECIMAL and
* NUMERIC types support both a precision and a scale of at least 15.
*
* <p>The sole distinction between DECIMAL and NUMERIC is that the SQL-92 specification
* requires that NUMERIC types be represented with exactly the specified precision, whereas
* for DECIMAL types, it allows an implementation to add additional precision beyond that
* specified when the type was created. Thus a column created with type NUMERIC(12,4) will
* always be represented with exactly 12 digits, whereas a column created with type
* DECIMAL(12,4) might be represented by some larger number of digits.
*
* <p>The recommended Java mapping for the DECIMAL and NUMERIC types is
* java.math.BigDecimal. The java.math.BigDecimal type provides math operations to allow
* BigDecimal types to be added, subtracted, multiplied, and divided with other BigDecimal
* types, with integer types, and with floating point types.
*
* <p>The method recommended for retrieving DECIMAL and NUMERIC values is
* ResultSet.getBigDecimal. JDBC also allows access to these SQL types as simple Strings or
* arrays of char. Thus, Java programmers can use getString to receive a DECIMAL or NUMERIC
* result. However, this makes the common case where DECIMAL or NUMERIC are used for
* currency values rather awkward, since it means that application writers have to perform
* math on strings. It is also possible to retrieve these SQL types as any of the Java
* numeric types.
*/
case Types.DECIMAL:
case Types.NUMERIC:
{
BigDecimal bd = null;
try {
// getBigDecimal() should get obsolete. Most seem to use getString/getObject anyway...
bd = result.getBigDecimal(i);
} catch (NullPointerException e) {
// But is it true? JDBC NPE exists since 13 years?
// http://forums.codeguru.com/archive/index.php/t-32443.html
// Null values are driving us nuts in JDBC:
// http://stackoverflow.com/questions/2777214/when-accessing-resultsets-in-jdbc-is-there-an-elegant-way-to-distinguish-betwee
}
if (bd == null || result.wasNull()) {
return null;
}
int scale = 2;
if (scale >= 0) {
bd = bd.setScale(scale, BigDecimal.ROUND_UP);
try {
long l = bd.longValueExact();
if (Long.toString(l).equals(result.getString(i))) {
// convert to long if possible
return l;
} else {
// convert to double (with precision loss)
return bd.doubleValue();
}
} catch (ArithmeticException e) {
return bd.doubleValue();
}
} else {
return bd.toPlainString();
}
}
/**
* The JDBC type DOUBLE represents a "double precision" floating point number that supports
* 15 digits of mantissa.
*
* <p>The corresponding SQL type is DOUBLE PRECISION, which is defined in SQL-92 and is
* widely supported by the major databases. The SQL-92 standard leaves the precision of
* DOUBLE PRECISION up to the implementation, but in practice all the major databases
* supporting DOUBLE PRECISION support a mantissa precision of at least 15 digits.
*
* <p>The recommended Java mapping for the DOUBLE type is as a Java double.
*/
case Types.DOUBLE:
{
String s = result.getString(i);
if (result.wasNull() || s == null) {
return null;
}
NumberFormat format = NumberFormat.getInstance(locale);
Number number = format.parse(s);
return number.doubleValue();
}
/**
* The JDBC type FLOAT is basically equivalent to the JDBC type DOUBLE. We provided both
* FLOAT and DOUBLE in a possibly misguided attempt at consistency with previous database
* APIs. FLOAT represents a "double precision" floating point number that supports 15 digits
* of mantissa.
*
* <p>The corresponding SQL type FLOAT is defined in SQL-92. The SQL-92 standard leaves the
* precision of FLOAT up to the implementation, but in practice all the major databases
* supporting FLOAT support a mantissa precision of at least 15 digits.
*
* <p>The recommended Java mapping for the FLOAT type is as a Java double. However, because
* of the potential confusion between the double precision SQL FLOAT and the single
* precision Java float, we recommend that JDBC programmers should normally use the JDBC
* DOUBLE type in preference to FLOAT.
*/
case Types.FLOAT:
{
String s = result.getString(i);
if (result.wasNull() || s == null) {
return null;
}
NumberFormat format = NumberFormat.getInstance(locale);
Number number = format.parse(s);
return number.doubleValue();
}
/**
* The JDBC type JAVA_OBJECT, added in the JDBC 2.0 core API, makes it easier to use objects
* in the Java programming language as values in a database. JAVA_OBJECT is simply a type
* code for an instance of a class defined in the Java programming language that is stored
* as a database object. The type JAVA_OBJECT is used by a database whose type system has
* been extended so that it can store Java objects directly. The JAVA_OBJECT value may be
* stored as a serialized Java object, or it may be stored in some vendor-specific format.
*
* <p>The type JAVA_OBJECT is one of the possible values for the column DATA_TYPE in the
* ResultSet objects returned by various DatabaseMetaData methods, including getTypeInfo,
* getColumns, and getUDTs. The method getUDTs, part of the new JDBC 2.0 core API, will
* return information about the Java objects contained in a particular schema when it is
* given the appropriate parameters. Having this information available facilitates using a
* Java class as a database type.
*/
case Types.OTHER:
case Types.JAVA_OBJECT:
{
return result.getObject(i);
}
/**
* The JDBC type REAL represents a "single precision" floating point number that supports
* seven digits of mantissa.
*
* <p>The corresponding SQL type REAL is defined in SQL-92 and is widely, though not
* universally, supported by the major databases. The SQL-92 standard leaves the precision
* of REAL up to the implementation, but in practice all the major databases supporting REAL
* support a mantissa precision of at least seven digits.
*
* <p>The recommended Java mapping for the REAL type is as a Java float.
*/
case Types.REAL:
{
String s = result.getString(i);
if (result.wasNull() || s == null) {
return null;
}
NumberFormat format = NumberFormat.getInstance(locale);
Number number = format.parse(s);
return number.doubleValue();
}
/**
* The JDBC type TINYINT represents an 8-bit integer value between 0 and 255 that may be
* signed or unsigned.
*
* <p>The corresponding SQL type, TINYINT, is currently supported by only a subset of the
* major databases. Portable code may therefore prefer to use the JDBC SMALLINT type, which
* is widely supported.
*
* <p>The recommended Java mapping for the JDBC TINYINT type is as either a Java byte or a
* Java short. The 8-bit Java byte type represents a signed value from -128 to 127, so it
* may not always be appropriate for larger TINYINT values, whereas the 16-bit Java short
* will always be able to hold all TINYINT values.
*/
/**
* The JDBC type SMALLINT represents a 16-bit signed integer value between -32768 and 32767.
*
* <p>The corresponding SQL type, SMALLINT, is defined in SQL-92 and is supported by all the
* major databases. The SQL-92 standard leaves the precision of SMALLINT up to the
* implementation, but in practice, all the major databases support at least 16 bits.
*
* <p>The recommended Java mapping for the JDBC SMALLINT type is as a Java short.
*/
/**
* The JDBC type INTEGER represents a 32-bit signed integer value ranging between
* -2147483648 and 2147483647.
*
* <p>The corresponding SQL type, INTEGER, is defined in SQL-92 and is widely supported by
* all the major databases. The SQL-92 standard leaves the precision of INTEGER up to the
* implementation, but in practice all the major databases support at least 32 bits.
*
* <p>The recommended Java mapping for the INTEGER type is as a Java int.
*/
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
{
try {
Integer integer = result.getInt(i);
return result.wasNull() ? null : integer;
} catch (SQLDataException e) {
Long l = result.getLong(i);
return result.wasNull() ? null : l;
}
}
case Types.SQLXML:
{
SQLXML xml = result.getSQLXML(i);
return xml != null ? xml.getString() : null;
}
case Types.NULL:
{
return null;
}
/**
* The JDBC type DISTINCT field (Types class)>DISTINCT represents the SQL3 type DISTINCT.
*
* <p>The standard mapping for a DISTINCT type is to the Java type to which the base type of
* a DISTINCT object would be mapped. For example, a DISTINCT type based on a CHAR would be
* mapped to a String object, and a DISTINCT type based on an SQL INTEGER would be mapped to
* an int.
*
* <p>The DISTINCT type may optionally have a custom mapping to a class in the Java
* programming language. A custom mapping consists of a class that implements the interface
* SQLData and an entry in a java.util.Map object.
*/
case Types.DISTINCT:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
/**
* The JDBC type STRUCT represents the SQL99 structured type. An SQL structured type, which
* is defined by a user with a CREATE TYPE statement, consists of one or more attributes.
* These attributes may be any SQL data type, built-in or user-defined.
*
* <p>The standard mapping for the SQL type STRUCT is to a Struct object in the Java
* programming language. A Struct object contains a value for each attribute of the STRUCT
* value it represents.
*
* <p>A STRUCT value may optionally be custom mapped to a class in the Java programming
* language, and each attribute in the STRUCT may be mapped to a field in the class. A
* custom mapping consists of a class that implements the interface SQLData and an entry in
* a java.util.Map object.
*/
case Types.STRUCT:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
case Types.REF:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
case Types.ROWID:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
default:
{
logger.warn("unknown JDBC type ignored: {}", type);
return null;
}
}
return null;
}
private String getColumnValue(ResultSet rs, int colType, int colIndex)
throws SQLException, IOException {
String value = "";
switch (colType) {
case Types.BIT:
case Types.JAVA_OBJECT:
value = handleObject(rs.getObject(colIndex));
break;
case Types.BOOLEAN:
boolean b = rs.getBoolean(colIndex);
value = Boolean.valueOf(b).toString();
break;
case NCLOB: // todo : use rs.getNClob
case Types.CLOB:
Clob c = rs.getClob(colIndex);
if (c != null) {
value = read(c);
}
break;
case Types.BIGINT:
value = handleLong(rs, colIndex);
break;
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.REAL:
case Types.NUMERIC:
value = handleBigDecimal(rs.getBigDecimal(colIndex));
break;
case Types.INTEGER:
case Types.TINYINT:
case Types.SMALLINT:
value = handleInteger(rs, colIndex);
break;
case Types.DATE:
value = handleDate(rs, colIndex);
break;
case Types.TIME:
value = handleTime(rs.getTime(colIndex));
break;
case Types.TIMESTAMP:
value = handleTimestamp(rs.getTimestamp(colIndex));
break;
case NVARCHAR: // todo : use rs.getNString
case NCHAR: // todo : use rs.getNString
case LONGNVARCHAR: // todo : use rs.getNString
case Types.LONGVARCHAR:
case Types.VARCHAR:
case Types.CHAR:
value = rs.getString(colIndex);
break;
default:
value = "";
}
if (value == null) {
value = "";
}
return value;
}
public void localTimestamps(String timeZone) throws Exception {
final String testDateFormat = "yyyy-MM-dd HH:mm:ss";
final List<String> datesToTest =
Arrays.asList(
"2015-09-03 12:00:00",
"2015-06-30 23:59:58",
"1997-06-30 23:59:59",
"1997-07-01 00:00:00",
"2012-06-30 23:59:59",
"2012-07-01 00:00:00",
"2015-06-30 23:59:59",
"2015-07-01 00:00:00",
"2005-12-31 23:59:59",
"2006-01-01 00:00:00",
"2008-12-31 23:59:59",
"2009-01-01 00:00:00",
"2015-06-30 23:59:60",
"2015-07-31 00:00:00",
"2015-07-31 00:00:01",
// On 2000-03-26 02:00:00 Moscow went to DST, thus local time became 03:00:00
"2000-03-26 01:59:59",
"2000-03-26 02:00:00",
"2000-03-26 02:00:01",
"2000-03-26 02:59:59",
"2000-03-26 03:00:00",
"2000-03-26 03:00:01",
"2000-03-26 03:59:59",
"2000-03-26 04:00:00",
"2000-03-26 04:00:01",
// On 2000-10-29 03:00:00 Moscow went to regular time, thus local time became 02:00:00
"2000-10-29 01:59:59",
"2000-10-29 02:00:00",
"2000-10-29 02:00:01",
"2000-10-29 02:59:59",
"2000-10-29 03:00:00",
"2000-10-29 03:00:01",
"2000-10-29 03:59:59",
"2000-10-29 04:00:00",
"2000-10-29 04:00:01");
con.createStatement().execute("delete from testtimezone");
Statement stmt = con.createStatement();
for (int i = 0; i < datesToTest.size(); i++) {
stmt.execute(
"insert into testtimezone (ts, seq) values ('" + datesToTest.get(i) + "', " + i + ")");
}
// Different timezone test should have different sql text, so we test both text and binary modes
PreparedStatement pstmt =
con.prepareStatement("SELECT ts FROM testtimezone order by seq /*" + timeZone + "*/");
Calendar expectedTimestamp = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat(testDateFormat);
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
ResultSet rs = pstmt.executeQuery();
for (int j = 0; rs.next(); j++) {
String testDate = datesToTest.get(j);
Date getDate = rs.getDate(1);
Timestamp getTimestamp = rs.getTimestamp(1);
String getString = rs.getString(1);
Time getTime = rs.getTime(1);
expectedTimestamp.setTime(sdf.parse(testDate));
assertEquals(
"getTimestamp: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(getTimestamp));
assertEquals(
"getString: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(sdf.parse(getString)));
expectedTimestamp.set(Calendar.HOUR_OF_DAY, 0);
expectedTimestamp.set(Calendar.MINUTE, 0);
expectedTimestamp.set(Calendar.SECOND, 0);
assertEquals(
"getDate: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(getDate));
expectedTimestamp.setTime(sdf.parse(testDate));
expectedTimestamp.set(Calendar.YEAR, 1970);
expectedTimestamp.set(Calendar.MONTH, 0);
expectedTimestamp.set(Calendar.DAY_OF_MONTH, 1);
assertEquals(
"getTime: "
+ testDate
+ ", transfer format: "
+ (i == 0 ? "text" : "binary")
+ ", timeZone: "
+ timeZone,
sdf.format(expectedTimestamp.getTimeInMillis()),
sdf.format(getTime));
}
rs.close();
}
}
public void testSetTime() throws Exception {
if (!min74) {
// We can't do timezones properly for time/timetz values before 7.4
System.err.println("Skipping TimezoneTest.testSetTime on a pre-7.4 server");
return;
}
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
con.createStatement().execute("delete from testtimezone");
PreparedStatement insertTimestamp =
con.prepareStatement("INSERT INTO testtimezone(seq,t,tz) VALUES (?,?,?)");
int seq = 1;
Time tJVM, tUTC, tGMT03, tGMT05, tGMT13;
// +0100 (JVM default)
tJVM = new Time(50400000L); // 1970-01-01 15:00:00 +0100
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tJVM); // 15:00:00
insertTimestamp.setTime(3, tJVM); // 15:00:00+03
insertTimestamp.executeUpdate();
// UTC
tUTC = new Time(54000000L); // 1970-01-01 15:00:00 +0000
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tUTC, cUTC); // 15:00:00
insertTimestamp.setTime(3, tUTC, cUTC); // 15:00:00+00
insertTimestamp.executeUpdate();
// +0300
tGMT03 = new Time(43200000L); // 1970-01-01 15:00:00 +0300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tGMT03, cGMT03); // 15:00:00
insertTimestamp.setTime(3, tGMT03, cGMT03); // 15:00:00+03
insertTimestamp.executeUpdate();
// -0500
tGMT05 = new Time(72000000L); // 1970-01-01 15:00:00 -0500
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tGMT05, cGMT05); // 15:00:00
insertTimestamp.setTime(3, tGMT05, cGMT05); // 15:00:00-05
insertTimestamp.executeUpdate();
// +1300
tGMT13 = new Time(7200000L); // 1970-01-01 15:00:00 +1300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tGMT13, cGMT13); // 15:00:00
insertTimestamp.setTime(3, tGMT13, cGMT13); // 15:00:00+13
insertTimestamp.executeUpdate();
insertTimestamp.close();
// check that insert went correctly by parsing the raw contents in UTC
checkDatabaseContents(
"SELECT seq::text,t::text,tz::text from testtimezone ORDER BY seq",
new String[][] {
new String[] {
"1", "15:00:00", "15:00:00+01",
},
new String[] {
"2", "15:00:00", "15:00:00+00",
},
new String[] {
"3", "15:00:00", "15:00:00+03",
},
new String[] {
"4", "15:00:00", "15:00:00-05",
},
new String[] {
"5", "15:00:00", "15:00:00+13",
}
});
//
// check results
//
seq = 1;
PreparedStatement ps = con.prepareStatement("SELECT seq,t,tz FROM testtimezone ORDER BY seq");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tJVM, rs.getTime(2));
assertEquals(tJVM, rs.getTime(3));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tUTC, rs.getTime(2, cUTC));
assertEquals(tUTC, rs.getTime(2, cUTC));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tGMT03, rs.getTime(2, cGMT03));
assertEquals(tGMT03, rs.getTime(2, cGMT03));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tGMT05, rs.getTime(2, cGMT05));
assertEquals(tGMT05, rs.getTime(2, cGMT05));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tGMT13, rs.getTime(2, cGMT13));
assertEquals(tGMT13, rs.getTime(2, cGMT13));
assertTrue(!rs.next());
ps.close();
}
}
public void testGetTime() throws Exception {
con.createStatement()
.executeUpdate(
"INSERT INTO testtimezone(tstz,ts,t,tz) VALUES('2005-01-01 15:00:00 +0300', '2005-01-01 15:00:00', '15:00:00', '15:00:00 +0300')");
PreparedStatement ps = con.prepareStatement("SELECT tstz,ts,t,tz from testtimezone");
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
checkDatabaseContents(
"SELECT tstz::text,ts::text,t::text,tz::text,d::text from testtimezone",
new String[] {
"2005-01-01 12:00:00+00", "2005-01-01 15:00:00", "15:00:00", "15:00:00+03"
});
Time t;
// timestamptz: 2005-01-01 15:00:00+03
t = rs.getTime(1);
assertEquals(
43200000L, t.getTime()); // 2005-01-01 13:00:00 +0100 -> 1970-01-01 13:00:00 +0100
t = rs.getTime(1, cUTC);
assertEquals(
43200000L, t.getTime()); // 2005-01-01 12:00:00 +0000 -> 1970-01-01 12:00:00 +0000
t = rs.getTime(1, cGMT03);
assertEquals(
43200000L, t.getTime()); // 2005-01-01 15:00:00 +0300 -> 1970-01-01 15:00:00 +0300
t = rs.getTime(1, cGMT05);
assertEquals(
43200000L, t.getTime()); // 2005-01-01 07:00:00 -0500 -> 1970-01-01 07:00:00 -0500
t = rs.getTime(1, cGMT13);
assertEquals(
-43200000L, t.getTime()); // 2005-01-02 01:00:00 +1300 -> 1970-01-01 01:00:00 +1300
// timestamp: 2005-01-01 15:00:00
t = rs.getTime(2);
assertEquals(50400000L, t.getTime()); // 1970-01-01 15:00:00 +0100
t = rs.getTime(2, cUTC);
assertEquals(54000000L, t.getTime()); // 1970-01-01 15:00:00 +0000
t = rs.getTime(2, cGMT03);
assertEquals(43200000L, t.getTime()); // 1970-01-01 15:00:00 +0300
t = rs.getTime(2, cGMT05);
assertEquals(72000000L, t.getTime()); // 1970-01-01 15:00:00 -0500
t = rs.getTime(2, cGMT13);
assertEquals(7200000L, t.getTime()); // 1970-01-01 15:00:00 +1300
// time: 15:00:00
t = rs.getTime(3);
assertEquals(50400000L, t.getTime()); // 1970-01-01 15:00:00 +0100
t = rs.getTime(3, cUTC);
assertEquals(54000000L, t.getTime()); // 1970-01-01 15:00:00 +0000
t = rs.getTime(3, cGMT03);
assertEquals(43200000L, t.getTime()); // 1970-01-01 15:00:00 +0300
t = rs.getTime(3, cGMT05);
assertEquals(72000000L, t.getTime()); // 1970-01-01 15:00:00 -0500
t = rs.getTime(3, cGMT13);
assertEquals(7200000L, t.getTime()); // 1970-01-01 15:00:00 +1300
// timetz: 15:00:00+03
t = rs.getTime(4);
assertEquals(43200000L, t.getTime()); // 1970-01-01 13:00:00 +0100
t = rs.getTime(4, cUTC);
assertEquals(43200000L, t.getTime()); // 1970-01-01 12:00:00 +0000
t = rs.getTime(4, cGMT03);
assertEquals(43200000L, t.getTime()); // 1970-01-01 15:00:00 +0300
t = rs.getTime(4, cGMT05);
assertEquals(43200000L, t.getTime()); // 1970-01-01 07:00:00 -0500
t = rs.getTime(4, cGMT13);
assertEquals(-43200000L, t.getTime()); // 1970-01-01 01:00:00 +1300
rs.close();
}
}
