Creating SQLite JDBC Application
SQLite Database
SQLite is a simple relational database management system(RDBMS) contained in a C library. SQLite is not a client-server database engine. SQLite is embedded in the end program. SQLite is not password protected like other databases, so it is a little insecure. It works best for small database applications where data security is not a prime requirement. It is fast, portable, and lightweight.
What does this SQLite application do?
This is a simple JDBC application demonstrating the process of creating an application enabled with the database at the back end.
- Programatically create a table in SQLite Database
- Insert data into the table
- Retrieve and display the data
Follow these steps to configure the project for Java Database Connectivity(JDBC)
1. Download this jar file containing SQLite
2. Store this file at some accessible directory
3. Create a new Java Project in Eclipse IDE
go to file->new->java project
4. Right-click on your project name in the project explorer
go to build path->add external Archives
5. Make sure the jar file is added to the project
6. Create a Class file with the main() method.
We need to follow a few steps to connect with a database, no matter which database is getting used. These steps are
- Register the driver class (By using Class.forName())
- Create a connection(By using DriverManager.getConnection())
- Create a statement(By using Statement, PreparedStatement or CallableStatement)
- Execute queries(by using any execute method on statement object)
- Closing connection(By calling close() method on Connection Object)
There are few classes and interfaces that are used to establish a JDBC connection with the RDBMS.
| DriverManager |
DriverManager is a Class that works between users and drivers. DriverManager keeps a list of the drivers that are available and manages the connection settings between a database and the appropriate driver.
|
| Statement |
Statement is an interface JDBC and it is used to execute SQL statements against a relational database.
|
| Connection |
Connection interface can be used to receive the object of Statement and Database Metadata as this is the factory for Statement objects. The Connection interface also provides many methods for transaction management like commit(), rollback(), etc.
|
| ResultSet |
A ResultSet is an interface that maintains a cursor to point to the current row in the result set. Basically, ResultSet refers to the row and column data obtained in a ResultSet object.
|
Step-1
Create a table in the database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
/******************SQLITE IS A SIMPLE, SMALL DATABASE CREATED USING JAVASCRIPT******/
public static void main(String[] args) {
/**********Create new table*******************/
try {
//1. Load Driver
Class.forName("org.sqlite.JDBC");
//2. Create Connection
Connection con=DriverManager.getConnection("jdbc:sqlite:emp.db");
//3. Create Statement
Statement stmt=con.createStatement();
//4. Execute statement
String sql="create table emps(id int primary key,"
+ "name varchar(20), dept varchar(20))";
boolean b=stmt.execute(sql);
System.out.println("Table created successfully");
//5. Close Connection
con.close();
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Output:
Table created successfully
Step-2
.
Insert data into the table
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
/******************SQLITE IS A SIMPLE, SMALL DATABASE CREATED USING JAVASCRIPT******/
public static void main(String[] args) {
/**********Insert data into table*******************/
try {
//1. Load Driver
Class.forName("org.sqlite.JDBC");
//2. Create Connection
Connection con=DriverManager.getConnection("jdbc:sqlite:emp.db");
//3. Create Statement
Statement stmt=con.createStatement();
//4. Execute statement
String sql1="insert into emps values(1,'ALEX','CSE')";
String sql2="insert into emps values(2,'STEVE','MANAGEMENT')";
String sql3="insert into emps values(3,'DAVE','IT')";
stmt.execute(sql1);
stmt.execute(sql2);
stmt.execute(sql3);
System.out.println("Data inserted into table successfully");
//5. Close Connection
con.close();
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Output:
Data inserted into table successfully
Step-3
Retrieve and display the data
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
/******************SQLITE IS A SIMPLE, SMALL DATABASE ******/
public static void main(String[] args) {
/***Retrieve and display the data from table***********/
try {
//1. Load Driver
Class.forName("org.sqlite.JDBC");
//2. Create Connection
Connection con=DriverManager.getConnection("jdbc:sqlite:emp.db");
//3. Create Statement
Statement stmt=con.createStatement();
//4. Execute statement
String sql="select * from emps";
ResultSet res=stmt.executeQuery(sql);
while(res.next())
{
System.out.println(res.getInt(1)+"\t"+res.getString(2)+"\t"+res.getString(3));
}
//5. Close Connection
con.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Output:
1 ALEX CSE
2 STEVE MANAGEMENT
3 DAVE IT
2 STEVE MANAGEMENT
3 DAVE IT
