【PostgreSQL】【Java】Javaからストアドを呼び出す

【例１】単純なサンプル

 * まずは動くかどうか見てみる
 * 特にストアドを用意する必要なし

Sample1PostgreSQLWithStored.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class Sample1PostgreSQLWithStored {

   /**
    * main
    */
   public static void main(String[] args) throws Exception {

      try {
         System.out.println("Start");

         SamplePostgreSQLWithStoredProcedure.dbAccess();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         System.out.println("Finished");
      }
   }

   private static void dbAccess() throws Exception {
      Connection connection = null;
      CallableStatement statement = null;

      try {
         Class.forName("org.postgresql.Driver");

         connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/Sample", "user", "password");
         // トランザクションを無効にする
         connection.setAutoCommit(false);
         // プロシージャ呼び出し
         statement = connection.prepareCall("{ ? = call upper( ? ) }");
         statement.registerOutParameter(1, Types.VARCHAR);
         statement.setString(2, "lowercase to uppercase");
         statement.execute();
         String upperCased = statement.getString(1);
         System.out.println(upperCased);
      } finally {
         if (statement != null) {
            statement.close();
         }
         if (connection != null) {
            connection.close();
         }
      }
   }
}

【例２】

http://blogs.yahoo.co.jp/dk521123/33329958.html

の例2のストアドを呼び出す

Sample2PostgreSQLWithStoredProcedure.java

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

public class Sample2PostgreSQLWithStoredProcedure {

   /**
    * main
    */
   public static void main(String[] args) throws Exception {

      try {
         System.out.println("Start");

         SamplePostgreSQLWithStoredProcedure.dbAccess();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         System.out.println("Finished");
      }
   }

   private static void dbAccess() throws Exception {
      Connection connection = null;
      PreparedStatement statement = null;
      ResultSet resultSet = null;

      try {
         Class.forName("org.postgresql.Driver");

         connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/Sample", "user", "password");
         statement = connection.prepareStatement("SELECT SampleStoredProcedure(?)");
         statement.setInt(1, 10);
         resultSet = statement.executeQuery();
         
         while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
         }
      } finally {
         if (resultSet != null) {
            resultSet.close();
         }
         if (statement != null) {
            statement.close();
         }
         if (connection != null) {
            connection.close();
         }
      }
   }
}

出力結果

* んーいまいち

Start
(X0000001,Mike,m)
Finished

参考文献

http://www.postgresql.jp/document/7.4/html/jdbc-callproc.html