Java Database Connectivity – MySQL, Oracle and SQLite

I have played around with database connectivity with Java for MySQL, Oracle and SQLite. I thought I would share some simple connection setup code with the world.

You will need to download and add the appropriate JARs to your project and or your class path, as well as having a database to connect to, unless you are using SQLite in which case the database is local.

MySQL

First up is MySQL connectivity. This first snippet will check you have loaded the JDBC driver correctly.

 

import java.sql.*;
public class MySqlLoadDriver {
  public static void main(String [] args) {
    Connection con = null;
    try {

      // Load the MySQL JDBC driver
      Class.forName("com.mysql.jdbc.Driver") ;
      System.out.println("MySQL JDBC driver loaded ok.");

    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

 

The following snippet will set up a connection to the database. Where the host in the example is set to localhost so you may change this to point to your database address or tunnel in to your database using a client like Putty. Also change username and password to your database username and password.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class SimpleConnection;
{

     static public void main(String args[])
     {

      Connection con = null;

      try {

         Class.forName("com.mysql.jdbc.Driver") ;
         System.out.println("MySQL JDBC driver loaded ok.");
         con = DriverManager.getConnection(
         "jdbc:mysql://127.0.0.1:3306/","username", "password");
         System.out.println("Connected with host:port/database.");
         con.close();

     } catch (Exception e) {
              System.err.println("Exception: "+e.getMessage());
     }

    }

}

 

Oracle

Oracle has a two types of connection I have played with Oracle Call Interface and Oracle’s JDBC Thin driver uses Java sockets to connect directly to Oracle. It provides its own TCP/IP version of Oracle’s SQL*Net protocol. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser. Depending on which one you want to use comment out or delete the other line. In the example I am using the OJDBC Thin Driver not the Oracle Call Interface.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleSimpleConnection {

     static public void main(String args[])

     {
         Connection con = null;

         try
         {
               Class.forName ("oracle.jdbc.OracleDriver");
               System.out.println("Oracle JDBC driver loaded ok.");
               con = DriverManager.getConnection(

                   //"jdbc:oracle:oci7:@//localhost:1521/ORCL","username", "password");
                   "jdbc:oracle:thin:@//192.168.104.11:1521/ORCL","username", "password");
               System.out.println("Success!!");
               System.out.println("Connected with host:port/database.");
               con.close();
          }
          catch (Exception e)
          {
               System.err.println("Exception: "+e.getMessage());
          }
       }
}

 

SQLite

Finally the creation and testing of an SQLite Database, adding in a few things with a simple query.

 

import java.sql.*;

public class SQLiteTest {

public static void main(String[] args) throws Exception {

    Class.forName("org.sqlite.JDBC");

    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();
    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);
    ResultSet rs = stat.executeQuery("select * from people;");

    while (rs.next()) {
          System.out.println("name = " + rs.getString("name"));
          System.out.println("job = " + rs.getString("occupation"));
    }

    rs.close();
    conn.close();

  }

}

 

Useful Links;

http://www.sqlite.org/

http://www.zentus.com/sqlitejdbc/

http://www.oracle-internals.com/?p=18

http://www.orafaq.com/wiki/JDBC#Thin_driver

How to install the Oracle JDBC into your local Maven repository

This will work to install any jar or package into your local Maven repository, just change the relevant sections in the command line entry.

To get the Oracle Driver go toand then to the JDBC Drivers page which at the time of writing is here:

Ensure you have Maven properly setup on your machine.

Navigate to the folder containing the jar in a command line prompt.

Enter the following;
mvn install:install-file -Dfile=ojdbc5.jar -DgroupId=com.oracle -DartifactId=ojdbc5 -Dversion=11.2.0.3 -Dpackaging=jar -DgeneratePom=true

Where:
-Dfile is the path to file
-DgroupId is the group ID
-DartifactId is the artefact ID
-Dversion is the version of the package
-Dpackaging is the package type i.e. jar
– DgeneratePom will automatically generate the pom.xml entry

Note: If you are using Powershell you will need to escape the – with a back tick so -Dfile=ojdbc5.jar becomes `-Dfile=ojdbc5.jar`
If you are adding Sun JARs is there is a helpful article on naming conventions to use here.

Useful links;
http://maven.apache.org/guides/mini/guide-3rd-party-jars-local.html
http://stackoverflow.com/questions/442230/how-to-manually-install-an-artifact-in-maven-2
http://stackoverflow.com/questions/1074869/find-jdbc-driver-in-maven-repository