java编程实现mysql数据库远程连接的简单示例
本文包括两部分:
一、设置mysql可以远程访问
二、java编程实现远程数据库连接
一、设置mysql可以远程访问
环境参数:
MySQL Server 5.0
数据库:javadb
用户名:javadbuser
密码:javadbpwd
目标:
使用用户'javadbuser'可以远程访问数据库'javadb'
以下步骤尽量在命令行完成,在phpMyAdmin进行用户赋权有时不起作用。
//进入mysql/bin目录
C:\>cd C:\Program Files\MySQL\MySQL Server 5.0\bin
//使用root登录mysql
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29 to server version: 5.0.22-community-nt
//对数据库'mysql'进行操作
mysql> use mysql
Database changed
//查询user表信息
mysql> select user,host from user;
+------------+-----------+
| user | host |
+------------+-----------+
| javadbuser | localhost |
| root | localhost |
+------------+-----------+
2 rows in set (0.00 sec)
//更改用户'javadbuser'可以登录的host值为任意主机'%'
mysql> update user set host='%' where user='javadbuser';
mysql> flush privileges;
//再次查询user表信息,javadbuser的host值已改为'%'(任意主机)
mysql> select user,host from user;
+------------+-----------+
| user | host |
+------------+-----------+
| javadbuser | % |
| root | localhost |
+------------+-----------+
2 rows in set (0.00 sec)
//授权用户'javadbuser'和密码'javadbpwd'在任意主机都可以连接到数据库'javadb'
//数据库'javadb'、用户'javadbuser'和密码'javadbpwd'都已事先建好
//mysql命令不区分大小写
mysql> GRANT ALL PRIVILEGES ON `javadb`.* TO 'javadbuser'@'%' IDENTIFIED BY 'javadbpwd' WITH GRANT OPTION ;
mysql> flush privileges;
//使用php程序检测是否可以远程连接数据库'javadb'
/*
先在数据库'javadb'建张表'atb'
//字段:
id,int(12)
info,varchar(20)
//记录:
id info
1 OK
2 OKK
*/
//conn.php
<?php
mysql_connect("192.168.1.2:3306", "javadbuser", "javadbpwd") or die("Could not connect: " . mysql_error()); mysql_select_db("javadb"); $result = mysql_query("SELECT id,info FROM atb"); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { printf ("ID: %s Info: %s<br>", $row["id"], $row["info"]); } mysql_free_result($result);
?>
/*正常连接后显示的结果:
ID: 1 Info: OK
ID: 2 Info: OKK
*/
以上过程说明使用用户"javadbuser"远程连接mysql数据库'javadb'已经成功,可以在java客户端上进行测试了。
二、java编程实现远程数据库连接
import java.sql.*; public class BaseMySQL { public static void main(String []arg){ String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:MySQL://192.168.1.2:3306/javadb"; String username = "javadbuser"; String password = "javadbpwd"; try{ Class.forName(driver); Connection conn = DriverManager.getConnection(url,username,password); Statement statement = conn.createStatement(); String sql = "select * from atb "; System.out.println("######################"); //获取数据 ResultSet rs = statement.executeQuery(sql); while(rs.next()){ System.out.println(rs.getString("id")+"\t"+rs.getString("info")); } //关闭连接 rs.close(); conn.close(); }catch(ClassNotFoundException e){ System.out.println("数据库驱动未找到"); e.printStackTrace(); }catch(SQLException e){ e.printStackTrace(); }catch(Exception e){ e.printStackTrace(); } } }
/*运行结果:
######################
1 OK
2 OKK
*/
ycjnx,2014-9-8(完)