使JDBC后端程序和MySQL数据库在局域网内分离教程
程序员文章站
2022-04-27 19:20:37
简述
上一篇中，MySQL数据库和后端程序都放在了我的32位CentOS上，这台240块的电脑很不好用，假期希望用另一台稍微好点的学习JDBC。数据库服务还是用那台CentOS上...
简述
上一篇中，MySQL数据库和后端程序都放在了我的32位CentOS上，这台240块的电脑很不好用，假期希望用另一台稍微好点的学习JDBC。数据库服务还是用那台CentOS上的MySQL，尝试在这台windows上连接它。
数据库驱动跟着后端程序跑，应用程序通过JDBC去加载JDBC-数据库驱动，来访问另一台电脑上的数据库：
在win7上配置MySQL的JDBC驱动
首先查看一下平时用的jre在哪，在新建Java项目时，点击Configure JREs...就能看到：喎? f/ware/vc/"="" target="_blank" class="keylink">vcD4NCjxwPjxpbWcgYWx0PQ=="这里写图片描述" src="/uploadfile/Collfiles/20180210/20180210103733382.png" title="\" />
用的一直是下载的jdk目录下面的jre，而且能看到教材上说的jre类库的扩展目录\lib\ext\目录也会被使用：
在windows下装MySQL驱动，解压不了.tar.gz，去下载一个.zip的，解压后放在JAVA_HOME\jre\lib\ext下。
确保能ping通
C:\Users\Shinelon>ping 192.168.0.106
正在 Ping 192.168.0.106 具有 32 字节的数据:
来自 192.168.0.106 的回复: 字节=32 时间=158ms TTL=64
来自 192.168.0.106 的回复: 字节=32 时间<1ms TTL=64
来自 192.168.0.106 的回复: 字节=32 时间<1ms TTL=64
来自 192.168.0.106 的回复: 字节=32 时间=5ms TTL=64
192.168.0.106 的 Ping 统计信息:
数据包: 已发送 = 4，已接收 = 4，丢失 = 0 (0% 丢失)
往返行程的估计时间(以毫秒为单位):
最短 = 0ms，最长 = 158ms，平均 = 40ms
C:\Users\Shinelon>
测试
确保在数据库服务器上开启了MySQL数据库服务。
程序：
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
try {
// 要求JVM查找并加载指定的类
Class.forName("com.mysql.jdbc.Driver");
// 声明一个sql连接对象
java.sql.Connection con = null;
String uri = "jdbc:mysql://192.168.0.106:3306/testJDBC? useSSL=true";
String user = "root";
String password = "3838438";
// 和指定的数据库建立连接
con = DriverManager.getConnection(uri, user, password);
// 用Statement声明一个SQL语句对象,用createStatement()创建之
java.sql.Statement sql = con.createStatement();
// 对数据库进行查询,返回ResultSet对象
ResultSet rs = sql.executeQuery("SELECT * FROM MyUsr");
// 一行一行查看查询结果
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println(id + " " + name + " " + age);
}
// 最后关闭连接
con.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
不能连接，即便关闭了防火墙也不行，报错：
java.sql.SQLException: null, message from server: "Host '192.168.0.108' is not allowed to connect to this MySQL server"
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1040)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2188)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2219)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2014)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at Main.main(Main.java:17)
回到数据库服务器，查看一下mysql数据库中的user表：
mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM user; +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ | localhost | root | *8DB48AEB9C60890632E7E41C908FB2BC5F7C605D | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | hostlzh | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | hostlzh | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ 5 rows in set (0.00 sec)
从host字段可以看出user名为root的用户只能在主机名为localhost，127.0.0.1，hostlzh的电脑上登录，都是指这台电脑，自然不能远程登录。
修改host项：
UPDATE user SET host='%' WHERE user='root' and host='localhost';
网上说’%’表示任意主机，但是这样好像还是连接不了。
我的解决方法
插入新的表项，显式指明host在192.168.0.108，密码的哈希值(第三个字段)先不写东西试试：
INSERT INTO user
(Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,ssl_cipher,x509_issuer,x509_subject,max_questions,max_updates,max_connections,max_user_connections)
VALUES
('192.168.0.108','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');
重启sql服务：
[root@hostlzh /]# mysqladmin -u root -p shutdown Enter password: 180209 18:44:18 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended [1]+ Done mysqld_safe [root@hostlzh /]# mysqld_safe & [1] 4261 [root@hostlzh /]# 180209 18:44:33 mysqld_safe Logging to '/var/log/mysqld.log'. 180209 18:44:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
在这台windows上尝试连接，自然现在是无密码连接的，密码只要一个空串：
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
try {
// 要求JVM查找并加载指定的类
Class.forName("com.mysql.jdbc.Driver");
// 声明一个sql连接对象
java.sql.Connection con = null;
String uri = "jdbc:mysql://192.168.0.106:3306/testJDBC? useSSL=true";
String user = "root";
String password = "";// 空串
// 和指定的数据库建立连接
con = DriverManager.getConnection(uri, user, password);
// 用Statement声明一个SQL语句对象,用createStatement()创建之
java.sql.Statement sql = con.createStatement();
// 对数据库进行查询,返回ResultSet对象
ResultSet rs = sql.executeQuery("SELECT * FROM MyUsr");
// 一行一行查看查询结果
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println(id + " " + name + " " + age);
}
// 最后关闭连接
con.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
输出
1 lzh 20 2 pika 4 3 aaa 19
成功了。
解决数据库新出现的问题
现在，我的数据库出现了一些问题，用传统的登录方式提示不需要密码：
[root@hostlzh /]# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@hostlzh /]# mysql -u root -p Enter password: Welcome to the MySQL monitor....
而此时登录的显然不是之前的那个root，所能查看的数据库变少了：
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec
那个mysql数据库显然不是消失了，而是我登录的用户不对，我认为这是因为我前面按照网上的方式把localhost改成了’%’造成的。先用环回地址登录那个真正的root(这时也不需要密码，受前面看到过的user表的127.0.0.1那行影响)：
[root@hostlzh /]# mysql -u root -h 127.0.0.1 -p Enter password: Welcome to the MySQL monitor....
查看一下：
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | testJDBC | +--------------------+ 4 rows in set (0.00 sec)
就是这个root，现在把那一项改回来：
mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> UPDATE user SET host='localhost' WHERE user='root' and host='%'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
出去重新用密码登录，还是不行，记得刚修改成’%’的时候也不行，因为没有重启mysql服务。重启一下，当然这个时候用之前的方式已经没有权限shutdown之了！还是利用环回地址重启：
[root@hostlzh /]# mysqladmin -u root -h 127.0.0.1 -p shutdown Enter password: [root@hostlzh /]# 180209 19:12:21 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended [1]+ Done mysqld_safe [root@hostlzh /]# mysqld_safe & [1] 4522 [root@hostlzh /]# 180209 19:12:41 mysqld_safe Logging to '/var/log/mysqld.log'. 180209 19:12:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
这回再尝试一下，好好输入密码：
[root@hostlzh /]# mysql -u root -p Enter password: Welcome to the MySQL monitor....
没问题！再看看是否有操作那几个表的权力：
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | testJDBC | +--------------------+ 4 rows in set (0.00 sec)
OK了。
手动设定密码哈希值
为了安全起见，还是要为其设定密码，虽然上篇文章里已经公开了密码的明文。把之前密码的哈希值记录下来，更改user表项：
mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user
-> SET Password='*8DB48AEB9C60890632E7E41C908FB2BC5F7C605D'
-> WHERE user='root' and host='192.168.0.108';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
现在，再重启一下MySQL服务：
[root@hostlzh /]# mysqladmin -u root -p shutdown Enter password: 180209 19:20:27 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended [1]+ Done mysqld_safe [root@hostlzh /]# mysqld_safe & [1] 4659 [root@hostlzh /]# 180209 19:20:31 mysqld_safe Logging to '/var/log/mysqld.log'. 180209 19:20:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
测试程序：
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
try {
// 要求JVM查找并加载指定的类
Class.forName("com.mysql.jdbc.Driver");
// 声明一个sql连接对象
java.sql.Connection con = null;
String uri = "jdbc:mysql://192.168.0.106:3306/testJDBC? useSSL=true";
String user = "root";
String password = "3838438"; // 使用密码
// 和指定的数据库建立连接
con = DriverManager.getConnection(uri, user, password);
// 用Statement声明一个SQL语句对象,用createStatement()创建之
java.sql.Statement sql = con.createStatement();
// 对数据库进行查询,返回ResultSet对象
ResultSet rs = sql.executeQuery("SELECT * FROM MyUsr");
// 一行一行查看查询结果
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println(id + " " + name + " " + age);
}
// 最后关闭连接
con.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
输出
1 lzh 20 2 pika 4 3 aaa 19
成功了。
喎?>上一篇： iOS OC 的命名规范
下一篇： 爆冷的同学和室友伙伴们