<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class="">Seeing you comment that sql needs to be just one line, I just had to throw in my two cents. Admittedly untested, but still:</div><div class=""><br class=""></div><div class=""><pre style="background-color: rgb(43, 43, 43);" class=""><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">import </span><font color="#a9b7c6" face="Menlo" class="">java.sql.Connection</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">import </span><font color="#a9b7c6" face="Menlo" class="">java.sql.DriverManager</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">import </span><font color="#a9b7c6" face="Menlo" class="">java.sql.ResultSet</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">import </span><font color="#a9b7c6" face="Menlo" class="">java.sql.Statement</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""><br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">public class </span><font color="#a9b7c6" face="Menlo" class="">DatabaseTest {<br class=""><br class=""> </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">public </span><font color="#a9b7c6" face="Menlo" class="">Connection </font><span style="color: rgb(255, 198, 109); font-family: Menlo;" class="">getConnection</span><font color="#a9b7c6" face="Menlo" class="">() </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">throws </span><font color="#a9b7c6" face="Menlo" class="">Exception {<br class=""> String driver = </font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"org.postgresql.Driver"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">String url = </font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"jdbc:postgresql://myserver/mydb"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">String user = </font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"user"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">String password = </font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"password"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">Class.</font><span style="color: rgb(169, 183, 198); font-family: Menlo; font-style: italic;" class="">forName</span><font color="#a9b7c6" face="Menlo" class="">(driver)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> return </span><font color="#a9b7c6" face="Menlo" class="">DriverManager.</font><span style="color: rgb(169, 183, 198); font-family: Menlo; font-style: italic;" class="">getConnection</span><font color="#a9b7c6" face="Menlo" class="">(url</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">, </span><font color="#a9b7c6" face="Menlo" class="">user</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">, </span><font color="#a9b7c6" face="Menlo" class="">password)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">}<br class=""><br class=""> </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">public void </span><span style="color: rgb(255, 198, 109); font-family: Menlo;" class="">getData</span><font color="#a9b7c6" face="Menlo" class="">() {<br class=""><br class=""> </font><span style="color: rgb(128, 128, 128); font-family: Menlo;" class="">// Using a stringbuffer adds a bit of readability<br class=""></span><font color="#808080" face="Menlo" class=""> // Using syntactic sugar ”with" expressions takes it one more step<br class=""></font><span style="color: rgb(128, 128, 128); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">StringBuffer sql = </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">new </span><font color="#a9b7c6" face="Menlo" class="">StringBuffer()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> with f as ("</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> SELECT 'Feature' As type,"</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> ST_AsGeoJSON(lg.geog)::json As geometry,"</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> row_to_json((ani_id, name, lat, lon, animalpref)) As properties"</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> FROM animals "</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> ), "</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> fc as ("</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM f "</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> ) "</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">sql.append(</font><span style="color: rgb(106, 135, 89); font-family: Menlo;" class="">"</span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">\n</span><span style="color: rgb(106, 135, 89); font-family: Menlo;" class=""> SELECT row_to_json(fc) FROM fc"</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""><br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">System.</font><span style="color: rgb(152, 118, 170); font-family: Menlo; font-style: italic;" class="">out</span><font color="#a9b7c6" face="Menlo" class="">.println(sql.toString())</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""><br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><span style="color: rgb(128, 128, 128); font-family: Menlo;" class="">// Execute SQL and clean up resources afterwards<br class=""></span><span style="color: rgb(128, 128, 128); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">Connection con = </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">Statement stmt = </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">ResultSet rs = </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">String json = </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> try </span><font color="#a9b7c6" face="Menlo" class="">{<br class=""> con = getConnection()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">stmt = con.createStatement()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">rs = stmt.executeQuery(sql.toString())</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> if</span><font color="#a9b7c6" face="Menlo" class="">(rs.next()) {<br class=""> json = rs.getString(</font><span style="color: rgb(104, 151, 187); font-family: Menlo;" class="">1</span><font color="#a9b7c6" face="Menlo" class="">)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">}<br class=""> } </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">catch </span><font color="#a9b7c6" face="Menlo" class="">(Exception e) {<br class=""> e.printStackTrace()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">} </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">finally </span><font color="#a9b7c6" face="Menlo" class="">{<br class=""> </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">try </span><font color="#a9b7c6" face="Menlo" class="">{<br class=""> </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">if </span><font color="#a9b7c6" face="Menlo" class="">(rs != </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null</span><font color="#a9b7c6" face="Menlo" class="">)<br class=""> rs.close()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> if </span><font color="#a9b7c6" face="Menlo" class="">(stmt != </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null</span><font color="#a9b7c6" face="Menlo" class="">)<br class=""> stmt.close()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> if </span><font color="#a9b7c6" face="Menlo" class="">(con != </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">null</span><font color="#a9b7c6" face="Menlo" class="">)<br class=""> con.close()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">} </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">catch </span><font color="#a9b7c6" face="Menlo" class="">(Exception e) {}<br class=""> }<br class=""> System.</font><span style="color: rgb(152, 118, 170); font-family: Menlo; font-style: italic;" class="">out</span><font color="#a9b7c6" face="Menlo" class="">.println(json)</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">}<br class=""><br class=""> </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">public static void </span><span style="color: rgb(255, 198, 109); font-family: Menlo;" class="">main</span><font color="#a9b7c6" face="Menlo" class="">(String[] args) {<br class=""> DatabaseTest dt = </font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">new </span><font color="#a9b7c6" face="Menlo" class="">DatabaseTest()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">dt.getData()</font><span style="color: rgb(204, 120, 50); font-family: Menlo;" class="">;<br class=""></span><span style="color: rgb(204, 120, 50); font-family: Menlo;" class=""> </span><font color="#a9b7c6" face="Menlo" class="">}<br class=""><br class="">}<br class=""></font></pre><div class=""><br class=""></div></div><div class="">Cheers,</div><div class="">P-O</div><div class=""><br class=""></div><div class=""><br class=""></div><br class=""><div><blockquote type="cite" class=""><div class="">7 okt 2015 kl. 00:44 skrev Andrew Davis <<a href="mailto:drsockmonkee@gmail.com" class="">drsockmonkee@gmail.com</a>>:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">Thanks for the help everyone..<div class=""><br class=""></div><div class="">It works.. I have a little bit of polishing up but it is printing out the correct information.</div><div class=""> </div><div class="">The money portion ...</div><div class=""><br class=""></div><div class=""><div class="">Class.forName("org.postgresql.Driver");</div><div class=""><span class="" style="white-space:pre"> </span>con = DriverManager.getConnection(</div><div class=""><span class="" style="white-space:pre"> </span>"jdbc:postgresql://myserver/mydb", "user", "password");<span class="" style="white-space:pre"> </span></div><div class=""><span class="" style="white-space:pre"> </span></div><div class=""><span class="" style="white-space:pre"> </span>st = con.createStatement();</div><div class=""><span class="" style="white-space:pre"> //This has to be in a single line to work.</span></div><div class=""><span class="" style="white-space:pre"> </span>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;";</div><div class=""><span class="" style="white-space:pre"> </span></div><div class=""><span class="" style="white-space:pre"> </span></div></div><div class=""><div class="">rs = st.executeQuery(myQuery);</div><div class=""><span class="" style="white-space:pre"> </span></div><div class="">String returnValue = "";</div><div class=""><span class="" style="white-space:pre"> </span></div><div class="">while(rs.next()){</div><div class=""> String myResults = rs.getString(1);</div><div class=""><span style="white-space:pre" class=""> </span>returnValue = myResults;</div><div class="">}</div><div class=""><span class="" style="white-space:pre"> </span></div><div class="">System.out.println(returnValue);</div></div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">Andy</div></div><div class="gmail_extra"><br class=""><div class="gmail_quote">On Tue, Oct 6, 2015 at 3:21 PM, Andrew Davis <span dir="ltr" class=""><<a href="mailto:drsockmonkee@gmail.com" target="_blank" class="">drsockmonkee@gmail.com</a>></span> wrote:<br class=""><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p dir="ltr" class="">Thanks... i will</p><div class="HOEnZb"><div class="h5">
<div class="gmail_quote">On Oct 6, 2015 3:21 PM, "Walter Nordmann" <<a href="mailto:walter.nordmann@web.de" target="_blank" class="">walter.nordmann@web.de</a>> wrote:<br type="attribution" class=""><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">have a look at JDBC. thats's the right software library to use.<br class="">
<br class="">
Walter<br class="">
<br class="">
<br class="">
<br class="">
_______________________________________________<br class="">
postgis-users mailing list<br class="">
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank" class="">postgis-users@lists.osgeo.org</a><br class="">
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank" class="">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br class="">
</blockquote></div>
</div></div></blockquote></div><br class=""></div>
_______________________________________________<br class="">postgis-users mailing list<br class=""><a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class="">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</div></blockquote></div><br class=""></body></html>