Oracle XE JDBC Example with Sample Data

This quick tutorial demonstrates basic JDBC connectivity to the HR database which is already setup in Oracle XE and is part of the Sample Data. This database is small, filled with data, easy to grasp and ideal to quickly put some prototype code together without worrying about DDLs and other initialisation scripts.

Please first refer to my previous post on how to get Oracle XE installed on any version of Mac OS that it also applies for any OS given that the correct version of VirtualBox gets installed.

Given that we have Oracle XE now installed and communication via TCP port 1521 is allowed we are good to go.

By using IntelliJ datasource capabilities we can quickly test connectivity to the Oracle’s Sample HR database (username: hr, password: oracle (if the DB is installed via the VM methodology described) otherwise password: hr (if the DB is natively installed) ):

Data Source Properties

while testing the connection yields success:

IntelliJ IDEA-1

.HR.EMPLOYEES - ScalaProj - [~_Dimitris_ScalaProjects_ScalaProj]

and for completeness here’s the UML of the sample database as it appears from IntelliJ:

DATABASE_1f59cfcd-b0bc-4ab3-a0ca-fd6402de2165.schema_HR - ScalaProj - [~_Dimitris_ScalaProjects_ScalaProj]

and also how it appears from Oracle Modeler:

Oracle Developer Days [Running]

Oracle Developer Days [Running]-1

Advertisements

Maven install ojdbc6

I really wished the Oracle driver jar was part of any (legal) publicly available Maven repo, but it’s not. So we’ll have to take matters on our hands and install it in our local repo once and for all so we can effortlessly thereafter summon it via our pom file:

       <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

Steps:

  • Download the jdbc6.jar from the Oracle website. I tried to automate this step via a Groovy script but this pesky agreement radio-button gets in the way (which is there for a reason to be fair)
  • Supposing mvn is already setup in your path:
  • mvn install:install-file -Dfile=ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -DgeneratePom=true
    

JDBC MySQL Maven working example

Here’s a minimal working example of Java connecting to MySQL using the JDBC interface materialised by the MySQL driver connector.

First off we need a new database, a user, privileges and a new table with some values here’s the script:

create database javaTestDB;
use javaTestDB;
CREATE USER javauser IDENTIFIED BY 'javapass';
grant usage on *.* to javauser@localhost identified by 'javapass';
grant all privileges on javaTestDB.* to javauser@localhost;

CREATE TABLE TEST_TABLE (
		id INT NOT NULL AUTO_INCREMENT,
		TEXT VARCHAR(400) NOT NULL,
		PRIMARY KEY (ID)
	);

INSERT INTO TEST_TABLE values (default, 'test text');

Since we are using Maven the POM looks like this:

4.0.0
  com.dimitrisli
  javaMysql
  0.0.1-SNAPSHOT

  	JavaMySQL
  	

  			org.apache.maven.plugins
  			maven-compiler-plugin
  			2.3.2

  				<source />1.6
  				1.6
  				UTF-8




  		mysql
  		mysql-connector-java
  		5.1.18


  
  	UTF-8

Notice how conveniently we are bringing the MySQL JDBC driver.

The main Class that deals with the JDBC connection is as follows:

package com.dimitrisli.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class MySQLJava {

	enum TestTableColumns{
		id,TEXT;
	}

	private final String jdbcDriverStr;
	private final String jdbcURL;

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;
	private PreparedStatement preparedStatement;

	public MySQLJava(String jdbcDriverStr, String jdbcURL){
		this.jdbcDriverStr = jdbcDriverStr;
		this.jdbcURL = jdbcURL;
	}

	public void readData() throws Exception {
		try {
			Class.forName(jdbcDriverStr);
			connection = DriverManager.getConnection(jdbcURL);
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from javaTestDB.test_table;");
			getResultSet(resultSet);
			preparedStatement = connection.prepareStatement("insert into javaTestDB.test_table values (default,?)");
			preparedStatement.setString(1,"insert test from java");
			preparedStatement.executeUpdate();
		}finally{
			close();
		}
	}

	private void getResultSet(ResultSet resultSet) throws Exception {
		while(resultSet.next()){
			Integer id = resultSet.getInt(TestTableColumns.id.toString());
			String text = resultSet.getString(TestTableColumns.TEXT.toString());
			System.out.println("id: "+id);
			System.out.println("text: "+text);
		}
	}

	private void close(){
		try {
			if(resultSet!=null) resultSet.close();
			if(statement!=null) statement.close();
			if(connection!=null) connection.close();
		} catch(Exception e){}
	}
}

Few things to notice here:

  • During runtime the MySQL driver is loaded into classpath using the reflective Class.forName(); By the way, since JDBC 4.0, this is not necessary and this line can be omitted.
  • By calling the static factory method DriverManager.getConnection() behind the scenes the registered JDBC driver is serving this request. Pure reflection magic.
  • Out of the materialised connection we creating our statement upon which we execute the query.
  • ResultSet is picking up the results and we query it based on the name of the column and the type we are expecting. Notice we receive the ID which is integer in the Integer wrapper class guarding against null returns in case of non-existence.
  • Another way to query is using preparedStatement where the query string includes question marks for all the inputs and by calling type specific setter classes we securely setting the input.
  • Notice how a try {}finally{} block is guarding all the SQL operations leading to an impossible to escape finally{close();} where we release safely all the resources. Even if there was a return statement or a caught/uncaught exception was thrown, regardless the finally block would be first executed before exiting the try block.

Finally the main method that kickstarts the JVM looks like this:

package com.dimitrisli.mysql;

public class Main {
	public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	public static final String MYSQL_URL = "jdbc:mysql://localhost/javaTestDB?"
											+ "user=javauser&password=javapass";

	public static void main(String[] args) throws Exception {
		MySQLJava dao = new MySQLJava(MYSQL_DRIVER,MYSQL_URL);
		dao.readData();
	}
}

The code can be found in this Github repository.