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

Custom Colours for Checkboxes in User Forms Excel 2007

This guide will show you how to make a checkbox in Excel match the fill of a cell, background or text colour.

This guide is using Excel 2007.

Select the cell which has the fill you require, then in the Home ribbon select the fill icon, and click on More Colors…

Excel Colours

In the Colors dialogue select the Custom tab. Take a note of the RGB code for the fill of the cell. So in the screen shot the RGB is 255,204,153.

Colour Picker

 

Now you need to convert the RGB value to Hexadecimal. I would recommend using http://www.endprod.com/colors/rgb2hex.htm but you can always Google for a different converter.

Now with your Hex value you need to format it ready for input into Excel. The simple explanation is that for Excel you need to build a String to enter as the BackColor property.

The String will start with &H00 followed the Hex you just generated. However, you need to swap the last two characters of the hex string with the last, so if your Hex code is ffcc99 then you need to use 99ccff. So, now you have your string &H00*****, where the asterisks are replaced with your Hex code add a final & to the string and you are now ready to go.

Return to Excel, in the Developer tab on the Ribbon (to add Click the Microsoft Office Button, and then click Excel Options. Click Popular, and then select the Show Developer tab in the Ribbon check box) click on the Design Mode icon. Now single click on the CheckBox you want to change the color of and then in the Properties box enter your new color string in the BackColor, or if it is for the text color enter the string into the ForeColor property.