Spgeed

Overview

Spgeed (pronounce speed) stands for "System to PostGresql Extended Exploitation of Database".

Spgeed is a lightweight JAVA library that gives you the opportunity to use the full potential of PostgreSQL database with the flexibility of an ORM and the efficiency of direct SQL calls.

Why should you use Spgeed ?

As you have probably noticed, PostgreSQL is now offering a lot of of very efficient and advanced features :

  • Security
  • Table inheritance
  • Complex types (JSON, ARRAY, ...)
  • CSV Import/Export
  • Soundex search
  • Native full-text search
  • Relationnal and/or document model support
  • ...

Developping an application is usually easier when relying on an ORM (Object Relational Mapping) System since it avoids to worry about:

  • how the database is deeply structured.
  • what are the specifity of the database we are comunicating with.

In other words, thanks to ORM systems, developpers are now able to stay in the amazing world of objects and stop worrying about where data come from.

Unfortunately, this comes with a price: as you are addressing your database in a generic manner, you are not able to use its specifity anymore. What if you could have the best of both worlds ?

Spgeed is a wrapper around JDBC which gives you access to the full potential of PostgreSQL system with the flexibility of a traditional ORM.

What's wrong with Hibernate ?

Hibernate is a great and very extensive library, especially when it comes to get the support for multiple databases. However, it comes with a few downsides :

  • Performance, who has never used SQL statements instead of HQL statements because you couldn't get decent performances ?
  • Lazy Loading, You need to be very cautious with your model objects. If you're not, lazy loading can quickly result in "n+1 selects" problem or "LazyInitializationException" problem.
  • Views and Stored Procedures, you won't be able to use them.

Hibernate comes with this great support for multiple database systems. However, do you really always need for multiple databases support ?

Spgeed is a proposition of an ORM alternative. Spgeed specifically aims to address performance issues, support for database specifity and easy object mapping.

Get started

To use Spgeed, you need to have a PostgreSQL database. If you don't, install PostgreSQL and create a database.

Warning

You need at least Postgresql version 9.5.

To use Spgeed in your project, you first need to add it to your classpath. If you use maven, add this line to your pom.xml:

<dependency>
    <groupId>org.nuiton</groupId>
    <artifactId>spgeed</artifactId>
    <version>1.0.6</version>
</dependency>

Warning

You need at least Java version 8 and enable the debug parameter in javac.

<plugins>
    <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
            <compilerArgs>
                <arg>-parameters</arg>
            </compilerArgs>
        </configuration>
    </plugin>
</plugins>

Create your first request

With Spgeed, requests are always written in native SQL. To execute a request, you need to create a DAO interface. Each method of that interface should define a sql statement as an annotation. The method parameters can be used in the statement using this simple "${parameter}" expression. The result of the statement will be mapped with the method return type.

public interface JourneyDao {

    @Update(sql = "INSERT INTO journey (name, location) " +
                "VALUES (${name}, ${location})")
    int save(String name, String location);

    @Select(sql = "SELECT * FROM journey")
    Journey[] getJourneyArray();
}

Mapping

As you can see the method parameters are injected into the request with ${}. The result is mapped with the method return type.

The previous interface example can be used in a JAVA application as follows :

DataSource ds = //... your postgresql datasource

try (SqlSession session = new SqlSession(ds)) {
    JourneyDao dao = session.getDao(JourneyDao.class);
    Journey[] journeys = dao.getJourneyArray();
}

If you want to know more about how you can create and retrieve a datasource, please consult the connection section.

How does it work internally ?

Spgeed is relying on PostgreSQL JSON capability. When using Spgeed, requests return in JSON format. From there, either the result can be used "as-is" and returned directly to the clients (if you are developping a REST api for instance) or the result is converted and injected into POJOs thanks to Jackson library.