YouTip

## Installation Before using SQLite in a Java program, we need to ensure that the SQLite JDBC Driver and Java are already installed on the machine. 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 a [download of sqlite-jdbc version 3.7.2](https://static.jyshare.com/download/sqlite-jdbc-3.7.2.jar). For the latest _sqlite-jdbc-(VERSION).jar_ version, you can visit [https://github.com/xerial/sqlite-jdbc/releases](https://github.com/xerial/sqlite-jdbc/releases) to download. * Add the downloaded jar file _sqlite-jdbc-(VERSION).jar_ to your classpath, or use it with the -classpath option, which will be explained in the examples below. Before learning the knowledge in the following sections, you must have a preliminary understanding of Java JDBC concepts. If you are not yet familiar with the relevant knowledge, it is recommended that you spend half an hour learning about JDBC tutorials first. This will help you learn the knowledge explained next. ## Connecting to a 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 that the available JDBC driver version in the current path is _sqlite-jdbc-3.7.2.jar_. $javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBCOpen database successfully If you want to use a Windows machine, you can compile and run your code as follows: $javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBCOpened 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 file list 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 shows how to fetch and display records from the COMPANY table created earlier: 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 will produce the following results: 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.0Operation done successfully ## UPDATE Operation The following Java code shows how to use the UPDATE statement to update any record, and then fetch 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 will produce the following results: 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.0Operation done successfully ## DELETE Operation The following Java code shows how to use the DELETE statement to delete any record, 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 will produce the following results: 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.0Operation done successfully