Java JDBC using SQLite/Connecting

Assuming that your classpath is now established, and your JDK is ready to go, we can have a quick and dirty attempt to throw together a very simple database with only one table in it. As this is something of a disposable 'HelloWorld' mini-app on steroids, let's call it 'HelloDatabase'.

You really need to know one thing about the JDBC driver: its registered DriverName. For SqlLite this is:

org.sqlite.JDBC

This value is used to register the driver in Java. Different JDBC drivers for different database implementations have different registered DriverNames. While it is entirely possible to discover DriverNames from the containing jar file(s) programatically, this lies outside the scope of this document and they can be looked up fairly quickly on the Internet e.g. at [1].

Now we come to constructing the URL string which is used in establishing the connection to the database. Different JDBCs for different database types have different URL definitions; the SqlLite URL string really just needs to know that it is a jdbc of the type 'sqlite' and the name of the target database which we wish to use. If for example we wish to connect to a new database called order.db we would need to compose a string comprised of the string 'jdbc', the database type name, in this case 'sqlite' plus the name of the database, separated by colons, i.e.:

jdbc:sqlite:order.db

other examples

jdbc:sqlite://dirA/dirB/dbfile

jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile

jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile

an in-memory option is also available.

As a minimal set of imports for this mini-app (and for most simple JDBC connectors), you will want the following set of imports available as shown in the following code. The two .*MetaData imports are useful and often necessary for debugging and investigative purposes, as well as being handy for abstraction and ultimate reduction in the quantity of hard code that your programs will require.

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
 
public class HelloDatabase
{
    public static void main (String[] args) throws Exception
    {
        // register the driver 
        String sDriverName = "org.sqlite.JDBC";
        Class.forName(sDriverName);
 
        // now we set up a set of fairly basic string variables to use in the body of the code proper
        String sTempDb = "hello.db";
        String sJdbc = "jdbc:sqlite";
        String sDbUrl = sJdbc + ":" + sTempDb;
        // which will produce a legitimate Url for SqlLite JDBC :
        // jdbc:sqlite:hello.db
        int iTimeout = 30;
        String sMakeTable = "CREATE TABLE dummy (id numeric, response text)";
        String sMakeInsert = "INSERT INTO dummy VALUES(1,'Hello from the database')";
        String sMakeSelect = "SELECT response from dummy";
 
        // create a database connection
        Connection conn = DriverManager.getConnection(sDbUrl);
        try {
            Statement stmt = conn.createStatement();
            try {
                stmt.setQueryTimeout(iTimeout);
                stmt.executeUpdate( sMakeTable );
                stmt.executeUpdate( sMakeInsert );
                ResultSet rs = stmt.executeQuery(sMakeSelect);
                try {
                    while(rs.next())
                        {
                            String sResult = rs.getString("response");
                            System.out.println(sResult);
                        }
                } finally {
                    try { rs.close(); } catch (Exception ignore) {}
                }
            } finally {
                try { stmt.close(); } catch (Exception ignore) {}
            }
        } finally {
            try { conn.close(); } catch (Exception ignore) {}
        }
    }
 
}

You should now also find that you have a file called hello.db in your working directory, (or whatever else you called it in the database name component for the URL). If you attempt to run this code more than once, you will get a SQL Exception error since you will be trying to create a table that already exists; you can delete the .db file, or use SqLite to drop the table, or insert a new String with a drop table statement in it and use stmt.executeUpdate to run the drop string before attempting to create the table...

• Next Page: Improving
• Previous Page: Preparation