How to connect on-premises Microsoft SQL Server DB from AWS Lambda using java.

In this article we will see how we can connect with Microsoft SQL Server to insert data into table using lambda function in same region.

Suppose we have one requirement to insert the data into table from the response to API call. This is a daily schedule activity. We can do this task using below steps.

Steps:

Step 1: First create a Lambda function.
Step 2: Write a handler in Java.
Step 3: Schedule Lambda function using AWS Events.
Step 4: Add dependency com.microsoft.sqlserver:mssql-jdbc:12.1.0.jre8-preview in your build file under dependencies.

Step 5: Create a jar and test the Lambda.

Create a Lambda Function and schedule it:

This step is mention in my article on Lambda function scheduling . You can follow the same steps to archive this steps. Also this article explains how to create a jar and test it on console.

Write a Java handler :

This section explain about creating the handler and logic to insert data into table. Now our scenario is to collect the response from API first and then insert the response data in to the MSQL table. So in the handleRequest method we call the callToApi(). We store the data in the map by calling the method callToApi which takes date as parameter (you can take system date also)

@Override
public String handleRequest(Object input, Context context) {
    String indata= input != null ? input.toString() : "{}";
    indata=indata.replace('{',' ');
    indata=indata.replace('}',' ');
    indata=indata.replace("date=","");
    logger.info("date: "+indata);
    Map data=callToApi(indata);

public Map callToApi(String yourdate){
....
...call to the Http method for getting data..
...
return eMap;
}

Now the data is available in Map ,pass this map to the method for inserting data to DB.

String sqlQueryParams="INSERT INTO ETABLE (EMPID, DATE, ROLE, SAL ) values";
List <String> empList=new ArrayList();
 if(data!=null||!data.isEmpty()){
 logger.info("records received.");
 for (Object key : data.values()) {
 String s=key.toString();
 //Extract EMPID, DATE, ROLE and SAL from s
 s=sqlQueryParams+" ('"+EMPID+"',CAST('"+datet+"' AS DateTime),'"+role+"','"+sal+"')";
 // logger.info("Info: " +s);
 empList.add(s);
 }
 }
 insertInDB(empList);

Code for inserting to DB.

private void insertInDB(List<String> empList) {
    logger.info("Calling Insert");
    String driverName ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String dbURL = "//your connection string";
    String db ="Table Name";
    String dbUser ="User Id";
    String dbPassword = "Password";
    String dbOther = "encrypt=true;trustServerCertificate=true";
    Connection connection =null;
    Statement stmt=null;
    //logger.info("Conn Str: "+dbURL+db+dbUser+dbPassword+dbOther);
    try {
        Class.forName(driverName);
        DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
        connection = DriverManager.getConnection(dbURL + db + dbUser + dbPassword + dbOther);
        if(connection!=null) {
            stmt = connection.createStatement();
            logger.info("connected to DB");
            connection.setAutoCommit(false);
            for(String empInsert:empList){
                logger.info("Inserting into DB : "+empInsert.toString());
                stmt.addBatch(empInsert.toString());
            }
            stmt.executeBatch();
            connection.commit();

        }
        logger.info("Successfully inserted records : ");
        connection.close(); // close connection
    }
    catch (Exception e) {
        logger.info("Exception in Insert...."+e.getMessage());
    }
}

Create a jar and test your application.

Thank You.