Android直接使用JDBC+SSH连接外网MySQL数据库

         网上很多人都说这种做法是不合理的，其实我也是这么觉得的，因为安全得不到保障，而且对数据库操作的业务逻辑不可能都放在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();}@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_test);}@Overrideprotected 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