MySQL ResultSet JsonWriter: jsonWriter.value((java.sql.Date) resultSet.getObject(column));

Question

I want to write a MySQL Date column value with

jsonWriter.value((java.sql.Date) resultSet.getObject(column));

But it seems like it is not possible like that. It says long, Date, String all are not applicable for the type. How can I achieve this?

Full code:

private static void writeField(final ResultSet resultSet, final JsonWriter jsonWriter, final ResultSetMetaData metaData, final int column) throws SQLException, IOException {
final int columnType = metaData.getColumnType(column);

switch ( columnType ) {
    case Types.BIT:
    case Types.TINYINT:
    case Types.SMALLINT:
        throw new UnsupportedOperationException("TODO: " + JDBCType.valueOf(columnType));
    case Types.INTEGER:
        jsonWriter.value((Integer) resultSet.getObject(column));
        break;
    case Types.BIGINT:
    case Types.FLOAT:
    case Types.REAL:
    case Types.DOUBLE:
    case Types.NUMERIC:
    case Types.DECIMAL:
    case Types.CHAR:
        throw new UnsupportedOperationException("TODO: " + JDBCType.valueOf(columnType));
    case Types.VARCHAR:
        jsonWriter.value((String) resultSet.getObject(column));
        break;
    case Types.LONGVARCHAR:
        jsonWriter.value((String) resultSet.getObject(column));
        break;
    case Types.DATE:
// here's the problem. i dont know to what to case the column.
        jsonWriter.value((java.sql.Date) resultSet.getObject(column));
        break;
    case Types.TIME:
    case Types.TIMESTAMP:
        jsonWriter.value((long) resultSet.getObject(column));
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
    case Types.NULL:
    case Types.OTHER:
    case Types.JAVA_OBJECT:
    case Types.DISTINCT:
    case Types.STRUCT:
    case Types.ARRAY:
    case Types.BLOB:
    case Types.CLOB:
    case Types.REF:
    case Types.DATALINK:
    case Types.BOOLEAN:
    case Types.ROWID:
    case Types.NCHAR:
    case Types.NVARCHAR:
    case Types.LONGNVARCHAR:
    case Types.NCLOB:
    case Types.SQLXML:
    case Types.REF_CURSOR:
    case Types.TIME_WITH_TIMEZONE:
    case Types.TIMESTAMP_WITH_TIMEZONE:
        throw new UnsupportedOperationException("TODO: " + JDBCType.valueOf(columnType));
    default:
        throw new UnsupportedOperationException("Unknown type: " + columnType);
    }
}

I also need to implement Timestamp later - in case their related.

Thank you very much! :)

JSON does not support dates, but it does support strings. What would you do knowing this fact? — Lyubomyr Shaydariv, Mar 08 '17 at 13:41
I guess id have to write something that can format the column output into something String like? or tells how to convert the output of resultSet.getObject(column); into eg. YYYY/MM/DD and for Timestamp the same for YYYY/MM/DD HH:MM:ss ? but ive never read about that. i think? i just have a few java books. i learned c/c++ at school, and now i want to use java. i had good marks but ... its 8 years back. ;) — Uwe Pfeifer, Mar 08 '17 at 13:45
i need some method dateToString(column); and later on client stringToDate. — Uwe Pfeifer, Mar 08 '17 at 13:53
_I guess id have to write something that can format the column output into something String like?_ Absolutely. Take a look at http://stackoverflow.com/questions/19544067/how-to-format-a-java-sql-timestampyyyy-mm-dd-hhmmss-s-to-a-dateyyyy-mm-dd-h and customize it up to your needs. — Lyubomyr Shaydariv, Mar 08 '17 at 13:54
ok thank you. its nice of you to help me here all the time btw. :) — Uwe Pfeifer, Mar 08 '17 at 13:58
No problem. If you're using Java 8, I'd recommend you to use Java 8 `DateTimeFormatter` and its `ISO_LOCAL_DATE` predefined formatter: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#ISO_LOCAL_DATE -- `SimpleDateFormat` has some implementation flaws with thread safety. — Lyubomyr Shaydariv, Mar 08 '17 at 14:03
ISO_LOCAL_DATE hm, my problem is the program will be used by people from different countries and so the date should be formated always yyyy/mm/DD because else they might mix up things, like, in germany its dd.mm.yyyy in america its mm.dd.yyyy.- so what i now have is: DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd"); im trying to get further and ill later post an answer. — Uwe Pfeifer, Mar 08 '17 at 14:14
hm, i now have Date dateDate = (Date) resultSet.getObject(column); String dateString = dateDate.toString(); jsonWriter.value((String) dateString); is it possible to do it like this? but i havent used DateTimeFormatter. — Uwe Pfeifer, Mar 08 '17 at 14:21
It's an ISO format: it's supposed to be _universal_ for any general purpose. And it would allow you to deserialize dates to `java.util.Date`, and then format dates to any locale. Again, `ISO_LOCAL_DATE` can be supposed as the transport for encoding the dates, whilst the locale-oriented dates can be formatted on demand for a particular user based on his/her preferences. — Lyubomyr Shaydariv, Mar 08 '17 at 14:22
ok i now have a format: DateTimeFormatter format = DateTimeFormatter.ofPattern(ISO_LOCAL_DATE); but i cant find out how to go on from there, getting my resultSet.getObject(column) to be formated to String. ill continue research. — Uwe Pfeifer, Mar 08 '17 at 14:27
1) Extract a date from a result set. 2) Convert it to `LocalDate` (there are dozens Q/A at S.O. regarding this question, however Javadocs are your best friends.) 3) Format the date using the `ISO_LOCAL_DATE` formatter. 4) Write it to the JSON writer as a string. Then at the client side: 4) Read the date string from the JSON reader. 3) Parse the string to create `LocalDate` (note the trick: it's always `ISO_LOCAL_DATE`-formatted as a standard, so no format info is passed along). 2) Format the `LocalDate` date to the end-user upon his/her regional settings. 1) Enjoy having it done. That's all — Lyubomyr Shaydariv, Mar 08 '17 at 14:27
hm, is this correct: LocalDate localDate = (LocalDate) resultSet.getObject(column); DateTimeFormatter format = DateTimeFormatter.ISO_LOCAL_DATE; String formattedDate = localDate.format(format); jsonWriter.value((String) formattedDate); if yes Id need the same for a timestamp. ill look that up now. — Uwe Pfeifer, Mar 08 '17 at 14:42
ok now i need to do the same just for the timestamp. ill try to have a look. — Uwe Pfeifer, Mar 08 '17 at 14:59

Uwe Pfeifer · Answer 1 · 2017-03-08T15:06:55.543

Ok, I finally found out how to do it (I hope, not 100% sure as I couldn't display data on client yet - but it compiles and runs. Here's the code:

    case Types.DATE:
        java.sql.Date sqlDate = (java.sql.Date) resultSet.getObject(column);
        LocalDate localDate = sqlDate.toLocalDate();
        DateTimeFormatter formatDt = DateTimeFormatter.ISO_LOCAL_DATE;
        String formattedDate = localDate.format(formatDt);
        jsonWriter.value((String) formattedDate);
        break;

I hope I didn't format it twice the same way? :)

Here also the code for the Timestamp:

case Types.TIMESTAMP:
    java.sql.Timestamp sqlTimeStamp = (java.sql.Timestamp) resultSet.getObject(column);
    LocalDateTime localDateTime = sqlTimeStamp.toLocalDateTime();
    DateTimeFormatter formatTimestamp = DateTimeFormatter.ISO_LOCAL_DATE;
    String formattedDateTime = localDateTime.format(formatTimestamp);
    jsonWriter.value((String) formattedDateTime);
    break;

A few: 1) You can use `getDate()` and `getTimestamp()` rather than `getObject()` with cast. 2) You should check if `sqlDate` and `sqlTimeStamp` are `null` first and, if yes, write `null` to the output with `jsonWriter.nullValue();` or your code crashes. 3) Otherwise, if there were no null, just inline the redundant `format*` variables and use `ISO_LOCAL_DATE` directly (or use something like `private static final ...` to "save" the formatted within the class showing your intention). 4) `Timestamp` also has the time part, so `ISO_LOCAL_DATE_TIME` fits better. 5) Check whether _ms_ can be written — Lyubomyr Shaydariv, Mar 09 '17 at 15:04

MySQL ResultSet JsonWriter: jsonWriter.value((java.sql.Date) resultSet.getObject(column));

1 Answers1