環境/前提条件 †
- OS X 10.11 El Capitan
- Eclipse Java EE IDE for Web Developers - Version: 4.5.2 (Mars 2)
- Apache Tomcat 8.0.39
- MySQL 5.7.13
- データベース名: test
- テーブル名: users
mysql> select * from users;
+------+---------+-----------------+----------+
| id | name | email | password |
+------+---------+-----------------+----------+
| 1 | kimura | kimura@foo.com | kimpwd |
| 2 | tanaka | tanaka@foo.com | tanapwd |
| 3 | yoshida | yoshida@foo.com | yoshipwd |
+------+---------+-----------------+----------+
JDBCドライバのダウンロード †
- 解凍フォルダ内の mysql-connector-java-5.1.40-bin.jar をTomcatのlibフォルダ配下へコピー
$ cp ~/Downloads/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar /Applications/apache-tomcat-8.0.39/lib/
サンプルプロジェクト＆Servlet作成 †
- Dynamic Web Projectを新規作成（プロジェクト名: DBSampleServlet）
- Servletを新規作成（クラス名: SelectSampleServlet）
- 事前に用意したusersテーブルからSELECTしてHTMLとして出力するサンプルプログラム
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 SelectSampleServlet
*/
@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/test", "root", "hoge");
stmt = conn.createStatement();
rs = stmt.executeQuery("Select * from users");
while (rs.next()) {
String s = "<td>" + rs.getInt(1) + "</td>" + "<td>" + rs.getString(2) + "</td>"
+ "<td>" + rs.getString(3) + "</td>" + "<td>" + rs.getString(4) + "</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>" + "Sample DB Servlet" + "</title></head>");
out.println("<body>");
out.println("<table border=1>");
out.println("<tr><th>id</th>" + "<th>name</th>" + "<th>email</th>" + "<th>password</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);
}
}
Servlet実行 †
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
