【Android 远程数据库操作】

按正常情况下，前端不应该直接进行远程数据库操作，这不是一个明智的方式，应该是后端提供对应接口来处理，奈何公司各方面原因需要前端这样做。

对此，我对远程数据库操作做了总结，便于自己复盘，同时，也分享给有需要的朋友们。

0、下载jdbc库

下载mysql官网：https://dev.mysql.com/downloads/connector/j/，按图片步骤下载所需jdbc的库
在这里插入图片描述

1、添加jdbc驱动依赖包

在Android studio的项目工程app-bulid.grade添加依赖：implementation files(‘libs/mysql-connector-java-5.1.48.jar’)或implementation 'mysql:mysql-connector-java:5.1.48’这两种方式
在这里插入图片描述

2、数据库连接操作

			private String DB_URL = "jdbc:mysql://127.0.0.1:3306/远程数据库名";//换成远程地址private String USER = "admin";//账号private String PASS = "123456";	//密码// Step 1: 加载 JDBC driverClass.forName("com.mysql.jdbc.Driver");//mysql高版本这里：com.mysql.cj.jdbc.Driver// Step 2: 打开连接Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

3、执行数据库语句操作，进行相关业务处理

			String sql = "select name, age, sex from User";// sql语句Statement statement = connection.createStatement();ResultSet rs = statement.executeQuery(sql);while (rs.next() && !isStopped) {// todo 相应业务处理}

4、数据库关闭

			// 操作完毕，数据库关闭rs.close();statement.close();connection.close();

5、完整代码

远程数据库操作工具类DatabaseAccessUtil，由于是耗时动作，需要在线程处理。

public class DatabaseAccessUtil {private static final String TAG = "F100 DatabaseAccess";private String DB_URL = "jdbc:mysql://127.0.0.1:3306/数据库名";private String USER = "admin";private String PASS = "123456";private static DatabaseAccessUtil instance;private ExecutorService executorService;private volatile boolean isStopped = false;private Future<?> currentTask = null; // 用于保存当前的任务private ProgressDialogUtil progressDialogUtil;private Handler mainHandler;private DatabaseAccessUtil() {this.executorService = Executors.newSingleThreadExecutor();mainHandler = new Handler(Looper.getMainLooper());}public static synchronized DatabaseAccessUtil getInstance() {if (instance == null) {instance = new DatabaseAccessUtil();}return instance;}public void start(Context context) {stop();isStopped = false;if (executorService == null || executorService.isShutdown()) {executorService = Executors.newSingleThreadExecutor();}if (!isStopped && !executorService.isShutdown()) {currentTask = executorService.submit(() -> downloadData(context));}}public void stop() {isStopped = true;if (currentTask != null) {currentTask.cancel(true); // 尝试取消当前任务currentTask = null;executorService.shutdownNow(); // 停止执行器服务}}private void showProgressDialog(Context context) {mainHandler.post(() -> {if (progressDialogUtil == null) {progressDialogUtil = new ProgressDialogUtil(context);progressDialogUtil.setOnCancelListener(() -> {stop();});}progressDialogUtil.showProgressDialog();});}// 查询表的总数量private int getTotalCountFromDB(Connection connection) throws SQLException {String sql = "select count(*) from User";Statement statement = connection.createStatement();ResultSet rs = statement.executeQuery(sql);rs.next();int totalCount = rs.getInt(1);rs.close();statement.close();return totalCount;}// 下载数据（相应业务处理）private void downloadData(Context context) {if (isStopped) return;try {String sql = "select name, age, sex from User";// Step 1: Register JDBC driverClass.forName("com.mysql.jdbc.Driver");//mysql高版本这里：com.mysql.cj.jdbc.Driver// Step 2: Open a connectionConnection connection = DriverManager.getConnection(DB_URL, USER, PASS);// Step 3: Execute a queryint totalCount = getTotalCountFromDB(connection);if (totalCount == 0) {connection.close();EventBus.getDefault().post(new CommonEvent(EventCode.FLAG_NO_DATA));return;}// 显示进度条// todo showProgressDialog(context);// 执行sql 业务逻辑Statement statement = connection.createStatement();ResultSet rs = statement.executeQuery(sql);int count = 0;while (rs.next() && !isStopped) {// 进度条更新count++;int progress = (count * 100) / totalCount;updateProgress(progress);// Retrieve by column nameString name = rs.getString("name");int age = rs.getInt("age");String sex = rs.getString("sex");// todo 业务逻辑处理}// 关闭rs.close();statement.close();connection.close();// 主线程回调， 这里我使用订阅EventBus.getDefault().post(new CommonEvent(EventCode.FLAG_SUCCESS));} catch (Exception e) {// 主线程回调EventBus.getDefault().post(new CommonEvent(EventCode.FLAG_FAIL));} finally {// 进度条关闭mainHandler.post(() -> {if (progressDialogUtil != null) progressDialogUtil.dismissProgressDialog();});}}// 进度条更新private void updateProgress(int progress) {mainHandler.post(() -> {if (progressDialogUtil != null) {progressDialogUtil.updateProgress(progress);}});}}

进度条ProgressDialogUtil，布局：一个进度条+进度条进度+取消按钮，【取消】按钮是1分钟后可点击。代码如下：

public class ProgressDialogUtil {private final long DELAY_TIME = 1 * 60 * 1000;//2分钟  2 * 60 * 1000private Dialog progressDialog;private ProgressBar progressBar;private TextView tvProgress;private Button btnCancel;private Handler handler;private boolean cancelEnabled = false;public ProgressDialogUtil(Context context) {progressDialog = new Dialog(context, R.style.CustomProgressDialog);progressDialog.setContentView(R.layout.dialog_progress);progressDialog.setCancelable(false);progressBar = progressDialog.findViewById(R.id.progressBar);tvProgress = progressDialog.findViewById(R.id.tvProgress);btnCancel = progressDialog.findViewById(R.id.btnCancel);btnCancel.setEnabled(false);handler = new Handler();}public void showProgressDialog() {Window window = progressDialog.getWindow();if (window != null) {WindowManager.LayoutParams params = window.getAttributes();params.width = 650;window.setAttributes(params);}progressDialog.show();// 2分钟后显示handler.postDelayed(new Runnable() {@Overridepublic void run() {if (progressDialog.isShowing()) {cancelEnabled = true;btnCancel.setEnabled(true);}}}, DELAY_TIME);}public void dismissProgressDialog() {if (progressDialog != null && progressDialog.isShowing()) progressDialog.dismiss();}public void updateProgress(int progress) {
//        while (progress <= 97) {progressBar.setProgress(progress);tvProgress.setText(progress + "%");
//        }}public void setOnCancelListener(Runnable cancelAction) {btnCancel.setOnClickListener(v->{if (cancelEnabled) {cancelAction.run();dismissProgressDialog();}});}
}