Poseidon Athens Half Marathon Registrations – Architecture, Technical & Infrastructure Overview

Table of Contents

• Overview
• Samples
  • Front-end Samples
  • Admin Site Samples
  • Report Site Samples
• Architectural Overview
• Technologies/ Frameworks
• Infrastructure
• Future Improvements

Overview

I have been helping my dad who is organising the 3rd biggest running event in Greece, the Poseidon Athens Half Marathon and Parallel Races, by putting together a registration capability. This article is describing all the technical aspects of this effort.

The effort has been originally started in 2019, in line for the 2020 event, but Covid hit and all the running events in Greece got postponed by two years, so it eventually went live at the end of 2021 in line for the 2022 event which successfully took place in the middle of past April.

Samples

Front-end Samples

Although the registrations for 2023 are currently closed on the main public front end in preparation for the 2023 event, here are some screenshots from the test site:

Following that page comes a secured payment page that forwards the request via the registration-server to a confirmation or rejection page.

Admin Site Samples

All the below are sample dummy data in the test environment

Report Site Samples

All the below are sample dummy data in the test environment

Architectural Overview

In short, there is a public Front-end for the registration forms that consumes and directly interacts exclusively with a public Back-end component. The latter component is also responsible for scheduling jobs and email notification sendouts. To support Organisation members activities there are two additional components: the Admin site that is an authenticated/authorised view of all the data and the Report site for anything regarding reports and analytics. Finally, there is also a standalone tool that is responsible of parsing exclusively group registrations received via a customised excel spreadsheet.

Technologies/ Frameworks

The public Front-end has been built using the Create React App npx command. Multiple Ant Design components have been utilised especially the Form component capabilities. For internalisation the i18next React library has been used while for routing, the React Router has been used with the HashRouter variant.

The public Back-end has been built using Spring Boot exposing Rest Endpoints. For Database connectivity Hibernate has been used. The Spring Scheduling capabilities have been utilised for sending the email notifications while Apache Camel Barcode component has been used to generate the QR code on the email notifications. For the generation of the PDF attachment on the email, the iText library has been used.

For the creation of the private Admin site, the JHipster generator has been used to bootstrap the project in its React variant. With its Liquibase embedded capabilities, it is the master for the database generation and future changes application. Special care has been taken to maintain the script written in JDL (JHipster Domain Language) utilising the excellent JDL Studio Visualiser. The Authentication capability has been enhanced to allow some new more granular roles that are guarding certain functionalities.

The private Report site has been built as a single application where the backend endpoints are served via Spring Boot while the frontend code is served via React components. Special care has been taken to construct it in a generic fashion in that everything that appears takes the form of tiles that are showing some diagram or numerical value along with some icon and a download report link. For the visualisation part the Ant Design Charts have been used. For the Database connectivity the Jooq library has been utilised.

Infrastructure

The overall infrastructure can be summarised on the below diagram:

The code repositories are all hosted in GitHub while CI/CD has been setup as GitHub Action triggering AWS CodePipeline

All of the backend applications (registrations-server) as well as the standalone applications with both frontend and backend code (registrations-report and registrations-admin) have been deployed into AWS Elastic Beanstalk which is AWS’s PaaS solution sitting on top of their AWS EC2 solution.

Each one of the application has been deployed twice, representing Test and Production environments, each having their configurations injected.

The registrations-client front-end codebase also triggers via GitHub actions AWS CodePipeline, but this time gets deployed in AWS Amplify solution. In particular there is a main branch for Test deployment while there exist a prod branch for the Production deployment. Aside from making it very easy to deploy front end codebases, AWS Amplify is also giving out of the box an Amazon Certificate on the autogenerated domain or the explicitly owned and associated domain.

For the registrations-server public application in particular, the Load Balancing capabilities of Elastic Beanstalk have been utilised to auto-scale (up or down) based on a Network traffic threshold strategy.

This setup is bringing two additional benefits: Firstly it makes it very easy to associate a domain and/or certificate to the Load Balancer and secondly it makes it equally easy to do the same on subdomains.

For that reason via AWS Route 53 a domain has been acquired: stc-events-registrations-server.org and underneath it two subdomains have been setup: test.stc-events-registrations-server.org and prod.stc-events-registrations-server.org, each being setup via DNS records to forward directly to the Load Balanced AWS Elastic Beanstalk instance as it is shown below:

Having this setup is also giving out-of-the-box a certificate associated at the Load Balancer level.

Future Improvements

• Transition to a more generic and dynamic, metadata-based configuration where more than one registration forms could be accommodated. In such an implementation the front-end registration code will be quite thin and all the metadata configuration about what is to be displayed and how will be coming from the back-end. That would facilitate setting up future registration events effortlessly without the need to write any line of code.
• Decoupling the public back-end from scheduling activities that should be residing to a dedicated standalone other back-end application responsible exclusively for batch operations.
• Although Card Payment is supported currently via Cardlink, invoicing is still issued manually. In a future improvement, invoice will also be generated automatically and instantly at the point of purchase.
• Enhancing the Group Registrations capability. Currently it is accommodated via Excel spreadsheets that are sent out to Organisations and received back, being parsed later via a standalone tool. The nature of Excel is limited in terms of form handling so quality of data is lacking introducing delays in processing group registration requests. This functionality will be migrated to a web-based solution where the registration will be happening online.

Spring JdbcDaoSupport, DBCP, MySQL demonstration

Following up from the Spring MySQL JdbcDaoSupport demonstration example, we augment this to add DBCP support in our application.

All we have to do is first Add the DBCP dependency in the POM file:

    <dependency>
    	<groupId>commons-dbcp</groupId>
    	<artifactId>commons-dbcp</artifactId>
    	<version>1.4</version>
    </dependency>

and then add the DBCP related datasource in the datasource bean config file:

<bean id="dataSourceDBCP" 
         class="org.apache.commons.dbcp.BasicDataSource">
	<property name="driverClassName" value="${jdbc.driverClassName}" />
	<property name="url" value="${jdbc.url}" />
	<property name="username" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
	<property name="initialSize" value="${dbcp.initialSize}" />
	<property name="maxActive" value="${dbcp.maxActive}" />
</bean>

In this bean we setup our database connection pool to have an initial size of 5 db connection that can be increased up to 10 connections.

Then, thanks to the dependency injection magic, all we have to do is change the name of the dataSource bean to the DAO bean config file:

   <bean id="personSupportDao" class="com.dimitrisli.springMySQL.dao.PersonDaoSupportImpl" >
   		<property name="dataSource" ref="dataSourceDBCP"></property>
   </bean>

The source code can be found <a href="https://github.com/dimitrisli/SpringJdbcSupportMySQL">in this Github repository</a>.

Spring, Hibernate, MySQL integration example

This is a working example showcasing Spring, Hibernate and MySQL integration in a Maven project.

The POM looks like this:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.dimitrisli</groupId>
  <artifactId>SpringMySQL</artifactId>
  <version>1.0</version>
  <build>
  	<finalName>SpringHibernateMySQL</finalName>
  	<plugins>
  		<plugin>
  			<groupId>org.apache.maven.plugins</groupId>
  			<artifactId>maven-compiler-plugin</artifactId>
  			<version>2.3.2</version>
  			<configuration>
  				<source>1.6</source>
  				<target>1.6</target>
  				<encoding>UTF-8</encoding>
  			</configuration>
  		</plugin>
  	</plugins>
  </build>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.18</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.1.0.RELEASE</version>
      <scope>compile</scope>
    </dependency>
	<dependency>
	  <groupId>org.hibernate</groupId>
	  <artifactId>hibernate-core</artifactId>
	  <version>3.6.9.Final</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-orm</artifactId>
		<version>3.1.0.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>javassist</groupId>
		<artifactId>javassist</artifactId>
		<version>3.12.1.GA</version>
	</dependency>
  </dependencies>
  <properties>
  	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
</project>

Our MySQL database 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 PERSON_RECORD (
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(15) NOT NULL,
	SURNAME VARCHAR(15) NOT NULL,
	PRIMARY KEY (ID)
);

This is basically a table that is representing our Person Java bean:

package com.dimitrisli.springHibernateMySQL.model;

import java.io.Serializable;

public class Person implements Serializable {

	private static final long serialVersionUID = 1L;
	private Integer id;
	private String name;
	private String surname;

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSurname() {
		return surname;
	}
	public void setSurname(String surname) {
		this.surname = surname;
	}

	public void setId(Integer id){
		this.id = id;
	}

	public Integer getId(){
		return this.id;
	}
}

Each DB table record represents a Person Object. The Person CRUD operations are represented in this interface:

package com.dimitrisli.springHibernateMySQL.dao;

import com.dimitrisli.springHibernateMySQL.model.Person;

public interface PersonORMDao {

	public void create(Person person);
	public Person read(String name, String surname);
	public void update(Person person);
	public void delete(Person person);
}

Since we are using Spring it’s a good practise to have an associated interface for every DAO bean we are planning to have since Spring behind the scenes is creating a proxy between this interface and the POJO before creating the bean.

The implementation of this interface looks like this:

package com.dimitrisli.springHibernateMySQL.dao;

import java.util.List;

import com.dimitrisli.springHibernateMySQL.model.Person;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class PersonDaoHibernateImpl extends HibernateDaoSupport implements PersonORMDao{

	public void create(Person person) {
		getHibernateTemplate().save(person);
	}

	public Person read(String name, String surname) {
		List list = getHibernateTemplate().find("from PERSON_RECORD where NAME=? AND SURNAME=?",name,surname);
		return (Person) list.get(0);
	}

	public List readAll() {
		List list = getHibernateTemplate().find("from " + Person.class.getName());
		return list;
	}

	public void update(Person person) {
		getHibernateTemplate().update(person);
	}

	public void delete(Person person) {
		getHibernateTemplate().delete(person);
	}

	public void deleteAll() {
		getHibernateTemplate().deleteAll(readAll());
	}
}

Notice that this DAO implementation is extending from HibernateDaoSupport which is a Spring Hibernate convenience abstract class and is using its getHibernateTemplate() for all the data access operations. Also notice the hibernate query language (HQL) to express sql where conditions.

We’ll pack the property related files under src/main/resources grouped in meaningful folders maintaining a good housekeeping.

The Hibernate related DB mapping into is maintained under resources/hibernate/person.hbm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.dimitrisli.springHibernateMySQL.model.Person" table="PERSON_RECORD">
        <id name="id" type="java.lang.Integer">
            <column name="ID" />
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="NAME" length="15" not-null="true" unique="false" />
        </property>
        <property name="surname" type="string">
            <column name="SURNAME" length="15" not-null="true" unique="false" />
        </property>
    </class>
</hibernate-mapping>

MySQL related information like the driver name, the connection URL, the username/pass are found in the resources/properties/database.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/javaTestDB
jdbc.username=javauser
jdbc.password=javapass

The database related beans are stored in resources/spring/database/database.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location">
		<value>properties/database.properties</value>
	</property>
</bean>

<bean id="dataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="${jdbc.driverClassName}" />
	<property name="url" value="${jdbc.url}" />
	<property name="username" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
</bean>

</beans>

Things to notice:

• Spring’s PropertyPlaceholderConfigurer is conveniently reading the database properties and make them available to the datasource bean.

The Hibernate session factory represented in a LocalSessionFactoryBean is stored in the resources/spring/database/hibernate.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">
 
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource">
      <ref bean="dataSource"/>
    </property>
    <property name="hibernateProperties">
       <props>
         <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
         <prop key="hibernate.show_sql">true</prop>
       </props>
     </property>
 	 <property name="mappingResources">
		<list>
           <value>/hibernate/person.hbm.xml</value>
		</list>
     </property>
    </bean>
</beans>

Note how the mapping config file is loaded as a property on the bean. Also how we are activating the HQL and show the output.

the final configuration file is gathering all the resources and found in resources/spring/config:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
 
	<import resource="../database/datasource.xml" />
	<import resource="../database/hibernate.xml" />
 	<import resource="../beans/beans.xml" />
 	
</beans>

Finally the test class that is creating the application context and making some basic demonstration queries:

package com.dimitrisli.springHibernateMySQL.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dimitrisli.springHibernateMySQL.dao.PersonDaoHibernateImpl;
import com.dimitrisli.springHibernateMySQL.model.Person;

public class Main {

	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("spring/config/beanLocations.xml");
		PersonDaoHibernateImpl personDao = (PersonDaoHibernateImpl) context.getBean("personHibernateDao"); 
		
		System.out.println("Adding data...");
		Person dimitris = new Person(); dimitris.setName("dimitris"); dimitris.setSurname("liapis");
		Person michalis = new Person(); michalis.setName("michalis"); michalis.setSurname("liapis");
		Person kostas = new Person(); kostas.setName("kostas"); kostas.setSurname("liapis");
		Person stella = new Person(); stella.setName("stella"); stella.setSurname("liapis");
		personDao.create(dimitris);
		personDao.create(michalis);
		personDao.create(kostas);
		personDao.create(stella);
		
		System.out.println("Retrieving data..");
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
		System.out.println("Deleting a record and viewing...");
		personDao.delete(kostas);
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
		System.out.println("Updating a record and viewing...");
		dimitris.setName("newDimitrisName");
		personDao.update(dimitris);
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
		System.out.println("Deleting everything and viewing...");
		personDao.deleteAll();
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
	}
}

The output, subtracting the initialisation coding of the frameworks, looks like this:

Adding data...
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Retrieving data..
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
From DB: name=dimitris, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=kostas, surname=liapis
From DB: name=stella, surname=liapis
Deleting a record and viewing...
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
From DB: name=dimitris, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
Updating a record and viewing...
Hibernate: update PERSON_RECORD set NAME=?, SURNAME=? where ID=?
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
Deleting everything and viewing...
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_

The source code can be found in this Github repository.

Spring JdbcDaoSupport, MySQL demonstration

This is a variation of the Spring MySQL Jdbc demonstration article that we will alter to use Spring’s JdbcDaoSupport abstract Class.

Our DaoSupport Class bean will read now:

package com.dimitrisli.springMySQL.dao;

import java.util.List;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.dimitrisli.springMySQL.model.Person;


public class PersonDaoSupportImpl extends JdbcDaoSupport implements PersonDao{

	public void createPerson(String name, String surname) {
		getJdbcTemplate().update("INSERT INTO PERSON_RECORD (NAME,SURNAME) VALUES (?,?)",
				new Object[]{name,surname});
	}

	public List<Person> selectPerson(String name, String surname) {
		return getJdbcTemplate().query("SELECT NAME,SURNAME FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[] {name,surname},
				new PersonRowMapper());
	}

	public List<Person> selectAll() {
		return getJdbcTemplate().query("SELECT NAME,SURNAME FROM PERSON_RECORD",
				new PersonRowMapper());
	}

	public void deletePerson(String name, String surname) {
		getJdbcTemplate().update("DELETE FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[]{name,surname});
	}

	public void deleteAll() {
		getJdbcTemplate().update("DELETE FROM PERSON_RECORD");
	}

	
}

while the bean declaration of this Class is similar in that it gets the dataSource injected:

   <bean id="personSupportDao" class="com.dimitrisli.springMySQL.dao.PersonDaoSupportImpl" >
   		<property name="dataSource" ref="dataSource"></property>
   </bean>

The difference is that we don’t need in our class a setter method since super class JdbcDaoSupport is taking care of that.

The code can be found in this Github repository.

Spring JdbcTemplate, MySQL demonstration

This is a demonstration of Spring, MySQL and Maven integration and a quick showcase of database CRUD operations using Spring’s JdbcTemplate.

First off the POM:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.dimitrisli</groupId>
  <artifactId>SpringMySQL</artifactId>
  <version>1.0</version>
  <build>
  	<finalName>SpringMySQL</finalName>
  	<plugins>
  		<plugin>
  			<groupId>org.apache.maven.plugins</groupId>
  			<artifactId>maven-compiler-plugin</artifactId>
  			<version>2.3.2</version>
  			<configuration>
  				<source>1.6</source>
  				<target>1.6</target>
  				<encoding>UTF-8</encoding>
  			</configuration>
  		</plugin>
  	</plugins>
  </build>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.18</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.1.0.RELEASE</version>
      <scope>compile</scope>
    </dependency>
  </dependencies>
  <properties>
  	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
</project>

Things to notice:

• Adding the spring-jdbc dependency is enough to bring all the spring jars required including the spring-context that is integral for creating the application context. The minimum number of spring dependencies we need are:
  • spring-beans
  • spring-core
  • spring-asm
  • commons-logging
  • spring-context
  • spring-expression
• mysql-connector-java is the java MySQL driver. Effortlessly added thanks to the Maven central repo.

The Bean we are going to deal with is a simple POJO bean representing a Person having a name and a surname:

package com.dimitrisli.springMySQL.model;

public class Person {

	private String name;
	private String surname;

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSurname() {
		return surname;
	}
	public void setSurname(String surname) {
		this.surname = surname;
	}
}

The MySQL script to create the database, user, privileges and table:

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 PERSON_RECORD (
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(15) NOT NULL,
	SURNAME VARCHAR(15) NOT NULL,
	PRIMARY KEY (ID)
);

Each row in the database corresponds to a Person object and Spring does this transition for us using the RowMapper:

package com.dimitrisli.springMySQL.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.dimitrisli.springMySQL.model.Person;

public class PersonRowMapper implements RowMapper<Person> {

	public Person mapRow(ResultSet resultSet, int arg1) throws SQLException {
		Person person = new Person();
		person.setName(resultSet.getString(1));
		person.setSurname(resultSet.getString(2));
		return person;
	}
}

In big projects good housekeeping principles are rewarded along the way so although a small test case project we’ll follow a structured way storing our resources.

First, under resources/properties/database.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/javaTestDB
jdbc.username=javauser
jdbc.password=javapass

We’ll store the Spring related resources under resources/spring.

For the dataSource we have resources/spring/database/datasource.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location">
		<value>properties/database.properties</value>
	</property>
</bean>

<bean id="dataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="${jdbc.driverClassName}" />
	<property name="url" value="${jdbc.url}" />
	<property name="username" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
</bean>

</beans>

Things to notice here:

• Spring’s PropertyPlaceholderConfigurer is taking good care of retrieving property entries and make it available in the Spring xml config files.
• Spring’s DriverManagerDataSource is picking up the database property specifics and initialising the dataSource for us.

For the PersonDao now we first create an interface. It’s a good practice to hide our implementation behind an interface when using Spring since behind the scenes when the Spring framework is creating a bean it is creating a proxy between our interface and the implementation.

Our CRUD operations are captured in the PersonDao Interface:

package com.dimitrisli.springMySQL.dao;

import java.util.List;

import com.dimitrisli.springMySQL.model.Person;

public abstract interface PersonDao {

	public abstract void createPerson(String name, String surname);
	public abstract List<Person> selectPerson(String name, String surname);
	public abstract List<Person> selectAll();
	public abstract void deletePerson(String name, String surname);
	public abstract void deleteAll();
}

and its implementation looks like this:

package com.dimitrisli.springMySQL.dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.dimitrisli.springMySQL.model.Person;

public class PersonDaoImpl implements PersonDao{

	DataSource dataSource;

	public void createPerson(String name, String surname) {
		JdbcTemplate createPerson = new JdbcTemplate(dataSource);
		createPerson.update("INSERT INTO PERSON_RECORD (NAME,SURNAME) VALUES (?,?)",
				new Object[]{name,surname});
	}

	public List<Person> selectPerson(String name, String surname) {
		JdbcTemplate selectPerson = new JdbcTemplate(dataSource);
		return selectPerson.query("SELECT NAME,SURNAME FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[] {name,surname},
				new PersonRowMapper());
	}

	public List<Person> selectAll() {
		JdbcTemplate selectPerson = new JdbcTemplate(dataSource);
		return selectPerson.query("SELECT NAME,SURNAME FROM PERSON_RECORD",
				new PersonRowMapper());
	}

	public void deletePerson(String name, String surname) {
		JdbcTemplate deletePerson = new JdbcTemplate(dataSource);
		deletePerson.update("DELETE FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[]{name,surname});
	}

	public void deleteAll() {
		JdbcTemplate deleteAll = new JdbcTemplate(dataSource);
		deleteAll.update("DELETE FROM PERSON_RECORD");
	}

	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}

}

Things to note:

• JdbcTemplate is taking care of the DB interaction doing our CRUD operation. All it needs is the dataSource which is injected as a property bean as we’ll see further on.
• No need to open connections, close resources or deal with the low level technicalities surrounding DB operations JdbcTemplate is doing that for free. Also the Exceptions are all Runtime in a more expressive flavour than the java.sql.SQLException that developers are welcome to catch and deal with otherwise don’t deal programatically.
• Notice how we are passing the parameters as an array of Objects and whenever we are expecting results back we are passing our PersonRowMapper.

All beans are defined in the resources/spring/beans/beans.xml which in our case is the PersonDaoImpl having explicitly injecting the dataSource that is needed for all the CRUD operations. The dataSource interface includes two getConnection() methods responsible to setup the DB connection.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">
 
   <bean id="personDao" class="com.dimitrisli.springMySQL.dao.PersonDaoImpl" >
   		<property name="dataSource" ref="dataSource"></property>
   </bean>
 
</beans>

The central point for the Spring configs which gathers up all the Spring resources is resources/spring/config/beanLocations.xml that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
 
 	<import resource="../beans/beans.xml" />
	<import resource="../database/datasource.xml" />
 
</beans>

The code can be found in this Github repository.

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.