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

原创 2016年05月30日 15:13:27

         网上很多人都说这种做法是不合理的,其实我也是这么觉得的,因为安全得不到保障,而且对数据库操作的业务逻辑不可能都放在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




Android 使用JDBC连接数据库

在大多数Android项目中,数据的读取和保存都是通过调用服务器的接口来实现的,所以对于本地数据库的操作是很少的。但是,在前段时间的项目中,客户提出了要直接连接服务器上的数据库来操作数据,这对于一个A...

Android学习笔记(21)————利用JDBC连接服务器数据库

/********************************************************************************************  * aut...
  • conowen
  • conowen
  • 2012年04月07日 16:51
  • 112803

Android使用JDBC连接mysql数据库

移动设备中常用的数据库时sqlite数据库,Android设备中也不例外,但是今天我们就来讨论一下如何使用Android连接mysql数据库在实际的项目中貌似很少使用android连接mysql数据库...

Android通过JDBC直连MySql

最近在做一个Android的应用软件,为了图省事就打算使用Android通过JDBC连接Mysql,在这个过程中也遇到了些问题,通过网上查找最终整好了,把这个过程整理下。 一、准备条件 1.ADT...

AndroidStudio使用JDBC并利用Mysql6.0进行增删改查

AndroidStudio使用JDBC并利用Mysql6.0进行增删改查 在AndroidStudio创建java项目 AndroidStudio引入Mysql jar包 6.0正确的DBHelper...

Libs文件夹下的Jar文件为什么不会自动放在Android Private Libraries目录下

这个问题一开始我出现了一种“自以为是”的答案,在Android Private Libraries目录下的是会打包到工程可执行文件当中去的,而不自动编译的原因是因为Libs下面的jar文件太大导致的,...

Android消息循环机制

转载请注明出处:http://blog.csdn.net/crazy1235/article/details/50771703Android的消息循环机制主要先关的类有: Handler Looper...

java jdbc使用SSH隧道连接mysql数据库demo

package com.yws.echo_socket; import com.jcraft.jsch.JSch; import com.jcraft.jsch.Session; import ...
  • earbao
  • earbao
  • 2015年12月08日 12:16
  • 9052
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Android直接使用JDBC+SSH连接外网MySQL数据库
举报原因:
原因补充:

(最多只允许输入30个字)