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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s