JDBCドライバの設定(mac+eclipse+tomcat+MySQL)

Javaでデータベースの接続

mac環境での資料が少なく手こずったのでメモ。

環境

MacBook Pro (15-inch, Mid 2012)
OSX Yosemite 10.10.5
eclipse、tomcat、MySQLはインストール済み

データベース作成

後で動作確認する時のためにDBをMySQLから作成しておく。下記参照。

create database SAMPLEDB;
USE SAMPLEDB;

CREATE TABLE STUDENT(
  STUDENTNO INT (4) PRIMARY KEY,
  STUDENTNAME VARCHAR(50),
  SCHOOLYEAR INT (1)
);

CREATE TABLE SUBJECT(
  SUBJECTID INT(2)PRIMARY KEY,
  SUBJECTNAME VARCHAR(50)
);

CREATE TABLE GRADE(
  STUDENTNO INT(4),
  SUBJECTID INT(2),
  SCORE INT(3),
  PRIMARY KEY(STUDENTNO,SUBJECTID),
  FOREIGN KEY(STUDENTNO)REFERENCES STUDENT(STUDENTNO),
  FOREIGN KEY(SUBJECTID)REFERENCES SUBJECT(SUBJECTID)
);

GRANT ALL PRIVILEGES ON SAMPLEDB.*
  TO USER@LOCALHOST IDENTIFIED BY 'password';

INSERT INTO STUDENT VALUES(1001,'田中太郎',3);
INSERT INTO STUDENT VALUES(1002,'鈴木次郎',3);
INSERT INTO STUDENT VALUES(1003,'佐藤三郎',2);
INSERT INTO STUDENT VALUES(1004,'高橋四郎',1);
INSERT INTO STUDENT VALUES(1005,'渡辺五郎',1);

INSERT INTO SUBJECT VALUES(10,'Java入門');
INSERT INTO SUBJECT VALUES(11,'オブジェクト指向プログラミング');
INSERT INTO SUBJECT VALUES(12,'Webアプリケーション構築入門');

INSERT INTO GRADE VALUES(1001,10,80);
INSERT INTO GRADE VALUES(1001,11,50);
INSERT INTO GRADE VALUES(1001,12,60);
INSERT INTO GRADE VALUES(1002,10,75);
INSERT INTO GRADE VALUES(1002,11,85);
INSERT INTO GRADE VALUES(1002,12,90);
INSERT INTO GRADE VALUES(1003,10,80);
INSERT INTO GRADE VALUES(1003,11,60);
INSERT INTO GRADE VALUES(1003,12,90);
INSERT INTO GRADE VALUES(1004,10,100);
INSERT INTO GRADE VALUES(1004,11,40);
INSERT INTO GRADE VALUES(1004,12,80);
INSERT INTO GRADE VALUES(1005,10,70);
INSERT INTO GRADE VALUES(1005,11,70);
INSERT INTO GRADE VALUES(1005,12,80);

MySQLコネクタJDBCドライバのインストール

下記よりドライバのダウンロード
MySQL :: Download Connector/J
f:id:sattamassagana:20151122151335p:plain
ターミナルに以下を打ち込んでファイルを解凍する。

$ tar zxvf mysql-connector-java-5.1.37.tar.gz

解凍したmysql-connector-java-5.1.37-bin.jarを/Applications/apache-tomcat-7.0.64/lib/にコピーする。

eclipseでプロジェクトを作成して動作確認

eclipseのプロジェクトエクスプローラで右クリック→新規→動的Webプロジェクト→プロジェクト名をDBSampleServletと入力→完了
作成したプロジェクトを選択し右クリック→新規→サーブレット→パッケージにchapter6、クラス名にSelectSampleServletと入力→完了→次のJavaソースを入力

Javaソース

package chapter6;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class SelectSampleServle
 */
@WebServlet("/SelectSampleServlet")
public class SelectSampleServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public SelectSampleServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        java.sql.Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<String> list = new ArrayList<String>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/SAMPLEDB", "root", "");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("Select * from student");

            while (rs.next()) {
                String s = "<td>" + rs.getInt(1) + "</td>" + "<td>" + rs.getString(2) + "</td>" + "<td>" + rs.getInt(3)
                        + "</td>";
                list.add(s);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) {

                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlEx) {

                }
            }
        }
        PrintWriter out = response.getWriter();
        out.println("<html>");
        out.println("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">");
        out.println("<head><title>" + "データベース接続のサンプルサーブレットSelect" + "</title></head>");
        out.println("<body>");
        out.println("<table border=1>");
        out.println("<tr><th>学籍番号</th>" + "<th>学生氏名</th>" + "<th>学年</th></tr>");
        for (String str : list) {
            out.println("<tr>" + str + "</tr>");
        }
        out.print("</table>");
        out.println("</body>");
        out.println("</html>");
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}