Java - SQLite
TOPIC 01 – INTRO TO SQLite DATABASE

LESSON NOTE

WARNING: This lesson is not complete at this point. Mr. Campeau simply rushed to put material on here so that it could be used later to create a complete lesson.

OUTSIDE OF JAVA

There is a free tool that works in Windows Command Prompt that allows you to manipulate SQLite databases. At the time of writing this, it is called sqlite3.exe. Its available on the sqlite website.

EXCELLENT RESOURCE

Click here to find an excellent resource on how to connect to an SQLite database using Java. There is a little bit of setting up to do beforehand though.

SETUP

SQLite is different than other databases. It is fully contained within a JAR file that you can download at https://bitbucket.org/xerial/sqlite-jdbc/downloads/

In order to use the functionality of that JAR library, you need to include it into your Java project. Here is how it is done in Eclipse:

Source: http://stackoverflow.com/questions/24963259/need-help-setting-up-sqlite-on-eclipse-with-java-for-the-first-time (Answered by Jan Kaufmann)

PROGRAM TO INSERT INTO A TABLE

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

/**

*

* @author sqlitetutorial.net

*/

public class Practice01

{

/**

* Connect to a sample database

*

* @param fileName the database file name

*/

public static void createNewDatabase(String fileName) {

//String url = "jdbc:sqlite:C:/sqlite/" + fileName;

String url = "jdbc:sqlite:" + fileName;

Statement stmt;

try (Connection conn = DriverManager.getConnection(url)) {

if (conn != null)

{

DatabaseMetaData meta = conn.getMetaData();

System.out.println("The driver name is " + meta.getDriverName());

System.out.println("Connected to database.");

stmt = conn.createStatement();

String sql = "INSERT INTO fun (a,b) VALUES (4,10);";

stmt.execute(sql);

stmt.close();

conn.close();

}

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

createNewDatabase("test.db");

}

}

PROGRAM TO READ FROM A TABLE

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

*

* @author sqlitetutorial.net

*/

public class Practice02

{

/**

* Connect to a sample database

*

* @param fileName the database file name

*/

public static void createNewDatabase(String fileName) {

//String url = "jdbc:sqlite:C:/sqlite/" + fileName;

String url = "jdbc:sqlite:" + fileName;

Statement stmt;

try (Connection conn = DriverManager.getConnection(url)) {

if (conn != null)

{

DatabaseMetaData meta = conn.getMetaData();

System.out.println("The driver name is " + meta.getDriverName());

System.out.println("A new database has been created.");

stmt = conn.createStatement();

String sql = "SELECT * FROM fun;";

ResultSet rs = stmt.executeQuery( sql );

while ( rs.next() )

{

int first = rs.getInt("a");

int second = rs.getInt("b");

System.out.print( "a = " + first + ", ");

System.out.println( "b = " + second );

}

rs.close();

stmt.close();

conn.close();

}

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

createNewDatabase("test.db");

}

}