【Java】ACCESS を Java で利用する

設定

手順

※事前にACCESSファイル(例「customerDB.mdb」)を用意しておく

 [1] [コントロールパネル]-[管理ツール]-[データソース(ODBC)]を選択
 [2] [ユーザーDNS]で「追加」ボタン押下
 [3] 「Microsoft Access Diver(.mdb)」を選択し、「完了」ボタン押下
 [4] データソース名を入力（例「customerDB」）し、「選択」ボタン押下し、
     データベース名に事前に用意しておいたACCESSファイル(例「customerDB.mdb」)を指定
 [5]「OK」ボタン押しまくり

サンプル

customerDB.mdb(ACCESSデータベース)

* 以下の表を作成しておく

CREATE DATABASE customer (
  CustomerID nchar(4) NOT NULL,
  CustomerName nchar(20),
  CustomerJob nchar(20)
)

SampleJdbcToInsert.java

* Insert文

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

public class SampleJdbcToInsert {

   public static void main(String[] args) throws SQLException {
      Connection con = null;
      PreparedStatement pstmt = null;
      
      try {
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         con = DriverManager.getConnection("jdbc:odbc:customerDB");
         pstmt = con.prepareStatement(
               "INSERT INTO customer VALUES(?, ?, ?)");
         pstmt.setString(1, "X001");
         pstmt.setString(2, "Mike");
         pstmt.setString(3, "programmer");

         pstmt.executeUpdate();

         System.out.println("Successful!");
      } catch (Exception e) {
         System.out.println("Error : " + e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (con != null) {
            con.close();
         }         
      }
   }
}

SampleJdbcToSelect.java

* Select文

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

public class SampleJdbcToSelect {
   public static void main(String[] args) throws SQLException {
      Connection con = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      
      try {
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         con = DriverManager.getConnection("jdbc:odbc:customerDB");
         pstmt = con.prepareStatement("SELECT * FROM customer WHERE CustomerID=?");
         pstmt.setString(1, "X001");

         rs = pstmt.executeQuery();

         while (rs.next()) {
            System.out.println("Customer ID : " + rs.getString("CustomerID"));
            System.out.println("Customer Name : " + rs.getString("CustomerName"));
            System.out.println("Customer Job : " + rs.getString("CustomerJob"));
         }
      } catch (Exception e) {
         System.out.println("Error : " + e);
      } finally {
         if (rs != null) {
            rs.close();
         }
         if (pstmt != null) {
            pstmt.close();
         }
         if (con != null) {
            con.close();
         }
      }
   }
}

SampleJdbcToUpdate.java

* Update文

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

public class SampleJdbcToUpdate {

   public static void main(String[] args) throws SQLException {
      Connection con = null;
      PreparedStatement pstmt = null;
      
      try {
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         con = DriverManager.getConnection("jdbc:odbc:customerDB");
         pstmt = con.prepareStatement(
               "UPDATE customer SET CustomerName=?, CustomerJob=? WHERE CustomerID=?");
         pstmt.setString(1, "Tom");
         pstmt.setString(2, "Doctor");
         pstmt.setString(3, "X001");

         pstmt.executeUpdate();

         System.out.println("Successful!");
      } catch (Exception e) {
         System.out.println("Error : " + e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (con != null) {
            con.close();
         }         
      }
   }
}

SampleJdbcToDelete.java

* Delete文

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

public class SampleJdbcToDelete {
   public static void main(String[] args) throws SQLException {
      Connection con = null;
      PreparedStatement pstmt = null;
      
      try {
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         con = DriverManager.getConnection("jdbc:odbc:customerDB");
         pstmt = con.prepareStatement("DELETE FROM customer WHERE CustomerID=?");
         pstmt.setString(1, "X001");
         pstmt.executeUpdate();
         
         System.out.println("Successful!");
      } catch (Exception e) {
         System.out.println("Error : " + e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (con != null) {
            con.close();
         }
      }
   }
}