Android开发之MySQL操作

来源：互联网发布：淘宝店铺入口图编辑：程序博客网时间：2024/05/01 05:50

如果想远程连接 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' @'%' identified by '123456';

授予用户 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

eclipse --- New Java project

注意：

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

MySqlUtil.java

[java] view plain copy print?

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

[java] view plain copy print?

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:

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

[java] view plain copy print?

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，采用上面的方式也是可以的，但是速度不是很快。

0 0