JDBC Tutorial

---

JDBC SQLite Tutorial

      SQLite is an open source in-memory Relational Database Developed by sqlite.org. With no setup,intilalization, configuraton etc.,

sqlite is an embedded SQL database engine, unlike other databases SQLite does not have seperate server-process.

sqlite reads and writes directly into a hard disk.

sqlite supports multiple tables, indexes, views, triggers etc.,

sqlite database is cross platform, database file easily ported to other operating systems.

sqlite Database Transactional supports ACID properties.

sqlite is a small library, size around less than 1MB , compact library

sqlite designed and tested on multiple platforms, it is pre-installed software on Mobile Devices, computers ,tabs etc.,

sqlite is develeoped using C-programming language, finally it is fully featured SQL Database engine

sqlite is a Single-User Database, and it's does not support concurrency(no more than one user writing ino database at a time).

SQLite DataTypes

• INTEGER

    Numeric values without precision

• REAL

    Numeric values with precision

• TEXT

    Any texual data,including date & time fields

• BLOB

    Binary Large Object, it allows us to store any PDF documents, Media files(like mp3,mp4 ...) etc.,

Installing SQLite

Check version of SQLite

      $ sqlite3 --version

      3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafaalt1

Download SQLite JDBC Driver

SQLite Database JDBC Driver available in sqlite.org website.

In this tutorial, using sqlite-jdbc-3.40 version jar file. It's compatible with JDBC 4.0 specification.

		sqlite-jdbc-3.40.0.0.jar
		sqlite-jdbc-3.40.0.0-javadoc.jar
		sqlite-jdbc-3.40.0.0-sources.jar
	

SQLite JDBC URL format

Steps Required to Connect to SQLite Database.

1. Get Connection Object using DriverManager
2. create statement/preparedstatement object
3. execute sql statement using executequery/executeupdate functions
4. close the resources

There are two ways to connect to SQLite Database

• Using Java SE, JDBC Packages
• Using SQLite JDBC Packages.

SQLite Table

		CREATE TABLE address(  id int not null primary key, 
					name text, 
					plot text, 
					city varchar(20),
					country text,
					"date created" text);	   
	   

DML INSERT Operation

Using Statement Object

	Connection conn1 = DriverManager.getConnection("jdbc:sqlite:/sqliteTutorial/location.db");
	Statement stmt = conn1.createStatement();

	String sql = "INSERT INTO ADDRESS VALUES(1,'SAM','1-23','PILER','INDIA',DATE())";
	stmt.executeUpdate(sql);
	
	

Using PreparedStatement Object

	sql = "INSERT INTO ADDRESS VALUES(?,?,?,?,?,?)";
	PreparedStatement pstmt =  conn1.prepareStatement(sql);
	pstmt.setInt(1, 2);
	pstmt.setString(2, "SQLite");
	pstmt.setString(3, "3/222");
	pstmt.setString(4, "Tirupati");
	pstmt.setString(5, "INDIA");
	pstmt.setString(6, LocalDate.now().toString());
	
	pstmt.executeUpdate();
	
	

SELECT DATA from ADDRESS table

	Connection conn1 = DriverManager.getConnection("jdbc:sqlite:/sqliteTutorial/location.db");
	Statement stmt = conn1.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT * FROM ADDRESS");
	
	while(rs.next()) {
	    
	    System.out.println(
		    	rs.getInt(1)+" "+
		    	rs.getString(2)+" "+
		    	rs.getString(3)+" "+
		    	rs.getString(4)+" "+
		    	rs.getString(5)+" "+
		    	rs.getString("date created")	    	
		    );
	}
	
	

DML UPDATE Operation

	Connection conn1 = DriverManager.getConnection("jdbc:sqlite:/sqliteTutorial/location.db");
	Statement stmt = conn1.createStatement();

	String sql = "UPDATE ADDRESS SET COUNTRY='US' WHERE ID=2";
	stmt.executeUpdate(sql);
	
	

Using PreparedStatement

	sql = "UPDATE ADDRESS SET COUNTRY=? WHERE ID=?";
	PreparedStatement pstmt = conn1.prepareStatement(sql);
	
	pstmt.setInt(2, 2);
	pstmt.setString(1, "INDIA");
	
	pstmt.executeUpdate();
	

DML DELETE Operation

	Connection conn1 = DriverManager.getConnection("jdbc:sqlite:/sqliteTutorial/location.db");
	Statement stmt = conn1.createStatement();

	String sql = "DELETE FROM ADDRESS WHERE ID=3";
	stmt.executeUpdate(sql);
	
	

	
	sql = "DELETE FROM ADDRESS  WHERE ID=?";
	PreparedStatement pstmt = conn1.prepareStatement(sql);
	
	pstmt.setInt(1, 4);
	
	pstmt.executeUpdate();
	
	

Autogenerated Keys

     Identity Column of a Table can be Auto-Incremented, Whenever user inserts a row into the table, Auto-Incremented column updates its value by incrementing by one. Using JDBC it can be retreived using getGeneratedKeys method

	create table country(
	         id INTEGER primary key autoincrement , 
	         name varchar(25));

	Connection conn1 = DriverManager.getConnection("jdbc:sqlite:/sqliteTutorial/location.db");
	Statement stmt = conn1.createStatement();

	/INSERT  1st record

	String sql = "INSERT INTO COUNTRY(NAME) values('INDIA')";
	stmt.executeUpdate(sql);
	
	ResultSet rsKeys = stmt.getGeneratedKeys();
	while(rsKeys.next()) {
	    System.out.println(rsKeys.getInt(1));
	}

	/INSERT  2nd record
	
	sql = "INSERT INTO COUNTRY(NAME) values('USA')";
	stmt.executeUpdate(sql);
	
	rsKeys = stmt.getGeneratedKeys();
	while(rsKeys.next()) {
	    System.out.println(rsKeys.getInt(1));
	}	

/OUTPUT
1
2

Connect to SQLite Database using SQLite packages

	
	  import org.sqlite.jdbc4.*;

	  Properties p = new Properties();
	  
	  try(JDBC4Connection conn = new JDBC4Connection("/sqliteTutorial/location.db",
	  					     "/sqliteTutorial/location.db",p)) 
	  {
		  Statement stmt = conn.createStatement();

		  String SQL="SELECT * FROM ADDRESS"; 
		  
		  ResultSet rs = stmt.executeQuery(SQL);

		  while(rs.next()) {
		  
		 	 System.out.println(rs.getInt(1)+" "+
		 	 		    rs.getString(2)+" "+
		 	 		    rs.getString(3)+" "+
		 	 		    rs.getString(4)+" "+
		 	 		    rs.getString(5)+" "+
		 	 		    rs.getString(6)	 	 		    
		 	 		    
		 	 		    ); 
		  }
	  }

/OUTPUT
2 SQLite 3/222 Tirupati INDIA 2023-02-17