Prerequisites:
You have to complete the following tasks before executing the code of this tutorial:
A. You can install the latest version of Java to practice the code of this tutorial. Run the following command to install the openjdk-19-jre that is used here:
B. Run the following command to download the SQLite JDBC jar file that requires Java to work with the SQLite database:
Database Operations Using Java and SQLite
The basic database operations using SQLite database and Java script are shown in this tutorial. The list of database tasks that are covered in this tutorial is mentioned as follows:
- Make an SQLite Database Connection
- Create a New Table
- Insert the Data into the Table
- Read the Data from the Table
- Update the Data of the Table
- Delete the Data from the Table
SQLite Database Connection Using Java
Create a Java file named “JavaSqlite.java” with the following code that makes the connection with the SQLite database named “company” using JDBC. The db_connection object is created to make the connection with the SQLite database. The getConnection() method of the DriverManager class is used to create a connection with the “company.db” file. If any error occurs at the time of establishing the database connection, the SQLException class is used to display the error message.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JavaSqlite {
/*
Declare constructor to make database connection
*/
public static void JavaSqlite() {
//Declare object to make database connection
Connection db_connection = null;
//Set the database file location
String db_url = "jdbc:sqlite:/home/fahmida/company.db";
try
{
//Create the database connection
db_connection=DriverManager.getConnection(db_url);
//Print the success message
System.out.println("Sqlite database connected successfully.");
}
catch (SQLException e)
{
//Print the error message
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
JavaSqlite();
}
}
Run the following command to compile the code of the “JavaSqlite.java” file. If the code is error-free, the “JavaSqlite.class” file is created.
Run the following command to execute the “JavaSqlite.class” file with the sqlite-jdbc:
The following output apears if the SQLite database is connected successfully by Java:
Create a Table in SQLite Database Using Java
Create a Java file named “JavaSqliteCreateTable.java” with the following code that makes the connection with the SQLite database named “company” and create a database table named “customers” if it does not exist in the database yet. The table contains five fields and a primary key with the auto-increment attribute. The statement package is imported into the code to execute the CREATE TABLE SQL statement. If the table is created successfully, a success message is printed. Otherwise, an error message is printed by the SQLException class.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JavaSqliteCreateTable {
/*
Declare constructor to make database connection
*/
public static void JavaSqliteCreateTable() {
//Declare object to make database connection
Connection db_connection = null;
//Define the database file location
String db_url = "jdbc:sqlite:/home/fahmida/company.db";
//Define the query for creating the table
String query = "CREATE TABLE IF NOT EXISTS customers " +
"(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
" name CHAR(50) NOT NULL, " +
" address TEXT NOT NULL, " +
" email CHAR(50) NOT NULL, " +
" contact_no CHAR(25) NOT NULL)";
try
{
//Create the database connection
db_connection=DriverManager.getConnection(db_url);
//Declare statement object
Statement st = db_connection.createStatement();
//Execute the CREATE TABLE query
st.execute(query);
//Print the success message
System.out.println("Table created successfully.");
}
catch (SQLException e)
{
//Print the error message
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
JavaSqliteCreateTable();
}
}
Run the following command to compile the code of the “JavaSqliteCreateTable.java” file. If the code is error-free, the “JavaSqliteCreateTable.class” file is created:
Run the following command to execute the “JavaSqliteCreateTable.class” file with the sqlite-jdbc:
The following output appears if the table is created successfully in the database:
Insert the Data into the SQLite Table Using Java
Create a Java file named “JavaSqliteInsertData.java” with the following code that makes the connection with the SQLite database named “company” and insert three records into the “customers” table. The statement package is imported in the code like the previous example to execute the INSERT query. If the records are inserted successfully in the table, a success message is printed. Otherwise, an error message is printed by the SQLException class.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JavaSqliteInsertData {
/*
Declare constructor to make database connection
*/
public static void JavaSqliteInsertData() {
//Declare object to make database connection
Connection db_connection = null;
//Define the database file location
String db_url = "jdbc:sqlite:/home/fahmida/company.db";
//Define the query for creating the table
String query = "INSERT INTO customers " +
"VALUES" +
"(null,'Jamal uddin','45, Dhanmondi 5, Dhaka','[email protected]','01958975634')," +
"(null, 'Robiul Islam', '56/1, Mirpur, Dhaka', '[email protected]','01822675453')," +
"(null, 'Ismail Hossain', '126, Mohammedpur, Dhaka', '[email protected]', '01729785300');";
try
{
//Create the database connection
db_connection=DriverManager.getConnection(db_url);
//Declare statement object
Statement st = db_connection.createStatement();
//Execute the CREATE TABLE query
st.execute(query);
//Print the success message
System.out.println("3 records inserted successfully.");
}
catch (SQLException e)
{
//Print the error message
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
JavaSqliteInsertData();
}
}
Run the following command to compile the code of “JavaSqliteInsertData.java” file. If the code is error free, the “JavaSqliteCreateTable.class” file is created.
Run the following command to execute the “JavaSqliteInsertData.class” file with the sqlite-jdbc:
The following output appears if three records are inserted successfully in the database:
Read the Data from the SQLite Table Using Java
Create a Java file named “JavaSqliteReadData.java” with the following code that makes the connection with the SQLite database named “company” and read all records from the “customers” table. The statement package is imported into the code like in the previous example to execute the SELECT query. The ResultSet package is imported in the code to read the returned values of the SELECT query. The first SELECT query is used in the code to read all records of the “customers” table. The second SELECT query is used to read the particular record of the “customers” table based on the “id” value that is taken from the user. If the records are retrieved successfully, the records are printed in the terminal. Otherwise, an error message is printed by the SQLException class.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.*;
public class JavaSqliteReadData {
/*
Declare constructor to make database connection
*/
public static void JavaSqliteReadData() {
//Declare a variable to take customer ID
int id;
//Declare the scanner object to take input
Scanner input = new Scanner(System.in);
//Declare object to make database connection
Connection db_connection = null;
//Define the database file location
String db_url = "jdbc:sqlite:/home/fahmida/company.db";
//Define the query to select all records of the customer
String query = "SELECT * FROM customers";
try
{
//Create the database connection
db_connection=DriverManager.getConnection(db_url);
//Declare statement object
Statement st = db_connection.createStatement();
//Execute the query
ResultSet rs = st.executeQuery(query);
//Print all records
System.out.println("All data of the customer table:");
System.out.println("ID\tName\t\t\tAddress\t\tEmail\tContact No");
//Read each record through loop
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" +
rs.getString("name") + "\t" +
rs.getString("address") + "\t" +
rs.getString("Email") + "\t" +
rs.getString("contact_no") + "\n");
}
//Take the id value from the user
System.out.print("Enter a valid customer ID:");
id = input.nextInt();
//Define query for selecting specific records
query = "SELECT * FROM customers WHERE id = " + id;
//Execute the query
rs = st.executeQuery(query);
//Read the result of the query
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + "\n" +
"Name: " + rs.getString("name") + "\n" +
"Address: " + rs.getString("address") + "\n" +
"Email: " + rs.getString("Email") + "\n" +
"Contact No: " + rs.getString("contact_no"));
}
}
catch (SQLException e)
{
//Print the error message
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
JavaSqliteReadData();
}
}
Run the following command to compile the code of the “JavaSqliteReadData.java” file. If the code is error-free, the “JavaSqliteReadData.class” file is created:
Run the following command to execute the “JavaSqliteReadData.class” file with the sqlite-jdbc:
The following output appears if all records of the customers table are retrieved successfully and the valid customer id is taken from the user:
Update the Data of the SQLite Table Using Java
Create a Java file named “JavaSqliteUpdateData.java” with the following code that makes the connection with the SQLite database named “company” and update a particular record of the “customers” table based on the “id” value. Next, that record is printed to check whether the data is updated or not. The statement package is imported in the code like the previous example to execute the UPDATE and SELECT queries. The ResultSet package is imported into the code to read the returned values of the SELECT query. The UPDATE query is used to update the name field’s value of the particular “id” that is taken from the user. If the records are updated and retrieved successfully, the updated records are printed in the terminal. Otherwise, an error message is printed by the SQLException class.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.*;
public class JavaSqliteUpdateData {
/*
Declare constructor to make database connection
*/
public static void JavaSqliteUpdateData() {
//Declare a variable to take customer ID
int id;
//Declare the scanner object to take input
Scanner input = new Scanner(System.in);
//Declare object to make database connection
Connection db_connection = null;
//Define the database file location
String db_url = "jdbc:sqlite:/home/fahmida/company.db";
try
{
//Create the database connection
db_connection=DriverManager.getConnection(db_url);
//Declare statement object
Statement st = db_connection.createStatement();
//Take the id value from the user
System.out.print("Enter a valid customer ID:");
id = input.nextInt();
//Define the update query
String query = "UPDATE customers SET name='Farzana Rahman' WHERE id = " + id;
//Execute update query
st.execute(query);
//Define query for selecting specific records
query = "SELECT * FROM customers WHERE id = " + id;
//Execute the select query
ResultSet rs = st.executeQuery(query);
System.out.println("Updated value of the customer ID:" + id);
//Read the result of the query
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + "\n" +
"Name: " + rs.getString("name") + "\n" +
"Address: " + rs.getString("address") + "\n" +
"Email: " + rs.getString("Email") + "\n" +
"Contact No: " + rs.getString("contact_no"));
}
}
catch (SQLException e)
{
//Print the error message
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
JavaSqliteUpdateData();
}
}
Run the following command to compile the code of the “JavaSqliteUpdateData.java” file. If the code is error-free, the “JavaSqliteUpdateData.class” file is created:
Run the following command to execute the “JavaSqliteUpdateData.class” file with the sqlite-jdbc:
The following output appears if 3 is taken from the user as the customer “id” value. According to the output, the name value of the customer is changed to “Farzana Rahman” which contains the id value of 3. All field values of that customer are printed later:
Delete the Data from the SQLite Table Using Java
Create a Java file named “JavaSqliteDeleteData.java” with the following code that makes the connection with the SQLite database named “company” and delete a particular record of the “customers” table based on the “id” value. Next, all records of the table are printed to check whether the record is deleted or not. The statement package is imported into the code like the previous example to execute the DELETE and SELECT queries. The ResultSet package is imported into the code to read the returned values of the SELECT query. The DELETE query is used to delete the record that contains the particular “id” value. If the particular record is deleted and all records are retrieved successfully, the records of the “customers” table after the update are printed in the terminal. Otherwise, an error message is printed by the SQLException class.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.*;
public class JavaSqliteDeleteData {
/*
Declare constructor to make database connection
*/
public static void JavaSqliteDeleteData() {
//Declare a variable to take customer ID
int id;
//Declare the scanner object to take input
Scanner input = new Scanner(System.in);
//Declare object to make database connection
Connection db_connection = null;
//Define the database file location
String db_url = "jdbc:sqlite:/home/fahmida/company.db";
try
{
//Create the database connection
db_connection=DriverManager.getConnection(db_url);
//Declare statement object
Statement st = db_connection.createStatement();
//Take id value from the user
System.out.print("Enter a valid customer ID:");
id = input.nextInt();
//Define the update query
String query = "DELETE FROM customers WHERE id = " + id;
//Execute update query
st.execute(query);
//Define query for selecting specific records
query = "SELECT * FROM customers";
//Execute the select query
ResultSet rs = st.executeQuery(query);
System.out.println("Records of the customers table after delete:");
//Read the result of the query
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + "\n" +
"Name: " + rs.getString("name") + "\n" +
"Address: " + rs.getString("address") + "\n" +
"Email: " + rs.getString("Email") + "\n" +
"Contact No: " + rs.getString("contact_no"));
}
}
catch (SQLException e)
{
//Print the error message
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
JavaSqliteDeleteData();
}
}
Run the following command to compile the code of the “JavaSqliteDeleteData.java” file. If the code is error-free, the “JavaSqliteDeleteData.class” file is created.
Run the following command to execute the “JavaSqliteDeleteData.class” file with the sqlite-jdbc:
The following output appears if 3 is taken from the user as the customer “id” value. According to the output, the 3rd record is deleted and the other records of the “customers” table are printed:
Conclusion
Different types of basic database operations on the SQLite database table using the Java language are shown in this tutorial by creating multiple Java files. The methods of creating the table, inserting the data into the table, reading the data from the table, updating the data of the table, and deleting the data from the table are shown in the examples of this tutorial.
