[Java] 運用Eclipse與JDBC連接MySQL資料庫

用Eclipse寫Java程式，目前一切很正常

今天需要連接到MySQL，所以就小試了一下，程式碼如下

import java.sql.*;
public class dbconn {
public static void main(String[] args) {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://ipaddress:3306/dbname";
String user = "taiwanwolf";
String password = "mypassword";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,password);
if (conn != null && !conn.isClosed()) {
System.out.println("test ok");
conn.close();
}
else {
System.out.println("fail");
}
}
catch (ClassNotFoundException e) {
System.out.println("can not found driver class");
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}
}

}

---我是分隔線---

上面看起來很正常吧，結果按CTRL+F11，居然出現class not found的錯誤

想必是找不到JDBC的Class，google一下找到mysql 的JDBC Driver

下載點

點選Project -> properties
Java Build Path => Libraries -> Add External JARs
再選上面壓縮檔解開的Jar => mysql-connector-java-5.1.7-bin.jar

全部弄好之後居然出現另一個錯誤訊息
not allowed to connect to this MySQL serverConnection closed by foreign host

把 firewall 抽掉也不行，google了一下發現是MySQL預設是只接受localhost的連線

所以必須新增User並且指定可連線的IP，先用console登入, 然後用 grant 新增

一般新增語法
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

我是採用這個
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on dbname.* TO 'taiwanwolf'@'123.111.222.%' identified by 'mypass';

FLUSH PRIVILEGES;

再試一次，搞定!

P.S1: 上述的123.111.222.%是表示只允許此段IP網段連入

P.S 2: iptables 裡面開啟3306可被連線的語法為
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT