2015年2月19日 星期四

Tomcat + Java + MySQL

由於 AS2 需要運行在 Tomcat 之上,而沿用開的 Apache + PHP + MySQL 方式跟 Tomcat 沒有接軌。我的解決方法是讓 Java 能直接讀取 MySQL 內的數據。因此需要設定 Tomcat + Java + MySQL 的方案。安裝方法如下:

1. 到 http://dev.mysql.com/downloads/connector/j/ 下載 MySQL Connector/J
2. 解壓後把 mysql-connector-java-5.1.34-bin.jar 拷到 /usr/local/tomcat7/lib
3. 修改 /usr/local/tomcat7/conf/context.xml
4. 在 <context></context> 中加入「<Resource name="jdbc/MySQLDS" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" username="username" password="password" maxActive="20" maxIdle="10" maxWait="-1" />」
5. 以 /usr/local/tomcat/bin/shutdown.sh 關閉 Tomcat
6. 以 /usr/local/tomcat/bin/startup.sh 重啟 Tomcat
7. 完成

留意上面用了 localhost:3306,所以記得要在 /etc/sysconfig/iptables 開通 3306 埠:
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -m comment --comment "SFTP port" -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -m comment --comment "Apache WEB server port" -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -s 192.168.1.1 -m comment --comment "MySQL server port" -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 8080 -m comment --comment "Tomcat Server port" -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
之後簡單寫了一個 JSP 測試以 JNDI 方式讀取 MySQL 內的數據:
<%@ page language="java" contentType="text/html; charset=UTF-8"
 pageEncoding="UTF-8"%>
<%@page import="com.mysql.*,java.util.*,javax.naming.*,java.sql.*,javax.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <title>Insert title here</title>
 </head>
 <body>
  <h1>JSP MySQL Connection Test</h1>
<%
 Context context = new InitialContext();
 String lookup = "java:comp/env/jdbc/MySQLDS";
 DataSource dataSource = (DataSource)context.lookup(lookup);
 Connection connection = dataSource.getConnection();
 Statement statement = connection.createStatement();

 String query = "SELECT * FROM databaseName.tableName";
 ResultSet resultSet = statement.executeQuery(query);
 while (resultSet.next())  {

   out.println(resultSet.getString(1));
   out.println(resultSet.getString(2));
   out.println("<br>");
 }
%>
 </body>
</html>

沒有留言: