Lazy SQL

What

A java Annotation processor which generates Code for you to perfom JDBC Database queries. Your Queries will be checked by your database at compile time and the results are mapped to typesafe java types.

Why

Our main use case is developing against a third party database, where you do not have control over the schema and which may change in the future during updates.

With LazySQL you just need to recompile your app in order to make sure everyithing in the database still works as expected (no tables/fields changed or removed that you use).

Goals

• Catch as much SQL problems as possible during COMPILE time (Syntax errors, missing tables/flieds)
• Let the developer use all bells and whistles of SQL (no meta-language or superset)

Examples

class MyClass {

    //You need to provide the connection to each method call.
    //This way you can get/open/close connections when it's appropriate
    private Connection con;
    //The db is an object of the database class generated by LazySQL
    private MyClassLazyDb db;

    @LazySQLSelect("SELECT * FROM user Where login = :login" )
    public FindUserResult findUser(String login) throws SQLException
    {
        return db.findUser(con, cLogin);
    }
}

During compilation the following class is generated:

public class MyClassLazyDb { //the class name is your class + LazyDb

  //method has the same name and params as the annotated method
  public List<FindUserResult> findUser(Connection con, String login) throws SQLException {
     //the params in the query are replaced by ?
    try (
        PreparedStatement stmtObj = db.prepareStatement(
            "SELECT * from user where login = ? "
        );
    )
    {
        //and all params are applied in correct order
        stmtObj.setString(1, login);
        ResultSet resObj = stmtObj.executeQuery();
        //The result class is generated WITH NAME/TYPE INFORMATION FROM THE DATABASE
        List<FindUserResult> result = new LinkedList<>();
        while (resObj.next()) {
          FindUserResult r = new FindUserResult(); 
          r.setKBenutzer(resObj.getInt("kBenutzer"));
          if (resObj.wasNull()) { r.setKBenutzer(null); };
          r.setCLogin(resObj.getString("cLogin"));
          r.setCPasswort(resObj.getString("cPasswort"));
          r.setCName(resObj.getString("cName"));
          r.setKFirma(resObj.getInt("kFirma"));
          if (resObj.wasNull()) { r.setKFirma(null); };
          r.setCEMail(resObj.getString("cEMail"));
          result.add(r);
        }
        return result;
    }
  }

  //The database returns what fields the SQL query returns and from that information
  //this type is generated
  public static class FindUserResult {
    Integer kBenutzer;
    String cLogin;
    String cPassword;
    String cName;
    Integer kFirma;
    String cEMail;
    //...getters and setters are omittet..
  }
}

Installation

You just have to include the follwing in your pom.xml

<dependency>
    <groupId>de.thomas-oster</groupId>
    <artifactId>lazysql</artifactId>
    <version>1.14</version>
</dependency>

(use the latest version )

If you use Java 9 or higher you have to include this as well, because some Annotations have been removed from the JDK:

<dependency>
    <groupId>javax.annotation</groupId>
    <artifactId>javax.annotation-api</artifactId>
    <version>1.3.2</version>
</dependency>

Configuration

The parameters for the database can be stored in many ways. If no configuration is present, LazySQL will still compile everything but cannot check anything for you and does not auto-complete. See FAQ

Environment Variables

lazysql.yml in resources

This is the recommended way of configuration. Just put a lazysql.yml file in youre resoures path or project root. Example:

dburl: jdbc:sqlserver://example.com:1434;databaseName=test
dbuser: sa
dbpassword: verysecure
# this speeds up compilation, because every query is cached. Don't forget
# to delete the cache file if your database changed
# if you specify a relative path, it's relative to your project root
cachefile: /tmp/lazysqlcache.json

Spring Boot

If you are using Spring boot and use an application.yml in your resource path (and no lazysql.yml) this file is parsed. You have to create a lazysql block for configuration, but if a spring.datasource is present, this will be used for db configuration (if not overridden in the lazysql block). However this does not take the current spring profile into account whatsoever, because remember we work at compile time, not runtime. If a spring boot file is detected, dependencyInjection and addJsonAnnotations default to true.

spring.datasource:
  driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
  url: jdbc:sqlserver://example.com:1337;databaseName=mydb
  username: sa
  password: insecure

lazysql:
  cachefile: lazysqlcache.json
  dependencyInjection: true
  addJsonAnnotations: false

@LazySQLConfig annotation

You can also configure LazySQL on class level. The @LazySQLConfig is only used for that class.

package com.example;

@LazySQLConfig(
    dburl = "jdbc:mysql://blabla",
    dbuser = "john",
    dbpassword = "stuff"
)
public class MyService {

}

However this is not recommended, because you would store your DB credentials in the source code and you would need to add an annotation to every class using LazySQL stuff

AutoComplete and Compile on Save

At least with NetBeans IDE LazySQL works really good with Compile on Save and Autocomplete.

Annotations

The following Annotations are available

@LazySQLSelect

Used for Select queries and generated the return type

@LazySQLUpdate

Used for Update queries. Returns the number of changed rows

@LazySQLExec

Used for multiple/batch queries. Does not return anything

@LazySQLInsert

Creates an insert for the given table (No need to write SQL!). By default an Object representing a to-insert row is created and used as parameter. If you want to give the parameters directly, use useObjectAsInput=false

@LazySQLStoredProcedure

Creates a method for calling a stored procedure

@LazySQLUpsert

Creates a method for performing an update. If the update did not affect any rows, an insert is performed instead

FAQ

Is a database connection needed during compile time? What about CI/CD?

LazySQL can work without a database connection. However than it cannot check any types and you have to provide the type information through returns and params values. The Idea is to have an active database connection during development so all your params and returns are checked against the database while you code and can be auto-completed. Then the compilation can run without a database. You can use Environment variables to override your settings in a lazysql.yml during your CI build in order to skip trying to connect to the database. e.g.

LAZYSQL_DB=null mvn package