The only data structure recognized in traditional SQL databases was the row, and repeating groups were extracted into child rows linked through foreign keys. It worked well but could be somewhat artificial and cumbersome for programmers. With more powerful processors and plentiful memory, today’s SQL DMBSs have shrugged off those limitations. Modern SQL DBMSs implement the concepts of data structures and of arrays — making the Java programmer’s job much easier.
Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery. Download Now.
How one uses these features in Java is not necessarily intuitive, but it is reasonably easy to learn. The JDBC API has introduced two classes embodying the new concepts, java.sql.Struct and java.sql.Array, to enable them to be accessed in Java. Through the DBMS’s JDBC driver’s API methods, these classes can be initialized, accessed, and manipulated. Otherwise, JDBC use remains the same.
As with most of what we programmers do, it is much easier to show than to tell. The telling is in the Java API Documentation. For the showing, we have written a thoroughly commented example program, below that demonstrates the use of SQL Java Structs and Arrays. Enjoy!
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.List;
/*
*
CREATE OR REPLACE TYPE address AS OBJECT {
mnemonic varchar2(10),
street varchar2(30),
street2 varchar2(30),
city varchar2(20),
state varchar2(3),
zip varchar2(10)
}
*/
/* The table definition used in the examples
*
Column | Type | Modifiers
-----------+-----------------------------+-----------
empno | numeric(4,0) | not null
ename | character varying(10) |
job | character varying(9) |
mgr | numeric(4,0) |
hiredate | timestamp without time zone |
sal | numeric(7,2) |
comm | numeric(7,2) |
deptno | numeric(2,0) |
addresses | address[] |
*/
/* Sample output
*
Addresses in: {
"(Home,416 Lincoln Road,,Franklin,MA,02038)",
"(Work,3321 Elm Street,Suite 374A,Sudbury,MA,01976)"}
Addresses out:
mnemonic = Home
street = 416 Lincoln Road
street2 =
city = Franklin
state = MA
zip = 02038
mnemonic = Work
street = 3321 Elm Street
street2 = Suite 374A
city = Sudbury
state = MA
zip = 01976
*/
/**
* Sample program using JDBC with SQL Structs and Arrays on PPAS 9.4
*
* @author ddowdell
*
*/
public class PPASStructsAndArrays {
/*
* Connection constants
*/
private static final String DB_URL = "jdbc:edb://windows10ppas94:5444/edb";
private static final String DB_USER = "enterprisedb";
private static final String DB_PASSWORD = "edb";
/*
* Employee number constant
*/
private static final int EMPNO = 7369;
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String update = "UPDATE EMP SET ADDRESSES = ? WHERE EMPNO = ?";
String query = "SELECT ADDRESSES FROM EMP WHERE EMPNO = ?";
List<Object> addressList = new ArrayList<>();
Struct address = null;
Array addresses = null;
try {
/*
* Register and establish connection to server
*/
Class.forName("com.edb.Driver");
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
/*
* Setup and load array of addresses
*/
address = conn.createStruct("address", new Object[] { "Home",
"416 Lincoln Road", "", "Franklin", "MA", "02038" });
addressList.add(address);
address = conn
.createStruct("address", new Object[] { "Work",
"3321 Elm Street", "Suite 374A", "Sudbury", "MA",
"01976" }); // Create an SQL Struct for an address
// type
addressList.add(address);
/*
* Create an SQL Array object as a parameter
*/
addresses = conn.createArrayOf("address", addressList.toArray());
System.out.println("\nAddresses in: " + addresses);
/*
* Setup and execute prepared SQL UPDATE statement
*/
stmt = conn.prepareStatement(update);
stmt.setArray(1, addresses);
stmt.setInt(2, EMPNO);
int rc = stmt.executeUpdate();
stmt.close();
/*
* Setup and execute prepared SQL SELECT statement
*/
stmt = conn.prepareStatement(query);
stmt.setInt(1, EMPNO);
rs = stmt.executeQuery();
System.out.println("\nAddresses out:");
/*
* Iterate through the query result set (should be only one result)
*/
while (rs.next()) {
addresses = rs.getArray("addresses"); // Get SQL Array from
// result
Struct[] results = (Struct[]) addresses.getArray(); // Get array
// of SQL
// Structs
// from SQL
// Array
/*
* Iterate through the array of address SQL Structs
*/
for (Struct addr : results) {
Object[] attrs = addr.getAttributes(); // Get individual
// fields from
// address
String[] labels = new String[] { " mnemonic",
" street", " street2", " city", " state",
" zip" }; // Setup field labels
System.out.println();
/*
* Iterate through address fields
*/
for (int idx = 0; idx < attrs.length; idx++) {
System.out.println(" " + labels[idx] + " = "
+ attrs[idx].toString()); // Print out
// individual field
}
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
/*
* Clean up JDBC artifacts
*/
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("\n\nDone");
}
}
To get more information on Postgres, please contact us.
Dick Dowdell is a Senior Architect, xDB Replication Server, at EnterpriseDB.