杂记6： jdbc连接云服务器数据库

如果想远程连接 mysql，需要记住：mysql 允许被远程访问。

1. 启动 MySql

sudo /etc/init.d/mysql.server start

mysql -u root -p

2. 增加一个用户

use mysql（mysql 是自带的数据库文件，里面有张表 user）
执行下面的两条命令：

grant all privileges on *.* to 'mark' @'%b' identified by '123456'
flush privileges

解释：授予用户mark,密码123456，可以使用任意ip访问任何数据库。
查看表中数据：

可以看出 mark 这个用户创建了。
password 是被加密了的。host 为 % 表示通配任意 ip（ipv4）。

3. 命令行验证

• ctrl + c 停止 mysql 交互模式
• ifconfig 查看本机的 ip：192.168.1.102
• mysql -h 192.168.1.102 -umark -p123456

如果进入交互模式，就 ok.
新建一个数据库文件 mydb 和表 mytable:

4、jdbc

1、 eclipse — New java project

注意：
要将 jdbc（connector-java-5.1.6-bin.jar 版本） 的 jar 文件 Build path 到这个项目。

2、MySqlUtil.java

package net.mark.util;  
  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
  
public class MySqlUtil {  
  
    public static Connection openConnection(String url, String user,  
            String password) {  
        Connection conn = null;  
        try {  
            final String DRIVER_NAME = "com.mysql.jdbc.Driver";  
            Class.forName(DRIVER_NAME);  
            conn = DriverManager.getConnection(url, user, password);  
        } catch (ClassNotFoundException e) {  
            conn = null;  
        } catch (SQLException e) {  
            conn = null;  
        }  
  
        return conn;  
    }  
  
    public static void query(Connection conn, String sql) {  
  
        if (conn == null) {  
            return;  
        }  
  
        Statement statement = null;  
        ResultSet result = null;  
  
        try {  
            statement = conn.createStatement();  
            result = statement.executeQuery(sql);  
            if (result != null && result.first()) {  
                int idColumnIndex = result.findColumn("id");  
                int nameColumnIndex = result.findColumn("name");  
                while (!result.isAfterLast()) {  
                    System.out.println("------------------");  
                    System.out.print("id " + result.getString(idColumnIndex) + "\t");  
                    System.out  
                            .println("name " + result.getString(nameColumnIndex));  
                    result.next();  
                }  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                if (result != null) {  
                    result.close();  
                    result = null;  
                }  
                if (statement != null) {  
                    statement.close();  
                    statement = null;  
                }  
  
            } catch (SQLException sqle) {  
  
            }  
        }  
    }  
  
    public static boolean execSQL(Connection conn, String sql) {  
        boolean execResult = false;  
        if (conn == null) {  
            return execResult;  
        }  
  
        Statement statement = null;  
  
        try {  
            statement = conn.createStatement();  
            if (statement != null) {  
                execResult = statement.execute(sql);  
            }  
        } catch (SQLException e) {  
            execResult = false;  
        }  
  
        return execResult;  
    }  
}  

Main.java

package net.mark;  
import java.sql.Connection;  
import net.mark.util.MySqlUtil;  
public class Main {  
    private static final String URL = "jdbc:mysql://192.168.1.102/mydb";  
    private static final String USER = "mark";  
    private static final String PASSWORD = "123456";  
    public static void main(String[] args) throws Exception {  
        Connection conn = MySqlUtil.openConnection(URL, USER, PASSWORD);  
        System.out.println("All users info:");  
        MySqlUtil.query(conn, "select * from mytable");  
    }  
  
}  

Run as java application:

还可以插入、删除或者更新数据：

package net.mark;  
import java.sql.Connection;  
import net.mark.util.MySqlUtil;  
public class Main {  
    private static final String URL = "jdbc:mysql://192.168.1.102/mydb";  
    private static final String USER = "mark";  
    private static final String PASSWORD = "123456";  
    public static void main(String[] args) throws Exception {  
        Connection conn = MySqlUtil.openConnection(URL, USER, PASSWORD);  
        System.out.println("All users info:");  
        MySqlUtil.execSQL(conn, "insert into mytable values(56,'小李')");  
        MySqlUtil.execSQL(conn, "update mytable set name='mark' where id=1");  
        MySqlUtil.execSQL(conn, "delete from mytable where id=6");  
        MySqlUtil.query(conn, "select * from mytable");  
    }  
  
}  

上面的访问是基于局域网通过 jdbc 操作 mysql。
远程访问 mysql，采用上面的方式也是可以的，但是速度不是很快。

运行结果正确，但是出现警告，如下，有人出现这种情况么？该怎么办呢？

WARN: Establishing SSL connection without server's identity verification is not recommended. 

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","123456");

改为

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");