CRUD: create, read, update and delete in sqlite with Java

sqlite create
[code language=”sql”]
CREATE TABLE web_blog
(ID INTEGER PRIMARY KEY autoincrement,
NAME CHAR(50) NOT NULL,
message TEXT NOT NULL,
date_added datetime);
[/code]

sqlite read
[code language=”sql”]
SELECT * FROM web_blog;
[/code]

sqlite update
[code language=”sql”]
UPDATE web_blog set message = ‘This is updated by a query’ where ID=1;
[/code]

sqlite delete
[code language=”sql”]
DELETE from web_blog where ID=1;
[/code]

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. This program will create a file myBlog.sqlite in the root folder of the project. It requires the sqlite-jdbc.jar
[code language=”java”]
import java.sql.*;

public class SQLiteJDBC
{
public static void main( String args[] )
{
connectDB();
createDB();
insertDB();
selectDB();
//updateDB();
//deleteDB();
}

public static void connectDB()
{
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}
public static void createDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "CREATE TABLE web_blog " +
"(ID INTEGER PRIMARY KEY autoincrement," +
" NAME CHAR(50) NOT NULL, " +
" message TEXT NOT NULL, " +
" date_added datetime)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}

public static void insertDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "INSERT INTO web_blog (NAME,message,date_added) " +
"VALUES (‘Ken’, ‘Hello every one!!!’, datetime())," +
" (‘Jim’, ‘whats up!!!’,datetime());";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}

public static void selectDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM web_blog;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
String message = rs.getString("message");
String date_added = rs.getString("date_added");
System.out.println( "ID : " + id );
System.out.println( "Name : " + name );
System.out.println( "Message : " + message );
System.out.println( "Date Added : " + date_added );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}

public static void updateDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "UPDATE web_blog set message = ‘This is updated by updateDB()’ where ID=1;";
stmt.executeUpdate(sql);
c.commit();

ResultSet rs = stmt.executeQuery( "SELECT * FROM web_blog;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
String message = rs.getString("message");
String date_added = rs.getString("date_added");
System.out.println( "ID : " + id );
System.out.println( "Name : " + name );
System.out.println( "Message : " + message );
System.out.println( "Date Added : " + date_added );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}

public static void deleteDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "DELETE from web_blog where ID=1;";
stmt.executeUpdate(sql);
c.commit();

ResultSet rs = stmt.executeQuery( "SELECT * FROM web_blog;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
String message = rs.getString("message");
String date_added = rs.getString("date_added");
System.out.println( "ID : " + id );
System.out.println( "Name : " + name );
System.out.println( "Message : " + message );
System.out.println( "Date Added : " + date_added );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
[/code]