Basic JDBC Integration

There are good reasons to integrate a JDBC datasource in both a content authoring and a content delivery context.  For this reason, I want to point out that what we're demonstrating here works for both authoring and delivery plugins.
What we're going to demonstrate is the most basic form of integration with a JDBC datasource. We're going to create  a single REST API that:

1. Pulls in the proper JDBC libraries for the target database.
2. Establishes a connection with the database
3. Executes a query
4. Structures and returns the response in a useful way.

In the real world, there are many other concerns, including code factoring (use of service classes and Spring beans), connection pooling, and managing our credentials, to name a few.  For the purpose of this blog, we will ignore these to focus on the basic objectives above.  Let's get started.

To begin, we will need to create a REST API endpoint.  The code we're going to add here can work in any other kind of context (a controller, a filter, etc.), but a REST API is the easiest context to work, so that's why we're using it.

Step 1: Create the Basic REST API

In CrafterCMS we create a REST API endpoint by creating a new Groovy script in the following location:  Scipts > rest 

1. On the folder named rest, click the options menu and choose "New Controller"
2. Enter the name "my-jdbc-api.get.groovy" This will add a delivery GET method REST API at the url /api/my-jdbc-api.json and will open a code editor for you.
   
3. Now we can add some code. We can start with the following just to get our API in place:
   

   return "hello world"

    

4. Add the code above and click save. We can test the script via CURL, Postman or an equivalent by issuing a GET request to
   http(s)://SERVERNAME:PORT/api/my-jdbc-api.json

Step 2:  Getting the Correct Driver Into the System

To get the driver library for the database into the system, we need to pull it from a repository of Java libraries/dependencies. You can use Maven Central or configure CrafterCMS to use a custom repository. In our example, we will connect to a SQLServer database and thus we will need a SQLServer library. We will acquire that by adding a Grab statement at the top of our source file. This pulls the dependency (called a Grape) into the system so that we can access the classes associated with it.  Add the following to the top of your source file:

@Grab(value = 'com.microsoft.sqlserver:mssql-jdbc:10.2.0.jre17', changing = true, initClass = false)

The Grab you use will depend on the type of database you are connecting to. The statement above is for SQLServer. The statement below is for Oracle:

@Grab(value = 'com.oracle.database.jdbc:ojdbc11:23.3.0.23.09', changing = true, initClass = false)

Regardless of the database library you are using, be sure to use and maintain the latest version to ensure you have up to date patches for bugs and security concerns.

Step 3: Import the Required Classes

Now, you will need to import the classes you need to connect to and interact with the database. The first set of classes is database-specific. Here I am showing the classes required for SQLServer. Oracle, MySQL, MariaDB, Postgres, and others will all have their own classes for this. Add the following code AFTER the grab.

import com.microsoft.sqlserver.jdbc.SQLServerDriver
import com.microsoft.sqlserver.jdbc.SQLServerDataSource

Next we'll import a few more classes to interact with (query) the database. Technically in Groovy, we can get away without these imports because we don't have to "type" our variables.  I will show an example below of both styles.

import java.sql.Connection
import java.sql.Statement
import java.sql.ResultSet

Step 4: Making the Conntection

Now we can connect to our database. Add the following code after the import statements.
Note the use of def or defines in Groovy allows me to create instances of objects without explicitly typing them, which means I don't have to import them either. In a few places, I used the specific class, as we can see with Connection. If you like Strong Typing, use it. If not, don't. That's up to you. My only advice (which is contrary to my example here is to be consistent with your approach.) The one place I MUST specify the class in this scenario is the Driver class: SQLServerDataSource.

def connectionString = "jdbc:sqlserver://<database hostname>:<database port>;databaseName=<database name>"
def username = "russ"
def password = "superSecretPassword123"
def dbDatasource = new SQLServerDataSource()

dbDatasource.setURL(connectionString)
dbDatasource.setUser(username)
dbDatasource.setPassword(password)

Connection dbConnection = dbDatasource.getConnection()

Another important note is that your connection string can vary quite a bit from one database type to another. It's important to get these correct.  Make sure you follow the documentation for the specific type of database you are connecting to.

Step 5: Issue a Query

Now that we have a connection, we can define and issue a query.  

def QUERY = "SELECT id, first, last FROM Employees"
def queryStatement = dbConnection.createStatement()
def queryResult = queryStatement.executeQuery(QUERY)

Step 6: Structure the Results Into a Response for the API

Once we have a result, we can do something with it. In our example, we'll just structure the rows of the result into a response for our REST API.

def employees = [] 

while (queryResult.next()) {
   def employee = [:]
   employee.id = queryResult.getInt("id")
   employee.firstName = queryResult.getString("first")
   employee.lastName = queryResult.getString("last")

   employees.add(employee)
}

Step 7: Return Our Result

By adding the return statement to the bottom of the script we tell Crafter Engine we want it to marshal the object we're returning to JSON.

return employees

Step 8: Putting It All Together

The full script looks like this:
I switched to def statements consistently and removed the import statements not required when this style was used.

@Grab(value = 'com.microsoft.sqlserver:mssql-jdbc:10.2.0.jre17', changing = true, initClass = false)

import com.microsoft.sqlserver.jdbc.SQLServerDataSource

def connectionString = "jdbc:sqlserver://<database hostname>:<database port>;databaseName=<database name>"
def username = "russ"
def password = "superSecretPassword123"

def dbDatasource = new SQLServerDataSource()
dbDatasource.setURL(connectionString)
dbDatasource.setUser(username)
dbDatasource.setPassword(password)

def dbConnection = dbDatasource.getConnection()

def QUERY = "SELECT id, first, last FROM Employees"
def queryStatement = dbConnection.createStatement()
def queryResult = queryStatement.executeQuery(QUERY)

def employees = [] 

while (queryResult.next()) {
   def employee = [:]

   employee.id = queryResult.getInt("id")
   employee.firstName = queryResult.getString("first")
   employee.lastName = queryResult.getString("last")

   employees.add(employee)
}

return employees

Now let's run the script with the followinf CURL command:

curl http://localhost:8080/api/my-jbc-api.json?crafterSite=jdbcExample

When we execute this request we get the following resposne:

[{"id":1,"firstName":"Russ", "lastName":"Danner"}, {"id":2,"firstName":"Roger", "lastName":"Wilco"} ]

Note that just as we changed the database field from "first" to "firstName" in the JSON response we can also control other aspects of the response. With very minor adjustments to the Groovy code above we could have returned a structure such as:

{ employees: [{"id":1,"firstName":"Russ", "lastName":"Danner"}, {"id":2,"firstName":"Roger", "lastName":"Wilco"} ] }

Conclusion

This blog covered how to perform a basic JDBC-based integration in CrafterCMS with an external database. As I mentioned above, many topics have been ignored, such as error handling, connection pooling, credentials management, and code factoring. We'll cover these topics in their own blogs. I hope this simple example helps you on your journey in your next integration!