AWS Lambda Java, connect to MySQL RDS

Question

I need to develop an AWS Lambda Java function to retrieve some records from RDS MySQL database.

Should I use JDBC? Should I use the standard JDBC example:

try {
    String url = "jdbc:msql://200.210.220.1:1114/Demo";
    Connection conn = DriverManager.getConnection(url,"","");
    Statement stmt = conn.createStatement();
    ResultSet rs;

    rs = stmt.executeQuery("SELECT Lname FROM Customers WHERE Snum = 2001");
    while ( rs.next() ) {
        String lastName = rs.getString("Lname");
        System.out.println(lastName);
    }
    conn.close();
} catch (Exception e) {
    System.err.println("Got an exception! ");
    System.err.println(e.getMessage());
}

Answer 1

One thing I am particularly noticing in your codebase is that even when you use this Lambda function for connecting to the specific RDS you have, the hostname may not be the correct one for Amazon RDS. It needs to be the endpoint of the RDS you are trying to connect to and your complete connection url would look something like this below -

//jdbc:mysql://hostname (endpoint of RDS):port/databasename

String url = "jdbc:mysql://"+dbHost+":3306/"+dbName;

Since those endpoints can change for different databases and servers, you can make them as environment variables within Lambda and refer using

String dbHost = System.getenv("dbHost");

String dbName = System.getenv("dbName");

for a much cleaner and stateless design that Lambda supports.

Answer 2

Step 1:

1. login IAM console
2. roles -> create new roles
3. role name :lambda-vpc-execution-role

4. AWS service roles ->

   a) Select aws lambda

   b) Attach policy "AWSLambdaFullAccess"

Step 2:

1. Get code from https://github.com/vinayselvaraj/lambda-jdbc-sample (note this is maven project)

2. Right click on project select Run as --->5.maven build...

   for goal provide name package shade:shade

3. Go to project folder and target/lamda-0.0.1-SNAPSHOT-shaded.jar

Step 3:

1. Login to lambda console(skip blueprint)
2. Create new lambda

3. name: time-test

   a) runtime-java

   b) upload .zip(.jar) file (target/lamda-0.0.1-SNAPSHOT-shaded.jar)

4. Provide package.class-name::myhandler -> Handler

5. Role -> lambda-vpc-exceution-role

6. vpc provide rds-vpc details (this should work in same vpc group)

7. Create function

In the Action drop down list select configure test event result will shown down like this "Execution result: succeeded (logs)"

Answer 3

Yes, you need to use standard JDBC code in your lambda function class. The code you provided looks okay. There are a few more things you need to do when accessing RDS or any other RDBMS through a Lamda function -

1. Create a jar or a zip file for your Lambda function
2. Your zip file needs to have a lib folder in which your JDBC driver file goes. The Lambda function doc says this is one of the two standard ways, but it didn't work for me.
3. You can create a jar file in which the driver classes are put in. This works. The best way to do it is through the Maven Shade plugin, which extracts the JDBC drivers and packs the classes in one single jar file.
4. Setup the handler function and specify it at the time of Lambda deployment
5. Define execution role and VPC as needed.
6. Upload and publish your jar or zip file.

You can test the Lambda function through the console, and see the actual output in the CloudWatch logs.

Answer 4

You could use this kinda implementation:

public static DataSource getDataSource(){

    Utils._logger.log("Get data source");
    MysqlDataSource mysqlDs = null;
    try{
        mysqlDs = new MysqlDataSource();
        mysqlDs.setURL('jdbc:msql://'+'url');
        mysqlDs.setUser('user');
        mysqlDs.setPassword('pwd');
        Utils._logger.log("Object "+mysqlDs.getUrl()+" "+mysqlDs.getUser()+" ");
        return mysqlDs;
    }
    catch(Exception e) {
        Utils._logger.log("No se pudo abrir el archivo de properties");
        e.printStackTrace();
    }
    return mysqlDs;

}