SQLite is a self-contained, serverless, zero-configuration database system. It allows SQL commands to query/update databases, stored as ordinary disk files. For example, SuppDB is a database, with data in multiple tables, stored in one disk file called sqlite/SuppDB.
Directory Structure
cs4311 +--> sqlite +--> ex1, ex2, etc.
Load Data for Database (already exists; don't need to do this)
linux$ sqlite3 SuppDB <suppDB.dd linux$ sqlite3 SuppDB <suppDB.data linux$ sqlite3 genDB <genDB.dd linux$ sqlite3 genDB <genDB.data
.dd has SQL commands for table names, field names, keys
.data has SQL commands for actual data to insert into tables
Execute a Query in Linux
linux$ cd sqlite linux$ cp sqlite-linux sqlite3 linux$ sqlite3 SuppDB sqlite> select * from S; sqlite> .quit
Execute a Query in Windows
win: cd sqlite win: sqlite3.exe SuppDB sqlite> select * from S; sqlite> .quit
Example Java class with JDBC commands (Page 22 of CS 4311 class notes)
import java.sql.*;
public class SuppDBquery {
public static void main(String args[]) {
try {
// 1: load specific code to use JDBC
Class.forName("org.sqlite.JDBC");
// 2: establish connection to file (DB) using protocol, file loacation
// This connection is for java files on ex1, ex2, etc.
// Connection con = DriverManager.getConnection("jdbc:sqlite:../sqlite/SuppDB");
// This connection is for SuppDBquery located on the sqlite dir itself
Connection con = DriverManager.getConnection("jdbc:sqlite:SuppDB");
// 3: environment for queries
Statement stmt = con.createStatement();
// 4: SQL query of a supplier table
stmt.execute("select * from S where STATUS > 10");
// 5: rs encapsulates rows which match the query
ResultSet rs = stmt.getResultSet();
// 6: iterate through the rows with a cursor
// next() returns false when cursor goes beyond results
while (rs.next()) {
// 7: extract columns from the row pointed to by the cursor
System.out.println(rs.getString("S_NO") + " " + rs.getInt("STATUS"));
}
// 8: closes READ action, allows READ or WRITE action later
rs.close();
} catch (Exception e) {System.out.println(e.getMessage());}
}
}
Execution of Java class with JDBC commands
From the sqlite directory:
linux$ java -classpath ".:sqlite-jdbc-3.8.6.jar" SuppDBquery win : java -classpath ".;sqlite-jdbc-3.8.6.jar" SuppDBquery
From the ex5 directory (after a student completes Client.java):
linux$ java -classpath ".:../sqlite/sqlite-jdbc-3.8.6.jar" Client win : java -classpath ".;../sqlite/sqlite-jdbc-3.8.6.jar" Client