Insert the json format into mysql and recover in Java

Asked

Viewed 949 times

2

I have the following string in format json:

{
  "ADT":"0",
  "CHD":"0",
  "INF":"0"
}

I need to do the Insert in a table on mysql database and retrieve it in Java. I’m using VARCHAR(50) and it looks like this:

{
"ADT":"0",
"CHD":"0",
"INF":"0"

I saw that in Mysql you have the type of field that is called Blob, used for binaries, but not understood how to recover in Java.

java mysql json
  • 2

    Depending on the size, it can be a varchar column or even text.

  • Okay, thanks I’ll try the text

  • 3

    You can retrieve Blob as a byte array.

  • Postgresql has a wide variety of column types, including XML and JSON. Mysql, as far as I know, is just text and blob.

  • You can put only the string itself and then add the {} keys in java or use some stringfy command.

2 answers

0

If the JSON have only these three properties, maybe it is better to persist as text. Anyway, Java has an object to work with Blob:

Inserting:

String jsonString = "{'ADT':'0','CHD':'0','INF':'0'}";

Blob blob = con.createBlob(); // 'con' = um objeto de conexão criado anteriormente
blob.setBytes(1, jsonString.getBytes());

PreparedStatement pstm = con.prepareStatement("INSERT INTO foo VALUES(?)");
pstm.setBlob(1, blob);


Recovering:

Blob blob = rs.getBlob("data"); // 'rs' = algum ResultSet criado anteriormente
byte[] blobBytes = blob.getBytes(1, (int) blob.length());
String jsonString = new String(blobBytes);

0

BLOB type fields we use to save files like image, documents, etc. If you want a large text field use CLOB.

Beware of CLOB, as it is a very heavy field and can harm you in querys that bring many records from this table. I’ve had problems with CLOB in database copies, in dumps, avoid if you can.

If it’s a small JSON saved as actual text! (Mysql is varchar? I don’t remember...)

If you want to turn JSON into an object when you return with it from the database use Google GSON.

Hugs

Browser other questions tagged java mysql json

You are not signed in. Login or sign up in order to post.