Using JDBC to Operate SQLite in Java

Introduction to SQLite

SQLite is a lightweight database system that can be used without installation, and can also be very easily embedded into a system. FireFox embeds SQLite, so you can directly use JavaScript on FireFox to call and operate the database.

SQLite is written in C, can cross platforms such as Linux and Windows, and for access and operations in Java, JDBC can be used to connect to SQLite.

For connecting to SQLite with JDBC, there are roughly two approaches: one is connecting to the data with Pure-Java, and the other is directly using Java to call a library written in C. In actual tests, the approach of directly calling the C library should be faster, but on platforms where a suitable C library cannot be found, the Pure-Java version can be used.

SQLiteJDBC this version currently found is more actively updated, and in usage it is almost the same as ordinary JDBC, so getting started with the program is very simple.

SQLiteJDBC can be downloaded from here. The following examples were made with version 3.5.7, providing examples of creating a Table, removing a Table, querying, inserting, deleting, and updating.

Example Code

import java.sql.*;
 
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;
public class MyTest {
    public Connection getConnection() throws SQLException
    {
        SQLiteConfig config = new SQLiteConfig();
        // config.setReadOnly(true);   
        config.setSharedCache(true);
        config.enableRecursiveTriggers(true);
    
            
        SQLiteDataSource ds = new SQLiteDataSource(config); 
        ds.setUrl("jdbc:sqlite:sample.db");
        return ds.getConnection();
        //ds.setServerName("sample.db");
 
        
    }
    //create Table
    public void createTable(Connection con )throws SQLException{
        String sql = "DROP TABLE IF EXISTS test ;create table test (id integer, name string); ";
        Statement stat = null;
        stat = con.createStatement();
        stat.executeUpdate(sql);
        
    }
    //drop table
    public void dropTable(Connection con)throws SQLException{
        String sql = "drop table test ";
        Statement stat = null;
        stat = con.createStatement();
        stat.executeUpdate(sql);
    }
    
    //新增
    public void insert(Connection con,int id,String name)throws SQLException{
        String sql = "insert into test (id,name) values(?,?)";
        PreparedStatement pst = null;
        pst = con.prepareStatement(sql);
        int idx = 1 ; 
        pst.setInt(idx++, id);
        pst.setString(idx++, name);
        pst.executeUpdate();
        
    }
    //修改
    public void update(Connection con,int id,String name)throws SQLException{
        String sql = "update test set name = ? where id = ?";
        PreparedStatement pst = null;
        pst = con.prepareStatement(sql);
        int idx = 1 ; 
        pst.setString(idx++, name);
        pst.setInt(idx++, id);
        pst.executeUpdate();
    }
    //刪除
    public void delete(Connection con,int id)throws SQLException{
        String sql = "delete from test where id = ?";
        PreparedStatement pst = null;
        pst = con.prepareStatement(sql);
        int idx = 1 ; 
        pst.setInt(idx++, id);
        pst.executeUpdate();
    }
    
    public void selectAll(Connection con)throws SQLException{
        String sql = "select * from test";
        Statement stat = null;
        ResultSet rs = null;
        stat = con.createStatement();
        rs = stat.executeQuery(sql);
        while(rs.next())
        {
            System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
        }
    }
    public static void main(String args[]) throws SQLException{
        MyTest test = new MyTest();
        Connection con = test.getConnection();
        //建立table
        test.createTable(con);
        //新增資料
        test.insert(con, 1, "第一個");
        test.insert(con, 2, "第二個");
        //查詢顯示資料
        System.out.println("新增二筆資料後狀況:");
        test.selectAll(con);
        
        //修改資料
        System.out.println("修改第一筆資料後狀況:");
        test.update(con, 1, "這個值被改變了!");
        //查詢顯示資料
        test.selectAll(con);
        
        //刪除資料
        System.out.println("刪除第一筆資料後狀況:");
        test.delete(con, 1);
        //查詢顯示資料
        test.selectAll(con);
        
        //刪除table
        test.dropTable(con);
        
        con.close();
        
    }
}

Explanation

SQLite does not have a user login mechanism, so you only need to specify the location of the database file to access in order to use it. In jdbc:sqlite:sample.db, sample.db is the file name; you can also specify its path location c:\sample.db.

The following is the execution result:

The sample.db file, which did not originally exist, was also created automatically, under the project directory.

References

https://blog.yslifes.com/archives/971