Monday, February 10, 2020

SQLite JDBC Application

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.
  1. Programatically create a table in SQLite Database
  2. Insert data into the table
  3. 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 



sqlite jdbc project

5. Make sure the jar file is added to the project


sqlite jdbc 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

  1. Register the driver class (By using Class.forName())
  2. Create a connection(By using DriverManager.getConnection())
  3. Create a statement(By using Statement, PreparedStatement or CallableStatement)
  4. Execute queries(by using any execute method on statement object)
  5. 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.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


Video Tutorial