CODEWITHSUNDEEP
javamysqlresultset
Forseth11
Forseth11

Reputation: 1438

How to use Java ResultSet to convert the results to a String

I am creating a program to remotely connect to a MySQL server and create a .sql file without and console access. I am manually doing it to keep track of the progress completed.

In the .sql dump files, there is a line(s) which is INSERT INTO. I am easily able to make this work with numbers and String objects, but how can I go about doing this with Blobs and Dates?

Is there any way I can use set.getObject(column); and convert that into a String which I can place into the .sql file?

If not, could I use set.getMetaData().getColumnType(column) then compare to each of the many data types? Then from there, how can I convert that into a String to place into the file?

Thank you for any help.

Also, I made this post earlier, and it provides additional information about what I am doing and why.

EDIT: This was marked as a duplicate, but the other answer does not answer my question even in the slightest. This explains how to do it with text datatypes. I need to know how to do it with BLOBS, Date, and all other SQL datatypes.

EDIT 2: I need a solution which will work with all datatypes, not just the ones mentioned.

Upvotes: 0

Views: 779

Answers (1)

Serg M Ten
Serg M Ten

Reputation: 5606

For the dates use ResultSet.getDate() and then convert to String using SimpleDateFormat with yyyy-MM-dd HH:mm:ss pattern. Or ResultSet.getTimestamp() and create a new Date from the Timestamp time in milliseconds. Then write the date-to-string into the generated SQL file with CAST('2018-03-12 14:15:16' AS DATETIME) or CAST('2018-03-12 14:15:16' AS DATE).

For the blobs use ResultSet.getBinaryStream() write the stream into a byte[] array. Then walk the array and generate an hexadecimal string with two digits per byte. Last put the string into output SQL as INSERT INTO table (the_blob_column) VALUES (x'89A099D153EF')

Upvotes: 2

Related Questions