In this post, we will look into how to retrieve auto-generated keys in JDBC. We will also explore usage of PreparedStatementCreator and PreparedStatementCallback in JdbcTemplate.

There are cases when you rely on Database server to auto generate values for some columns of the table. E.g. auto increment primary key, creation_date or any other column while inserting records. There is a way with which you can retrieve those auto-generated keys when you execute the insert statement. Let's see how you can do this using Spring JDBC but first we will see what PreparedStatementCreator and PreparedStatementCallback interfaces are.

What is PreparedStatementCreator?

There are cases when you want to create PreparedStatement yourself. One use case is to return auto generated keys. In that case, Spring JDBC provides you an option to do so by providing implementation for PreparedStatementCreator. Let's create an implementation of PreparedStatementCreator which sets those options.

public class ReturnGeneratedKeysPreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
  private final String sql;
  private String[] generatedColumnNames;

  public ReturnGeneratedKeysPreparedStatementCreator(String sql) {
    this(sql, Collections.emptyList());
  }

  public ReturnGeneratedKeysPreparedStatementCreator(String sql, List generatedColumnNames) {
    this.sql = sql;
    this.generatedColumnNames = Objects.nonNull(generatedColumnNames)
    ? generatedColumnNames.toArray(new String[generatedColumnNames.size()])
    : new String[0];
  }

  @Override
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    return generatedColumnNames.length > 0 ? con.prepareStatement(this.sql, this.generatedColumnNames)
    : con.prepareStatement(this.sql, Statement.RETURN_GENERATED_KEYS);
  }

  @Override
  public String getSql() {
    return this.sql;
  }

}

There are two options which you can use to retrieve generated keys. One is to get all the generated keys and other is to pass the columsn you want to retrieve.

What is PreparedStatementCallback?

In normal usage, you might never need to implement this interface but will provide implementation only if you need to execute some other code e.g. retrieval of auto-generated keys. Let's see how you can do this with implementing this interface.

class GeneratedKeysPreparedStatementCallback implements PreparedStatementCallback {

  @Override
  public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
    int updated = ps.executeUpdate();
    if (updated > 0) {
      try (ResultSet rs = ps.getGeneratedKeys()) {
        if (rs.next())
          return rs.getInt("id");
      }
   
      throw new DataRetrievalFailureException("There was no key auto generated by the database");
    }
    throw new DataRetrievalFailureException("Nothing was updated");
  }
}

Usage

Integer key = jdbcTemplate.execute(new ReturnGeneratedKeysPreparedStatementCreator(
    "insert into product(name, category, description) values('Acer Laptop', 'laptop', 'Predator series')"),
    new GeneratedKeysPreparedStatementCallback());
log.info(() -> String.format("Product saved in database with key: %d", key));

That's it. You can find the complete code on Github.

There are many different variants of #batchUpdate methods available in JdbcTemplate. We will specifically look into those who uses BatchPreparedStatementSetter and ParameterizedPreparedStatementSetter.

What is BatchPreparedStatementSetter?

It is an interface used by JdbcTemplate to execute batch updates. This has methods to determine the batch size and method to set parameters in the PreparedStatement. Using this, JdbcTemplate will run only execute single batch based on the batch size returned by implementation this interface.

How to use BatchPreparedStatementSetter?

Let's create a ProductBatchPreparedStatementSetter which can set parameters in the statement.

public class ProductBatchPreparedStatementSetter implements BatchPreparedStatementSetter {

  private final List products;

  public ProductBatchPreparedStatementSetter(List products) {
    Objects.requireNonNull(products);
    
    // Ideally you should do a defensive copy of this list.
    // this.products = new ArrayList<>(products);
    this.products = products;
  }

  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException {
    Product product = products.get(i);
    ps.setString(1, product.getName());
    ps.setString(2, product.getCategory());
    ps.setString(3, product.getDescription());
  }

  @Override
  public int getBatchSize() {
    return products.size();
  }

}

Usage

int[] results = jdbcTemplate.batchUpdate("insert into product (name, category, description) values(?,?,?)", 
    new ProductBatchPreparedStatementSetter(Arrays.asList(new Product("Lenovo Laptop", "laptop", "Thinkpad series laptop"),
 new Product("Acer Laptop", "laptop", "Predator series laptop"))));
log.info(() -> String.format("Inserted rows: %s", Arrays.toString(results)));

What is ParameterizedPreparedStatementSetter?

It is an interface used by JdbcTemplate to execute batch updates. It has only one method which takes PreparedStatement and Typed object as parameters. Using this, JdbcTemplate can execute multiple batch based on the batch size passed in the #batchUpdate method.

How to use ParameterizedPreparedStatementSetter?

Let's create a pretty straightforward implementation of this interface for our Product example.

ParameterizedPreparedStatementSetter<Product> pss = (ps, product) -> {
    ps.setString(1, product.getName());
    ps.setString(2, product.getCategory());
    ps.setString(3, product.getDescription());
  };

Usage

int batchSize = 5;
int[][] result = jdbcTemplate.batchUpdate("insert into product (name, category, description) values(?,?,?)",
    products, batchSize, pss);
log.info(Arrays.deepToString(result));

#batchUpdate method which uses BatchPreparedStatementSetter returns 1-D int array whereas #batchUpdate method which uses ParameterizedPreparedStatementSetter returns 2-D array. This means that BatchPreparedStatementSetter executed single batch whereas ParameterizedPreparedStatementSetter executed multiple batches.

That's it. You can find the complete code of this example on Github.

What is PreparedStatementSetter?

It is a callback interface used by JdbcTemplate after PreparedStatement is created to set the values in the statement object.

How to use it?

PreparedStatementSetter is also functional interface, so we will use lambda expression in this example to demonstrate PreparedStatementSetter's usage. We will use it in #update method of JdbcTemplate.

int updateCount = jdbcTemplate.update("insert into product(name, category, description) values(?,?,?)", ps -> {
    ps.setString(1, "Lenovo Bag");
    ps.setString(2, "bag");
    ps.setString(3, "Handcrafted bags by Lenovo");
  });
  
  log.info(() -> String.format("Product inserted: %d", updateCount));

You can get the full code of this example from here.

What is ResultSetExtractor?

It is an interface used by #query methods of JdbcTemplate. It is better suitable if you want to map one result object per ResultSet otherwise RowMapper is simpler choice to map one row of ResultSet with one object.

How to use it?

Let's first create a ResultSetExtractor which maps all the rows of ResultSet to single object. For this we will create a ProductResultSetExtractor which returns ProductResponse.

public class ProductResultSetExtractor implements ResultSetExtractor {
  private final RowMapper productRowMapper;
 
  public ProductResultSetExtractor(RowMapper productRowMapper) {
    super();
    this.productRowMapper = productRowMapper;
  }

  @Override
  public ProductResponse extractData(ResultSet rs) throws SQLException {
    final List products = new ArrayList<>();

    int rowNum = 0;
    while(rs.next()) {
      products.add(productRowMapper.mapRow(rs, rowNum));
      rowNum++;
    }

    return ProductResponse.of(products);
  }
}

Now, we will use #query method of JdbcTemplate to use this ProductResultSetExtractor to return result.

ProductResponse productResponse = jdbcTemplate.query("select * from product", new ProductResultSetExtractor(new ProductRowMapper()));

log.info(productResponse::toString);

That's it. You can find the full example code on github.

In this post, we will discuss what RowMapper is and how to use it when writing Jdbc code using Spring JDBC module.

What is RowMapper?

It is an interface of Spring JDBC module which is used by JdbcTemplate to map rows of java.sql.ResultSet. It is typically used when you query data.

Example usage of RowMapper

Let's first create a RowMapper which can map products.

class ProductRowMapper implements RowMapper {

  @Override
    public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
      Product product = new Product();
      product.setId(rs.getInt("id"));
      product.setName(rs.getString("name"));
      product.setDescription(rs.getString("description"));
      product.setCategory(rs.getString("category"));
      return product;
   }
 
}

Now, we will use this ProductRowMapper in #queryForObject of JdbcTemplate.

Product product = jdbcTemplate.queryForObject("select * from product where id=1", new ProductRowMapper());
log.info(product::toString);

You can find the github code here.

What is Spring JdbcTemplate?

JdbcTemplate is the core class of Spring JDBC. It simplifies your interaction with low-level error prone details of JDBC access. You only pass the SQL statement to execute, parameters and processing logic for the returned data and rest is handled by it i.e. Opening Connection, transaction handling, error handling and closing Connection, Statement and Resultset.

How to create object of JdbcTemplate?

1. Calling no args constructor.

JdbcTemplate jdbcTemplate = new JdbcTemplate();

// You need to set datasource in later point in time and also have to call afterPropertiesSet.
jdbcTemplate.setDataSource(DataSource ds);
jdbcTemplate.afterPropertiesSet();

2. By Calling constructor with datasource.

JdbcTemplate jdbcTemplate = new JdbcTemplate(Datasource ds);

3. By Calling constructor with datasource and lazyInit parameter.

JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSource dataSource, boolean lazyInit);

Querying with JdbcTemplate

There are many variants of querying using JdbcTemplate. We will look into queryForObject and queryForList method.

JdbcTemplate.queryForObject(String sql, Class<T> requiredType)

We will use this variant of #queryForObject when the ResultSet returns only single column.

Integer count = jdbcTemplate.queryForObject("select count(*) from product", Integer.class);
log.info(() -> String.format("There are total %d products", count));

JdbcTemplate.queryForObject(String sql, Class requiredType, @Nullable Object... args)

We will use this variant when we need to pass sql binding parameters.

Integer mobileProducts = jdbcTemplate.queryForObject("select count(*) from product where category=?", Integer.class, "mobile");
log.info(() -> String.format("There are total %d mobile products", mobileProducts));

JdbcTemplate.queryForList(String sql, Class elementType)

This variant is useful when query return list of values but for single column.

// E.g. getting list of product names
List mobileNames = jdbcTemplate.queryForList("select name from product where category='mobile'", String.class);
log.info(() -> String.format("Name of mobiles: %s", mobileNames.toString()));

You can get the full example code here.

In the previous post, we have setup ELK stack and ran data analytics on application events and logs. In this post, we will discuss how you can watch real-time application events that are being persisted in the Elasticsearch index and raise alerts if condition for watcher is breached using SentiNL (Kibana plugin).

Few examples of alerting for application events (see previous posts) are:

  • Same user logged in from different IP addresses.
  • Different users logged in from same IP address.
  • PermissionFailures in last 15 minutes.
  • Particular kind of exception in last 15 minutes/ hour/ day.

Watching and alerting on Elasticsearch index in Kibana

There are many plugins available for watching and alerting on Elasticsearch index in Kibana e.g. X-Pack, SentiNL.

X-Pack is a paid extension provided by elastic.co which provides security, alerting, monitoring, reporting and graph capabilities.

SentiNL is free extension provided by siren.io which provides alerting and reporting functionality to monitor, notify and report changes in elasticsearch index using standard queries, programmable validators and configurable actions.

We will be using SentiNL for watching and alerting on Elasticsearch index.

Installing SentiNL

Prerequisite

For debian, we need libfontconfig and libfreetype6 libraries, if not installed already.

sudo apt-get install libfontconfig libfreetype6

For centos, we need fontconfig and freetype libraries, if not installed already.

sudo yum install fontconfig freetype

// Installing SentiNL plugin
/opt/kibana/bin/kibana-plugin --install sentinl -u https://github.com/sirensolutions/sentinl/releases/download/tag-4.6.4-4/sentinl.zip

Configuring SentiNL

SentiNL have wide range of actions that you can configure for watchers. You can send an email, integrate with Slack channel or pushapps, send payload to custom webhook. Open kibana.yml file and add below properties for SentiNL. For our example, we will only enable notification through email.

sentinl:
  es:
    host: 'localhost'
    port: 9200
  settings:
    email:
      active: true
      host: "smtp.gmail.com"
      user: "[EMAIL_ID]"
      password: "[PASSWORD]"
      port: 465
      domain: "gmail.com"
      ssl: true
      tls: false
      authentication: ['PLAIN', 'LOGIN', 'CRAM-MD5', 'XOAUTH2']
      timeout: 20000  # mail server connection timeout
      # cert:
      #   key: '/full/sys/path/to/key/file'
      #   cert: '/full/sys/path/to/cert/file'
      #   ca: '/full/sys/path/to/ca/file'
    slack:
      active: false
      username: 'username'
      hook: 'https://hooks.slack.com/services/'
      channel: '#channel'
    webhook:
      active: false
      host: 'localhost'
      port: 9200
      # use_https: false
      # path: ':/{{payload.watcher_id}}'
      # body: '{{payload.watcher_id}}{payload.hits.total}}'
      # method: POST
    report:
      active: false
      executable_path: '/usr/bin/chromium' # path to Chrome v59+ or Chromium v59+
      timeout: 5000
      # authentication:
      #   enabled: true
      #   mode:
      #     searchguard: false
      #     xpack: false
      #     basic: false
      #     custom: true
      #   custom:
      #     username_input_selector: '#username'
      #     password_input_selector: '#password'
      #     login_btn_selector: '#login-btn'
      # file:
      #   pdf:
      #     format: 'A4'
      #     landscape: true
      #   screenshot:
      #     width: 1280
      #     height: 900
    pushapps:
      active: false
      api_key: ''
That's it!!! Let's start Kibana to configure watcher and alerting in SentiNL.

Creating Watchers and alerting in Kibana

We will be configuring watcher for different users logged in from same IP address and will send e-Mail alerts.

  • Open Kibana dashboard on your local machine (Url for Kibana on my local machine is http://localhost:5601).
  • Click on SentiNL option in the left nav-pane. You will see a dashboard as below. Click on the New option to create a new watcher.
  • Click on the Watcher link highlighted as below.
  • Enter watcher name and schedule in the General tab.
  • Click on Input tab and enter below mentioned query json in the body. You can also give a name to the query and save.
    {
      "search": {
        "request": {
          "index": [
            "app-events*"
          ],
          "body": {
            "query": {
              "bool": {
                "filter": [
                  {
                    "range": {
                      "@timestamp": {
                        "gte": "now-30m"
                      }
                    }
                  },
                  {
                    "query_string": {
                      "default_field": "appEvent.eventType",
                      "query": "LOGIN_SUCCESS OR LOGIN_FAILURE"
                    }
                  }
                ]
              }
            },
            "aggs": {
              "group_by_requestIP": {
                "terms": {
                  "field": "appEvent.requestIP.keyword",
                  "size": 5
                },
                "aggs": {
                  "group_by_identifier": {
                    "terms": {
                      "field": "appEvent.identifier.keyword",
                      "size": 5
                    },
                    "aggs": {
                      "get_latest": {
                        "terms": {
                          "field": "@timestamp",
                          "size": 1,
                          "order": {
                            "_key": "desc"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
    
  • Click on Condition tab and enter below mentioned condition json in the body. You can also give a name to this condition and save.
    {
      "script": {
        "script": "var requestIPbuckets = payload.aggregations.group_by_requestIP.buckets; payload.collector = []; requestIPbuckets.filter(function(requestIP) { return requestIP.key; }).forEach(function(requestIP) { var requestIPKey = requestIP.key; var users = requestIP.group_by_identifier.buckets; if (users.length > 1) { users.filter(function(user) { return user.key; }).forEach(function(user) { payload.collector.push({ 'ip': requestIPKey, 'identifier': user.key, 'count': user.doc_count  }); }); }}); payload.collector.length > 0;"
      }
    }
    
  • Click on Action tab and select email as an action for alerting. Give title, to, from, subject and add below mentioned content in the body of email.
    Found {{payload.collector.length}} Events
    {{#payload.collector}}
    {{#.}}
    ip : {{ip}}, identifier: {{identifier}}, count: {{count}}
    {{/.}}
    {{/payload.collector}}
    
  • Save the watcher.

This watcher will run periodically based on the schedule that you have set and if the condition for breach is met, will send an email alert. The configured email looks like below.

This is how you can watch real-time changing data in Elasticsearch index and raise alerts based on the configured conditions.