Re: JDBC prepared statement is not treated as prepared statement - Mailing list pgsql-general

高健  wrote:
> I  have one question about prepared statement.
> I use Java via JDBC, then send prepared statement to execute.
> I thought that the pg_prepared_statments  view will have one record after my execution.
> But I can't find.
> 
> Is the JDBC's prepared statement  differ from  SQL execute by prepare command ?
> http://www.postgresql.org/docs/current/static/sql-prepare.html
> 
> My simple java program is the following:
> 
> import java.sql.*;
> 
> public class Test01 {
>         public static void main(String argsv[]){
>         try
>          {
>            Class.forName("org.postgresql.Driver").newInstance();
>            String url = "jdbc:postgresql://localhost:5432/postgres" ;
>            Connection con = DriverManager.getConnection(url,"postgres","postgres" );
>            ///Phase 1:-------------Select data from table-----------------------
>            System.out.println("Phase 1------------------------start");
>            String strsql = " select * from customers where cust_id = ?";
>            PreparedStatement pst=con.prepareStatement(strsql);
>            pst.setInt(1,3); //find the customer with cust_id of 3.
>            ResultSet rs = pst.executeQuery();
>            while (rs.next())
>             {
>                System.out.print("cust_id:"+rs.getInt( "cust_id"));
>                System.out.println("...cust_name:"+rs.getString( "cust_name" ));
>            }
> 
>            System.out.println("Phase 1------------------------end\n");
> 
> 
> 
>            ///Phase 2:-------------Use connection again,to select data from data dictionary-----------
> ------------
>            System.out.println("Phase 2------------------------start");
>            strsql = "select * from pg_prepared_statements";
>            pst=con.prepareStatement(strsql);
>            rs = pst.executeQuery();
>            while (rs.next())
>            {
>               System.out.println("statement:"+rs.getString( "statement"));
>            }
>            System.out.println("Phase 2------------------------end\n");
> 
> 
> 
>            ///Phase 3:-------------Use connection again,to select data from table---------------------
> --
>            System.out.println("Phase 3------------------------start");
>            strsql = "select * from customers";
>            pst=con.prepareStatement(strsql);
>            rs = pst.executeQuery();
>            while (rs.next())
>            {
>               System.out.print("cust_id:"+rs.getInt( "cust_id"));
>               System.out.println("...cust_name:"+rs.getString( "cust_name" ));
>           }
>           System.out.println("Phase 3------------------------end\n");
>           rs.close();
>           pst.close();
>           con.close();
>        }
>         catch (Exception ee)
>         {
>            System.out.print(ee.getMessage());
>        }
>         }
> }
> 
> 
> 
> The result of it's execution is:
> 
> Phase 1------------------------start
> 
> cust_id:3...cust_name:Taylor
> 
> Phase 1------------------------end
> 
> 
> 
> Phase 2------------------------start
> 
> Phase 2------------------------end
> 
> 
> 
> Phase 3------------------------start
> 
> cust_id:1...cust_name:Smith
> 
> cust_id:2...cust_name:Brown
> 
> cust_id:3...cust_name:Taylor
> 
> Phase 3------------------------end
> 
> 
> 
> That is to say: my prepared statement is not cached by PG?
> 
> Then how to write a  java program to made it's prepared statement realized by PG to treat it as a
> "prepared statement"?
> 
> Thank you.

See http://jdbc.postgresql.org/documentation/head/server-prepare.html

Set the prepare threshold of a PreparedStatement and use the statement
at least as many times.  Then you should see an entry in
pg_prepared_statements.

In your example, no PreparedStatement is used more than once.

Yours,
Laurenz Albe