Wednesday, May 28, 2014

JDBC Connectivity To SQLite Database Example Tutorial [Note]

First of all, download the necessary JAR files for SQLiteJDBC driver to include in the Java Program. 


https://bitbucket.org/xerial/sqlite-jdbc


Include the JAR in your project.


[[For NetBeans]]
Click on the project name - > Properties - > Libraries - > Add JAR - > The file you've just downloaded

Or if you're not using NetBeans, set the CLASSPATH accordingly.

JDBC uses drivers to connect to databases. Drivers act as an intermediate between your program code and the database. Different SQL dialects like MySQL, SQLite need different drivers.

The connection is requested from the DriverManager object and is configured using a URL (String).


URL has the form:

jdbc:sqlite:aDataBaseFile.db


aDataBaseFile.db form assumes that the database file is in the same directory as your java code file.

Here is a link explaining JDBC architecture.
There are three ways to specify paths:

    jdbc:sqlite://dirA/dirB/dbfile
    jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
    jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile
The database file is created if it already does not exist.

Example code that demonstrates the whole process: 



import java.sql.*;

public class SQLExample {

    /* First, specify the driver to be used.
            org.sqlite.JDBC     //as we're using SQLite
    */
    
    public static final String driver = "org.sqlite.JDBC";

    public static void main(String[] args) {
        try {
          //Get a Driver object Instance using the class object of driver
Driver d = (Driver) Class.forName(driver).newInstance();
            
          //Register the driver with Driver Manager
          DriverManager.registerDriver(d); 
        } 
         //If anything goes wrong
        catch (Exception e) {     
            System.out.println("Error loading database driver: " + e.toString());
        }
        //We need to get a connection to the database through the driver  
Connection con;  
         try {
            /*
            Creates a new database file if it already isn't there
         
            There are three ways to specify paths:
            
            jdbc:sqlite://dirA/dirB/dbfile
            jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
            jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile           
            */
            
        //Specify which database file to connect to
        String url = "jdbc:sqlite:/C:/xampp/htdocs/SQL/test.db";     
        /*
         Get a connection through driver manager and set con to 
         refer to this connection object
        */
        con = DriverManager.getConnection(url);                    
        } 
        //If anything goes wrong.
        catch (SQLException e) {                                     
            System.out.println("Error creating connection: " + e.toString());
        }

        /*
        Now that we are connected to the database, 
        we need to run statements and get results
        /*
        Declare a statement object. This takes a string 
        as input to its executeQuery(String) method 
        to contact the databse
        */
        Statement stmt;     
        ResultSet res;      // The ResultSet object contains the query output
        try {

            //The command to give to executeQuery(String)
            String sql = "SELECT * FROM people"
            //Get an actual statement object
            stmt = con.createStatement();   

            //store the result in res           
            res = stmt.executeQuery(sql);
        //If table not found or other invalid statements
        catch (SQLException e) {                  
            System.out.println("Error running the statement: " + e.toString());
            try {
                con.close();
            } 
            catch (Exception f) {/*Can do nothing now*/}
        }

        //Get the results:
        try {
            String id, Name;
            while (res.next()) {  //while the result has more rows to show...
                id = res.getString("id");   //get them
                Name = res.getString("Name");

                //do whatever you like with them
                System.out.println(id + " " + Name); 
            }
        } catch (Exception e) {
            System.out.println("Error processing results: " + e.toString());
            try {
                res.close();
                stmt.close();
                con.close();
            } catch (Exception ex) {}
        }

        //Finally: Cleanup : Close Everything [This is optional]
        try {
            res.close();
            stmt.close();
            con.close();
        } catch (SQLException e) {
            System.out.println("Error closing connection: " + e.toString());
        }
    }
}

No comments:

Post a Comment