Parse and Update Dynamic JSON into Database with esProc

Problem source: http://bbs.csdn.net/topics/390611005

Below is JSON data (s.json) the system acquires:   

{     "SUCCESS": [         {             "MESSAGE": "IMEI Service List",             "LIST": {                 "MOVISTAR SPAIN": {                     "GROUPNAME": "MOVISTAR SPAIN",                     "SERVICES": {                         "3": {                             "SERVICEID": 32,                             "SERVICENAME": "MOVISTAR NOKIA INSTANTE",                             "CREDIT": 4,                             "TIME": "1-30 Minutes",                             "INFO": "<p style=\"text-align: center;\">…… </p>",                             "Requires.Network": "None",                             "Requires.Mobile": "None",                             "Requires.Provider": "None",                             "Requires.PIN": "None",                             "Requires.KBH": "None",                             "Requires.MEP": "None",                             "Requires.PRD": "None",                             "Requires.Type": "None",                             "Requires.Locks": "None",                             "Requires.Reference": "None"                         },                         "8": {                             "SERVICEID": 77,                             "SERVICENAME": "MOVISTAR NOKIA 20 NCK",                             "CREDIT": 12,                             "TIME": "1-30 Minutes",                             "INFO": "<p style=\"text-align: center;\">……</p>",                             "Requires.Network": "None",                             "Requires.Mobile": "None",                             "Requires.Provider": "None",                             "Requires.PIN": "None",                             "Requires.KBH": "None",                             "Requires.MEP": "None",                             "Requires.PRD": "None",                             "Requires.Type": "None",                             "Requires.Locks": "None",                             "Requires.Reference": "None"                         }                     }                 },                 "VODAFONE SPAIN": {                     "GROUPNAME": "VODAFONE SPAIN",                     "SERVICES": {                         "5": {                             "SERVICEID": 50,                             "SERVICENAME": "VODAFONE NOKIA BB5 SL3",                             "CREDIT": 5,                             "TIME": "1-60 Minutes",                             "INFO": "<p style=\"text-align: center;\">……</p>",                             "Requires.Network": "None",                             "Requires.Mobile": "None",                             "Requires.Provider": "None",                             "Requires.PIN": "None",                             "Requires.KBH": "None",                             "Requires.MEP": "None",                             "Requires.PRD": "None",                             "Requires.Type": "None",                             "Requires.Locks": "None",                             "Requires.Reference": "None"                         },                         "10": {                             "SERVICEID": 95,                             "SERVICENAME": "VODAFONE SONY&;SONY ERIC(RAPIDO)",                             "CREDIT": 16,                             "TIME": "1-24 Hours",                             "INFO": "<p style=\"text-align: center;\">……</p>",                             "Requires.Network": "None",                             "Requires.Mobile": "None",                             "Requires.Provider": "None",                             "Requires.PIN": "None",                             "Requires.KBH": "None",                             "Requires.MEP": "None",                             "Requires.PRD": "None",                             "Requires.Type": "None",                             "Requires.Locks": "None",                             "Requires.Reference": "None"                         }                     }                 }             }         }     ],     "apiversion": "2.0.0" }

Based on above JSON data, you need to update database tables with property values of corresponding section. Below is the two tables need updating:

Create table [dbo].[Groups]

(

  [ID] [int] IDENTITY(1,1) NOT NULL,                   --id

  [Groupname] [nvarchar] (50) not null default(''),    --name

  [groupid] [int] not null default(0),

 CONSTRAINT [PK_Groups_id] PRIMARY KEY CLUSTERED

(

    [id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Services](

  [id]    [int] IDENTITY(1,1) NOT NULL,                             --id

  [Serviceid] [int] not null default(0),           

  [Servicename] [nvarchar] (50) not null default(''),   

  [groupid] [int] not null default(0),                

  [Credit] [decimal] not null default(0.00),

  [Time] [nvarchar] (50) not null default(''),

  [INFO] [nvarchar] (3000) not null default(''),

  [Network] [nvarchar] (100) not null default('none'),

  [Mobile] [nvarchar] (100) not null default('none'),

  [Provider] [nvarchar] (100) not null default('none'),

  [PIN] [nvarchar] (100) not null default('none'),

  [KBH] [nvarchar] (100) not null default('none'),

  [MEP] [nvarchar] (100) not null default('none'),

  [PRD] [nvarchar] (100) not null default('none'),

  [Type] [nvarchar] (100) not null default('none'),

  [Locks] [nvarchar] (100) not null default('none'),

  [Reference] [nvarchar] (100) not null default('none'),

  [isstatus] [nvarchar] (1) not null default('0'),

  [remark] [nvarchar] (255) not null default(''),

  [Pricingid] [int] not null default(0),

 CONSTRAINT [PK_Services_id] PRIMARY KEY CLUSTERED

(

    [id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

The property for SERVICES is groupid, such as 3, 5, 8, 10. The rest of the fields correspond to other properties respectively. That the property names under LIST and SERVICES are not fixed makes the data parsing difficult. Compared with common high-level languages, esProc supports dynamic data structure and set operations and thus can provide easy solution. esProc script is as follows:

A1: Read JSON file into strings and convert them into a cascaded table sequence with rows and columns using import@j().

A2-A3: Create empty table sequences based on the two target tables, in order to store parsing results that will be updated into the database in one go.

A4-B4: Run a loop in A4 and calculate the number of sections under LIST in B4.

B5-C6: Get the content of each section of LIST by loop and calculate the number of sections under SERVICES in C6.

C7-D8: Get property names and values from each SERVICES’s section by loop.

D9-D10: Write parsing results respectively back into the empty table sequences in A2 and A3.

A11: Update A2’s data into groups table through the primary key groupid.

A12: Update A3’s data into services table through the primary key Serviceid.

Export Dynamic Text Files to Database with esProc

Problem source: http://bbs.csdn.net/topics/330190185

There are multiple text files in a certain directory. Each of their names includes one of the letters a/b/c at the beginning and a date. The letter is fixed while the date is dynamic. You need to write contents of these files as well as the dates into a database table by a specified date. The text files are as follows:

a20100325.txt

col1 col2 col3

11    22    33

b20100325.txt

col1 col2 col3

44    55    66

c20100325.txt

col1 col2 col3

77    88    99

The expected database table, tb1, after data export is as follows:

col1 col2 col3 d_date

11    22    33    20100325

44    55    66    20100325

77    88    99    20100325

esProc script for doing this:

A1: A sequence composed of a, b, c.

A2: Import text files named after the three letters in A1 and the dates specified by parameter ddate by loop. Merge the three files using conj() and add a date column (d_date) with derive. Then enter the dates as values of the newly added column.

A3: Update the table tb1 with A2’s result. @i means inserting data only. 

esProc Helps Process Structured Text in Java - Import data into the database

While importing the structured text files into the database using Java alone, we need to combine the SQL statements together manually, and to deal with various troublesome situations as well, like if the data in a table has been existed, whether we should update it or insert data into it, if some fields are included in the file, and if the fields in the file are consistent with those in the table.

As esProc participates in Java programming, these problems can be solved without self-programmed code. Let's give an example to explain this clearly.

The text file sales.txt contains data of sales orders, with tab being the column separator. The first lines of data are as follows: 

Now it is required to import the data of sales.txt into an empty table of the same structure.

First, write the script in esProc and name it test.dfx.

In the above script, import function is used to import the text file. tab is the column separator by default and the option @t means the first row is set as the column name. update function is used to import the data in A1 into the database in batches. OracleDB is the name of data source. Tsales is the table name.

With the script, all data has been imported into the database. Then we just need to call the script in Java code.

         // create a connection using esProc jdbc

         Class.forName("com.esproc.jdbc.InternalDriver");

         con= DriverManager.getConnection("jdbc:esproc:local://");

         // call the esProc script, whose name is test.

         st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

         // execute the esProc script in a similar way as executing the stored procedure

         st.execute();

After the above Java code is executed, sales.txt is imported by the esProc engine into table Tsales.

This script written in esProc is simple, so it can be embedded directly in Java code without creating a script file. The code is written as follows:

st.executeQuery("=OracleDB.update(file("E:\\sales.txt").import@t(),Tsales)");

Sometimes the table in the database may not be empty. In this case, we shouldn't import all data into it. We should first compare the data in the file with that in the table according to the primary keys instead. update statements will be generated if the data shares the same primary key and insert statements will be generated if the primary keys in the text file cannot be found in the table. There are several situations:

If the table in the database already has primary keys (it is OrderID in this example), the code remains the same as that in the above. In other words, the esProc engine can automatically compare the primary keys (including composite primary keys) of the database with the fields of the text file and generate corresponding update statements or insert statements.

But if there are no primary keys in the table, we just need to set them manually in the update function, that is, change the script in cell A2 into    =OracleDB.update(A1,Tsales;OrderID).  

If we don't want to change the original data in the table, we need to add a function option @i to the script to make esProc generate the insert statements only, like =OracleDB.update@i(A1,Tsales). Similarly, option @u means generating update statements only.

In the example above, we assumed that the table in the database and the text file are of the same structure. But, in reality, they may not have the same structure. For example, the table has three more fields than the text file: State, OrderYear and Memo. It is required to fill a default value "done" in the field State of the table, compute the years of OrderYear according to the data of OrderDate, and make Memo remains empty. To solve this problem, we just need to write script as follows:

In the script, derive function can add new fields, or computed columns, to A1.update function will automatically skip field Memo.

If the number of fields in the table is less than that in the text file, only some of the columns should be imported. For example:

         =file("E:\\sales.txt").import@t(OrderID,SellerId,Amount,OrderDate)

This line of script code means field Client will be skipped and only the rest of four fields will be imported from the file.

In the example above, the first line of the text file happens to be the names of fields in the table. But sometimes there is no information about column names and we need to set them by ourselves. The code is as follows:

It can be seen that as the first line is not the column names, there is no need to use option @t in import function. According to the code in A1, the related data will get default names for its fields: _1, _2, _3 and so forth. Order_ID:_1 in update function represents importing field "_1" in A1 into field OrderID of the table. And the rest can be done in the same manner.

If the names of fields in the text file are different from those in the table. For example, field Client in the table becomes ClientID in the text file. We can use the same method to match them. The code is as follows:

The column separator in the example above is tab, but sometimes it is comma or other signs. In such cases, just modify import function into =file("E:\\sales.txt").import(; ",").