关闭

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

标签: JDBCandroidandroid studioeclipsessh
2274人阅读 评论(5) 收藏 举报
分类:

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




1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:23680次
    • 积分:438
    • 等级:
    • 排名:千里之外
    • 原创:19篇
    • 转载:0篇
    • 译文:0篇
    • 评论:10条
    文章分类
    最新评论