JDBC + SQLite 3: Connect, Create, Update, Select and Delete Example

By Arvind Rai, 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.
JDBC + SQLite 3: Connect, Create, Update, Select and Delete Example

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'
} 
Steps to Connect

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();
                }
	   }	
} 
Find the output.
id:111, name: Concretepage
 

Download Source Code

POSTED BY
ARVIND RAI
ARVIND RAI







©2024 concretepage.com | Privacy Policy | Contact Us