Re: Right version of jdbc - Mailing list pgsql-general

I fix previous error what was my bad knowledge,

But new error occur which is related to postgres postgis jars.

If You are kind to answer me more;

Java code is :

public static boolean CheckIsNewInMushrooms(Connection connection, Point AddLocation, String AddDescription) {

boolean IsNew = true;

try {

String sqlQuery = "SELECT location, description FROM mushrooms";

try (PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery)) {

ResultSet resultSet = preparedStatement.executeQuery();

while (resultSet.next()) {

Point point = (Point) resultSet.getObject("location");

String description = resultSet.getString("description");

if (AddLocation.x == point.x && AddLocation.y == point.y && AddDescription.equals(description))

IsNew = false;

}

}

} catch (SQLException e) {

e.printStackTrace();

}

return IsNew;

}

and at line

Point point = (Point) resultSet.getObject("location");

java.lang.ClassCastException: org.postgresql.util.PGobject cannot be cast to org.postgis.Point
                at MushroomAPIs.ProcAddMushrooms.CheckIsNewInMushrooms(ProcAddMushrooms.java:45)

How to get Point from resultset ?

Is it related to postgis driver ?

Regards

Raivo 

On Sat, Sep 30, 2023 at 9:33 AM Raivo Rebane <raivore55@gmail.com> wrote:

Hi,

sometimes I am lucky and don't get the old error, but sometime not.

I tried to use PreparedStatement, but I got error -

org.postgresql.util.PSQLException: Can't use query methods that take a query string on a PreparedStatement.
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:145)
        at MushroomAPIs.Clean.deleteAllRecordsFromTable(Clean.java:34)

 and java code is -

String deleteQuery = "DELETE FROM " + tableNam

System.out.println(deleteQuery);

PreparedStatement statement = connection.prepareStatement(deleteQuery);

May be it's easy for me to use normal statement ?

Raivo

On Sat, Sep 30, 2023 at 8:27 AM Raivo Rebane <raivore55@gmail.com> wrote:

Sorry. I accidentally usin postgres 16, which was empty

Many thanks for advise !!!

Raivo

On Sat, Sep 30, 2023 at 8:18 AM Raivo Rebane <raivore55@gmail.com> wrote:

May be I have to copy PostGis draiver also to tomcat/lib ?

Raivo

On Sat, Sep 30, 2023 at 8:01 AM Raivo Rebane <raivore55@gmail.com> wrote:

Hi,

I copied the postgres driver to Tomcat/lib.

PS C:\Program Files\Apache Software Foundation\Tomcat 9.0\lib> dir post*
    Directory: C:\Program Files\Apache Software Foundation\Tomcat 9.0\lib
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        28.09.2023     14:40        1081604 postgresql-42.6.0.jar

but Tomcat remains to give error -

java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5432/mushroom_database

I am using Postgres 15. May be I have to use more older one ?

 

Raivo

On Sat, Sep 30, 2023 at 6:32 AM Craig McIlwee <craigm@vt.edu> wrote:

> for some reason the postgresql jar is not in the classpath.

This is due to the way that Tomcat loads drivers, which is documented at [1].  In short, the JDBC driver should be placed in the tomcat/lib directory and removed from the application's WEB-INF/lib directory.  After doing that, I was able to get past the "No suitable driver" exception.

OP, you should read that entire page, and in particular the portion at [2].  By registering a "Resource" with Tomcat, you can let it manage a connection pool for you (if you aren't already using something like PgBouncer or pgpool) and, if registered at the container level, in the future you can share that pool across all of your web applications in the container.

[1] https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html#DriverManager,_the_service_provider_mechanism_and_memory_leaks

[2] https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html#PostgreSQL

Craig