Java json from resultset

How to read/retrieve data from Database to JSON using JDBC?

A Json array is an ordered collection of values that are enclosed in square brackets i.e. it begins with ‘[’ and ends with ‘]’. The values in the arrays are separated by ‘,’ (comma).

Sample JSON array

The json-simple is a light weight library which is used to process JSON objects. Using this you can read or, write the contents of a JSON document using Java program.

JSON-Simple maven dependency

Following is the maven dependency for the JSON-simple library −

  com.googlecode.json-simple json-simple 1.1.1  

Paste this within the tag at the end of your pom.xml file. (before tag)

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );

Now, we will insert 5 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'); insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

To read the contents of the above created MyPlayers table to a JSON file using JDBC −

Retrieve the contents of the MyPlayers table

• Register the Driver class of the desired database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

• Create a connection object by passing the URL of the database, user-name and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

• Create a Statement object using the createStatement() method of the connection interface.

Statement stmt = con.createStatement();

• Execute the SELECT query to retrieve the contents of the MyPlayers table into the ResultSet object, using the executeQuery() method.

String query = "Select * from MyPlayers"; ResultSet rs = stmt.executeQuery(query);

Create a JSON array and add the retrieved MyPlayers data

• Create a JSON documents (to represent the MyPlayers table) by Instantiating the JSONObject class of the json-simple library.

//Creating a JSONObject object JSONObject jsonObject = new JSONObject();

• Create a JSON array to hold the records of the MyPlayers table, by instantiating the JSONArray class.

JSONArray array = new JSONArray();

• For each record in the MyPLayers table, create a JSON (again) object insert the contents of the record (obtained from the ResultSet object) into it using the put() method.

Finally, add the JSON object to the array created in the previous step.

Write the JSON object to a file using FileReader

• After adding all the required records of the JSON array add it to the parent JSON object using the put() method

jsonObject.put("Players_data", array);

FileWriter file = new FileWriter("E:/json_array_output.json"); file.write(jsonObject.toJSONString()); file.close();

Following JDBC program reads the contents of the MyPlayers table into a JSON file.

Example

import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import org.json.simple.JSONArray; import org.json.simple.JSONObject; public class DataBaseToJson < public static ResultSet RetrieveData() throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/sample_database"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Creating the Statement Statement stmt = con.createStatement(); //Retrieving the records ResultSet rs = stmt.executeQuery("Select * from MyPlayers"); return rs; >public static void main(String args[]) throws Exception < //Creating a JSONObject object JSONObject jsonObject = new JSONObject(); //Creating a json array JSONArray array = new JSONArray(); ResultSet rs = RetrieveData(); //Inserting ResutlSet data into the json object while(rs.next()) < JSONObject record = new JSONObject(); //Inserting key-value pairs into the json object record.put("ID", rs.getInt("ID")); record.put("First_Name", rs.getString("First_Name")); record.put("Last_Name", rs.getString("Last_Name")); record.put("Date_Of_Birth", rs.getDate("Date_Of_Birth")); record.put("Place_Of_Birth", rs.getString("Place_Of_Birth")); record.put("Country", rs.getString("Country")); array.add(record); >jsonObject.put("Players_data", array); try < FileWriter file = new FileWriter("E:/output.json"); file.write(jsonObject.toJSONString()); file.close(); >catch (IOException e) < // TODO Auto-generated catch block e.printStackTrace(); >System.out.println("JSON file created. ); > >

Output

If you observe the output.json file you can see the read content as −

Источник

Converting a JDBC ResultSet to JSON in Java

Repeatedly, code that works in dev breaks down in production. Java performance issues are difficult to track down or predict.

Simply put, Digma provides immediate code feedback. As an IDE plugin, it identifies issues with your code as it is currently running in test and prod.

The feedback is available from the minute you are writing it.

Imagine being alerted to any regression or code smell as you’re running and debugging locally. Also, identifying weak spots that need attending to, based on integration testing results.

Of course, Digma is free for developers.

As always, the writeup is super practical and based on a simple application that can work with documents with a mix of encrypted and unencrypted fields.

We rely on other people’s code in our own work. Every day.

It might be the language you’re writing in, the framework you’re building on, or some esoteric piece of software that does one thing so well you never found the need to implement it yourself.

The problem is, of course, when things fall apart in production — debugging the implementation of a 3rd party library you have no intimate knowledge of is, to say the least, tricky.

Lightrun is a new kind of debugger.

It’s one geared specifically towards real-life production environments. Using Lightrun, you can drill down into running applications, including 3rd party dependencies, with real-time logs, snapshots, and metrics.

Learn more in this quick, 5-minute Lightrun tutorial:

Slow MySQL query performance is all too common. Of course it is. A good way to go is, naturally, a dedicated profiler that actually understands the ins and outs of MySQL.

The Jet Profiler was built for MySQL only, so it can do things like real-time query performance, focus on most used tables or most frequent queries, quickly identify performance issues and basically help you optimize your queries.

Critically, it has very minimal impact on your server’s performance, with most of the profiling work done separately — so it needs no server changes, agents or separate services.

Basically, you install the desktop application, connect to your MySQL server, hit the record button, and you’ll have results within minutes:

DbSchema is a super-flexible database designer, which can take you from designing the DB with your team all the way to safely deploying the schema.

The way it does all of that is by using a design model, a database-independent image of the schema, which can be shared in a team using GIT and compared or deployed on to any database.

And, of course, it can be heavily visual, allowing you to interact with the database using diagrams, visually compose queries, explore the data, generate random data, import data or build HTML5 database reports.

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

1. Overview

In some scenarios, we might need to send the result of a database query via an API call to another system or a messaging platform. For such cases, we often use JSON as the data exchange format.

In this tutorial, we’ll see multiple ways to convert a JDBC ResultSet object to the JSON format.

2. Code Example

We’ll use the H2 database for our code example. We have a sample CSV file, which we’ve read into a table words using JDBC. Here are three lines from the sample CSV file, with the first line being the header:

Username,Id,First name,Last name doe1,7173,John,Doe smith3,3722,Dana,Smith john22,5490,John,Wang

The line of code to form the ResultSet looks like this:

ResultSet resultSet = stmt.executeQuery("SELECT * FROM words");

For JSON processing, we use the JSON-Java (org.json) library. First, we add its corresponding dependency to our POM file:

3. Using No External Dependencies

The JDBC API predates modern Java collection frameworks. Therefore, we cannot use the likes of for-each iteration and Stream methods.

Instead, we have to rely on iterators. Moreover, we need to extract the number and list of column names from the metadata of the ResultSet.

This leads to a basic loop consisting of forming a JSON object per row, adding objects to a List, and finally converting that List to a JSON array. All these functionalities are available in the org.json package:

ResultSetMetaData md = resultSet.getMetaData(); int numCols = md.getColumnCount(); List colNames = IntStream.range(0, numCols) .mapToObj(i -> < try < return md.getColumnName(i + 1); >catch (SQLException e) < e.printStackTrace(); return "?"; >>) .collect(Collectors.toList()); JSONArray result = new JSONArray(); while (resultSet.next()) < JSONObject row = new JSONObject(); colNames.forEach(cn -> < try < row.put(cn, resultSet.getObject(cn)); >catch (JSONException | SQLException e) < e.printStackTrace(); >>); result.add(row); >

Here, we first run a loop to extract the name of each column. We later use these column names in forming the resulting JSON object.

In the second loop, we go through the actual results and convert each one to a JSON object, using the column names we computed in the previous step. We then add all these objects to a JSON array.

We have left the extraction of column names and column count out of the loop. This helps in making the execution faster.

The resulting JSON looks like this:

4. Using jOOQ with Default Settings

The jOOQ framework (Java Object Oriented Querying) provides, among other things, a set of convenient utility functions to work with JDBC and ResultSet objects. First, we need to add the jOOQ dependency to our POM file:

After adding the dependency, we can actually use a single-line solution for converting a ResultSet to a JSON object:

JSONObject result = new JSONObject(DSL.using(dbConnection) .fetch(resultSet) .formatJSON());

The resulting JSON element is an object consisting of two fields called fields and records, where fields have the names and types of the columns, and records contain the actual data. This is slightly different from the previous JSON object and looks like this for our example table:

5. Using jOOQ with Customized Settings

In case we don’t like the default structure of the JSON object produced by jOOQ, there is room to customize it.

We’ll do this by implementing the RecordMapper interface. This interface has a map() method that receives a Record as input and returns the desired object of an arbitrary type.

We then feed the RecordMapper as input to the map() method of the jOOQ result class:

List json = DSL.using(dbConnection) .fetch(resultSet) .map(new RecordMapper() < @Override public JSONObject map(Record r) < JSONObject obj = new JSONObject(); colNames.forEach(cn ->obj.put(cn, r.get(cn))); return obj; > >); return new JSONArray(json); 

Here, we returned a JSONObject from the map() method.

The resulting JSON looks like this, similar to Section 3:

6. Conclusion

In this article, we explored three different ways to convert a JDBC ResultSet to a JSON object.

Each approach can have its own uses. What we choose depends on the required structure of the output JSON object and possible limitations on the dependency size, for example.

As always, the source code for the examples is available over on GitHub.

Slow MySQL query performance is all too common. Of course it is. A good way to go is, naturally, a dedicated profiler that actually understands the ins and outs of MySQL.

The Jet Profiler was built for MySQL only, so it can do things like real-time query performance, focus on most used tables or most frequent queries, quickly identify performance issues and basically help you optimize your queries.

Critically, it has very minimal impact on your server’s performance, with most of the profiling work done separately — so it needs no server changes, agents or separate services.

Basically, you install the desktop application, connect to your MySQL server, hit the record button, and you’ll have results within minutes:

Источник