Accessing SQL Server in AWS Lambda – Part 2

This is Part 2 in a series describing how to get Microsoft SQL Server and AWS Lambda playing nicely together. You can find Part 1 here. If you already know how to use java SQL to connect to a database, you can skip these steps and move onto Part 3. Otherwise if you work better just looking at a sample, just jump straight to the GitHub project.

Step 4: The Database
This is when things start to get slightly more interesting. We need to write the code that the Lambda handlers will use to the database. Create a new Package under mannanlive named repository and inside create a New – Java Class named QueryDao.java

package com.mannanlive.repository;

import com.mannanlive.query.Query;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class QueryDao {

    public T executeSingleton(Query query) {
        List execute = execute(query);
        if (execute.isEmpty()) {
            return null;
        }
        return execute.get(0);
    }

    public List execute(Query query) {
        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://your-server.ap-southeast-2.rds.amazonaws.com:1433;" +
                "databaseName=your-db";

        // Declare the JDBC objects.
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // Establish the connection.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(connectionUrl, "your-username", "your-password");

            // Create and execute an SQL statement that returns a set of data and then display it.
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query.getQuery());
            return query.translate(resultSet);
        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
        return new ArrayList();
    }

    private void safeClose(AutoCloseable closeable) {
        if (closeable != null) {
            try {
                closeable.close();
            } catch (Exception ignored) { }
        }
    }
}

And change your-server/db/username/password to be the values of your SQL Server Database. Hopefully it should be fairly easy to see what is happening here. What we next need to do is add the database driver to the project so that Java can connect to SQL Server. You can use this to connect to any type of database if you can find the right Java library to use. To do this, we need to create a libs folder at the very root of your project, and download a copy of Microsoft’s JDBC Driver and put it (sqljdbc4.jar) inside there. In your build.gradle, in the decencies section add a new line: ‘compile fileTree(dir: ‘libs’, include: [‘*.jar’])’ which indicates that your project will use any libraries in that folder. Finally run ‘gradlew cIM iM’ (shortcut to clean out your IntelliJ modules only).

Step 5: Testing
This step is optional but I would strongly recommend it. Firstly create a new folder chain src/test/java. Secondly, in your build.gradle replace the current dependencies section with:

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    testCompile 'junit:junit:4.12'
    testCompile 'com.fasterxml.jackson.core:jackson-databind:2.8.3'
}

Finally run ‘gradlew cIM iM’ (shortcut to clean out your IntelliJ modules only). Go back to your QueryDao.java then press CTRL+Shift+T and IntelliJ will pop open a Create Test dialog. Select JUnit4 and check executeSingleton then press OK. Then change it to look something like this:

package com.mannanlive.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.mannanlive.domain.TimeResponseClass;
import com.mannanlive.query.TimeQuery;
import org.junit.Assert;
import org.junit.Test;

public class QueryDaoTest {
    @Test
    public void testSingleQueries() throws Exception {
        TimeResponseClass test = new QueryDao().executeSingleton(new TimeQuery(1));
        Assert.assertEquals("{\"currentTime\":\"2016-10-09\"}", new ObjectMapper().writeValueAsString(test));
    }
}

Change the date to be tomorrow’s date and then run the test (CTRL+Shift+F10), hopefully if everything is working, it should pass. You can play around by passing in negative numbers and check that the date is decreased by the same amount. Here you can re-write the query and translate methods as well as your response class to be what ever you want returned from the database.

This is what your project should look like:

The project's structure.
The project’s structure.

About the Author

Mannan

Mannan is a software engineering enthusiast and has been madly coding since 2002. When he isn't coding he loves to travel, so you will find both of these topics on this blog.

Leave a Reply

Your email address will not be published. Required fields are marked *