Insert Items to DynamoDB Tables using Node.js

On a previous article we learned how to create DynamoDB Tables using Node.js.

Next step is to insert items to the DynamoDB Tables previously created.

Keep in mind that for the insert action the most basic step is to specify the the primary key.
For the table users the primary key is the attribute email. You can add as many attributes as you want however the cumulative size should not surpass 400 KB.

var AWS = require("aws-sdk");

	var dynamodb = new AWS.DynamoDB();
	var params = {
			TableName:"Users",
		    Item:{
		    	email : { S:"jon@doe.com"},
		        fullname: { S:"Jon Doe"}
		    }
		};
	
	dynamodb.putItem(params,callback);

DynamoDB also supports Batch writes. In this case the main benefit lies on less I/O, however nothing changes regarding consumed capacity. In our case we will add a batch of login attempts.

var AWS = require("aws-sdk");

var insetBatchLogins = function(callback) {
	
	var dynamodb = new AWS.DynamoDB();
	var batchRequest = {
			RequestItems: {
				"Logins": [
				           {
				        	   PutRequest: { 
				        		   Item: {
				        			   "email": { S: "jon@doe.com" },
				        			   "timestamp": { N: "1467041009976" }
				        			   }
				           }},
				           {
				        	   PutRequest: { 
				        		   Item: {
				        			   "email": { S: "jon@doe.com" },
				        			   "timestamp": { N: "1467041019976" }
				        			   }
				           }}]
		    }
		};

	dynamodb.batchWriteItem(batchRequest,callback);
};

In case of an insert with a global/local secondary index all you have to do is to specify the corresponding attributes for the index. Take into consideration that you can have empty index related attributes or even duplicates.

	var dynamodb = new AWS.DynamoDB();
	
	var params = {
			TableName:"Supervisors",
		    Item:{
		    	name: { S:"Random SuperVisor"},
		    	company: { S:"Random Company"},
		    	factory: { S:"Jon Doe"}
		    }
		};
	
	dynamodb.putItem(params,callback);

You can find the sourcecode on github.

Insert Items to DynamoDB Tables using Java

On a previous article we learned how to create DynamoDB Tables using Java.

Next step is to insert items to the DynamoDB Tables previously created.

Keep in mind that for the insert action the most basic step is to specify the the primary key.
For the table users the primary key is the attribute email. You can add as many attributes as you want however the cumulative size should not surpass 400 KB.

 Map<String,AttributeValue> attributeValues = new HashMap<>();
        attributeValues.put("email",new AttributeValue().withS("jon@doe.com"));
        attributeValues.put("fullname",new AttributeValue().withS("Jon Doe"));

        PutItemRequest putItemRequest = new PutItemRequest()
                .withTableName("Users")
                .withItem(attributeValues);

        PutItemResult putItemResult = amazonDynamoDB.putItem(putItemRequest);

DynamoDB also supports Batch writes. In this case the main benefit lies on less I/O, however nothing changes regarding consumed capacity. In our case we will add a batch of login attempts.

        Map<String,AttributeValue> firstAttributeValues = new HashMap<>();
        firstAttributeValues.put("email",new AttributeValue().withS("jon@doe.com"));

        Long date = new Date().getTime();

        firstAttributeValues.put("timestamp",new AttributeValue().withN(Long.toString(date)));

        PutRequest firstPutRequest = new PutRequest();
        firstPutRequest.setItem(firstAttributeValues);

        WriteRequest firstWriteRequest = new WriteRequest();
        firstWriteRequest.setPutRequest(firstPutRequest);

        Map<String,AttributeValue> secondAttributeValues = new HashMap<>();
        secondAttributeValues.put("email",new AttributeValue().withS("jon@doe.com"));
        secondAttributeValues.put("timestamp",new AttributeValue().withN(Long.toString(date+100)));

        PutRequest secondPutRequest = new PutRequest();
        secondPutRequest.setItem(secondAttributeValues);

        WriteRequest secondWriteRequest = new WriteRequest();
        secondWriteRequest.setPutRequest(secondPutRequest);

        List<WriteRequest> batchList = new ArrayList<WriteRequest>();
        batchList.add(firstWriteRequest);
        batchList.add(secondWriteRequest);

        Map<String, List<WriteRequest>> batchTableRequests = new HashMap<String, List<WriteRequest>>();
        batchTableRequests.put("Logins",batchList);

        BatchWriteItemRequest batchWriteItemRequest = new BatchWriteItemRequest();
        batchWriteItemRequest.setRequestItems(batchTableRequests);

        amazonDynamoDB.batchWriteItem(batchWriteItemRequest);

In case of an insert with a global/local secondary index all you have to do is to specify the corresponding attributes for the index. Take into consideration that you can have empty index related attributes or even duplicates.

        Map<String,AttributeValue> attributeValues = new HashMap<>();
        attributeValues.put("name",new AttributeValue().withS("Random SuperVisor"));
        attributeValues.put("company",new AttributeValue().withS("Random Company"));
        attributeValues.put("factory",new AttributeValue().withS("Jon Doe"));

        PutItemRequest putItemRequest = new PutItemRequest()
                .withTableName("Supervisors")
                .withItem(attributeValues);

        PutItemResult putItemResult = amazonDynamoDB.putItem(putItemRequest);

You can find the sourcecode on github.

I’ve compiled a cheat sheet that lists dynamodb functions in Java
Sign up in the link to receive it.

Create DynamoDB tables with Node.js

On this post we will create Tables on a DynamoDB Database using node.js

Before getting started we need to have local dynamodb installed since we want to avoid any costs for dynamodb usage. There was a previous post on local dynamodb.

In case you use docker you can find a local dynamodb image or you can create one on you own as described here.

Using local DynamoDB and node.js is extremely handy for debugging. Local dynamodb provides as with an web user interface on http://localhost:8000/shell. The local dynamodb shell is a javascript shell, therefore the actions for node.js can be issued straight to the DynamoDB shell.

The actions would be the same as described on the corresponding java tutorial.

First step is to create a table with a hash key. In this case the email of the user would be the hash key.

var createUsers = function(callback) {

	var dynamodb = new AWS.DynamoDB();

	var params = {
	    TableName : "Users",
	    KeySchema: [       
	        { AttributeName: "email", KeyType: "HASH"}
	    ],
	    AttributeDefinitions: [       
	        { AttributeName: "email", AttributeType: "S" }
	    ],
	    ProvisionedThroughput: {       
	        ReadCapacityUnits: 5, 
	        WriteCapacityUnits: 5
		   }
		};

	dynamodb.createTable(params, callback);	
};

The next table will be called Logins. Logins should keep track each time the user logged in. To do so apart from using a hash key we will also use a range key for the date it occurred.

var createLogins = function(callback) {

	var dynamodb = new AWS.DynamoDB();

	var params = {
	    TableName : "Logins",
	    KeySchema: [       
	        { AttributeName: "email", KeyType: "HASH"},
	        { AttributeName: "timestamp", KeyType: "RANGE"}
		],
	    AttributeDefinitions: [       
	        { AttributeName: "email", AttributeType: "S" },
	        { AttributeName: "timestamp", AttributeType: "N" }
	    ],
	    ProvisionedThroughput: {       
	        ReadCapacityUnits: 5, 
	        WriteCapacityUnits: 5
		   }
		};

	dynamodb.createTable(params, callback);	
};

Next table is Supervisors. The hash key of Supervisor would be his name. A supervisor will work for a company. The company will be our global secondary index. Since the companies own more than one factories the field factory would be the range key.

var createSupervisors = function(callback) {

	var dynamodb = new AWS.DynamoDB();

	var params = {
	    TableName : "Supervisors",
	    KeySchema: [       
	        { AttributeName: "name", KeyType: "HASH"}
		],
	    AttributeDefinitions: [       
	        { AttributeName: "name", AttributeType: "S" },
	        { AttributeName: "company", AttributeType: "S" },
	        { AttributeName: "factory", AttributeType: "S" }    
	    ],
	    ProvisionedThroughput: {       
	        ReadCapacityUnits: 5, 
	        WriteCapacityUnits: 5
		   },
		GlobalSecondaryIndexes: [{
				IndexName: "FactoryIndex",
				KeySchema: [
				    {
				    	AttributeName: "company",
				    	KeyType: "HASH"
				    },
					{
						AttributeName: "factory",
						KeyType: "RANGE"
					}
				],
				Projection: {
					ProjectionType: "ALL"
				},
				ProvisionedThroughput: {
					ReadCapacityUnits: 1,
					WriteCapacityUnits: 1
				}
		    }]
	};

	dynamodb.createTable(params, callback);	
};

Next table would be the table Companies. The hash key would be the parent company and the range key the subsidiary company. Each company has a CEO. The CEO would be the range key for the local secondary index.

var createCompanies = function(callback) {

	var dynamodb = new AWS.DynamoDB();

	var params = {
	    TableName : "Companies",
	    KeySchema: [       
	        { AttributeName: "name", KeyType: "HASH"},
	        { AttributeName: "subsidiary", KeyType: "RANGE"}
		],
	    AttributeDefinitions: [       
	        { AttributeName: "name", AttributeType: "S" },
	        { AttributeName: "subsidiary", AttributeType: "S" },
	        { AttributeName: "ceo", AttributeType: "S" }    
	    ],
	    ProvisionedThroughput: {       
	        ReadCapacityUnits: 5, 
	        WriteCapacityUnits: 5
		   },
		LocalSecondaryIndexes: [{
				IndexName: "CeoIndex",
				KeySchema: [
				    {
				    	AttributeName: "name",
				    	KeyType: "HASH"
				    },
					{
						AttributeName: "ceo",
						KeyType: "RANGE"
					}
				],
				Projection: {
					ProjectionType: "ALL"
				}
		    }]
	};

	dynamodb.createTable(params, callback);	
};

You can find the source code on github.

Create DynamoDB tables with Java

On this post we will create Tables on a DynamoDB Database the java way.

Before getting started we need to have local dynamodb installed since we want to avoid any costs for dynamodb usage. There was a previous post on local dynamodb.

In case you use docker you can find a local dynamodb image or you can create one on you own as described here.

The dynamodb java sdk gives us the ability to create dynamodb tables using java code.

The most basic action is to create a table with a hash key. In this case the email of the user would be the hash key.

List<KeySchemaElement> elements = new ArrayList<KeySchemaElement>();
        KeySchemaElement keySchemaElement = new KeySchemaElement()
                .withKeyType(KeyType.HASH)
                .withAttributeName("email");
        elements.add(keySchemaElement);

        List<AttributeDefinition> attributeDefinitions = new ArrayList<>();

        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("email")
                .withAttributeType(ScalarAttributeType.S));

        CreateTableRequest createTableRequest = new CreateTableRequest()
                .withTableName("Users")
                .withKeySchema(elements)
                .withProvisionedThroughput(new ProvisionedThroughput()
                        .withReadCapacityUnits(5L)
                        .withWriteCapacityUnits(5L))
                .withAttributeDefinitions(attributeDefinitions);

        amazonDynamoDB.createTable(createTableRequest);   

What we did is creating the Users table using his email for a hash key.

The next table will be called Logins. Logins should keep track each time the user logged in. To do so apart from using a hash key we will also use a range key.

        List<KeySchemaElement> elements = new ArrayList<KeySchemaElement>();
        KeySchemaElement hashKey = new KeySchemaElement()
                .withKeyType(KeyType.HASH)
                .withAttributeName("email");
        KeySchemaElement rangeKey = new KeySchemaElement()
                .withKeyType(KeyType.RANGE)
                .withAttributeName("timestamp");
        elements.add(hashKey);
        elements.add(rangeKey);


        List<AttributeDefinition> attributeDefinitions = new ArrayList<>();

        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("email")
                .withAttributeType(ScalarAttributeType.S));


        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("timestamp")
                .withAttributeType(ScalarAttributeType.N));

        CreateTableRequest createTableRequest = new CreateTableRequest()
                .withTableName("Logins")
                .withKeySchema(elements)
                .withProvisionedThroughput(new ProvisionedThroughput()
                        .withReadCapacityUnits(5L)
                        .withWriteCapacityUnits(5L))
                .withAttributeDefinitions(attributeDefinitions);


        amazonDynamoDB.createTable(createTableRequest);

By using the email as a hash key we can query for the logins of the specific user.
By using the date that the login occured as a range key with can find sort the login entries or perform advanced queries based on the login date for a specific user.

However most of the times a hash key and range key are not enough for our needs.
DynamoDB provides us with Global Secondary indexes and Local secondary Indexes.

We will create the table SupervisorS. The hash key of Supervisor would be his name. A supervisor will work for a company. The company will be our global secondary index. Since the companies own more than one factories the field factory would be the range key.

List<KeySchemaElement> elements = new ArrayList<>();
        KeySchemaElement hashKey = new KeySchemaElement()
                .withKeyType(KeyType.HASH)
                .withAttributeName("name");
        elements.add(hashKey);

        List<GlobalSecondaryIndex> globalSecondaryIndices = new ArrayList<>();

        ArrayList<KeySchemaElement> indexKeySchema = new ArrayList<>();

        indexKeySchema.add(new KeySchemaElement()
                .withAttributeName("company")
                .withKeyType(KeyType.HASH));  //Partition key
        indexKeySchema.add(new KeySchemaElement()
                .withAttributeName("factory")
                .withKeyType(KeyType.RANGE));  //Sort key


        GlobalSecondaryIndex factoryIndex = new GlobalSecondaryIndex()
                .withIndexName("FactoryIndex")
                .withProvisionedThroughput(new ProvisionedThroughput()
                        .withReadCapacityUnits((long) 10)
                        .withWriteCapacityUnits((long) 1))
                .withKeySchema(indexKeySchema)
                .withProjection(new Projection().withProjectionType(ProjectionType.ALL));
        globalSecondaryIndices.add(factoryIndex);

        List<AttributeDefinition> attributeDefinitions = new ArrayList<>();

        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("name")
                .withAttributeType(ScalarAttributeType.S));
        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("company")
                .withAttributeType(ScalarAttributeType.S));
        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("factory")
                .withAttributeType(ScalarAttributeType.S));

        CreateTableRequest createTableRequest = new CreateTableRequest()
                .withTableName("Supervisors")
                .withKeySchema(elements)
                .withProvisionedThroughput(new ProvisionedThroughput()
                        .withReadCapacityUnits(5L)
                        .withWriteCapacityUnits(5L))
                .withGlobalSecondaryIndexes(factoryIndex)
                .withAttributeDefinitions(attributeDefinitions);

        amazonDynamoDB.createTable(createTableRequest);

Next table would be the table Companies. The hash key would be the parent company and the range key the subsidiary company. Each company has a CEO. The CEO would be the range key for the local secondary index.

List<KeySchemaElement> elements = new ArrayList<>();
        KeySchemaElement hashKey = new KeySchemaElement()
                .withKeyType(KeyType.HASH)
                .withAttributeName("name");
        KeySchemaElement rangeKey = new KeySchemaElement()
                .withKeyType(KeyType.RANGE)
                .withAttributeName("subsidiary");

        elements.add(hashKey);
        elements.add(rangeKey);

        List<LocalSecondaryIndex> localSecondaryIndices = new ArrayList<>();

        ArrayList<KeySchemaElement> indexKeySchema = new ArrayList<>();

        indexKeySchema.add(new KeySchemaElement()
                .withAttributeName("name")
                .withKeyType(KeyType.HASH));
        indexKeySchema.add(new KeySchemaElement()
                .withAttributeName("ceo")
                .withKeyType(KeyType.RANGE));

        LocalSecondaryIndex ceoIndex = new LocalSecondaryIndex()
                .withIndexName("CeoIndex")
                .withKeySchema(indexKeySchema)
                .withProjection(new Projection().withProjectionType(ProjectionType.ALL));
        localSecondaryIndices.add(ceoIndex);

        List<AttributeDefinition> attributeDefinitions = new ArrayList<>();

        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("name")
                .withAttributeType(ScalarAttributeType.S));
        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("subsidiary")
                .withAttributeType(ScalarAttributeType.S));
        attributeDefinitions.add(new AttributeDefinition()
                .withAttributeName("ceo")
                .withAttributeType(ScalarAttributeType.S));

        CreateTableRequest createTableRequest = new CreateTableRequest()
                .withTableName("Companies")
                .withKeySchema(elements)
                .withProvisionedThroughput(new ProvisionedThroughput()
                        .withReadCapacityUnits(5L)
                        .withWriteCapacityUnits(5L))
                .withLocalSecondaryIndexes(localSecondaryIndices)
                .withAttributeDefinitions(attributeDefinitions);

        amazonDynamoDB.createTable(createTableRequest);

You can find the source code on github.

I’ve compiled a cheat sheet that lists dynamodb functions in Java
Sign up in the link to receive it.

Scheduling jobs on a Sails.js application

In one of my projects there was the need to put scheduled tasks on my Sails.js application.
Agenda and node-schedule are the tools of my choice when scheduling jobs on a node.js app. What we are gona cover is adding scheduling to our Sails.js application using node-schedule and agenda.

To get started let’s create our application

sails new SailsScheduling
cd SailsScheduling

My approach to use node-schedule is to add some configuration on the bootstrap.js file.

npm install node-schedule --save

We will add a service to our Sails.js application. Services on a Sails.js application reside on the api/services/ path.

Suppose that we implement a service that will send emails

/**
 * Created by gkatzioura on 6/20/16.
 */

var send = function (text,callback) {

  sails.log.info("Should send text: "+text)
  callback();
};

module.exports =  {
  send: send
}

Then we add our job triggering code on bootstrap.js.

/**
 * Bootstrap
 * (sails.config.bootstrap)
 *
 * An asynchronous bootstrap function that runs before your Sails app gets lifted.
 * This gives you an opportunity to set up your data model, run jobs, or perform some special logic.
 *
 * For more information on bootstrapping your app, check out:
 * http://sailsjs.org/#!/documentation/reference/sails.config/sails.config.bootstrap.html
 */
var scheduler = require('node-schedule');

module.exports.bootstrap = function(cb) {

  // It's very important to trigger this callback method when you are finished
  // with the bootstrap!  (otherwise your server will never lift, since it's waiting on the bootstrap)

  var emailService = EmailService;


  var minuteJob  = scheduler.scheduleJob('* * * * *', function(){
    EmailService.send("Random text",function (err, result) {
      sails.log.info("Job executed")
    });
  });

  cb();
};

The next example would use agenda. Instead of rolling out our own configuration we will use sails-hook-jobs which integrates wonderfully to our sails application as a grunt task.

npm install mongodb@~1.4 --save
npm install sails-hook-jobs --save

We need mongodb 1.4 version for mongo-skin.

Agenda is backed by mongodb.
For docker users you can issue

docker run --name some-mongo -d mongo

and have a mongodb server up and running.

Next step is creating the file config/jobs.js containing the configuration.

/**
 * Default jobs configuration
 * (sails.config.jobs)
 *
 * For more information using jobs in your app, check out:
 * https://github.com/vbuzzano/sails-hook-jobs
 */

module.exports.jobs = {

  // Where are jobs files
  "jobsDirectory": "api/jobs",

  // agenda configuration. 
  // for more details about configuration,
  // check https://github.com/rschmukler/agenda
  "db": { 
    "address"    : "localhost:27017/jobs",
    "collection" : "agendaJobs" 
  },
  "name": "process name",
  "processEvery": "10 seconds",
  "maxConcurrency": 20,
  "defaultConcurrency": 5,
  "defaultLockLifetime": 10000
};

Next step is to create the directory jobs on our api folder.
In order to add a job we should create a javascript source file on the api/jobs folder.
You file should have the ending Job.js. Pay special attention to this, you do not want to spend hours on figuring out what went wrong like I did.

Our job would send an email every five minutes.

module.exports = function(agenda) {
  var job = {

    frequency: 'every 5 minutes',
    run: function(job, done) {
      EmailService.send("Test email",function (err,result) {

        if(err) {
          sails.log.error("Job was not executed properly");
          done(err);
        } else {
          sails.log.info("Agenda job was executed");
          done();
        }
      });
    },
  };
  return job;
}

All in all there are definitely more tools out there for Sails.js scheduling.
My personal choice is agenda, due to its approach on managing your jobs and integrating as a sails task.

You can find the source code on github.

Scheduling jobs on Node.js with agenda

There are many ways to schedule jobs in your application. A very common practice is to back our jobs with persistence.
By doing so we will be informed in the future if the job did fail or succeed and when it should be the next execution.

Agenda is a light-weight job scheduling library for node.js. It is backed with mongodb.

It is really simple to get started. The first thing we have to do is to configure the mongodb database connection sting.

var Agenda = require('agenda');

var connectionString = "127.0.0.1:27017/scheduled_jobs";
var agenda = new Agenda({db: { address: connectionString, collection: 'jobs' }});

Next step would be to specify jobs.

Suppose we have an EmailService like this

EmailService = {
        send:function(callback){
            console.log("sending email");
            callback();
        }
};

Then we shall define a job

agenda.define('send email', function(job, done) {
    EmailService.send(function(err,result) {
        if(err) {
            done(err);
        } else {
            done();  
        }
    });  
});

We just defined a job with agenda in a human way.

agenda.on('ready',function() {
 agenda.every('1 day','send email'); 
 agenda.start();
});

Once we defined the jobs we need to set the time interval that the agenda instance will look up for new jobs.

agenda.processEvery('1 minute');

By querying to our mongodb database we are able to receive our job status.

>db.jobs.find({})
{ "_id" : ObjectId("5767110c779be08d4e1b3109"), "name" : "send email", "type" : "single", "data" : null, "priority" : 0, "repeatInterval" : "1 day", "repeatTimezone" : null, "lastModifiedBy" : null, "nextRunAt" : ISODate("2016-06-20T21:39:24.931Z"), "lockedAt" : null, "lastRunAt" : ISODate("2016-06-19T21:39:24.931Z"), "lastFinishedAt" : ISODate("2016-06-19T21:39:24.932Z") }

Agenda is pretty featureful.
For example you can use it with cron format too.

For example running our job every minute

agenda.on('ready',function() {
 agenda.every('* * * * *','send email');
 agenda.start();
});

Other cool features is retrieving jobs in a mongodb query format and modifying them.

agenda.jobs({name: 'send email'}, function(err, jobs) {
});

Last but not least agenda-ui is a great tool for visualizing agenda jobs.

Overall agenda is my personal favorite when it comes to adding josb to my node.js application.
It is backed by mongodb and easy to configure.
I believe that one of its main strengths is giving you good control over your jobs.

Integrating Quartz with Spring

When it comes to scheduling jobs in a java application, Quartz is the first tool that comes into consideration.

Quartz is job scheduler backed up by most popular RDBMSes.
It is really convenient and gets integrated with spring quite easy.

In order to create the quartz schema you have to download the quartz distribution
and extract the folder located in quartz-2.2.3/docs/dbTables/

Choose the quartz schema according to the database that you use.
In our case we will use a local h2 database therefore I will use the tables_h2.sql schema.

In order to avoid any manual sql actions i will use the Spring boot database initialization feature.

Let’s start with our gradle file.

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.8

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.3.3.RELEASE")
    }
}

apply plugin: 'idea'
apply plugin: 'spring-boot'

repositories {
    mavenCentral()
}

dependencies {
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-web', version: '1.3.3.RELEASE'
    compile group: 'org.springframework', name: 'spring-context-support', version: '4.2.4.RELEASE'
    compile group: 'org.springframework', name:'spring-jdbc', version: '4.2.4.RELEASE'
    compile group: 'org.quartz-scheduler', name: 'quartz', version: '2.2.3'
    compile group: 'ch.qos.logback', name: 'logback-core', version:'1.1.3'
    compile group: 'ch.qos.logback', name: 'logback-classic',version:'1.1.3'
    compile group: 'org.slf4j', name: 'slf4j-api',version:'1.7.13'
    compile group: 'com.h2database', name: 'h2', version:'1.4.192'
    testCompile group: 'junit', name: 'junit', version: '4.11'
}

Apart from the quartz, spring and h2 dependencies, we add the spring-jdbc dependencies since we want to have the database initialized through spring.

We will also add an application.yml file

spring:
  datasource:
    continueOnError: true
org:
  quartz:
    scheduler:
      instanceName: spring-boot-quartz-demo
      instanceId: AUTO
    threadPool:
      threadCount: 5
job:
  startDelay: 0
  repeatInterval: 60000
  description: Sample job
  key: StatisticsJob

Due to the schema creation statements (lack of create if not exists statements), I set spring.datasource.continueOnError to false. According to your implementation the workaround will vary.

The application class

package com.gkatzioura.springquartz;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

/**
 * Created by gkatzioura on 6/6/16.
 */
@SpringBootApplication
public class Application {

    public static void main(String[] args) {

        SpringApplication springApplication = new SpringApplication();
        ApplicationContext ctx = springApplication.run(Application.class,args);
    }
}

The h2 datasource configuration neeeded by quartz

package com.gkatzioura.springquartz.config;

import org.h2.jdbcx.JdbcDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * Created by gkatzioura on 6/6/16.
 */
@Configuration
public class QuartzDataSource {

    //Since it a test database it will be located at the temp directory
    private static final String TMP_DIR = System.getProperty("java.io.tmpdir");

    @Bean
    public DataSource dataSource() {

        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:"+TMP_DIR+"/test");

        return ds;
    }

}

In our case we want to sent ‘spam’ emails every minute, therefore we define a simple email service

package com.gkatzioura.springquartz.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

/**
 * Created by gkatzioura on 6/7/16.
 */
@Service
public class EmailService {

    private static final Logger LOGGER = LoggerFactory.getLogger(EmailService.class);

    public void sendSpam() {

        LOGGER.info("Should send emails");
    }

}

I will also implement a SpringBeanJobFactory

package com.gkatzioura.springquartz.quartz;

import org.quartz.spi.TriggerFiredBundle;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.AutowireCapableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.scheduling.quartz.SpringBeanJobFactory;

/**
 * Created by gkatzioura on 6/7/16.
 */
public class QuartzJobFactory extends SpringBeanJobFactory implements ApplicationContextAware {

    private transient AutowireCapableBeanFactory beanFactory;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {

        beanFactory = applicationContext.getAutowireCapableBeanFactory();
    }

    @Override
    protected Object createJobInstance(TriggerFiredBundle bundle) throws Exception {

        final Object job = super.createJobInstance(bundle);
        beanFactory.autowireBean(job);
        return job;
    }
}

QuartzJobFactory will create the job instance and the will use the application context in order to inject any dependencies defined.

Next step is defining our job

package com.gkatzioura.springquartz.job;

import com.gkatzioura.springquartz.service.EmailService;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * Created by gkatzioura on 6/6/16.
 */
public class EmailJob implements Job {

    @Autowired
    private EmailService cronService;

    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {

        cronService.sendSpam();
    }
}

Last step is adding quartz config

package com.gkatzioura.springquartz.config;


import com.gkatzioura.springquartz.job.EmailJob;
import com.gkatzioura.springquartz.quartz.QuartzJobFactory;
import org.quartz.SimpleTrigger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.quartz.JobDetailFactoryBean;
import org.springframework.scheduling.quartz.SchedulerFactoryBean;
import org.springframework.scheduling.quartz.SimpleTriggerFactoryBean;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * Created by gkatzioura on 6/7/16.
 */
@Configuration
public class QuartzConfig {

    @Value("${org.quartz.scheduler.instanceName}")
    private String instanceName;

    @Value("${org.quartz.scheduler.instanceId}")
    private String instanceId;

    @Value("${org.quartz.threadPool.threadCount}")
    private String threadCount;

    @Value("${job.startDelay}")
    private Long startDelay;

    @Value("${job.repeatInterval}")
    private Long repeatInterval;

    @Value("${job.description}")
    private String description;

    @Value("${job.key}")
    private String key;

    @Autowired
    private DataSource dataSource;

    @Bean
    public org.quartz.spi.JobFactory jobFactory(ApplicationContext applicationContext) {

        QuartzJobFactory sampleJobFactory = new QuartzJobFactory();
        sampleJobFactory.setApplicationContext(applicationContext);
        return sampleJobFactory;
    }

    @Bean
    public SchedulerFactoryBean schedulerFactoryBean(ApplicationContext applicationContext) {

        SchedulerFactoryBean factory = new SchedulerFactoryBean();

        factory.setOverwriteExistingJobs(true);
        factory.setJobFactory(jobFactory(applicationContext));

        Properties quartzProperties = new Properties();
        quartzProperties.setProperty("org.quartz.scheduler.instanceName",instanceName);
        quartzProperties.setProperty("org.quartz.scheduler.instanceId",instanceId);
        quartzProperties.setProperty("org.quartz.threadPool.threadCount",threadCount);

        factory.setDataSource(dataSource);

        factory.setQuartzProperties(quartzProperties);
        factory.setTriggers(emailJobTrigger().getObject());

        return factory;
    }

    @Bean(name = "emailJobTrigger")
    public SimpleTriggerFactoryBean emailJobTrigger() {


        SimpleTriggerFactoryBean factoryBean = new SimpleTriggerFactoryBean();
        factoryBean.setJobDetail(emailJobDetails().getObject());
        factoryBean.setStartDelay(startDelay);
        factoryBean.setRepeatInterval(repeatInterval);
        factoryBean.setRepeatCount(SimpleTrigger.REPEAT_INDEFINITELY);
        factoryBean.setMisfireInstruction(SimpleTrigger.MISFIRE_INSTRUCTION_RESCHEDULE_NEXT_WITH_REMAINING_COUNT);
        return factoryBean;
    }

    @Bean(name = "emailJobDetails")
    public JobDetailFactoryBean emailJobDetails() {

        JobDetailFactoryBean jobDetailFactoryBean = new JobDetailFactoryBean();
        jobDetailFactoryBean.setJobClass(EmailJob.class);
        jobDetailFactoryBean.setDescription(description);
        jobDetailFactoryBean.setDurability(true);
        jobDetailFactoryBean.setName(key);

        return jobDetailFactoryBean;
    }
}

What we did is creating a scheduler factory bean using the QuartzJobFactory we defined and we registered the triggers needed for our jobs to run. In our case we implemented a simple trigger running every minute.

You can find the source code on github

Add Custom functionality to a Spring Data Repository

Spring Data is pretty convenient and speeds up development avoiding boilerplate code.

However there are cases where annotation queries are not enough for the custom functionality you might want to achieve.

Therefore spring data allows us to add custom methods to a Spring Data Repository.

I will use the same project structure from a previous blog post.

We have an entity called Employee

package com.gkatzioura.springdata.jpa.persistence.entity;

import javax.persistence.*;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Entity
@Table(name = "employee", schema="spring_data_jpa_example")
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastname;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    @Column(name = "salary")
    private Integer salary;

    public Long getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }
}

And the Spring Data repository

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long>{

}

Suppose that we want to add some custom sql functionality for example querying with a LIKE statement and joining with a table that is not mapped as an entity.

This is for demonstration purposes only. For your project you might have a better schema. Plus spring data comes with out of the box functionality for like statements, look at EndingWith, Containing, StartingWith.

We shall create the table bonus and add a reference to the employee table.

set schema 'spring_data_jpa_example';

create table bonus(
	id serial primary key,
	employee_id integer,
	amount real,
	foreign key (employee_id) references employee (id),
	unique (employee_id)
	);

insert into bonus
( employee_id, amount)
VALUES(1, 100);	

The sql query that we want to implement will query for employees whose name starts with a specified text and a bonus bigger than a certain amount.
In jdbc we have to pass our variable concatenated with the character ‘%’.

So what we need is a native jpa query like this one

        Query query = entityManager.createNativeQuery("select e.* from spring_data_jpa_example.bonus b, spring_data_jpa_example.employee e\n" +
                "where e.id = b.employee_id " +
                "and e.firstname LIKE ? " +
                "and b.amount> ? ", Employee.class);
        query.setParameter(1, firstName + "%");
        query.setParameter(2, bonusAmount);

In order to add this functionality to our spring data Repository we have to add an interface.
It is mandatory for our interface to follow the naming convention of ${Original Repository name}Custom.
Therefore the interface describing our custom functionality should be

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;

import java.util.List;

/**
 * Created by gkatzioura on 6/3/16.
 */
public interface EmployeeRepositoryCustom {

    List<Employee> getFirstNamesLikeAndBonusBigger(String firstName, Double bonusAmount);

}

And the implementation should be

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by gkatzioura on 6/3/16.
 */
@Transactional(readOnly = true)
public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @Override
    public List<Employee> getFirstNamesLikeAndBonusBigger(String firstName, Double bonusAmount) {
        Query query = entityManager.createNativeQuery("select e.* from spring_data_jpa_example.bonus b, spring_data_jpa_example.employee e\n" +
                "where e.id = b.employee_id " +
                "and e.firstname LIKE ? " +
                "and b.amount> ? ", Employee.class);
        query.setParameter(1, firstName + "%");
        query.setParameter(2, bonusAmount);

        return query.getResultList();
    }
}

And we should change our original spring data repository in order to inherit the custom functionality.

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long>, EmployeeRepositoryCustom {
}

Seems like a nice way of composition.

Now let’s add a method to a controller that will call this custom method

package com.gkatzioura.springdata.jpa.controller;

        import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
        import com.gkatzioura.springdata.jpa.persistence.repository.EmployeeRepository;
        import org.springframework.beans.factory.annotation.Autowired;
        import org.springframework.web.bind.annotation.RequestMapping;
        import org.springframework.web.bind.annotation.RequestParam;
        import org.springframework.web.bind.annotation.RestController;

        import java.util.List;

/**
 * Created by gkatzioura on 6/2/16.
 */
@RestController
public class TestController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @RequestMapping("/employee")
    public List<Employee> getTest() {

        return employeeRepository.findAll();
    }

    @RequestMapping("/employee/filter")
    public List<Employee> getFiltered(String firstName,@RequestParam(defaultValue = "0") Double bonusAmount) {

        return employeeRepository.getFirstNamesLikeAndBonusBigger(firstName,bonusAmount);
    }

}

The source code can be found on github.

Spring boot and Spring data JPA integration

Nowadays spring and JPA integration has become a piece of cake thanks to Spring Boot and spring Data.

I am gonna setup a postgresql server

docker pull postgres
#run the container
docker run --name postgreslocal -e POSTGRES_PASSWORD=postgres -d postgres
#get the ip
docker inspect --format '{{ .NetworkSettings.IPAddress }}' postgreslocal
#get the port
docker inspect --format '{{ .NetworkSettings.Ports }}' postgreslocal

Create the employees Table

create schema spring_data_jpa_example;

create table spring_data_jpa_example.employee(
	id  SERIAL PRIMARY KEY,
	firstname	TEXT	NOT NULL,
	lastname	TEXT	NOT NULL,	
   	email		TEXT 	not null,
   	age         INT     NOT NULL,
   	salary         real,
	unique(email)
);

insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary) 
values ('Emmanouil','Gkatziouras','gkatzioura@gmail.com',18,3000.23);

Let’s begin with our gradle file

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.8

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.3.3.RELEASE")
    }
}

apply plugin: 'idea'
apply plugin: 'spring-boot'

repositories {
    mavenCentral()
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web") {
        exclude module: "spring-boot-starter-tomcat"
    }
    compile("org.postgresql:postgresql:9.4-1206-jdbc42")
    compile("org.springframework.boot:spring-boot-starter-jetty")
    compile("org.springframework.boot:spring-boot-starter-data-jpa:1.3.3.RELEASE")
    compile("com.mchange:c3p0:0.9.5.2")
    testCompile("junit:junit:4.11");
}

As you see we added the c3p0 connection pool, the spring-boot-starter-data-jpa for hibernate and the postgres driver. That’s all we need.

The Application class

package com.gkatzioura.springdata.jpa;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

/**
 * Created by gkatzioura on 6/2/16.
 */
@SpringBootApplication
public class Application {


    public static void main(String[] args) {

        SpringApplication springApplication = new SpringApplication();
        ApplicationContext ctx = springApplication.run(Application.class, args);
    }
}

The DataSource configuration

package com.gkatzioura.springdata.jpa.config;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource createDataSource() throws Exception {

        ComboPooledDataSource ds = new ComboPooledDataSource();
        ds.setJdbcUrl("jdbc:postgresql://172.17.0.3:5432/postgres?user=postgres&password=postgres");
        ds.setDriverClass("org.postgresql.Driver");

        return ds;
    }

}

Our entity for the table employee

package com.gkatzioura.springdata.jpa.persistence.entity;

import javax.persistence.*;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Entity
@Table(name = "employee", schema="spring_data_jpa_example")
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastname;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    @Column(name = "salary")
    private Integer salary;

    public Long getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }
}

The repository that will help us access all users

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by gkatzioura on 6/2/16.
 */
public interface EmployeeRepository extends JpaRepository<Employee,Long>{
}

And a controller that will fetch all the data

package com.gkatzioura.springdata.jpa.controller;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import com.gkatzioura.springdata.jpa.persistence.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Created by gkatzioura on 6/2/16.
 */
@RestController
public class TestController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @RequestMapping("/employee")
    public List<Employee> getTest() {

        return employeeRepository.findAll();
    }
}

Pretty convenient considering the dependencies and the xml configuration overhead of the past.

You can find the source code on github .