Thursday, 4 September 2014

Variable in list with Postgres JDBC and Greenplum

I previously blogged on how to create a variable JDBC IN list with Oracle. Here is how you would do it with Pivotal Greenplum. Much easier , without having to write a function. In the Greenplum demo below we use the any function combined with string_to_array

http://theblasfrompas.blogspot.com.au/2008/02/variable-in-list-with-oracle-jdbc-and.html

Code as follows

  
import java.sql.*;
import java.sql.DriverManager;

/**
 * Created by papicella on 4/09/2014.
 */
public class VariableInListGreenplum
{

    public VariableInListGreenplum()
    {
    }

    private Connection getConnection() throws SQLException, ClassNotFoundException
    {
        Class.forName("org.postgresql.Driver");
        Connection conn = null;
        conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/apples","pas", "pas");

        return conn;
    }

    public void run() throws SQLException
    {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rset = null;
        String queryInList =
                        "SELECT DEPTNO, " +
                        "       DNAME, " +
                        "       LOC " +
                        "FROM   scott.DEPT " +
                        "WHERE DEPTNO = any(string_to_array(?,', ')) ";

        try
        {
            conn = getConnection();
            stmt = conn.prepareStatement(queryInList);
            stmt.setString(1, "10, 20, 30");
            rset = stmt.executeQuery();

            while (rset.next())
            {
                System.out.println("Dept [" + rset.getInt(1) + ", " +
                                              rset.getString(2) + "]");
            }
        }
        catch (Exception e)
        {
            System.out.println("Exception occurred");
            e.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                conn.close();
            }

            if (stmt != null)
            {
                stmt.close();
            }

            if (rset != null)
            {
                rset.close();
            }
        }
    }

    public static void main(String[] args) throws Exception
    {
        VariableInListGreenplum test = new VariableInListGreenplum();
        test.run();
    }
}

No comments: