Tomcat 連結MySQL

 

---

Context

• 本筆記紀錄如何在Tomcat下利用JSP去連接MySQL

---

安裝MySQL Connector

• 下載 MySQL JDBC Driver
• • http://dev.mysql.com/downloads/connector/j/
  • 並將mysql-connector-java-5.1.35-bin.jar檔至於tomcat的lib目錄中(如/home/pi/apache-tomcat-8.0.20/lib)

---

測試結果

• 撰寫JSP
• • <%@ page contentType="text/html;charset=utf-8"%>
    <%@ page import="java.sql.*"%>
    <%
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            try {
           //connect your mysql server
                String db_user = "root";          //mysql id
                String db_pwd = "raspberry";     //mysql password
                String db_database = "test";     //mysql database name
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + db_database + "?user=" + db_user + "&password=" + db_pwd + "&useUnicode=true&characterEncoding=UTF-8");
                try {
                    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    try {
                        String sql = "select * from test";          //query your table
                        ResultSet rs = stmt.executeQuery(sql);     //execute your sql command
    %>
    <div style="font-size:26px;color:#0000FF;">test</div>
    <table border=1>
        <tr><th>ID</th><th>Name</th>
                <%//show the result
                    while (rs.next()) {
                %>
        <tr>
            <td><%=rs.getString("id")%></td><td><%=rs.getString("name")%></td>
        </tr>
        <%
            }
        %>
    </table>
    <%
    // closs the mysql connect
                        rs.close();
                        rs = null;
                        stmt.close();
                        stmt = null;
                        conn.close();
                    } catch (Exception ex) {
                        out.println("can't read data");
                        out.println(ex.toString());
                    }
                } catch (Exception e) {
                    out.println("can't create statement");
                    out.println(e.toString());
                }
            } catch (Exception e) {
                out.println("can't content mysql database");
                out.println(e.toString());
            }
        } catch (Exception e) {
            out.println("can't load mysql driver");
            out.println(e.toString());
        }
    %>
• 將JSP檔至於tomcat網站根目錄(預設為Tomcat安裝目錄下的webapps/ROOT/)
• 察看結果：
•