Java SQLite: Database Management in Java

Have you ever needed to use a database in your Java application but didn’t want to deal with installing, configuring and maintaining a full database server? SQLite is the solution! SQLite is a self-contained, simple and lightweight relational database that doesn’t require any separate server processes or configuration. It’s perfect for adding database functionality to desktop, mobile and web apps written in Java.

In this comprehensive guide, you’ll learn how to set up SQLite in a Java project, perform basic database operations like creating tables, inserting data, querying and processing results. We’ll cover JDBC for database connectivity along with popular Java SQLite libraries that make things even easier.

Getting Started with SQLite and Java

To use SQLite in Java, you need to add the SQLite JDBC driver as a dependency. Here’s how to set it up in both Maven and Gradle:

<!-- Maven -->
<dependency>
  <groupId>org.xerial</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>3.36.0.3</version>
</dependency>

// Gradle
implementation 'org.xerial:sqlite-jdbc:3.36.0.3'

This will download the required JAR files. Next create a Connection to a new or existing SQLite database file:

import java.sql.Connection;
import java.sql.DriverManager;

public class MySQLiteExample {

  public static void main(String[] args) throws Exception {
    Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db");
  }

}

Now you have a connection conn to a SQLite database named sample.db and can start executing SQL statements.

Creating Tables in SQLite from Java

Let’s create a simple table to store contact information. We’ll use JDBC’s Statement to execute the SQL:

Statement stmt = conn.createStatement();

stmt.execute(
  "CREATE TABLE Contacts (" +
    "contact_id INTEGER PRIMARY KEY AUTOINCREMENT, " +  
    "first_name TEXT NOT NULL, " +
    "last_name TEXT NOT NULL, " +
    "phone TEXT NOT NULL, " + 
    "email TEXT NOT NULL" +
  ")"  
);

This SQL statement creates a table with named columns of appropriate SQLite data types. contact_id column is special:

• It’s declared INTEGER PRIMARY KEY AUTOINCREMENT so SQLite will populate it automatically with a unique ID for every inserted row.
• This saves you from managing unique IDs yourself every time you insert data.

Let’s confirm it worked by querying all data from this brand new table:

ResultSet rs = stmt.executeQuery("SELECT * FROM Contacts");
while(rs.next()) {
  System.out.println(rs.getInt("contact_id") + ", " +  
                     rs.getString("first_name") + " " + 
                     rs.getString("last_name"));
}

Since we haven’t inserted anything yet, above code won’t print any rows. But our Contacts table is now ready!

Inserting Data into SQLite Tables

Let’s add some contact data by again using SQLite INSERT SQL statement:

// Insert one row  
stmt.execute(
  "INSERT INTO Contacts (first_name, last_name, phone, email) " +
  "VALUES('Sachin', 'Sharma', '9876543210', '[email protected]')"
);

// Insert multiple rows together  
stmt.execute(
  "INSERT INTO Contacts (first_name, last_name, phone, email) VALUES " +
  "('John', 'Doe', '1234567890', '[email protected]'), " +
  "('Tony', 'Stark', '0987654321', '[email protected]')"  
);

Now run the same SELECT query from before and you’ll see the inserted rows printed out.

Querying Data with SELECT Statements

SQLite supports full SQL so you can query contacts data with rich and complex SELECT logic:

// Get all rows
SELECT * FROM Contacts

// With WHERE condition
SELECT * FROM Contacts
WHERE first_name = 'Sachin'

// Sorting resultset  
SELECT * FROM Contacts
ORDER BY last_name DESC

// Specific columns
SELECT first_name, last_name, phone 
FROM Contacts

As seen above, you can filter rows, sort resultsets and select specific columns needed by your application logic.

Updating and Deleting Rows

To modify data, use the SQL UPDATE statement. For example, to update Sachin’s phone number:

UPDATE Contacts 
SET phone = '0123456789'
WHERE contact_id = 1

Make sure to have the WHERE condition else all rows would get updated!

Similarly, to delete rows use DELETE statement:

DELETE FROM Contacts
WHERE email LIKE '%@olddomain.com'

This will delete contacts with the old email domain.

Transactions in SQLite

When making multiple changes to SQLite database, you typically want them as a transaction so that either all of them succeed or fail together. This prevents data corruption issues.

Wrap multiple statements in a transaction:

conn.setAutoCommit(false); // Start transaction block

try {

  // SQL execute statements  
  stmt.execute(/* SQL 1 */); 
  stmt.execute(/* SQL 2 */);

  conn.commit(); // Commit transaction if all good

} catch (Exception e) {
  
  conn.rollback(); // Rollback on error
  
} finally {

  conn.setAutoCommit(true); // Reset default commit
  
}

This ensures atomicity of database changes made by multiple SQL statements.

Using SQLite with Java Frameworks

While you can use raw JDBC as shown above, for complex data and production applications consider using SQLite with popular Java persistence frameworks like:

• Hibernate – De facto standard ORM in Java
• Spring Data JPA – Simple data access framework from Spring ecosystem
• QueryDSL – Typesafe SQL construction

These integrate with SQLite data sources and provide higher level APIs, reducing boilerplate database code.

For example to insert data with Hibernate:

Session session = factory.openSession(); // Hibernate Session
            
Transaction tx = session.beginTransaction(); 

Contact contact = new Contact(); // Entity class
        
contact.setFirstName("John");
contact.setLastName("Wick");
        
session.save(contact); // Persist entity
        
tx.commit(); // Commit transaction
session.close(); 

Much simpler than manual JDBC! Read Hibernate SQLite guide for details.

SQLite GUI Tools

While running SQLite from Java is very convenient, you may also want to visually inspect the database contents externally. Here are some useful SQLite GUI tools:

These provide schema browse, query builders, export options and other useful functionality.

So in summary – SQLite offers a simple local database option for desktop and mobile apps written in Java. With its small footprint and zero configuration, it can serve as an embedded lightweight data store. This guide covered the basics of using SQLite from plain JDBC to integrating with popular Java persistence frameworks like Hibernate. Give SQLite a try next time you need to add database functionality to your Java project!