Passing Arrays to a PostgreSQL PL/pgSQL Function

It can be handy to pass a collection of strings to a PL/pgSQL stored function via a PostgreSQL array. This is generally a very easy thing to accomplish, but this post demonstrates a couple of nuances to be aware of when passing an array to a PL/pgSQL function from JDBC or psql.

The next code listing is for a contrived PL/pgSQL stored function that will be used in this post. This function accepts an array of text variables, loops over them based on array length, and reports these strings via the PL/pgSQL RAISE statement.

printStrings.sql

CREATE OR REPLACE FUNCTION printStrings(strings text[]) RETURNS void AS $printStrings$
DECLARE
   number_strings integer := array_length(strings, 1);
   string_index integer := 1;
BEGIN
   WHILE string_index <= number_strings LOOP
      RAISE NOTICE '%', strings[string_index];
      string_index = string_index + 1;
   END LOOP;
END;
$printStrings$ LANGUAGE plpgsql;

The above PL/pgSQL code in file printStrings.sql can executed in psql with \ir as shown in the next screen snapshot.

The syntax for invoking a PL/pgSQL stored function with an array as an argument is described in the section "Array Value Input" in the PostgreSQL Arrays documentation. This documentation explains that "general format of an array constant" is '{ val1 delim val2 delim ... }' where delim is a delimited of comma (,) in most cases. The same documentation shows an example: '{{1,2,3},{4,5,6},{7,8,9}}'. This example provides three arrays of integral numbers with three integral numbers in each array.

The array literal syntax just shown is straightforward to use with numeric types such as the integers in the example shown. However, for strings, there is a need to escape the quotes around the strings because there are already quotes around the entire array ('{}'). This escaping is accomplished by surrounding each string in the array with two single quotes on each side. For example, to invoke the stored function just shown on the three strings "Inspired", "Actual", and "Events", the following syntax can be used in psql: SELECT printstrings('{''Inspired'', ''Actual'', ''Events''}'); as shown in the next screen snapshot.

Arrays can be passed to PL/pgSQL functions from Java code as well. This provides an easy approach for passing Java collections to PL/pgSQL functions. The following Java code snippet demonstrates how to call the stored function shown earlier with JDBC. Because this stored function returns void (it's more like a stored procedure), the JDBC code does not need to invoke any CallableStatement's overridden registerOutParameter() methods.

JDBC Code Invoking Stored Function with Java Array

final CallableStatement callable =
   connection.prepareCall("{ call printstrings ( ? ) }");
final String[] strings = {"Inspired", "Actual", "Events"};
final Array stringsArray = connection.createArrayOf("varchar", strings);
callable.setArray(1, stringsArray);
callable.execute();
callable.close();

Java applications often work more with Java collections than with arrays, but fortunately Collection provides the toArray(T[]) for easily getting an array representation of a collection. For example, the next code listing is adapted from the previous code listing, but works against an ArrayList rather than an array.

JDBC Code Invoking Stored Function with Java Collection

final CallableStatement callable =
   connection.prepareCall("{ call printstrings ( ? ) }");
final ArrayList<String> strings = new ArrayList<>();
strings.add("Inspired");
strings.add("Actual");
strings.add("Events");
final Array stringsArray =
   connection.createArrayOf(
      "varchar",
      strings.toArray(new String[strings.size()]));
callable.setArray(1, stringsArray);
callable.execute();
callable.close();

Conclusion

The ability to pass an array as a parameter to a PostgreSQL PL/pgSQL stored function is a straightforward process. This post specifically demonstrated passing an array of strings (including proper escaping) to a PL/pgSQL stored function from psql and passing an array of Strings to a PL/pgSQL stored function from JDBC using java.sql.Array and Connection.createArrayOf(String, Object[]).

Better JDBC With Groovy Sql

I don't mind using JDBC directly in many of the Java applications I work with, especially when using it in conjunction with Spring's JDBC support or with a simple SQL mapping framework like MyBatis. Groovy makes using JDBC particularly pleasant. I appreciate the combination of powerful flexibility of direct SQL with the ease and convenience of Groovy's treatment of JDBC. In this post, I look at various helpful aspects of Groovy's Sql class.

I looked at a basic and common use of Groovy's Sql class in the post GroovySql: Groovy JDBC. I began that post by looking at using Groovy's basic result set processing closure (Sql.eachRow) as an example of the Read/Retrieve portion of CRUD. I then demonstrated simple examples of CRUD operations Create and Delete using Sql.execute(String) and Sql.execute(String, List). Groovy's Sql class provides significantly more functionality than this and I demonstrate some of those methods here.

For convenience, the following Groovy code listing demonstrates accessing an instance of Sql and using it to query the Oracle hr/hr sample schema and process the result set. The example includes usage of rootLoader to avoid needing to provide the JDBC driver on the script's classpath explicitly.

Beginning of demoGroovySql.groovy Script

#!/usr/bin/env groovy

// Add JDBC driver to classpath as part of this script's bootstrapping.
// See http://marxsoftware.blogspot.com/2011/02/groovy-scripts-master-their-own.html.
// This location needs to be adjusted for specific user environment.
this.class.classLoader.rootLoader.addURL(
   new URL("file:///C:/app/Dustin/product/11.1.0/db_1/jdbc/lib/ojdbc6.jar"))


// Get instance of Groovy's Sql class
// See http://marxsoftware.blogspot.com/2009/05/groovysql-groovy-jdbc.html
import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                          "oracle.jdbc.pool.OracleDataSource")

// iterate over query's result set and "process" each row by printing two names
sql.eachRow("SELECT employee_id, last_name, first_name FROM employees")
{
   println "Employee ${it.first_name} ${it.last_name} has ID of ${it.employee_id}."
}

Although the overloaded Sql.eachRow methods are often exactly what's needed to iterate over a result set and apply a closure to each row of the result set, this is not the only Groovy approach to querying the database. Another approach is to instruct Groovy to simply access the first row of the underlying result set. This is particularly useful when it is known that only one result will be returned, which is case in the frequent situation of querying based on the primary (unique) key. The next example demonstrates using Sql.firstRow(String, Object[]) to get a single row out of a result set.

Using Sql.firstRow(String, Object[])

// when you know there's only one match, firstRow can be particularly useful
def employee140 = sql.firstRow("SELECT employee_id, last_name, first_name FROM employees where employee_id = ?", [140])
println "Employee 140 is ${employee140.first_name} ${employee140.last_name}"

As demonstrated in the above code listing, there is no need to use a closure or its implicit it variable because there is only a single object returned from Sql.firstRow. This object's properties are accessed by the same names as the underlying database columns. The few lines of the above example perform the query, extract the first row in the result set, and provide easy access to that row's values. All too easy.

There may be times when one needs direct access to the result set from the database query. In such cases, use of one of the overloaded Sql.query methods is apropos. In other situations, it might be most convenient to have the rows of the result set as a collection of these rows. This structure is obtained by invoking one of the overloaded Sql.rows methods. This might be preferred in situations where traditional Java iteration is preferred over use of the Sql.eachRow's closures.

The Groovy Sql class provides several approaches for inserting (creating) data (the "c" in CRUD). One such method is the Sql.executeInsert(String) method. This method's documentation states that this method "executes the given SQL statement (typically an INSERT statement)" and adds, "Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field."

The next Groovy code snippet shows use of the Sql.executeInsert(String) method. A screenshot is shown after the code listing which shows the results of running this code. As that output shows, the returned ID in this case is an Oracle ROWID. The ROWID is shown in both of the lines output with a println invocation.

// insert new employee with Sql.executeInsert
def insertStr =
"""insert into employees
   (employee_id, first_name, last_name, email, hire_date, job_id)
  values
   (employees_seq.nextval, 'Dustin', 'Marx', 'dustin@marxiansw.com',
    to_date('2011/02/28', 'yyyy/mm/dd'), 'IT_PROG')"""
def insertedEmployees = sql.executeInsert(insertStr)
println insertedEmployees.dump()
def insertedEmployeeId = insertedEmployees[0][0].toJdbc()
println "Employee ${insertedEmployeeId} added."

The Groovy Sql class supports a more traditional insertion via its Sql.execute(String) method (which can also be used for running DDL and other types of DML statements). The next code listing demonstrates this.

// insert new employee with Sql.execute
def insertStr =
"""insert into employees
   (employee_id, first_name, last_name, email, hire_date, job_id)
  values
   (employees_seq.nextval, 'Dustin', 'Marx', 'dustin@marxiansw.com',
    to_date('2011/02/28', 'yyyy/mm/dd'), 'IT_PROG')"""
def employeeInserted = sql.execute(insertStr)

Note that the same string with SQL insert command was used in both cases and it was simply a matter of which Sql method was desired. It is important to also note that there are overloaded versions of these methods that allow parameters to be passed. This is usually a better approach because it provides the advantages of using a PreparedStatement. I showed use of the Sql.execute method with parameters for deletion and for insertion in my post GroovySql: Groovy JDBC.

The Sql.executeUpdate(String) [and its overloaded versions] are handy to use when one wants to check the returned integer to know how many rows were updated. This is demonstrated in the next Groovy code listing.

// update the previously added employee
def updateStr =
"""update employees set email='dustin@marxblog.com'
   where employees.first_name='Dustin' and employees.last_name='Marx'"""
def numberRowsUpdated = sql.executeUpdate(updateStr)
println "${numberRowsUpdated} row(s) was/were updated."

Stored procedures and stored functions can also be executed using Groovy's Sql class. The next Groovy code listing demonstrates this in calling the Oracle supplied (built-in) function DBMS_UTILITY.GET_TIME().

// invoke PL/SQL stored procedure with Sql.call
def storedProcCall =
   """{? = call dbms_utility.get_time()}"""
sql.call(storedProcCall, [Sql.INTEGER])
{
   println "DBMS_UTILITY.GET_TIME(): ${it}"
}

The above example demonstrates calling an Oracle function and it demonstrates supplying Sql.INTEGER as an OutParameter.

Additional details regarding Groovy's database support can be found in the groovy.sql.Sql documentation (a nice example of how Javadoc documentation can provide examples of how to use the class), in the Database Features section of the Groovy User Guide, and in Practically Groovy: JDBC Programming with Groovy.

I find myself frequently accessing databases with my Groovy scripts. Database access has always been relatively straightforward in Java via JDBC, but Groovy makes this database access even more concise and fluent.

JDBC on DZone in May/June 2010

Several useful features related to JDBC have been posted on DZone/JavaLobby recently. I briefly reference these here.

JDBC Best Practices: Latest DZone RefCardz

The latest DZone Refcardz to be released is the JDBC Best Practices Refcardz. The DZone overview of this Refcardz states the following:

JDBC Best Practices has something for every developer. This DZone Refcard starts off with JDBC basics including descriptions for each of the 5 Driver Types. This is followed up with some advanced JDBC on Codeless Configuration, Single Sign-on with Kerberos, Debugging and Logging. We even threw in a SQL Quick Reference to round things off.

The ABCs of JDBC

Daniel Rubio has posted a series of articles with the title "The ABCs of JDBC." So far, they are:

• The ABCs of JDBC, Part 1 - Getting Started with JDBC
• The ABCs of JDBC, Part 2 - ResultSets
• The ABCs of JDBC, Part 3 - The JDBC API
• The ABCs of JDBC, Part 4 - Stored Procedures

New Challenges in JDBC Usage Patterns
(Added 14 June 2010)

New Challenges in JDBC Usage Patterns is a video that, according to the DZone link, features "Jesse Davis [giving] us an update on what's happening at the JDBC expert group in the JCP."


Conclusion

I am happy to see that I am not the only one that still sees advantages to learning and using JDBC even in these days of NoSQL and ORM popularity. As I wrote in SQLPhobia: The Irrational Fear of SQL, JDBC can be the appropriate solution in some cases. I especially like how easy it is to use JDBC with the Spring Framework.

GroovySql: Groovy JDBC

I have long felt that JDBC is one of the most underrated reliable work horses of the Java world. Although JDBC has proven its value many times over and, in my opinion, ranks up there with servlets and JMS in terms of time-proven worth, JDBC does have its drawbacks. I have written before regarding how the Spring Framework addresses many of the most common JDBC disadvantages. In this blog posting, I will address how Groovy similarly addresses drawbacks of JDBC.

As I wrote about in the article Add Some Spring to Your Oracle JDBC, JDBC can be somewhat tedious to work with due to its single primary checked exception (including the necessity to have nested try/catch blocks) and to other required boilerplate code that the Spring Framework largely covers via its JDBC template. Groovy similarly reduces the amount of boilerplate code required to work with JDBC.

Besides dealing with the handling of an SQLException (or one of the child exceptions introduced in Java SE 6 such as SQLRecoverableException), another task one commonly has had to perform when using JDBC to retrieve data is to iterate over the returned ResultSet. Groovy does much of this for us as shown in the next code listing.

getEmployees.groovy

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.eachRow("SELECT employee_id, last_name, first_name FROM employees")
{
   println "The employee's name is ${it.first_name} ${it.last_name}."
}

As with all Groovy code samples in this blog post, I am using Groovy more as a scripting language and less as an application development language. In other words, I'm not bothering with making Groovy classes.

The getEmployees.groovy script listed above uses Sql from the groovy.sql package. The Sql.newInstance method is has several overloaded versions and the version accepting a JDBC connection URL, username String, password String, and JDBC driver String is the version used in this example (as well as all other examples in this particular post). In this post's examples, the database is an Oracle database, but any database with a compliant JDBC driver could be used.

When the above code is run, the expected output sentence with each employee's full name is printed.

It is also easy to insert data into the database with GroovySql. The insertion of a new job row in the JOBS table of Oracle's HR schema is shown next.

insertJob.groovy

jobId = "QU_OPER"
jobTitle = "Quarry Operator"
minSalary = 1
maxSalary = 5
import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.execute(
   """INSERT INTO jobs(job_id, job_title, min_salary, max_salary)
   VALUES(${jobId}, ${jobTitle}, ${minSalary}, ${maxSalary})""")

The above example inserts the Quarry Operator position into the database. The values to be inserted are included directly within the Groovy strings using the ${} notation. In the next code listing, I again insert data into the database, but this time use a PreparedStatement approach to insert a new employee who happens to have this new job position of Quarry Operator.

insertEmployee.groovy

employeeId = 2000
firstName = "Fred"
lastName = "Flintstone"
eMail = "fred@slaterockngravel.com"
jobId = "QU_OPER"
hireDate = new java.sql.Date(System.currentTimeMillis())
import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.execute(
   """INSERT INTO employees(employee_id, first_name, last_name, email, job_id, hire_date)
   VALUES(?, ?, ?, ?, ?, ?)""", [employeeId, firstName, lastName, eMail, jobId, hireDate])

The insertion of the employee looked very similar to the insertion of the new job, but used the often preferred PreparedStatement approach instead.

Deleting a row from the table with Groovy is also easy. The following Groovy script removes both rows (job and employee) just added in this post's examples.

removeJobAndEmployee.groovy

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.execute("DELETE FROM employees WHERE employee_id = 2000")
sql.execute("DELETE FROM jobs WHERE job_id = ?", ["QU_OPER"])

Because each of the example scripts in this blog post ended with the extension .groovy, they can all be run simply by running "groovy <<scriptName>>". Of course, the particular JDBC driver class for your database of choice (in this case class oracle.jdbc.pool.OracleDataSource in JAR ojdbc6.jar) must also be on the classpath and can be included with the normal -cp option.

Finally, if you have a typo or other database exception, a rather terse message will be printed. To see the full stack trace, using Groovy's -d option. If you are running on Java SE 6, you are likely to see some of the new exceptions that extend SQLException in such exceptional cases.


Conclusion

Groovy can be used within applications that use JDBC, but I especially like it for writing simple tools and scripts that need JDBC. It is also a very rapid method for testing out newly written SQL statements against one's database. Groovy makes an already outstanding technology (JDBC) even easier to use.