Object Relational Mapping

Object-relational mapping (ORM) is an application (library) to map your objects to a relational database; typically, you describe which classes and properties in the code map to which tables and columns in the database, and then the library does all the copying and translating. In this scenario, ORM sits between JDBC and the rest of your Java application.

Sql2o

Sql2o is a small java library, with the purpose of making database interaction easy. To use Sql2o, you must add it as a dependency to your project. Open gradle.build and add the following line to the dependencies block.

implementation 'org.sql2o:sql2o:1.6.0'

Open the Gradle tool window and refresh the Gradle project.

Let's update the Demo program:

+import org.sql2o.Connection;
+import org.sql2o.Sql2o;
+import org.sql2o.Sql2oException;

 import java.net.URI;
 import java.net.URISyntaxException;
-import java.sql.*;
 
 public class Demo {
-  public static void main(String[] args) throws SQLException {
+  public static void main(String[] args) {
     try (Connection conn = getConnection()) {
 
       String sql = "CREATE TABLE IF NOT EXISTS Courses(" +
                       "offeringName VARCHAR(15) NOT NULL PRIMARY KEY," +
                       "title VARCHAR(30) NOT NULL" +
                     ");";
-      Statement st = conn.createStatement();
-      st.execute(sql);
 
-      sql = "INSERT INTO Courses (offeringName, title)" +
-          "VALUES ('EN.601.226', 'Data Structures');";
-      st.execute(sql);
+      conn.createQuery(sql).executeUpdate();
+
+      Course course = new Course("EN.601.280", "Full-Stack JavaScript");
+      add(conn, course);
 
-    } catch (URISyntaxException | SQLException e) {
+    } catch (URISyntaxException | Sql2oException e) {
       e.printStackTrace();
     }
   }
 
-  private static Connection getConnection() throws URISyntaxException, SQLException {
+  private static Connection getConnection() throws URISyntaxException, Sql2oException {
     String databaseUrl = System.getenv("DATABASE_URL");
     if (databaseUrl == null) {
       throw new URISyntaxException(databaseUrl, "DATABASE_URL is not set");
     }

     URI dbUri = new URI(databaseUrl);

     String username = dbUri.getUserInfo().split(":")[0];
     String password = dbUri.getUserInfo().split(":")[1];
     String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':'
         + dbUri.getPort() + dbUri.getPath() + "?sslmode=require";
 
-    return DriverManager.getConnection(dbUrl, username, password);
+    Sql2o sql2o = new Sql2o(dbUrl, username, password);
+    return sql2o.open();
   }
 }

Notice the main method calls add to add a course. Here is the implementation of add:

private static void add(Connection conn, Course course) throws Sql2oException {
  String sql = "INSERT INTO courses(offeringName, title) VALUES(:offeringName, :title);";
  conn.createQuery(sql)
      .bind(course)
      .executeUpdate();
}

Notice the SQL statement in the add method is a parametrized statement; the :offeringName and :title are parameters. Their values are read from the state of the course object. Notice the course object is bound to the query statement before execution. The advantage of Sql2o is in facilitating the binding to an object. For binding to work seamlessly, you need to have the same name for fields in the object as the column names in your table. If fields' name are not the same as column names, you may use column mapping feature of Sql2o.