[postgis-users] How do I handle a ResultSet from a java request?

[Per-Olof Norén]

Seeing you comment that sql needs to be just one line, I just had to throw in my two cents. Admittedly untested, but still:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DatabaseTest {

    public Connection getConnection() throws Exception {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://myserver/mydb";
        String user = "user";
        String password = "password";
        Class.forName(driver);
        return DriverManager.getConnection(url, user, password);
    }

    public void getData() {

        // Using a stringbuffer adds a bit of readability
        // Using syntactic sugar ”with" expressions takes it one more step
        StringBuffer sql = new StringBuffer();
        sql.append("\n with f as (");
        sql.append("\n   SELECT  'Feature' As type,");
        sql.append("\n           ST_AsGeoJSON(lg.geog)::json As geometry,");
        sql.append("\n           row_to_json((ani_id, name, lat, lon, animalpref)) As properties");
        sql.append("\n   FROM    animals ");
        sql.append("\n ), ");
        sql.append("\n fc as (");
        sql.append("\n   SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM f ");
        sql.append("\n )  ");
        sql.append("\n SELECT row_to_json(fc) FROM fc");

        System.out.println(sql.toString());

        // Execute SQL and clean up resources afterwards
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        String json = null;
        try {
            con = getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql.toString());
            if(rs.next()) {
                json = rs.getString(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e) {}
        }
        System.out.println(json);
    }

    public static void main(String[] args) {
        DatabaseTest dt = new DatabaseTest();
        dt.getData();
    }

}

Cheers,
P-O



> 7 okt 2015 kl. 00:44 skrev Andrew Davis <drsockmonkee at gmail.com>:
> 
> Thanks for the help everyone..
> 
> It works..  I have a little bit of polishing up but it is printing out the correct information.
>  
> The money portion ...
> 
> Class.forName("org.postgresql.Driver");
> 			con = DriverManager.getConnection(
> 					"jdbc:postgresql://myserver/mydb", "user", "password");								
> 			
> 			st = con.createStatement();
> 			//This has to be in a single line to work.
> 			String myQuery = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geog)::json As geometry, row_to_json((ani_id, name, lat, lon, animalpref)) As properties FROM animals As lg   ) As f )  As fc;";
> 			
> 			
> rs = st.executeQuery(myQuery);
> 			
> String returnValue = "";
> 			
> while(rs.next()){
>     String myResults = rs.getString(1);
>     returnValue = myResults;
> }
> 			
> System.out.println(returnValue);
> 
> 
> Andy
> 
> On Tue, Oct 6, 2015 at 3:21 PM, Andrew Davis <drsockmonkee at gmail.com <mailto:drsockmonkee at gmail.com>> wrote:
> Thanks...  i will
> 
> On Oct 6, 2015 3:21 PM, "Walter Nordmann" <walter.nordmann at web.de <mailto:walter.nordmann at web.de>> wrote:
> have a look at JDBC. thats's the right software library to use.
> 
> Walter
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151007/4e9becc3/attachment.html>