Re: JDBC prepared statement is not treated as prepared statement

高健 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