How to use SQLite with java

This tutorial is a quick guide to use SQLite in your java application. In this tutorial you will learn to create a database, to create a table,  insert data in to the table and to retrieve data from the table.

Before using SQLite with your java project, as with any database first you have to add the SQLite JDBC driver to the project. 

You can download SQLite JDBC driver from the below link
https://bitbucket.org/xerial/sqlite-jdbc/downloads

Create a new Java project and add this jar to your project as a normal library. Now you are ready to use SQLite with your application.

Look at following code samples and you will easily understand how to use SQLite with Java.

Create a new folder in your project and name it as 'dataFolder'. 
Below code creates a database names 'myDatabase.db' in that folder.

  public void createDatabase {
    try {
      Class.forName("org.sqlite.JDBC");
      //Following line creates a database named 'myDatabase.db' in 'dataFolder' folder.
      Connection con =
              DriverManager.getConnection("jdbc:sqlite:dataFolder/myDatabase.db");
      Statement stmt = con.createStatement();
     
     //Following line creates a table named 'employee' indatabase.
      String query = "CREATE TABLE employee(id INTEGER, name TEXT)";
      stmt.executeUpdate(query);
      stmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

Below method inserts two rows in to the employee table that we created in above method.

 private void insert() throws SQLException {
    Connection con =
             DriverManager.getConnection("jdbc:sqlite:dataFolder/myDatabase.db");
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into employee (id, name) values(1, 'john')");
    stmt.executeUpdate("insert into employee (id, name) values(2, 'william')");
    stmt.close();
    con.close();
 }

Below method retrieves all the records from employee table and prints the id and the name.

  private static void read() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite:dataFolder/myDatabase.db");
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("SELECT id, name FROM employee");
    while (rs.next()) {
      Integer id = rs.getInt("id");
      String name = rs.getString("name");
      System.out.println(id + ":" + name);
    }
  }