Program 1: Establishing a Connection with SQLite
This program establishes a connection between a Java application and a SQLite database file using JDBC.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class SQLiteConnectionExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
try {
Connection con =
DriverManager.getConnection(url);
System.out.println(
"Connected to SQLite database successfully.");
con.close();
} catch (SQLException e) {
System.out.println(
"Connection failed: " +
e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteConnectionExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteConnectionExample
Connected to SQLite database successfully.Code language: CSS (css)Program 2: Creating an Employee Table
This program creates an employee table in the SQLite database using the JDBC Statement interface.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateSQLiteEmployeeTable {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
String sql =
"CREATE TABLE IF NOT EXISTS employee (" +
"employee_id INTEGER PRIMARY KEY, " +
"employee_name TEXT, " +
"department TEXT, " +
"salary REAL)";
try {
Connection con =
DriverManager.getConnection(url);
Statement stmt =
con.createStatement();
stmt.executeUpdate(sql);
System.out.println(
"Employee table created successfully.");
stmt.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Error: " + e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" CreateSQLiteEmployeeTable.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" CreateSQLiteEmployeeTable
Employee table created successfully.Code language: CSS (css)Program 3: Inserting Records Using PreparedStatement
This program inserts an employee record into SQLite using a parameterized SQL statement with PreparedStatement.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class SQLitePreparedStatementExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(url);
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 101);
ps.setString(2, "Ravi");
ps.setString(3, "IT");
ps.setDouble(4, 50000);
ps.executeUpdate();
System.out.println(
"Employee inserted successfully.");
ps.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Error: " + e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLitePreparedStatementExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLitePreparedStatementExample
Employee inserted successfully.Code language: CSS (css)Program 4: Retrieving Records Using ResultSet
This program retrieves employee records from SQLite using Statement and ResultSet.
import java.sql.*;
public class SQLiteResultSetExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
try {
Connection con =
DriverManager.getConnection(url);
Statement stmt =
con.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT * FROM employee");
System.out.println(
"Employee Details");
System.out.println(
"-------------------------");
while (rs.next()) {
System.out.println(
rs.getInt("employee_id") + " " +
rs.getString("employee_name") + " " +
rs.getString("department") + " " +
rs.getDouble("salary"));
}
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteResultSetExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteResultSetExample
Employee Details
-------------------------
101 Ravi IT 50000.0Code language: CSS (css)Program 5: Updating Records Using PreparedStatement
This program updates the salary of an employee in SQLite using PreparedStatement.
import java.sql.*;
public class SQLiteUpdateExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
String sql =
"UPDATE employee " +
"SET salary = ? " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url);
PreparedStatement ps =
con.prepareStatement(sql);
ps.setDouble(1, 60000);
ps.setInt(2, 101);
int rows =
ps.executeUpdate();
System.out.println(
rows + " record updated.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteUpdateExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteUpdateExample
1 record updated.Code language: CSS (css)Program 6: Deleting Records Using PreparedStatement
This program deletes an employee record from SQLite using a parameterized SQL statement.
import java.sql.*;
public class SQLiteDeleteExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
String sql =
"DELETE FROM employee " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url);
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 101);
int rows =
ps.executeUpdate();
System.out.println(
rows + " record deleted.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteDeleteExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteDeleteExample
1 record deleted.Code language: CSS (css)Program 7: Performing Batch Insert Using PreparedStatement
This program inserts multiple employee records into SQLite using JDBC batch processing.
import java.sql.*;
public class SQLiteBatchExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(url);
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 201);
ps.setString(2, "Arun");
ps.setString(3, "IT");
ps.setDouble(4, 45000);
ps.addBatch();
ps.setInt(1, 202);
ps.setString(2, "Priya");
ps.setString(3, "HR");
ps.setDouble(4, 42000);
ps.addBatch();
ps.setInt(1, 203);
ps.setString(2, "Kiran");
ps.setString(3, "Finance");
ps.setDouble(4, 48000);
ps.addBatch();
int[] result =
ps.executeBatch();
System.out.println(
result.length +
" records processed in batch.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteBatchExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteBatchExample
3 records processed in batch.Code language: CSS (css)Program 8: Using Transactions with commit() and rollback()
This program performs multiple operations inside a SQLite transaction. If all operations are successful, commit() permanently saves the changes. If an error occurs, rollback() cancels the transaction.
import java.sql.*;
public class SQLiteTransactionExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
Connection con = null;
try {
con =
DriverManager.getConnection(url);
// Disable auto-commit
con.setAutoCommit(false);
PreparedStatement ps =
con.prepareStatement(
"UPDATE employee " +
"SET salary = salary + ? " +
"WHERE employee_id = ?");
// First update
ps.setDouble(1, 5000);
ps.setInt(2, 201);
ps.executeUpdate();
// Second update
ps.setDouble(1, 3000);
ps.setInt(2, 202);
ps.executeUpdate();
// Commit
con.commit();
System.out.println(
"Transaction committed successfully.");
ps.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Transaction failed.");
if (con != null) {
try {
con.rollback();
System.out.println(
"Transaction rolled back.");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteTransactionExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteTransactionExample
Transaction committed successfully.Code language: CSS (css)Program 9: Using Savepoint in SQLite Transaction
This program creates a savepoint after the first update and rolls back the second update to that savepoint. The first update can then be committed.
import java.sql.*;
public class SQLiteSavepointExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
try {
Connection con =
DriverManager.getConnection(url);
con.setAutoCommit(false);
Statement stmt =
con.createStatement();
// First update
stmt.executeUpdate(
"UPDATE employee " +
"SET salary = salary + 2000 " +
"WHERE employee_id = 201");
System.out.println(
"First update completed.");
// Create savepoint
Savepoint savepoint =
con.setSavepoint("AfterFirstUpdate");
System.out.println(
"Savepoint created.");
// Second update
stmt.executeUpdate(
"UPDATE employee " +
"SET salary = salary + 5000 " +
"WHERE employee_id = 202");
System.out.println(
"Second update completed.");
// Roll back to savepoint
con.rollback(savepoint);
System.out.println(
"Rolled back to savepoint.");
// Commit remaining changes
con.commit();
System.out.println(
"Transaction committed.");
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteSavepointExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteSavepointExample
First update completed.
Savepoint created.
Second update completed.
Rolled back to savepoint.
Transaction committed.Code language: CSS (css)Program 10: Calling a SQLite Function
This program uses SQLite’s built-in UPPER() function to convert an employee name to uppercase.
SQLite does not provide stored procedures in the same way as MySQL or PostgreSQL. Therefore, the previous CallableStatement examples for MySQL/PostgreSQL cannot be directly copied to SQLite. SQLite provides built-in SQL functions, and applications can also create custom functions through SQLite APIs supported by particular JDBC drivers. For a beginner JDBC syllabus, it is better to demonstrate a SQLite built-in function using PreparedStatement rather than incorrectly presenting a stored procedure.
import java.sql.*;
public class SQLiteFunctionExample {
public static void main(String[] args) {
String url =
"jdbc:sqlite:D:/test/jdbc_demo.db";
String sql =
"SELECT employee_id, " +
"UPPER(employee_name) AS employee_name " +
"FROM employee " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url);
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 201);
ResultSet rs =
ps.executeQuery();
if (rs.next()) {
System.out.println(
"Employee ID: " +
rs.getInt("employee_id"));
System.out.println(
"Employee Name: " +
rs.getString("employee_name"));
}
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteFunctionExample.java
PS D:\test> java -classpath ".;D:\test\sqlite-jdbc.jar" SQLiteFunctionExample
Employee ID: 201
Employee Name: ARUNCode language: CSS (css)