MYF

使用Java连接云服务器的mySQL

Posted on | In | | Visitors

本人服务器为MariaDB，使用MariaDB和MySQL的connector均可，实例如下

Statement + MariaDB connector

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
String ip = "123.45.6.7";
String DB_name = "TEST";
String url = "jdbc:mariadb://" + ip + ":3306/" + DB_name;
String user = "root";
String pwd = "root";
String sql = "select * from users";
Class.forName("org.mariadb.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, pwd);
java.sql.Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
	System.out.println("username = " + rs.getString(2)); //下标从1开始
}
rs.close();
st.close();
conn.close();

PreparedStatement + MySQL connector

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
String username = "admin";
String password = "password";
String ip = "123.45.6.7"; //服务器ip地址
String DB_name = "TEST";
String url = "jdbc:mysql://" + ip + ":3306/" + DB_name;
String user = "root";
String pwd = "root";
String sql = "select * from users where username = ? and password = ?;";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, pwd);
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, username); //下标从1开始
ps.setString(2, password); //下标从1开始
ResultSet rs = ps.executeQuery();
if (rs.next()) {
	rt = true; //存在对应username和password的元祖
}
rs.close();
st.close();
conn.close();

其他

连接服务器查询的时候，有两个问题是服务器端需要注意的

  1. 确保开启3306端口，如使用iptables的话使用iptables -I INPUT -p tcp --dport 3306 -j ACCEPT开启端口
  2. 确保使用的用户有足够的权限