Specifying Encoding in Maven POM

If the encoding is not defined while compiling with Maven you’ll likely see the following warning:

...
[WARNING] Using platform encoding (MacRoman actually) to copy filtered resources, i.e. build is platform dependent!
...

Explicitly specifying the encoding in the POM is the way to go:

<project>
  ...
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
  ...
</project>

and Maven acknowledges with the following while logging:

...
[INFO] Using 'UTF-8' encoding to copy filtered resources.
...

INSERT INTO SELECT Oracle PL/SQL faux pas

I’ve recently stumbled upon a bug that came down to the following piece of code:

INSERT INTO TABLE_A (
   SELECT ALPHA,
          GAMMA,
          BETA
   FROM TABLE_B
)
;

This is an INSERT query with respect to the column order of the table. In general the order of the columns in the table shouldn’t be important on our queries therefore the following more robust version of INSERT is guarding against ambiguity relying on the column order:

INSERT INTO TABLE_A (
   ALPHA,
   BETA,
   GAMMA
) VALUES
(  SELECT ALPHA,
          BETA,
          GAMMA
   FROM TABLE_B
)
;

How to create zip in Maven

The other day I was looking for a maven plugin to create a zip file of the target folder but it turns out that there is the maven-assembly-plugin that can do this already among other things.

First we need to add the plugin in POM:

<plugins>
  		<plugin>
  			<groupId>org.apache.maven.plugins</groupId>
  			<artifactId>maven-assembly-plugin</artifactId>
  			<version>2.2.1</version>
  			<configuration>
  				<descriptors>
  					<descriptor>src/main/assembly/zip.xml</descriptor>
  				</descriptors>
  			</configuration>
  		</plugin>
  	</plugins>

along with the following descriptor under src/main/assembly/zip.xml

<assembly xmlns="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.0 http://maven.apache.org/xsd/assembly-1.1.0.xsd">
  <id>myZip</id>
  <baseDirectory>/</baseDirectory>
  <formats>
    <format>zip</format>
  </formats>
  <fileSets>
    <fileSet>
      <directory>${project.build.directory}</directory>
    </fileSet>
  </fileSets>
</assembly>

Then it all comes down to:

mvn clean package assembly:assembly

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.