Accessing SQL Server in AWS Lambda – Part 2

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

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:

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:

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.