Android手持机扫码出入库的开发详解-9.MSSQL2000数据库上传与下载数据

Android手持机扫码出入库的开发详解-9.MSSQL2000数据库上传与下载数据

MSSQL2000数据库上传与下载数据

MSSQL2000数据交互程序图

flowchart TD
    subgraph 数据交互模块
        subgraph 数据库连接层
            DBConnection[数据库连接管理]
            JTDS[jTDS驱动连接]
            Microsoft[Microsoft驱动连接]
            DBConnection --> JTDS
            DBConnection --> Microsoft
            Close[资源关闭机制]
            DBConnection --> Close
        end
        
        subgraph 数据库操作层
            QueryData[数据库操作方法]
            Query[查询操作]
            Update[更新操作]
            Batch[批量操作]
            BatchParams[带参数批量操作]
            QueryData --> Query
            QueryData --> Update
            QueryData --> Batch
            QueryData --> BatchParams
        end
        
        subgraph 数据下载层
            DownloadData[数据下载]
            AsyncDownload[异步下载任务]
            DownloadQuery[执行MSSQL查询]
            LocalInsert[插入本地SQLite]
            DownloadData --> AsyncDownload
            AsyncDownload --> DownloadQuery
            DownloadQuery --> LocalInsert
        end
        
        subgraph 数据上传层
            InStorage[入库扫码上传]
            AsyncUpload[异步上传任务]
            LocalQuery[查询本地数据]
            BuildSQL[构建批量SQL]
            UploadBatch[执行批量上传]
            InStorage --> AsyncUpload
            AsyncUpload --> LocalQuery
            LocalQuery --> BuildSQL
            BuildSQL --> UploadBatch
        end
        
        %% 模块间关系
        QueryData --> DBConnection
        DownloadQuery --> QueryData
        UploadBatch --> QueryData
    end
    
    %% 整体流程
    User[用户操作] --> |下载请求| DownloadData
    User --> |上传请求| InStorage
    DownloadData --> |更新UI| UI[界面展示]
    InStorage --> |更新UI| UI

MSSQL2000数据交互源代码

1. 数据库连接管理 (DBConnection.java)

package cbw.materials.util;

import android.util.Log;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * MSSQL2000数据库连接管理类
 * 提供直接数据库连接和资源关闭功能
 */
public class DBConnection {
    /**
     * 获取MSSQL2000数据库连接 (jTDS驱动)
     * @return 数据库连接对象
     * @throws Exception 连接异常
     */
    public static Connection getConnectionJtds() throws Exception {
        Connection conn = null;
        try {
            // 加载jTDS驱动
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            // 建立MSSQL2000数据库连接
            conn = DriverManager.getConnection(
                    "jdbc:jtds:sqlserver://192.168.10.1:1433/Materials",
                    "sa", ""
            );
            Log.i("DBConnection", "连接数据库成功");
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("DBConnection", "数据库连接错误: " + e.getMessage());
            throw e;
        }
        return conn;
    }

    /**
     * 获取MSSQL2000数据库连接 (Microsoft驱动)
     * @return 数据库连接对象
     * @throws Exception 连接异常
     */
    public static Connection getConnectionMicrosoft() throws Exception {
        Connection conn = null;
        try {
            // 加载Microsoft SQL Server驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // 建立数据库连接
            conn = DriverManager.getConnection(
                    "jdbc:sqlserver://192.168.10.1:1433;DatabaseName=Materials",
                    "sa", ""
            );
            Log.i("DBConnection", "连接数据库成功");
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("DBConnection", "数据库连接错误: " + e.getMessage());
            throw e;
        }
        return conn;
    }

    /**
     * 关闭数据库连接资源
     * @param conn 数据库连接对象
     * @param stmt Statement对象
     * @param pstmt PreparedStatement对象
     * @param rs ResultSet对象
     */
    public static void close(Connection conn, Statement stmt, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
            Log.i("DBConnection", "数据库资源关闭成功");
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("DBConnection", "关闭数据库资源错误: " + e.getMessage());
        }
    }

    /**
     * 关闭数据库连接资源(重载方法)
     * @param conn 数据库连接对象
     * @param stmt Statement对象
     * @param rs ResultSet对象
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        close(conn, stmt, null, rs);
    }

    /**
     * 关闭数据库连接资源(重载方法)
     * @param conn 数据库连接对象
     * @param stmt Statement对象
     */
    public static void close(Connection conn, Statement stmt) {
        close(conn, stmt, null, null);
    }

    /**
     * 关闭数据库连接资源(重载方法)
     * @param conn 数据库连接对象
     */
    public static void close(Connection conn) {
        close(conn, null, null, null);
    }
}

2. 数据库操作方法 (QueryData.java)

package cbw.materials.util;

import android.util.Log;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * MSSQL2000数据库操作类
 * 提供查询、更新、批量操作等数据库操作方法
 */
public class QueryData {
    /**
     * 执行查询操作，返回二维数组结果
     * @param sql SQL查询语句
     * @return 查询结果二维数组
     * @throws SQLException SQL异常
     */
    public static String[][] RsRowsCols(String sql) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String[][] result = null;
        try {
            conn = DBConnection.getConnectionJtds();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            
            ResultSetMetaData rsmd = rs.getMetaData();
            int columns = rsmd.getColumnCount();
            
            // 先将结果存储到List中
            List<String[]> rows = new ArrayList<>();
            while (rs.next()) {
                String[] row = new String[columns];
                for (int i = 0; i < columns; i++) {
                    row[i] = rs.getString(i + 1);
                }
                rows.add(row);
            }
            
            // 转换为二维数组
            int rowsCount = rows.size();
            result = new String[rowsCount][columns];
            for (int i = 0; i < rowsCount; i++) {
                result[i] = rows.get(i);
            }
            
            Log.i("QueryData", "查询操作成功，返回" + rowsCount + "行数据");
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("QueryData", "查询操作失败: " + e.getMessage());
            throw new SQLException(e);
        } finally {
            DBConnection.close(conn, stmt, rs);
        }
        return result;
    }

    /**
     * 执行更新操作(INSERT/UPDATE/DELETE)
     * @param sql SQL更新语句
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public static int RsExecuteUpdate(String sql) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        int result = -1;
        try {
            conn = DBConnection.getConnectionJtds();
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
            Log.i("QueryData", "更新操作成功，影响" + result + "行");
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("QueryData", "更新操作失败: " + e.getMessage());
            throw new SQLException(e);
        } finally {
            DBConnection.close(conn, stmt);
        }
        return result;
    }

    /**
     * 执行批量SQL语句
     * @param sqls SQL语句数组
     * @return 每条语句影响的行数数组
     * @throws SQLException SQL异常
     */
    public static int[] BatchUpdatingSqls(String[] sqls) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        int[] results = null;
        try {
            conn = DBConnection.getConnectionJtds();
            conn.setAutoCommit(false); // 开始事务
            stmt = conn.createStatement();
            
            // 添加所有SQL语句到批处理
            for (String sql : sqls) {
                stmt.addBatch(sql);
            }
            
            // 执行批处理
            results = stmt.executeBatch();
            conn.commit(); // 提交事务
            Log.i("QueryData", "批量操作成功，执行" + results.length + "条语句");
        } catch (Exception e) {
            if (conn != null) {
                conn.rollback(); // 事务回滚
            }
            e.printStackTrace();
            Log.e("QueryData", "批量操作失败: " + e.getMessage());
            throw new SQLException(e);
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true); // 恢复自动提交
            }
            DBConnection.close(conn, stmt);
        }
        return results;
    }

    /**
     * 执行带参数的批量更新
     * @param sql SQL语句模板
     * @param params 参数数组
     * @return 每条语句影响的行数数组
     * @throws SQLException SQL异常
     */
    public static int[] BatchUpdatingRecords(String sql, String[] params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int[] results = null;
        try {
            conn = DBConnection.getConnectionJtds();
            conn.setAutoCommit(false); // 开始事务
            pstmt = conn.prepareStatement(sql);
            
            // 处理参数并添加到批处理
            for (String param : params) {
                String[] values = param.split(",");
                for (int i = 0; i < values.length; i++) {
                    String[] parts = values[i].split(",");
                    if (parts[0].trim().equals("int")) {
                        pstmt.setInt(i + 1, Integer.parseInt(parts[1]));
                    } else {
                        pstmt.setString(i + 1, parts[1]);
                    }
                }
                pstmt.addBatch();
            }
            
            // 执行批处理
            results = pstmt.executeBatch();
            conn.commit(); // 提交事务
            Log.i("QueryData", "带参数批量操作成功，执行" + results.length + "条语句");
        } catch (Exception e) {
            if (conn != null) {
                conn.rollback(); // 事务回滚
            }
            e.printStackTrace();
            Log.e("QueryData", "带参数批量操作失败: " + e.getMessage());
            throw new SQLException(e);
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true); // 恢复自动提交
            }
            DBConnection.close(conn, null, pstmt, null);
        }
        return results;
    }
}

3. 数据下载逻辑 (DownloadData.java核心部分)

package cbw.materials.controller;

import android.os.AsyncTask;
import android.util.Log;
import android.widget.Toast;

import cbw.materials.util.QueryData;

/**
 * 数据下载类，从MSSQL2000下载数据到本地SQLite
 */
public class DownloadData extends AppCompatActivity {
    // ... 其他代码省略 ...

    /**
     * 异步下载数据任务
     */
    private class QueryAddressTask extends AsyncTask<String, Integer, String> {
        @Override
        protected String doInBackground(String... params) {
            String result = "";
            try {
                // 构建MSSQL查询语句
                String oSql = "SELECT * FROM 材料编码表";
                
                // 执行查询获取数据
                String[][] materialData = QueryData.RsRowsColsNotPares(oSql);
                
                // 将数据插入本地SQLite数据库
                if (materialData != null && materialData.length > 0) {
                    dao_Material.batchInsert(db, materialData);
                    result = "材料编码表下载完成：" + materialData.length + "条记录";
                    Log.i("DownloadData", result);
                } else {
                    result = "材料编码表无数据";
                }
                
                // 继续下载其他表数据...
                
            } catch (Exception e) {
                e.printStackTrace();
                Log.e("DownloadData", "数据下载失败: " + e.getMessage());
                result = "数据下载失败：" + e.getMessage();
            }
            return result;
        }

        @Override
        protected void onPostExecute(String result) {
            // 在主线程更新UI
            Toast.makeText(DownloadData.this, result, Toast.LENGTH_LONG).show();
            progressBar.setProgress(100);
            textView_DownloadData_ExecInfo.setText(result);
        }
    }
    
    // ... 其他代码省略 ...
}

4. 数据上传逻辑 (InStorageScanCode.java核心部分)

package cbw.materials.controller;

import android.os.AsyncTask;
import android.util.Log;
import android.widget.Toast;

import cbw.materials.util.QueryData;

/**
 * 入库扫码类，将本地扫码数据上传到MSSQL2000
 */
public class InStorageScanCode extends AppCompatActivity {
    // ... 其他代码省略 ...

    /**
     * 上传数据按钮点击事件
     */
    private class UploadDataOnClickListenerImpl implements View.OnClickListener {
        @Override
        public void onClick(View v) {
            textView_InStorageScanCode_ExecInfo.setText("正在上传数据,请稍后......");
            executeFunctionName = "UploadData";
            
            // 启动后台异步线程上传数据
            QueryAddressTask queryAddressTask = new QueryAddressTask();
            queryAddressTask.execute("UploadData");
        }
    }

    /**
     * 异步上传数据任务
     */
    class QueryAddressTask extends AsyncTask<String, Integer, String> {
        @Override
        protected String doInBackground(String... params) {
            if ("UploadData".equals(params[0])) {
                try {
                    return UploadDataToSqlServer();
                } catch (Exception e) {
                    e.printStackTrace();
                    Log.e("InStorageScanCode", "数据上传失败: " + e.getMessage());
                    return "上传失败: " + e.getMessage();
                }
            }
            return "上传失败";
        }

        @Override
        protected void onPostExecute(String result) {
            if (executeFunctionName.equals("UploadData")) {
                if (result.contains("完毕")) {
                    try {
                        // 上传成功后清空本地数据
                        Cursor cursor = dao.deleteAllData(db);
                        Refresh_ListView(cursor);
                        textView_InStorageScanCode_ExecInfo.setText(result);
                        Toast.makeText(InStorageScanCode.this, result, Toast.LENGTH_LONG).show();
                    } catch (Exception e) {
                        e.printStackTrace();
                        textView_InStorageScanCode_ExecInfo.setText("上传成功，但本地数据清空失败");
                    }
                } else {
                    textView_InStorageScanCode_ExecInfo.setText(result);
                    Toast.makeText(InStorageScanCode.this, result, Toast.LENGTH_LONG).show();
                }
            }
        }
    }

    /**
     * 将数据上传到MSSQL2000数据库
     * @return 上传结果信息
     * @throws SQLException SQL异常
     */
    private String UploadDataToSqlServer() throws SQLException {
        String str_Msg = "";
        Cursor cursor = null;
        try {
            if (sda == null) {
                OpenOrCreateDatabase(); // 打开本地SQLite数据库
            }
            cursor = dao.queryAll(db); // 获取本地所有扫码数据
        } catch (Exception e) {
            e.printStackTrace();
            return "获取本地数据失败: " + e.getMessage();
        }

        if (cursor != null && cursor.moveToFirst()) {
            int cursor_rows = cursor.getCount();
            String[] Sqls = new String[cursor_rows];
            
            // 构建批量插入SQL语句
            for (int i = 0; i < cursor_rows; i++) {
                Sqls[i] = "INSERT INTO 国产材料扫码入库临时表(货位号,材料名称,材料规格,颜色,库管员,厂家缩写,CBW对照号,单位,材料条码,厂家代码,生产日期,批号,数量,库管编码,码文,扫码人,扫码时间,扫码仓库,上传标记) VALUES(";
                
                for (int j = 0; j < cursor.getColumnCount(); j++) {
                    if (!cursor.getColumnName(j).equals("_id")) {
                        if (cursor.getColumnName(j).equals("数量") || cursor.getColumnName(j).equals("上传标记")) {
                            Sqls[i] += cursor.getString(j);
                        } else {
                            Sqls[i] += "'" + cursor.getString(j) + "'";
                        }
                        if (j < cursor.getColumnCount() - 1) {
                            Sqls[i] += ",";
                        }
                    }
                }
                Sqls[i] += ")";
                cursor.moveToNext();
            }

            // 执行批量上传到MSSQL2000
            int[] results = QueryData.BatchUpdatingSqls(Sqls);
            if (results[0] != -1) {
                str_Msg = "数据上传保存完毕！共保存" + String.valueOf(results.length) + "条记录！";
                Log.i("InStorageScanCode", str_Msg);
            } else {
                str_Msg = "数据上传失败！请重新上传？";
                Log.e("InStorageScanCode", str_Msg);
            }
        } else {
            str_Msg = "对不起，请扫码后再上传保存数据？";
        }
        return str_Msg;
    }
    
    // ... 其他代码省略 ...
}

5. 关键技术特点说明

1. 直接数据库连接：使用jTDS驱动直接连接MSSQL2000数据库，无需HTTP中间层
2. 异步处理：所有数据库操作通过AsyncTask在后台线程执行，避免阻塞UI
3. 事务管理：批量操作使用事务确保数据一致性，失败时自动回滚
4. 资源管理：完善的数据库连接和资源关闭机制，防止内存泄漏
5. 错误处理：详细的日志记录和异常处理，便于问题排查
6. 本地缓存：使用SQLite作为本地缓存，提高离线操作能力
7. 批量操作优化：通过BatchUpdatingSqls方法减少数据库连接次数，提高性能