Sans Pareil Technologies, Inc.

Key To Your Business

Lab 6: JDBC


We will model a very simple database that represents sites and articles related to sites. We will use JDBC to persist the object model and develop a test suite that validates our implementation.
Database Design 
We will use the Database tool built into IntelliJ to quickly create a couple of tables, and exercise some SQL statements for interacting with those tables.

Create a new gradle based Java project named lab6. The only modification we will make to the gradle file is to add a dependency on the H2 database library.

compile 'com.h2database:h2:1.4.196'

We will create two tables - site and article and add some columns to represent the data we wish to store in those tables. We will look at adding some indices on the tables, as well as modifying the table structure and adding relationships and constraints between the tables.
Object Model 
We will develop an object model that maps to the two tables we created in the database. We will also model the relationship between the tables using object references (ORM).

Create a new package mis283.model and add a new interface named EntityWithId under it. This will impose a very basic structure on our model objects.
package mis283.model;

import java.io.Serializable;

public interface EntityWithId extends Serializable {
    int getId();
    void setId( final int id );
}
Next we will create a Site class that represents the structure of the site table in the database.
package mis283.model;

import java.net.URL;

public class Site implements EntityWithId {
    private int id = 0;
    private String name;
    private URL url;

    @Override
    public int getId() {
        return id;
    }

    @Override
    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public URL getUrl() {
        return url;
    }

    public void setUrl(URL url) {
        this.url = url;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Site site = (Site) o;

        if (id != site.id) return false;
        if (!name.equals(site.name)) return false;
        return url.equals(site.url);
    }

    @Override
    public int hashCode() {
        int result = id;
        result = 31 * result + name.hashCode();
        result = 31 * result + url.hashCode();
        return result;
    }
}
Next create an Article class that represents the article table.
package mis283.model;

import java.util.Optional;

public class Article implements EntityWithId {
    private int id;
    private String title;
    private String subtitle;
    private String description;
    private Optional<Site> site;

    @Override
    public int getId() {
        return id;
    }

    @Override
    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSubtitle() {
        return subtitle;
    }

    public void setSubtitle(String subtitle) {
        this.subtitle = subtitle;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Optional<Site> getSite() {
        return site;
    }

    public void setSite(Optional<Site> site) {
        this.site = site;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Article article = (Article) o;

        if (id != article.id) return false;
        if (!title.equals(article.title)) return false;
        if (subtitle != null ? !subtitle.equals(article.subtitle) : article.subtitle != null) return false;
        if (description != null ? !description.equals(article.description) : article.description != null) return false;
        return site != null ? site.equals(article.site) : article.site == null;
    }

    @Override
    public int hashCode() {
        int result = id;
        result = 31 * result + title.hashCode();
        result = 31 * result + (subtitle != null ? subtitle.hashCode() : 0);
        result = 31 * result + (description != null ? description.hashCode() : 0);
        result = 31 * result + (site != null ? site.hashCode() : 0);
        return result;
    }
}
Repository 
We will create a couple of repository classes that will abstract the database interactions. Create a package mis283.repository and create a Datastore class in it. This will serve as a factory for making connections to the database.
package mis283.repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import static java.lang.String.format;
import static java.util.logging.Level.SEVERE;
import static java.util.logging.Logger.getAnonymousLogger;

public class Datastore {
    private static final Datastore singleton = new Datastore();
    public static Datastore getDatastore() { return singleton; }

    Connection getConnection() {
        return getConnection( format( "%s/lab6", System.getProperty( "java.io.tmpdir" ) ) );
    }

    Connection getConnection( final String filePath ) {
        //getAnonymousLogger().info( format( "Opening database at path: %s", filePath ) );
        try {
            return DriverManager.getConnection( format( "jdbc:h2:%s", filePath ), "sa", "" );
        } catch ( final SQLException e ) {
            getAnonymousLogger().log( SEVERE, format( "Error connecting to database at path: %s", filePath ), e );
            throw new RuntimeException( e );
        }
    }
}
Create a BaseRepository class that will provide some common logic in our concrete repository classes.
package mis283.repository;

import mis283.model.EntityWithId;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Optional;

import static java.lang.String.format;
import static mis283.repository.Datastore.getDatastore;

abstract class BaseRepository<T extends EntityWithId> {

    Collection<T> retrieveAll( final String query ) {
        final Collection<T> results = new ArrayList<>();

        try ( final Connection connection = getDatastore().getConnection();
             final Statement statement = connection.createStatement() )
        {
            final ResultSet resultSet = statement.executeQuery( query );
            while ( resultSet.next() ) results.add( fromResultSet( resultSet ) );
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }

        return results;
    }

    Optional<T> retrieve( final int id, final String table ) {
        if ( id <= 0 ) return Optional.empty();

        final String query = format( "select * from %s where id = ?", table );

        try ( final Connection connection = getDatastore().getConnection();
             final PreparedStatement statement = connection.prepareStatement( query ) ) {
            statement.setInt( 1, id );
            final ResultSet resultSet = statement.executeQuery();
            if ( resultSet.next() )
            {
                return Optional.of( fromResultSet( resultSet ) );
            }
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }

        return Optional.empty();
    }

    int insert( final T entity, final String query ) {
        try ( final Connection connection = getDatastore().getConnection();
              final PreparedStatement statement = connection.prepareStatement( query ) ) {
            setParameters( entity, statement );
            final int count = statement.executeUpdate();
            final ResultSet resultSet = statement.getGeneratedKeys();
            if ( resultSet.next() ) entity.setId( resultSet.getInt( 1 ) );
            return count;
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }
    }

    int delete( final int id, final String query ) {
        try ( final Connection connection = getDatastore().getConnection();
              final PreparedStatement statement = connection.prepareStatement( query ) ) {
            statement.setInt( 1, id );
            return statement.executeUpdate();
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }
    }

    abstract T fromResultSet( final ResultSet resultSet ) throws Throwable;
    abstract  void setParameters( final T entity, final PreparedStatement statement ) throws Throwable;
}
Create a SiteRepository class that will handle all the database interactions with the site table.
package mis283.repository;

import mis283.model.Site;

import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.Optional;

import static mis283.repository.Datastore.getDatastore;

public class SiteRepository extends BaseRepository<Site> {
    private static final SiteRepository singleton = new SiteRepository();
    public static SiteRepository getSiteRepository() { return singleton; }

    public Site save( final Site site ) {
        if ( site.getId() == 0 ) insert( site );
        else update( site );
        return site;
    }

    public Collection<Site> retrieveAll() {
        final String query = "select * from site order by name";
        return retrieveAll( query );
    }

    public Optional<Site> retrieve(final int id ) {
        return retrieve( id, "site" );
    }

    public int delete( final Site site ) {
        final int count = delete( site.getId() );
        if ( count == 1 ) site.setId( 0 );
        return count;
    }

    public int delete( final int id ) {
        final String query = "delete from site where id = ?";
        return delete( id, query );
    }

    @Override
    Site fromResultSet( final ResultSet resultSet ) throws Throwable {
        final Site site = new Site();
        site.setId( resultSet.getInt( "id" ) );
        site.setName( resultSet.getString( "name" ) );
        site.setUrl( new URL( resultSet.getString( "url" ) ) );
        return site;
    }

    @Override
    void setParameters( final Site site, final PreparedStatement statement ) throws Throwable {
        statement.setString( 1, site.getName() );
        statement.setString( 2, site.getUrl().toString() );
    }

    private int insert( final Site site ) {
        final String query = "insert into site (name, url) values (?, ?)";
        return insert( site, query );
    }

    private int update( final Site site ) {
        final String query = "update site set name = ?, url = ? where id = ?";

        try ( final Connection connection = getDatastore().getConnection();
              final PreparedStatement statement = connection.prepareStatement( query ) ) {
            setParameters( site, statement );
            statement.setInt( 3, site.getId() );
            return statement.executeUpdate();
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }
    }
}
Create an ArticleRepository class that will handle all interactions with the article table.
package mis283.repository;

import mis283.model.Article;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.Collection;
import java.util.Optional;

import static mis283.repository.Datastore.getDatastore;
import static mis283.repository.SiteRepository.getSiteRepository;

public class ArticleRepository extends BaseRepository<Article> {
    private static final ArticleRepository singleton = new ArticleRepository();
    public static ArticleRepository getArticleRepository() { return singleton; }

    public Article save(final Article article ) {
        if ( article.getId() == 0 ) insert( article );
        else update( article );
        return article;
    }

    public Collection<Article> retrieveAll() {
        final String query = "select * from article order by title";
        return retrieveAll( query );
    }

    public Optional<Article> retrieve(final int id ) {
        return retrieve( id, "article" );
    }

    public int delete( final Article article ) {
        final int count = delete( article.getId() );
        if ( count == 1 ) article.setId( 0 );
        return count;
    }

    public int delete( final int id ) {
        final String query = "delete from article where id = ?";
        return delete( id, query );
    }

    @Override
    Article fromResultSet( final ResultSet resultSet ) throws Throwable {
        final Article article = new Article();
        article.setId( resultSet.getInt( "id" ) );
        article.setTitle( resultSet.getString( "title" ) );
        article.setSubtitle( resultSet.getString( "subtitle" ) );
        article.setDescription( resultSet.getString( "description" ) );
        final int siteId = resultSet.getInt( "site_id" );
        if ( siteId > 0 ) article.setSite( getSiteRepository().retrieve( siteId ));
        return article;
    }

    @Override
    void setParameters( final Article article, final PreparedStatement statement ) throws Throwable {
        statement.setString( 1, article.getTitle() );
        statement.setString( 2, article.getSubtitle() );
        statement.setString( 3, article.getDescription() );

        if ( article.getSite().isPresent() ) {
            statement.setInt( 4, article.getSite().get().getId() );
        } else {
            statement.setNull( 4, Types.INTEGER );
        }
    }

    private int insert( final Article article ) {
        final String query = "insert into article (title, subtitle, description, site_id) values (?, ?, ?, ?)";
        return insert( article, query );
    }

    private int update( final Article article ) {
        final String query = "update article set title = ?, subtitle = ?, description = ?, site_id = ? where id = ?";

        try (final Connection connection = getDatastore().getConnection();
             final PreparedStatement statement = connection.prepareStatement( query ) ) {
            setParameters( article, statement );
            statement.setInt( 5, article.getId() );
            return statement.executeUpdate();
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }
    }
}
Tests 
We will now write a couple of test classes that will validate our implementation. Create a mis283.repository package under the test source directory tree.

Create a SiteRepositoryTest class that will test the basic features provided by the SiteRepository class.
package mis283.repository;

import mis283.model.Site;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import java.net.URL;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Collection;
import java.util.Optional;

import static java.lang.String.format;
import static mis283.repository.Datastore.getDatastore;
import static mis283.repository.SiteRepository.getSiteRepository;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

public class SiteRepositoryTest {
    private static final String name = "Unit Test Site";
    private static final URL url;
    private static int id = 0;

    static {
        URL temp = null;

        try {
            temp = new URL( "http://test.com/" );
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }

        url = temp;
    }

    @BeforeClass
    public static void create() {
        createTable();

        final Site site = new Site();
        site.setName( name );
        site.setUrl( url );

        final Site result = getSiteRepository().save( site );
        assertTrue( result.getId() > 0 );
        id = result.getId();
    }

    @Test
    public void retrieveAll() {
        final Collection<Site> results = getSiteRepository().retrieveAll();
        assertFalse( results.isEmpty() );

        boolean found = false;
        final Site test = defaultSite();

        for ( final Site site : results ) {
            if ( test.equals( site ) )
            {
                found = true;
                break;
            }
        }

        assertTrue( found );
    }

    @Test
    public void retrieve() {
        final Optional<Site> optional = getSiteRepository().retrieve( id );
        assertTrue( optional.isPresent() );
        assertEquals( defaultSite(), optional.get() );
    }

    @Test
    public void save() {
        final Site site = defaultSite();
        final String modified = format( "%s modified", name );
        site.setName( modified );

        getSiteRepository().save( site );
        Optional<Site> optional = getSiteRepository().retrieve( id );
        assertTrue( optional.isPresent() );
        assertEquals( modified, optional.get().getName() );

        site.setName( name );
        getSiteRepository().save( site );
        optional = getSiteRepository().retrieve( id );
        assertTrue( optional.isPresent() );
        assertEquals( name, optional.get().getName() );
    }

    @AfterClass
    public  static void delete() {
        final Site site = defaultSite();
        final int count = getSiteRepository().delete( site );
        assertEquals( 1, count );
        assertEquals( 0, site.getId() );
    }

    static Site defaultSite() {
        final Site site = new Site();
        site.setId( id );
        site.setName( name );
        site.setUrl( url );
        return site;
    }

    private static void createTable() {
        final String table = "create table if not exists site ( id int primary key auto_increment, name varchar2(255) not null, url varchar2(512) not null )";
        final String index = "create unique index if not exists unq_site_name on site (name)";

        try ( final Connection connection = getDatastore().getConnection();
              final Statement statement = connection.createStatement() ) {
            statement.execute( table );
            statement.execute( index );
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }
    }
}
Create a ArticleRepositoryTest class for testing the ArticleRepository implementation. Notice that we make use of methods from the SiteRepositoryTest to use the Site entity that we relate to the Article entity.
package mis283.repository;

import mis283.model.Article;
import mis283.model.Site;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Statement;
import java.util.Collection;
import java.util.Optional;

import static java.lang.String.format;
import static mis283.repository.ArticleRepository.getArticleRepository;
import static mis283.repository.Datastore.getDatastore;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

public class ArticleRepositoryTest {
    private static final String title = "Unit Test Title";
    private static final String subtitle = "Unit Test Sub Title";
    private static final String description = "Unit Test Description for article.";
    private static int id = 0;
    private static Site site;

    @BeforeClass
    public static void create() {
        createTable();
        SiteRepositoryTest.create();
        site = SiteRepositoryTest.defaultSite();

        final Article article = new Article();
        article.setTitle( title );
        article.setSubtitle( subtitle );
        article.setDescription( description );
        article.setSite( Optional.of( site ) );

        final Article result = getArticleRepository().save( article );
        assertTrue( result.getId() > 0 );
        id = result.getId();
    }

    @Test
    public void retrieveAll() {
        final Collection<Article> results = getArticleRepository().retrieveAll();
        assertFalse( results.isEmpty() );

        boolean found = false;
        final Article test = defaultArticle();

        for ( final Article article : results ) {
            if ( test.equals( article ) )
            {
                found = true;
                break;
            }
        }

        assertTrue( found );
    }

    @Test
    public void retrieve() {
        final Optional<Article> optional = getArticleRepository().retrieve( id );
        assertTrue( optional.isPresent() );
        assertEquals( defaultArticle(), optional.get() );
        assertEquals( site, optional.get().getSite().get() );
    }

    @Test
    public void save() {
        final Article article = defaultArticle();
        final String modified = format( "%s modified", subtitle );
        article.setSubtitle( modified );

        getArticleRepository().save( article );
        Optional<Article> optional = getArticleRepository().retrieve( id );
        assertTrue( optional.isPresent() );
        assertEquals( modified, optional.get().getSubtitle() );

        article.setSubtitle( subtitle );
        getArticleRepository().save( article );
        optional = getArticleRepository().retrieve( id );
        assertTrue( optional.isPresent() );
        assertEquals( subtitle, optional.get().getSubtitle() );
    }

    @AfterClass
    public  static void delete() {
        final Article article = defaultArticle();
        final int count = getArticleRepository().delete( article );
        assertEquals( 1, count );
        assertEquals( 0, article.getId() );

        SiteRepositoryTest.delete();
    }

    private static Article defaultArticle() {
        final Article article = new Article();
        article.setId( id );
        article.setTitle( title );
        article.setSubtitle( subtitle );
        article.setDescription( description );
        article.setSite( Optional.of( site ) );
        return article;
    }

    private static void createTable() {
        final String table = "create table if not exists article ( id int primary key auto_increment, title varchar2(255) not null, subtitle varchar2(512), description text, site_id int )";
        final String index1 = "create index if not exists idx_article_title on article (title)";
        final String fk = "alter table if exists article add foreign key (site_id) references site (id) on delete cascade";
        final String index2 = "create index if not exists idx_article_site on article (site_id)";

        try ( final Connection connection = getDatastore().getConnection();
             final Statement statement = connection.createStatement() ) {
            statement.execute( table );
            statement.execute( index1 );
            statement.execute( fk );
            statement.execute( index2 );
        } catch ( final Throwable t ) {
            throw new RuntimeException( t );
        }
    }
}