【PostgreSQL】【Java】PostgreSQL / Java で INSERT/UPDATE/DELETE を行う

はじめに

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

で、SELECT文はやったが、 INSERT/UPDATE/DELETE文はやってなかったのでやる

　=> 結局、INSERT/UPDATE/DELETE文も、SQL文だけが変わるだけで、
　　 同じように書ける
　　（下記サンプルの「!! ここに注目 !!」のSQL文だけが変わるだけで後は同じ）

サンプル

【1】INSERT文の実行

SamplePostgreSQLWithInsert.java

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

public class SamplePostgreSQLWithInsert {

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

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

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

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

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

         connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/Sample", "user", "password");
         statement = connection.createStatement();

         // !! ここに注目 !!
         int number = statement.executeUpdate("INSERT INTO Person VALUES('X0000002','Tom','m',current_timestamp)");
         System.out.println(number);
         resultSet = statement.executeQuery("SELECT * FROM Person");
         while (resultSet.next()) {
            System.out.println(resultSet.getString("id"));
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getString("sex"));
            System.out.println(resultSet.getString("updateDate"));
         }
      } finally {
         if (resultSet != null) {
            resultSet.close();
         }
         if (statement != null) {
            statement.close();
         }
         if (connection != null) {
            connection.close();
         }
      }
   }
}

【2】Update文の実行

SamplePostgreSQLWithUpdate.java

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

public class SamplePostgreSQLWithUpdate {

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

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

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

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

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

         connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/Sample", "user", "password");
         statement = connection.createStatement();

         // !! ここに注目 !!
         int number = statement.executeUpdate(
               "UPDATE Person SET name='Amy',sex='f',updateDate=current_timestamp WHERE id='X0000002'");
         System.out.println(number);
         resultSet = statement.executeQuery("SELECT * FROM Person");
         while (resultSet.next()) {
            System.out.println(resultSet.getString("id"));
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getString("sex"));
            System.out.println(resultSet.getString("updateDate"));
         }
      } finally {
         if (resultSet != null) {
            resultSet.close();
         }
         if (statement != null) {
            statement.close();
         }
         if (connection != null) {
            connection.close();
         }
      }
   }
}

【3】Delete文の実行

SamplePostgreSQLWithDelete.java

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

public class SamplePostgreSQLWithDelete {

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

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

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

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

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

         connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/Sample", "user", "password");
         statement = connection.createStatement();

         // !! ここに注目 !!
         int number = statement.executeUpdate(
               "DELETE FROM Person WHERE id='X0000002'");
         System.out.println(number);
         resultSet = statement.executeQuery("SELECT * FROM Person");
         while (resultSet.next()) {
            System.out.println(resultSet.getString("id"));
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getString("sex"));
            System.out.println(resultSet.getString("updateDate"));
         }
      } finally {
         if (resultSet != null) {
            resultSet.close();
         }
         if (statement != null) {
            statement.close();
         }
         if (connection != null) {
            connection.close();
         }
      }
   }
}

参考文献

http://blog.zdrv.com/archives/339
http://ash.jp/db/pg_jdbc_prog.htm

関連記事

PreparedStatement で SELECT/INSERT/UPDATE/DELETE を行う

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