【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 driver
            Class.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 driver
            Class.forName("com.mysql.jdbc.Driver");//mysql高版本这里：com.mysql.cj.jdbc.Driver

            // Step 2: Open a connection
            Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

            // Step 3: Execute a query
            int 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 name
                String 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 * 1000

    private 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() {
            @Override
            public 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();
            }
        });
    }
}

这篇文章提供全部代码和操作思路，拿来就可以使用。

如果觉得还不错，给个一键三连呗~~~