Connect:

Search

Suggested keywords:
  • Java
  • Docker
  • Git
  • React
  • NextJs
  • Spring boot
  • Laravel

Explore SQLite JSON Operations Using JDBC

By Editorial Team
Nov 5, 2024 3 min read 1782 views
  • Share this:

post-title

SQLite is an library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine and provides support to create embedded database. SQLite is very well tested and most widely used in embedded platforms. It provides a complete SQL database with support for multiple tables, indices, triggers, and views, JSON Functions, Full text search and lot more.

In this article, we will explore SQLite JSON operations using JDBC. Refer to the article to learn about How to perform CRUD operations in SQLite using JDBC.

Storing JSON data in database is inevitable. Most of the database provide JSON functions to work with JSON column. We will explore few of the most important functions.

Dependency

<dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
      <version>3.46.1.3</version>
</dependency>

 

Let's get started

Let's connect to the database and create statement object.

Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

Statement statement = connection.createStatement();

 

Create table with JSON column.

statement.executeUpdate("CREATE TABLE employee (id integer, name string, data json)");

 

Add few records to the employee table.

statement.executeUpdate("insert into employee values(1, 'Raj', '{\"country\": \"India\"}')");
statement.executeUpdate("insert into employee values(2, 'Alex', '{\"country\": \"US\"}')");
statement.executeUpdate("insert into employee values(3, 'Kishore', '{\"country\": \"Singapore\"}')");
statement.executeUpdate("insert into employee values(4, 'Bob', '{\"country\": \"UK\"}')");

 

Now we have added JSON data to the employee table. Lets query and display the data.

ResultSet rs = statement.executeQuery("select * from employee");

while(rs.next())
{
	// read the result set
	System.out.println("id = " + rs.getInt("id"));
	System.out.println("name = " + rs.getString("name"));
	System.out.println("data = " + rs.getObject("data"));
	System.out.println("");
}

 

Data will be displayed as below.

id = 1
name = Raj
data = {"country": "India"}

id = 2
name = Alex
data = {"country": "US"}

id = 3
name = Kishore
data = {"country": "Singapore"}

id = 4
name = Bob
data = {"country": "UK"}

 

Now lets insert json field to existing JSON column. We will add countryCode to existing data column.

statement.executeUpdate("update employee set data = json_set(data, '$.countryCode', 'IN') 
                               where data->>'country' = 'India'");

 

We can use either json_set or json_insert function. The function takes arguments like source json field, json path and value. Json path has to be refered as $.fieldname.

Print the results.

id = 1
name = Raj
data = {"country":"India","countryCode":"IN"}

 

We can remove a particular field from JSON column using json_remove function. The functions takes source field and a json path as argument.

statement.executeUpdate("update employee set data = jsonb_remove(data, '$.countryCode') 
                                            where data->>'country' = 'India'");

 

Extract a particular field from JSON field we can use ->, ->> operator or json_extract function.

Below statement uses ->> operator to access json field.

ResultSet rs = statement.executeQuery("select id, name, data->>'country' as country from employee");

while(rs.next())
 {
   // read the result set
	System.out.println("id = " + rs.getInt("id"));
	System.out.println("name = " + rs.getString("name"));
	System.out.println("country = " + rs.getString("country"));
	System.out.println("");
 }

 

Below statement uses json_extract function.

ResultSet rs = statement.executeQuery("select id, name, json_extract(data,'$.country') 
                       as country from employee");

 

Conclusion

Using JSON fields are inevitable in database. SQLite database has very good performance in using JSON fields.

sqlitejdbc

Editorial Team

About author
This article is published by our editorial team.
Newsletter
Related Articles