JDBC + SQLite 3: Connect, Create, Update, Select and Delete Example
August 01, 2015
On this page we will provide JDBC and SQLite 3 connect, create, update, select and delete example. To integrate and work with SQLite is very easy. Here on this page I will provide the steps how to start with SQLite standalone using command line prompt and with JDBC.
Understanding SQLite and Working with Command Line
1. SQLite is a database which follows Atomicity, Consistency, Isolation and Durability (ACID) properties.2. It is server less.
3. Zero configuration.
4. Very Light.
5. Full SQL implementation.
6. Easy to use API
7. Supports Cross-platform
8. Command Line Interface
To start with SQLite, go to download and get the binary for your OS. In my case I have windows OS. I have downloaded sqlite-shell-win32-x86-3081101.zip. Extract zip file and we will get sqlite3.exe. Click on it we will get command prompt.
By default it will work in-memory and after closing command prompt, data will be washed out. To work with it we will open a database that will save data in a file with database name with extension .db. Suppose I have to create database cp1.db in the directory C:/cp/, our complete command line will be
.open D:/cp/cp1.db
Queries to create, update, select and delete will be normal SQL. Find the print screen.
Connect JDBC with SQLite and Run Create, Update, Select and Delete Query
To work with JDBC and SQLite, we need to configure JAR sqlite-jdbc-3.8.11.jar. I am using build.gradle to resolve the dependency.build.gradle
apply plugin: 'java'
apply plugin: 'eclipse'
archivesBaseName = 'concretepage'
version = '1'
repositories {
mavenCentral()
}
dependencies {
compile 'org.xerial:sqlite-jdbc:3.8.11'
}
1. To connect JDBC to SQLite, SQLite provides driver as
org.sqlite.JDBC.
2. Suppose our database name is cp2.db then database URL will be
jdbc:sqlite: cp2.db.
3. Put your database file in project root directory. If not available, database file will be created automatically.
4. Now we process create, insert, update, select and delete operation as usual using JDBC. Find the example.
SQLiteJDBC.java
package com.concretepage;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SQLiteJDBC {
public static void main (String[] args) throws Exception {
String driver = "org.sqlite.JDBC";
Class.forName(driver);
String dbName = "cp2.db";
String dbUrl = "jdbc:sqlite:" + dbName;
Connection conn = DriverManager.getConnection(dbUrl);
//create table
Statement st = conn.createStatement();
st.executeUpdate("CREATE table village (id int, name varchar(20))");
//insert row
st.executeUpdate("INSERT INTO village VALUES (111, 'Concretepage')");
//select
String query = "SELECT id, name from village";
ResultSet rs = null;
try {
rs = st.executeQuery(query);
while(rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println("id:"+ id+ ", name: "+ name);
}
//delete
st.executeUpdate("DELETE from village");
} finally {
rs.close();
}
}
}
id:111, name: Concretepage
