2

I know the title is quite cliched, but it is not about storing JSON data in SQL Server.

I have a JSONArray with JSONObjects with keys that match the column names in SQL Server 2012. I want to save the data to the database into the proper columns.

I know the obvious way to do this is the iterate through the JSONArray and save the values with individual insert commands. I was wondering if there was another way to do this.

I don't want to use T-SQL. I want to handle this from Java only.

Here is an example data that matches the format of my JSONArray:

[
    {
        "FEATURE":"A",
        "OPTION":"92384",
        "ERROR_TYPE":"MISSING",
        "DESCRIPTION":"Feature A is missing the option 92384",
        "SERIAL_NUMBER":"249752-23894"
    },
    {
        "FEATURE":"B",
        "OPTION":"0288394",
        "ERROR_TYPE":"MISSING",
        "DESCRIPTION":"Feature B is missing the option 0288394",
        "SERIAL_NUMBER":"Y2394-20392Q"
    }
]

My SQLServer table looks like this: SQL table

What would be best way to achieve this without looping through each JSONArray?

Penman
  • 163
  • 3
  • 14
  • 1
    I would suggest you use nvarchar instead of varchar. You might run into encoding difficulties along the way otherwise. – Tschallacka Aug 24 '17 at 08:00
  • 1
    @Tschallacka Noted. Will change that. – Penman Aug 24 '17 at 08:33
  • If you can update your database software to 2016 you can use nifty features such as https://learn.microsoft.com/en-us/sql/relational-databases/json/use-for-json-output-in-sql-server-and-in-client-apps-sql-server – Tschallacka Aug 24 '17 at 08:37
  • @Tschallacka I am aware that SQLServer 2016 supports a lot of features regarding JSON, but my project has been set entirely on SQLServer 2012 - so, changing it is a no go. – Penman Aug 24 '17 at 08:52

2 Answers2

1

As you have added java tag I would convert JSON to Java object and save it with Hibernate. Here are two useful links how to do that Json to Java Hibernate example

Jānis S
  • 51
  • 5
  • JSON data is being generated based on another functionality. I went for JSON rather than Java object intentionally - because it suits by business requirement. Also, I don't know how else to put this, but I cannot use Hibernate/Maven where I'm working. It has to be purely Java only. – Penman Aug 24 '17 at 08:51
-1
DECLARE @testJson NVARCHAR(4000)= N'[{"id":2,"name":"n2"},{"id":1,"name":"n1"}]'

INSERT
INTO
    test_table SELECT
        *
    FROM
        OPENJSON(@testJson) WITH (
            id int N'$.id',
            name VARCHAR(200) N'$.name'
        )

Update

use java

jsonStr -> jsonObject and getKeys (some json libs )

String youJson = "{\"id\":0, \"name\":\"n0\"}";
JsonParser parser = new JsonParser();
JsonObject jsonObject = parser.parse(json).getAsJsonObject();
Set<String> keys = jsonObject.keySet();

Then use NamedParameterJdbcTemplate and MapSqlParameterSource (spring-jdbc) to create sql and set parameters dynamically .

PS. I hate ORM in java.

yang hu
  • 9
  • 3