❮ Sqlite Create Table Sqlite Data Types ❯

SQLite - `Java`

Installation

Before using SQLite in a Java program, we need to ensure that the machine has the SQLite JDBC Driver and Java installed. You can refer to the Java tutorial to learn how to install Java on your computer. Now, let's see how to install the SQLite JDBC Driver on your machine.

This site provides the sqlite-jdbc 3.7.2 version download. The latest sqlite-jdbc-(VERSION).jar version can be downloaded from https://github.com/xerial/sqlite-jdbc/releases.

Add the downloaded jar file sqlite-jdbc-(VERSION).jar to your class path, or use it in the -classpath option, which will be explained in later examples.

Before learning the following sections, you must have a basic understanding of Java JDBC concepts. If you are not familiar with these concepts, it is recommended that you spend half an hour learning about JDBC tutorials, which will help you understand the upcoming explanations.

Connecting to the Database

The following Java program shows how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned.

import java.sql.*;

public class SQLiteJDBC
{
  public static void main( String args[] )
  {
    Connection c = null;
    try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
    } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
    }
    System.out.println("Opened database successfully");
  }
}

Now, let's compile and run the above program to create our database test.db in the current directory. You can change the path as needed. We assume the JDBC driver version available in the current path is sqlite-jdbc-3.7.2.jar.

$javac SQLiteJDBC.java
$java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

If you want to use a Windows machine, you can compile and run your code as shown below:

$javac SQLiteJDBC.java
$java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

Creating a Table

The following Java program will be used to create a table in the previously created database:

import java.sql.*;

public class SQLiteJDBC
{
  public static void main( String args[] )
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      String sql = "CREATE TABLE COMPANY " +
                   "(ID INT PRIMARY KEY     NOT NULL," +
                   " NAME           TEXT    NOT NULL, " + 
                   " AGE            INT     NOT NULL, " +

" ADDRESS        CHAR(50), " + 
" SALARY         REAL)"; 
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");
}
}

When the above program is compiled and executed, it will create the COMPANY table in test.db, and the final list of files will look like this:

-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar
-rw-r--r--. 1 root root    1506 May  8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root     832 May  8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root    3072 May  8 05:43 test.db

INSERT Operation

The following Java code shows how to create records in the COMPANY table created above:

import java.sql.*;

public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );"; 
stmt.executeUpdate(sql);

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );"; 
stmt.executeUpdate(sql);

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"; 
stmt.executeUpdate(sql);

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"; 
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");
}
}

When the above program is compiled and executed, it will create the given records in the COMPANY table and will display the following two lines:

Opened database successfully
Records created successfully

## SELECT Operation

The following Java program demonstrates how to retrieve and display records from the COMPANY table created earlier:

```java
import java.sql.*;

public class SQLiteJDBC
{
  public static void main( String args[] )
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         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");
  }
}

When the above program is compiled and executed, it produces the following result:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE Operation

The following Java code demonstrates how to use the UPDATE statement to update any record, and then retrieve and display the updated records from the COMPANY table:

import java.sql.*;

public class SQLiteJDBC
{
  public static void main( String args[] )
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);

System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();

ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
while (rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   int age = rs.getInt("age");
   String address = rs.getString("address");
   float salary = rs.getFloat("salary");
   System.out.println("ID = " + id);
   System.out.println("NAME = " + name);
   System.out.println("AGE = " + age);
   System.out.println("ADDRESS = " + address);
   System.out.println("SALARY = " + salary);
   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");
}
}

When the above program is compiled and executed, it produces the following result:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE Operation

The following Java code shows how to delete any record using the DELETE statement and then fetch and display the remaining records from the COMPANY table:

import java.sql.*;

public class SQLiteJDBC
{
public static void main(String args[])
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

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

ResultSet rs = stmt.executeQuery("SELECT * FROM COMPANY;");
while (rs.next()) {

int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println("ID = " + id);
System.out.println("NAME = " + name);
System.out.println("AGE = " + age);
System.out.println("ADDRESS = " + address);
System.out.println("SALARY = " + salary);
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");
}
}

When the above program is compiled and executed, it produces the following result:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

❮ Sqlite Create Table Sqlite Data Types ❯

SQLite - Java

Installation

Connecting to the Database

Creating a Table

INSERT Operation

UPDATE Operation

DELETE Operation

SQLite - `Java`