Java use JDBC connect SQLite

SQLite is a lightweight database system that can be used without installation and can be easily embedded in the system.

SQLite is written in C language, and it can cross Linux and Windows platforms. You can use JDBC to connect to SQLite for Java access and operation.

On JDBC connection to SQLite, it is roughly divided into two ways. One is to connect the data by Pure-Java, and the other is to call the function library written in C directly from Java. The method should be faster, but in platforms where a suitable C function library cannot be found, the Pure-Java version can be used.

SQLite JDBC github , This Example support create Table、drop Table、Query table、insert、delete and Update…

https://github.com/catyku/SQLiteExample

package com.izero.sqllite;
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);
	}
	
	//insert data
	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();
		
	}
	//update data
	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();
	}
	//delete data
	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();
		//create table
		test.createTable(con);
		//inser two rows
		test.insert(con, 1, "Eric");
		test.insert(con, 2, "Liu");
		//Query All 
		System.out.println("Query All Data Response :");
		test.selectAll(con);
		
		//Update first row data
		System.out.println("Update first row name:");
		test.update(con, 1, "Peter");
		//Query Response
		test.selectAll(con);
		
		//Delete first row data
		System.out.println("Delete first row :");
		test.delete(con, 1);
		//Query Response
		test.selectAll(con);
		
		//drop table
		test.dropTable(con);
		
		con.close();
		
	}
}

SQLite does not have a user login mechanism, so you only need to know the location of the database file to be accessed.

jdbc:sqlite:sample.db

sample.db is sqlite file name，you can use real path file name like c:\sample.db

Execute Response：

• 

You can find file sample.db