Java with SQLite CRUD

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

To use SQLite with java programs, you must have SQLite JDBC Driver and Java set up on the system.

Installation

Download latest version of sqlite-jdbc-(VERSION).jar from sqlite-jdbc repository.
Add the downloaded jar file to your class path.
You can now connect to the SQLite database using java.

Create Database using java

You can also create a new database in SQLite using java programming language. Let's create a database named "tutorjoes.db". Create a public class "dbPro"

CRUD (Create, Retrieve, Update and Delete)

This JDBC is going to help you learning how to do basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API. These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language.

Here is a Java program demonstrates how to create an embedded sqlite database as well as the create, read, update and deletion operation using Java's sqlite jdbc.

Source Code

dbProSqlite.java

package sqliteDemo;
 
import java.sql.*;
import java.util.Scanner;
 
public class dbPro {
	//CRUD Operation Using Java with SQLite Database
	public static void main(String[] args) throws Exception {
		Class.forName("org.sqlite.JDBC");
		Connection con =DriverManager.getConnection("jdbc:sqlite:/c:\\db\\tutorjoes.db");
 
		Statement stmt =con.createStatement();
		ResultSet rs;
		PreparedStatement st;
 
		String qry="";
		int id,age,choice;
		String name,city;
 
		Scanner in = new Scanner(System.in);
		Scanner str = new Scanner(System.in);
 
		while(true)
		{
			System.out.println("SQLite Java CRUD Operation");
			System.out.println("1. Insert");
			System.out.println("2. Update");
			System.out.println("3. Delete");
			System.out.println("4. Select");
			System.out.println("5. Exit");
			System.out.print("Enter a choice: ");
			choice = in.nextInt();
			System.out.println("-----------------------------------------");
			switch(choice){
			case 1:
				System.out.println("1. Insert New Data");
 
				System.out.println("Enter Name : ");
				name=str.nextLine();
				System.out.println("Enter Age : ");
				age=in.nextInt();
				System.out.println("Enter City : ");
				city=str.nextLine();
 
				qry="insert into users (NAME,AGE,CITY) values(?,?,?)";
				st= con.prepareStatement(qry);
 
				st.setString(1, name);
				st.setInt(2, age);
				st.setString(3, city);
				st.executeUpdate();
				System.out.println("Data Insert Success");
 
				break;
			case 2:
				System.out.println("2. Updating a Data");
 
				System.out.println("Enter ID : ");
				id=in.nextInt();
				System.out.println("Enter Name : ");
				name=str.nextLine();
				System.out.println("Enter Age : ");
				age=in.nextInt();
				System.out.println("Enter City : ");
				city=str.nextLine();
 
				qry="update users set NAME=?,AGE=?,CITY=? where ID=?";
 
				st= con.prepareStatement(qry);
 
				st.setString(1, name);
				st.setInt(2, age);
				st.setString(3, city);
				st.setInt(4, id);
				st.executeUpdate();
				System.out.println("Data Update Success");
 
 
				break;
			case 3:
				System.out.println("3. Deleting a Data");
 
				System.out.println("Enter ID : ");
				id=in.nextInt();
 
				qry="delete from users where ID=?";
				st= con.prepareStatement(qry);
				st.setInt(1, id);
 
				st.executeUpdate();
				System.out.println("Data Deleted Success");
 
				break;
			case 4:
				System.out.println("4. Print all Records");
				qry="SELECT ID,NAME,AGE,CITY from users";
				rs=stmt.executeQuery(qry);
				while(rs.next())
				{
					id=rs.getInt("ID");
					name=rs.getString("NAME");
					age=rs.getInt("AGE");
					city=rs.getString("CITY");
 
					System.out.print(id+" ");
					System.out.print(name+" ");
					System.out.print(age+" ");
					System.out.println(city+" ");
 
				}
				break;
			case 5:
				System.out.println("Thank You");
				System.exit(0);
				break;
			default:
				System.out.println("Invalid Selection");
				break;
			}
			System.out.println("-----------------------------------------");
		}
 
 
	}
 
}

To download raw file Click Here

tutorjoes.txt

CREATE TABLE "users" (
	"ID"	INTEGER,
	"NAME"	TEXT,
	"AGE"	INTEGER,
	"CITY"	TEXT,
	PRIMARY KEY("ID" AUTOINCREMENT)
);

To download raw file Click Here

Output

SQLite Java CRUD Operation
1. Insert
2. Update
3. Delete
4. Select
5. Exit
Enter a choice: 1
-----------------------------------------
1. Insert New Data
Enter Name : 
Sam
Enter Age : 
21
Enter City : 
Erode
Data Insert Success
-----------------------------------------
SQLite Java CRUD Operation
1. Insert
2. Update
3. Delete
4. Select
5. Exit
Enter a choice: 4
-----------------------------------------
4. Print all Records
1 siva 12 salem 
2 pooja 23 Hosur 
3 priya 23 chennai 
4 Ram 34 Namakkal 
5 Sam 21 Erode 
-----------------------------------------
SQLite Java CRUD Operation
1. Insert
2. Update
3. Delete
4. Select
5. Exit
Enter a choice: 2
-----------------------------------------
2. Updating a Data
Enter ID : 
3
Enter Name : 
Priya
Enter Age : 
21
Enter City : 
Chennai
Data Update Success
-----------------------------------------
SQLite Java CRUD Operation
1. Insert
2. Update
3. Delete
4. Select
5. Exit
Enter a choice: 3
-----------------------------------------
3. Deleting a Data
Enter ID : 
2
Data Deleted Success
-----------------------------------------
SQLite Java CRUD Operation
1. Insert
2. Update
3. Delete
4. Select
5. Exit
Enter a choice: 4
-----------------------------------------
4. Print all Records
1 siva 12 salem 
3 Priya 21 Chennai 
4 Ram 34 Namakkal 
5 Sam 21 Erode 
-----------------------------------------
SQLite Java CRUD Operation
1. Insert
2. Update
3. Delete
4. Select
5. Exit
Enter a choice: 5
-----------------------------------------
Thank You