Break New Ground

Connecting to Oracle Autonomous Database with MyBatis

Following the previous post of Connecting to Oracle Autonomous Database with JDBI today I'd like to showcase another lightweight option know as MyBatis. MyBatis began life as iBATIS back in 2001 moving to Apache some years later. While at Apache it gathered quite the following until competing DAO solutions emerged, despite this, the project kept going now outside of Apache and renamed to MyBatis. However the legacy of iBATIS continues on, you may find it in some package names. Given the history is no surprise there are organizations still running MyBatis to this day. Let's go through a quick introduction and demonstration, shall we?

MyBatis offers a couple of options for mapping SQL statements to Java types. Due to its origins during the dot-com bubble you can expect XML heavy configuration, like many tools of the time. As time passed by more Java oriented options were added thus you'll find naming conventions and an annotation-based API that may be familiar to many; and it's due to this familiarity that I'll stick with the annotation API though the documentation states there are use cases where the XML configuration is less verbose than the alternative.

We begin by having an Oracle Autonomous Database instance available for testing. If you don't have one already then I'd recommend you to follow the steps found in Connecting to Oracle Autonomous Database via JDBC and come back here (don't forget to download the wallet zip file!). Next, we have a look at the required dependencies for this small demo, which are the following

This time we only need a single dependency for this library. Once again we make use of the ojdbc8-production POM dependency to get all the Oracle JDBC dependencies required to connect to an Oracle Autonomous Database. Lombok appears one more time to simplify the code used for bean mapping, as a matter of fact, the code is identical to the previous demo. Remember the Todo class? Here it is again

Opposed to Jdbi where the Database Access Object (DAO) name is used MyBatis prefers the term Mappers, however, they basically cover the same functionality, as shown next

The TodoMapper interface defines mapping methods for CRUD operations as we would expect from a type with such responsibilities. The mapping capabilities are expressed with annotations; the SQL statements are pretty much identical to what we saw before except for the named parameter definitions, take note of that. If you do not like annotations and prefer naming conventions know that MyBatis' mapper method names can follow a naming convention for their method names, such as selectTodos() which would as the name suggests, select all rows from the todos table.

MyBatis has a couple of options for initializing its base class, the SqlSessionFactory. Instances of this type are long-lived, you should not have the need to create and dispose of many of these. Rather keep the instance around and obtain SqlSession objects from it. SqlSession in turn is not thread-safe so don't reuse, throw it away (actually close it before) one you're done with it. Creating a SqlSessionFactory requires an instance of Configuration which can be created using an XML resource. But given that I want to show how to connect to the database using a UCP pool we'll go with the programmatic API approach. First the DataSource provider:

Like in the previous demo this class pulls datasource configuration from an external file that loos like this

Now that we've covered the basic plumbing we can move to the Main class where we'll see how instances of SqlSessionFactory and SqlSession are created and used.

Here we can appreciate the following steps:

  1. Connecting to the Oracle Autonomous Database instance using the DataSource factory shown earlier. Notice that the wallet's location must be set before this step.
  2. The SqlSessionFactory instance is created using the datasource as input. Note that the TodoMapper must be explicitly specified otherwise we'll miss its mapping capabilities.
  3. MyBatis does not offer DDL capabilities but we can use standard JDBC for that. The code for the DatabaseBootstrap will be shown next.
  4. At this point, a sample dataset is added to the database, with just 3 todo items. Here we see the first instance of SqlSession in use. The try-with-resources block comes in handy when using this type as it implements the AutoCloseable interface.
  5. Finally, we see the rest of the CRUD operations in action, by updating, delete, querying and delete some more items.


The last piece of code to be shown is the DatabaseBootstrap class

Running the application results in the following output

We just scratched the surface of what MyBatis has to offer. I encourage you to look at their documentation should you found this library interesting for your work. and if you happen to be a MyBatis user already, well connecting to an Oracle Autonomous Database and interacting with it using MyBatis is no different from what you're used to.

Image by Miroslav Porochnavy

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.