Using Java to Manipulate SQL Structures and Arrays

November 06, 2015

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!

Example Java Program:

 

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. ​

Share this

More Blogs

PostgreSQL 16 Update: Grouping Digits in SQL

One of the exciting new features in PostgreSQL 16 is the ability to group digits in numeric literals by separating them with underscores. This blog post covers the details.
October 17, 2023