简述
上一篇中,MySQL数据库和后端程序都放在了我的32位CentOS上,这台240块的电脑很不好用,假期希望用另一台稍微好点的学习JDBC。数据库服务还是用那台CentOS上的MySQL,尝试在这台windows上连接它。
数据库驱动跟着后端程序跑,应用程序通过JDBC去加载JDBC-数据库驱动,来访问另一台电脑上的数据库:
在win7上配置MySQL的JDBC驱动
首先查看一下平时用的jre在哪,在新建Java项目时,点击Configure JREs..
.就能看到:
用的一直是下载的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.<init>(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.<init>(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
成功了。