网上很多人都说这种做法是不合理的,其实我也是这么觉得的,因为安全得不到保障,而且对数据库操作的业务逻辑不可能都放在Android断,这样会对APP造成很大压力。但是我们项目精灵非要做,于是我们就硬着头皮做了,也测试通了,我这里提供两个版本的DEMO,一个是连接内网,一个是连接外网,目前只在模拟器上测试过,真机不敢保证
附注:我的代码是参照别人的,本来想列举链接的,但是找不到了,如果以后找到原文链接,我一定补上。
首先是内网版(Eclipse):
package com.example.jdbctest;
import java.sql.Connection;
import java.sql.SQLException;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
public class TestActivity extends Activity {
private static final String REMOTE_IP = "192.168.1.7";//服务器地址
private static final String URL = "jdbc:mysql://" + REMOTE_IP + "/zw";
private static final String USER = "root";//数据库账户
private static final String PASSWORD = "root";//数据库密码
private Connection conn;
public void onConn(View view) {
new Thread() {
public void run() {
Log.e("============", "你麻痹");
conn = Util.openConnection(URL, USER, PASSWORD);
}
}.start();
}
public void onInsert(View view) {
new Thread() {
public void run() {
Log.e("============", "你麻痹的插入");
String sql = "insert into users values(3, 'yinhongbo', 'yinhongbo')";
Util.execSQL(conn, sql);
}
}.start();
}
public void onDelete(View view) {
String sql = "delete from mytable where name='mark'";
Util.execSQL(conn, sql);
}
public void onUpdate(View view) {
String sql = "update mytable set name='lilei' where name='hanmeimei'";
Util.execSQL(conn, sql);
}
public void onQuery(View view) {
new Thread() {
public void run() {
Log.e("============", "你麻痹的查询");
Util.query(conn, "select * from users");
}
}.start();
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_test);
}
@Override
protected void onDestroy() {
super.onDestroy();
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
conn = null;
} finally {
conn = null;
}
}
}
}
Util类:
package com.example.jdbctest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import android.util.Log;
public class Util {
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) {
Log.e("======conn结果======", "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("user_name");
Log.e("======结果======", "结果");
while (!result.isAfterLast()) {
Log.e("======id======", result.getString(idColumnIndex) + "\t\t");
Log.e("======name======", result.getString(nameColumnIndex));
// System.out.print(result.getString(idColumnIndex) + "\t\t");
// System.out.println(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;
}
}
外网版(Android Studio):
package com.jingchujie.jdbctestinas;
import java.sql.Connection;
import java.sql.SQLException;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
public class MainActivity extends Activity {
// private static final String REMOTE_IP = "192.168.1.7";
// private static final String URL = "jdbc:mysql://" + REMOTE_IP + "/zw";
// private static final String USER = "root";
// private static final String PASSWORD = "root";
private static final String REMOTE_IP = "localhost:33104";//这里是映射地址,可以随意写,不是服务器地址
private static final String URL = "jdbc:mysql://" + REMOTE_IP + "/mobile";
private static final String USER = "root";
private static final String PASSWORD = "";
private Connection conn;
public void onConnSsh(View view) {
new Thread() {
public void run() {
Log.e("============", "预备连接服务器");
Util.go();
}
}.start();
}
public void onConn(View view) {
new Thread() {
public void run() {
Log.e("============", "预备连接数据库");
conn = Util.openConnection(URL, USER, PASSWORD);
}
}.start();
}
public void onInsert(View view) {
new Thread() {
public void run() {
Log.e("============", "预备插入");
String sql = "insert into users values(3, 'yinhongbo', 'yinhongbo')";
Util.execSQL(conn, sql);
}
}.start();
}
public void onDelete(View view) {
String sql = "delete from mytable where name='mark'";
Util.execSQL(conn, sql);
}
public void onUpdate(View view) {
String sql = "update mytable set name='lilei' where name='hanmeimei'";
Util.execSQL(conn, sql);
}
public void onQuery(View view) {
new Thread() {
public void run() {
Log.e("============", "预备查询");
Util.query(conn, "select * from users");
}
}.start();
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
@Override
protected void onDestroy() {
super.onDestroy();
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
conn = null;
} finally {
conn = null;
}
}
}
}
Util类
package com.jingchujie.jdbctestinas;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import android.util.Log;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
public class Util {
public static void go() {
String user = "root";//SSH连接用户名
String password = "1q2w3e";//SSH连接密码
String host = "192.168.1.4";//SSH服务器
int lport = 33104;//本地端口(随便取)
String rhost = "localhost";//远程MySQL服务器
int rport = 3306;//远程MySQL服务端口
int port = 22;//SSH访问端口
try {
JSch jsch = new JSch();
Session session = jsch.getSession(user, host, port);
session.setPassword(password);
session.setConfig("StrictHostKeyChecking", "no");
session.connect();
Log.e("=======>", "服务器连接成功");
System.out.println(session.getServerVersion());//这里打印SSH服务器版本信息
int assinged_port = session.setPortForwardingL(lport, rhost, rport);//将服务器端口和本地端口绑定,这样就能通过访问本地端口来访问服务器
System.out.println("localhost:" + assinged_port + " -> " + rhost + ":" + rport);
} catch (Exception e) {
e.printStackTrace();
}
}
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);
Log.e("=====连接结果=======", "数据库连接成功");
} catch (ClassNotFoundException e) {
Log.e("=====连接结果=======", "报ClassNotFoundException异常");
conn = null;
} catch (SQLException e) {
Log.e("=====连接结果=======", "报SQLException异常");
conn = null;
}
return conn;
}
public static void query(Connection conn, String sql) {
if (conn == null) {
Log.e("=====连接前判断=======", "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("user_name");
Log.e("======结果======", "结果");
while (!result.isAfterLast()) {
Log.e("======id======", result.getString(idColumnIndex) + "\t\t");
Log.e("======name======", result.getString(nameColumnIndex));
// System.out.print(result.getString(idColumnIndex) + "\t\t");
// System.out.println(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;
}
}
源码地址:
内网版(Eclipse):http://download.csdn.net/detail/y280903468/9535368
外网版(Android studio):http://download.csdn.net/detail/y280903468/9535374