Use sqlite with java

SQLite — Java

In this chapter, you will learn how to use SQLite in Java programs.

Installation

Before you start using SQLite in our Java programs, you need to make sure that you have SQLite JDBC Driver and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now, let us check how to set up SQLite JDBC driver.

• Download latest version of sqlite-jdbc-(VERSION).jar from sqlite-jdbc repository.
• Add downloaded jar file sqlite-jdbc-(VERSION).jar in your class path, or you can use it along with -classpath option as explained in the following examples.

Following section assumes you have little knowledge about Java JDBC concepts. If you don’t, then it is suggested to spent half an hour with JDBC Tutorial to become comfortable with the concepts explained below.

Connect to Database

Following Java programs shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

import java.sql.*; public class SQLiteJDBC < public static void main( String args[] ) < Connection c = null; try < Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:test.db"); >catch ( Exception e ) < System.err.println( e.getClass().getName() + ": " + e.getMessage() ); System.exit(0); >System.out.println("Opened database successfully"); > >

Now, let’s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement. We are assuming the current version of JDBC driver sqlite-jdbc-3.7.2.jar is available in the current path.

$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC Open database successfully

If you are going to use Windows machine, then you can compile and run your code as follows −

$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC Opened database successfully

Create a Table

Following Java program will be used to create a table in the previously created database.

import java.sql.*; public class SQLiteJDBC < public static void main( String args[] ) < Connection c = null; Statement stmt = null; try < Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:test.db"); System.out.println("Opened database successfully"); stmt = c.createStatement(); String sql = "CREATE TABLE COMPANY " + "(ID INT PRIMARY KEY NOT NULL," + " NAME TEXT NOT NULL, " + " AGE INT NOT NULL, " + " ADDRESS CHAR(50), " + " SALARY REAL)"; stmt.executeUpdate(sql); stmt.close(); c.close(); >catch ( Exception e ) < System.err.println( e.getClass().getName() + ": " + e.getMessage() ); System.exit(0); >System.out.println("Table created successfully"); > >

When the above program is compiled and executed, it will create COMPANY table in your test.db and final listing of the file will be as follows −

-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar -rw-r--r--. 1 root root 1506 May 8 05:43 SQLiteJDBC.class -rw-r--r--. 1 root root 832 May 8 05:42 SQLiteJDBC.java -rw-r--r--. 1 root root 3072 May 8 05:43 test.db

INSERT Operation

Following Java program shows how to create records in the COMPANY table created in above example.

import java.sql.*; public class SQLiteJDBC < public static void main( String args[] ) < Connection c = null; Statement stmt = null; try < Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:test.db"); c.setAutoCommit(false); System.out.println("Opened database successfully"); stmt = c.createStatement(); String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (1, 'Paul', 32, 'California', 20000.00 );"; stmt.executeUpdate(sql); sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );"; stmt.executeUpdate(sql); sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"; stmt.executeUpdate(sql); sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"; stmt.executeUpdate(sql); stmt.close(); c.commit(); c.close(); >catch ( Exception e ) < System.err.println( e.getClass().getName() + ": " + e.getMessage() ); System.exit(0); >System.out.println("Records created successfully"); > >

When above program is compiled and executed, it will create given records in COMPANY table and will display following two line −

Opened database successfully Records created successfully

SELECT Operation

Following Java program shows how to fetch and display records from the COMPANY table created in the above example.

import java.sql.*; public class SQLiteJDBC < public static void main( String args[] ) < Connection c = null; Statement stmt = null; try < Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:test.db"); c.setAutoCommit(false); System.out.println("Opened database successfully"); stmt = c.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" ); while ( rs.next() ) < int String name = rs.getString("name"); int age = rs.getInt("age"); String address = rs.getString("address"); float salary = rs.getFloat("salary"); System.out.println( "ID = " + id ); System.out.println( "NAME = " + name ); System.out.println( "AGE = " + age ); System.out.println( "ADDRESS = " + address ); System.out.println( "SALARY = " + salary ); System.out.println(); >rs.close(); stmt.close(); c.close(); > catch ( Exception e ) < System.err.println( e.getClass().getName() + ": " + e.getMessage() ); System.exit(0); >System.out.println("Operation done successfully"); > >

When the above program is compiled and executed, it will produce the following result.

Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully

UPDATE Operation

Following Java code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table.

import java.sql.*; public class SQLiteJDBC < public static void main( String args[] ) < Connection c = null; Statement stmt = null; try < Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:test.db"); c.setAutoCommit(false); System.out.println("Opened database successfully"); stmt = c.createStatement(); String sql = "UPDATE COMPANY set SALARY = 25000.00 where stmt.executeUpdate(sql); c.commit(); ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" ); while ( rs.next() ) < int String name = rs.getString("name"); int age = rs.getInt("age"); String address = rs.getString("address"); float salary = rs.getFloat("salary"); System.out.println( "ID = " + id ); System.out.println( "NAME = " + name ); System.out.println( "AGE = " + age ); System.out.println( "ADDRESS = " + address ); System.out.println( "SALARY = " + salary ); System.out.println(); >rs.close(); stmt.close(); c.close(); > catch ( Exception e ) < System.err.println( e.getClass().getName() + ": " + e.getMessage() ); System.exit(0); >System.out.println("Operation done successfully"); > >

When the above program is compiled and executed, it will produce the following result.

Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully

DELETE Operation

Following Java code shows how to use use DELETE statement to delete any record and then fetch and display the remaining records from the our COMPANY table.

import java.sql.*; public class SQLiteJDBC < public static void main( String args[] ) < Connection c = null; Statement stmt = null; try < Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:test.db"); c.setAutoCommit(false); System.out.println("Opened database successfully"); stmt = c.createStatement(); String sql = "DELETE from COMPANY where stmt.executeUpdate(sql); c.commit(); ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" ); while ( rs.next() ) < int String name = rs.getString("name"); int age = rs.getInt("age"); String address = rs.getString("address"); float salary = rs.getFloat("salary"); System.out.println( "ID = " + id ); System.out.println( "NAME = " + name ); System.out.println( "AGE = " + age ); System.out.println( "ADDRESS = " + address ); System.out.println( "SALARY = " + salary ); System.out.println(); >rs.close(); stmt.close(); c.close(); > catch ( Exception e ) < System.err.println( e.getClass().getName() + ": " + e.getMessage() ); System.exit(0); >System.out.println("Operation done successfully"); > >

When the above program is compiled and executed, it will produce the following result.

Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully

Источник

SQLite Java

This SQLite Java section teaches you step by step how to interact with SQLite using Java JDBC API.

There are some interfaces that you can use to interact with SQLite using the Java language. Some of these are the native C API wrapper while the other implement the standardized Java Database Connectivity (JDBC) API.

In this section, we will introduce you to a modern JDBC driver which is called SQLiteJDBC package. The SQLiteJDBC package contains both Java classes, as well as native SQLite libraries for Windows, Mac OS X, and Linux.

• Connecting to an SQLite database: this tutorial shows you how to download SQLiteJDBC driver and connect to an existing SQLite database using JDBC.
• Creating a new SQLite database – in this tutorial, we will show you how to create a new SQLite database from a Java program using SQLiteJDBC driver.
• Creating a new table using JDBC – before working with data, you need to create a table. This tutorial shows you how to create a new table in an SQLite database from a Java program.
• Inserting data into a table from a Java program – this tutorial walks you through the steps for inserting data into a table from a Java program
• Querying data from a table with or without parameters – after having the data in the table, we show you how to query data using a SELECT statement. You will learn how to issue a simple SELECT statement to query all rows from a table, as well as use a query with parameters to select data based on user’s input.
• Updating existing data using PreparedStatement object – this tutorial guides you how to update existing data in a table.
• Deleting data from a table – this tutorial provides the steps for deleting existing data in a table.
• Managing transaction – this tutorial shows you how to manage SQLite transaction using Java JDBC API such as setAutoCommit , commit , and rollback .
• Writing and Reading SQLite BLOB – we will show you how to update the SQLite BLOB data into a table and query BLOB data for displaying.

Getting Started

Источник

SQLite JAVA Tutorial

Here we will learn how to use SQLite in JAVA programming language to connect SQLite database, CREATE Table, INSERT, UPDATE, DELETE and SELECT operations on SQLite tables using JDBC driver with examples.

SQLite in JAVA Interface

We can easily interact with SQLite in JAVA language using a JDBC driver. This JDBC driver is known as the SQLite-JDBC package which contains both JAVA classes and SQLite libraries to perform different operations like connect to the database, create tables, insert data in tables, etc. on Windows, Linux, and Mac OS platform.

Before we proceed to interact with SQLite using JAVA language first we need to make sure that JAVA setup available in our PC. In case if JAVA setup is not available means follow Java Tutorial for JAVA installation.

If JAVA setup available in our PC, now we will install SQLite-JDBC driver for that download latest JDBC driver sqlite-jdbc-version.jar from available list of JDBC drivers.

Now we need to add downloaded JDBC driver jar file (sqlite-jdbc-version.jar) to our classpath like shown in our following programs.

Connect to SQLite Database using Java

Now we will connect to the SQLite database using JAVA if exists otherwise it will create a new database and then connect to it.

Following is the JAVA program which is used to connect a database if it exists otherwise first it will create a database and then connect to it.

public static void main( String args[] )

c = DriverManager.getConnection( «jdbc:sqlite:SqliteJavaDB.db» );

System.err.println( e.getClass().getName() + «: » + e.getMessage() );

System. out .println( «database successfully created» );

If you observe above code we are trying to connect “ SqliteJavaDB.db ” if exists otherwise it will create new database in current path and we are assuming that sqlite-jdbc-3.8.11.2.jar is available at the same location where our program exists.

java -classpath «.;sqlite-jdbc-3.8.11.2.jar» SQLiteJDBC

database successfully created

The above statements will compile and run our program to create “SqliteJavaDB.db” in current directory. If you check the current directory of the program, one file called SqliteJavaDB.db created.

Create Table in SQLite Database using Java

Now, we will create new table in previously created database named SqliteJavaDB.db using java for that write the code like as shown below.

public class TableUsingJava

public static void main( String args[] )

c = DriverManager.getConnection( «jdbc:sqlite:SqliteJavaDB.db» );

System. out .println( «Database Opened. \n» );

String sql = «CREATE TABLE Product » +

«(p_id INTEGER PRIMARY KEY AUTOINCREMENT,» +

System.err.println( e.getClass().getName() + «: » + e.getMessage() );

System. out .println( «Table Product Created Successfully. » );

If you observe above code we are creating a new table called “ Product ” in our “ SqliteJavaDB.db ” database.

Now compile and run the program using the following commands.

java -classpath «.;sqlite-jdbc-3.8.11.2.jar» TableUsingJava

Table Product Created Successfully.

The above statements complied with our program and created “Product” table in our database.

DML (Insert, Select, Update, Delete) Operations Using Java

Now, we try to perform DML operations (insert, select, update, delete) on previously created table named Product using JAVA language.

Following program contains all 4 DML operations like INSERT, UPDATE, DELETE and SELECT.

public class OperationUsingJava

public static void main( String args[] )

System. out .println( «Select DML Operation For Product Table. » );

System. out .println( «1. Insert» );

System. out .println( «2. Update» );

System. out .println( «3. Delete» );

System. out .println( «4. Select» );

System. out .println( «5. Exit» );

Scanner reader = new Scanner(System. in );

System. out .println( «Enter a choice: » );

c = DriverManager.getConnection( «jdbc:sqlite:SqliteJavaDB.db» );

scanName= new Scanner(System. in );

System. out .println( «Enter Product Name:» );

System. out .println( «Enter Product Price:» );

System. out .println( «Enter Product Quantity:» );

sql = «INSERT INTO Product (p_name,price,quantity) » +

System. out .println( «Inserted Successfully. » );

System. out .println( «Enter Product id:» );

scanName= new Scanner(System. in );

System. out .println( «Enter Product Name:» );

scanName= new Scanner(System. in );

System. out .println( «Enter Product Price:» );

System. out .println( «Enter Product Quantity:» );

sql = «UPDATE Product SET p_name = ‘» + name + «‘,price color: #a31515;»>»,quantity color: #a31515;»>» WHERE p_id color: blue;»>out .println( «Updated Successfully. » );

System. out .println( «Enter Product id:» );

scanName= new Scanner(System. in );

sql= «DELETE FROM Product WHERE p_id color: #a31515;»>»;» ;

System. out .println( «Deleted Successfully. » );

ResultSet rs = stmt.executeQuery( «SELECT * FROM Product;» );

System. out .println( «ID\t Name\t\t Price\t Qty » );

quantity = rs.getInt( «quantity» );

System. out .println(id+ «\t » +name+ » \t » +price+ «\t » +quantity);

System. out .println( «Oops. Wrong Choice. » );

System.err.println( e.getClass().getName() + «: » + e.getMessage() );

System. out .println( «Continue Y OR N?» );

reader= new Scanner(System. in );

> while (flag.equalsIgnoreCase( «Y» ));

If you observe above program we are performing INSERT, UPDATE, DELETE and SELECT operations on table called “Product”. Now, let’s compile and run the program to examine the output like as shown below.

java -classpath «.;sqlite-jdbc-3.8.11.2.jar» OperationUsingJava

Select DML Operation For Product Table.

Select DML Operation For Product Table.

Select DML Operation For Product Table.

Select DML Operation For Product Table.

Select DML Operation For Product Table.

Select DML Operation For Product Table.

Select DML Operation For Product Table.

Источник