SQL Databases

Overview

Relational Database Management Systems (RDMBS)that use Structured Query Language (SQL) are ubiquitous and today’s software environment. They are a critical part of data input/output and storage for almost any complex system.

Java provides an abstraction layer, Java Database Connectivity (JDBC), that provides generic access to SQL RDBMS’s. While powerful, well-documented, and high-performance, the JDBC API is unfortunately fairly complex, procedural, and difficult to use for common tasks.

Partner, therefore, provides another abstraction layer atop JDBC that simplifies configuration and coding for most common tasks. Further, Partner distributes a number of JDBC drivers as Partner Platform Modules to support those databases common to our customers.

In Partner v4, the SQL framework lives in the core platform package com.partnersoft.sql. In v5, it lives in the Kilt framework module, under com.partnersoft.v5.kilt.core.sql and ``com.partnersoft.v4.kilt.javase.sql. Individual database drivers are distributed in modules with the name pattern *JDBCDriver.

Major Categories

SQL databases can be broadly divided into two major categories:

  • file databases, and
  • server databases.

File databases, also called embedded databases, do not run outside the program’s process. SQL commands to these databases work directly on the database structure using an API without being transferred over the network. They are the simplest to configure, generally requiring only a type and a file path.

Server databases run separately, on the same machine or another. Generally your Java process communicates with the server over TCP/IP network connections. These are more complex to configure, since you must generally supply IP address, database name, and user credentials.

Connection Configuration

You configure connections in code via an SqlDatabase object. This represents the database. There are GUIs in various parts of the software for these parameters, but you can also supply them in code:

  • type - the type of database
  • server - IP or DNS name of the database server
  • database - name or instance of the database server
  • user - user name portion of credentials
  • password - password portion of credentials
  • path - path to file database
  • properties - additional JDBC properties to append to connection string
  • url - specific JDBC URL to use for unsupported database types
  • driver - specific JDBC driver to use for unsupported database types

File Databases

File databases typically only require the following properties:

  • type
  • path

Server Databases

Server databases typically require the following properties:

  • type
  • server
  • database
  • user
  • password

Unsupported Databases

Databases that Partner has not provided drivers and built-in support for are of type “Other” and you must fill in the following properties:

  • type
  • url
  • driver

You must also create a module and add the appropriate JDBC driver.

This may also be a solution for database types that are supported, but that (for whatever reason) the default Partner configuration style doesn’t work for.

Database Connection and Data Access

An SqlDatabaseConnection provides the Partner wrapper around a java.sql.Connection object. You can create a new connection from an SqlDatabase using simply:

SqlDatabaseConnection connection = database.connect();

Consult the JavaDoc API in the development reference for thorough documentation, but here are three standard usage patterns:

Opening a Connection

import com.partnersoft.sql.SqlDatabase;
import com.partnersoft.sql.SqlDatabaseConnection;

// database configuration
SqlDatabase database = new SqlDatabase();
database.setType("SQLite");
database.setPath("data/MyModule/MyDatabase.db");

// open connection
SqlDatabaseConnection connection = database.connect();

Reading Data

We read data via an SqlDataRecordSource.

Fully correct Java should use the try-with-resources pattern. This ensures the SqlDataRecordSource is always closed.

import com.partnersoft.sql.SqlDataRecordSource;
import com.partnersoft.data.Naming;

try (SqlDataRecordSource source = connection.query("select * from foo")) {
    for (Naming record : source) {
        log.info(record);
        log.info("Field bar = " + record.get("bar"));
    }
}

Groovy may not support this, so a simpler pattern looks like:

import com.partnersoft.sql.SqlDataRecordSource;
import com.partnersoft.data.Naming;

SqlDataRecordSource source = null;
try {
    source = connection.query("select * from foo")) {
    for (Naming record : source) {
        log.info(record);
        log.info("Field bar = " + record.get("bar"));
    }
}
finally {
    source.close();
}

For minor things you can sloppily write in Groovy:

source = connection.query("select * from foo");
for (record in source) { // note use of IN keyword here
    log.info(record);
    log.info("Field bar = " + record.get("bar"));
}
source.close();

However this does not ensure that your source closes, which may cause for connections to pile up.

Executing SQL Commands

Executing commands such as DELETE, INSERT, or UPDATE is simple and based on convenience methods in the SqlDatabaseConnection object itself.

You may simply issue a String command like so:

connection.execute("delete from foo where bar='DeleteMe'");

However if you are including parameters it is recommended that you use the prepared statement style like so:

connection.execute("insert into foo set bar=?, baz=?", barValue, bazValue);

This takes care of type conversions etc. Simply supply the parameter values in the same order as their corresponding ? signs.

Specific Examples by Database Type

These are specific examples for various database types. The code will work equally well in Java or Groovy environments.

ODBC config

If you can’t find a more specific driver, ODBC is often a good fallback. It is also the best choice when (for security or other reasons) you must configure access via an ODBC connection in the user’s account.

The database property should match the name you configure in your ODBC settings. You shouldn’t need a user name or password or server.

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("ODBC");
database.setDatabase("SomeDatabase");

Microsoft SQL Server config

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("SQL Server");
database.setServer("10.0.0.1");
database.setName("SomeDatabase");
database.setUser("partner");
database.setPassword("secretmagicword");

Oracle config

Note that instances of Oracle are generally referred to as “SID”. We use the database property to store this.

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database(new SqlDatabase();
database.setType("Oracle");
database.setServer("10.0.0.1:1521"); // 1521 is usually the port number.
database.setName("SomeDatabase");    // a.k.a. SID
database.setUser("partner");
database.setPassword("secretmagicword");

MySQL config

The MySQL type is deprecated, and Partner now utilizes the MariaDB flavor of that database. However you may see this in older installations or use it for your own MySQL servers. Generally, however, the drivers are equivalent - Partner only ships the MariaDB JDBC driver now.

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("MySQL");
database.setServer("10.0.0.1");
database.setName("SomeDatabase");
database.setUser("partner");
database.setPassword("secretmagicword");

MariaDB config

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("MariaDB");
database.setServer("10.0.0.1");
database.setName("SomeDatabase");
database.setUser("partner");
database.setPassword("secretmagicword");

AS400 config

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("AS400");
database.setServer("10.0.0.1");
database.setName("SomeDatabase");
database.setUser("partner");
database.setPassword("secretmagicword");

Microsoft Access config

Microsoft Access is a file-type database. Generally you do not need to specify a database server, name, user, or password.

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("Access");
database.setPath("c:/BigPileOfDatabases/SomeAccess.mdb");

SQLite config

SQLite is built into the core platform and works on all operating systems. It is a good default database for embedded use.

import com.partnersoft.sql.SqlDatabase;

SqlDatabase database = new SqlDatabase();
database.setType("SQLite");
database.setPath("data/MyModule/MyDatabase.db");