Android中如何将SQL Server返回的ArrayList存入SQLite并绑定自定义ListView

你当前代码最大的问题是把每行记录的8个字段零散存入ArrayList，既无法按行区分数据，也没法对接后续的数据库插入和列表绑定，按以下步骤改即可：

1. 新建数据实体类 #

新建TaggedItem.java，用来按行结构化存储存储过程返回的数据：

package com.example.storedprocedures;

public class TaggedItem {
    private String mrCode;
    private String docIdBranch;
    private String brchName;
    private String industryType;
    private String specializationId;
    private String uName;
    private String classId;
    private String maxVisit;

    public TaggedItem() {}

    // 全字段构造、get/set方法可以用Android Studio自动生成，这里列核心方法
    public String getMrCode() { return mrCode; }
    public void setMrCode(String mrCode) { this.mrCode = mrCode; }
    public String getDocIdBranch() { return docIdBranch; }
    public void setDocIdBranch(String docIdBranch) { this.docIdBranch = docIdBranch; }
    public String getBrchName() { return brchName; }
    public void setBrchName(String brchName) { this.brchName = brchName; }
    public String getIndustryType() { return industryType; }
    public void setIndustryType(String industryType) { this.industryType = industryType; }
    public String getSpecializationId() { return specializationId; }
    public void setSpecializationId(String specializationId) { this.specializationId = specializationId; }
    public String getuName() { return uName; }
    public void setuName(String uName) { this.uName = uName; }
    public String getClassId() { return classId; }
    public void setClassId(String classId) { this.classId = classId; }
    public String getMaxVisit() { return maxVisit; }
    public void setMaxVisit(String maxVisit) { this.maxVisit = maxVisit; }
}

2. 实现SQLite批量插入逻辑 #

新建SQLiteHelper.java，用SQLite事务做批量插入，性能比循环单条插入高10倍以上：

package com.example.storedprocedures;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.List;

public class SQLiteHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "local_biz.db";
    private static final int DB_VERSION = 1;
    private static final String TABLE_TAGGED = "tagged_list";

    public SQLiteHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE IF NOT EXISTS " + TABLE_TAGGED + "(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "mrCode TEXT," +
                "docId_branch TEXT," +
                "brchName TEXT," +
                "industry_type TEXT," +
                "specialization_id TEXT," +
                "uName TEXT," +
                "class_id TEXT," +
                "max_visit TEXT)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAGGED);
        onCreate(db);
    }

    public void batchSaveTaggedList(List<TaggedItem> dataList) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            // 不需要保留旧数据就先清空，根据业务需求调整
            db.delete(TABLE_TAGGED, null, null);
            ContentValues cv = new ContentValues();
            for (TaggedItem item : dataList) {
                cv.clear();
                cv.put("mrCode", item.getMrCode());
                cv.put("docId_branch", item.getDocIdBranch());
                cv.put("brchName", item.getBrchName());
                cv.put("industry_type", item.getIndustryType());
                cv.put("specialization_id", item.getSpecializationId());
                cv.put("uName", item.getuName());
                cv.put("class_id", item.getClassId());
                cv.put("max_visit", item.getMaxVisit());
                db.insert(TABLE_TAGGED, null, cv);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
            db.close();
        }
    }
}

3. 实现自定义ListView #

首先新建列表项布局res/layout/item_tagged.xml，按需加要展示的字段控件：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical"
    android:padding="12dp"
    android:background="?attr/selectableItemBackground">

    <TextView
        android:id="@+id/tv_mrcode"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="16sp"
        android:textStyle="bold"/>

    <TextView
        android:id="@+id/tv_brch"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="14sp"
        android:layout_marginTop="4dp"/>

    <TextView
        android:id="@+id/tv_user"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="14sp"
        android:layout_marginTop="2dp"/>
</LinearLayout>

新建自定义适配器TaggedListAdapter.java，替换原生ArrayAdapter：

package com.example.storedprocedures;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
import java.util.List;

public class TaggedListAdapter extends BaseAdapter {
    private Context context;
    private List<TaggedItem> dataList;

    public TaggedListAdapter(Context context, List<TaggedItem> dataList) {
        this.context = context;
        this.dataList = dataList;
    }

    @Override
    public int getCount() { return dataList.size(); }
    @Override
    public Object getItem(int position) { return dataList.get(position); }
    @Override
    public long getItemId(int position) { return position; }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        ViewHolder holder;
        if (convertView == null) {
            convertView = LayoutInflater.from(context).inflate(R.layout.item_tagged, parent, false);
            holder = new ViewHolder();
            holder.tvMrCode = convertView.findViewById(R.id.tv_mrcode);
            holder.tvBrch = convertView.findViewById(R.id.tv_brch);
            holder.tvUser = convertView.findViewById(R.id.tv_user);
            convertView.setTag(holder);
        } else {
            holder = (ViewHolder) convertView.getTag();
        }
        TaggedItem item = dataList.get(position);
        holder.tvMrCode.setText("MR编码：" + item.getMrCode());
        holder.tvBrch.setText("所属网点：" + item.getBrchName());
        holder.tvUser.setText("负责人：" + item.getuName());
        return convertView;
    }

    static class ViewHolder {
        TextView tvMrCode;
        TextView tvBrch;
        TextView tvUser;
    }
}

4. 修改MainActivity逻辑 #

把原来两个按钮点击事件里的零散取值逻辑替换成结构化存储、插库、绑列表的流程，以查询全部的按钮为例：

btnviewall.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
        try {
            PreparedStatement statement = connect.prepareStatement("EXEC SP_TaggedList");
            ArrayList<TaggedItem> list = new ArrayList<>();
            rs = statement.executeQuery();
            while (rs.next()) {
                TaggedItem item = new TaggedItem();
                item.setMrCode(rs.getString("mrCode"));
                item.setDocIdBranch(rs.getString("docId_branch"));
                item.setBrchName(rs.getString("brchName"));
                item.setIndustryType(rs.getString("industry_type"));
                item.setSpecializationId(rs.getString("specialization_id"));
                item.setuName(rs.getString("uName"));
                item.setClassId(rs.getString("class_id"));
                item.setMaxVisit(rs.getString("max_visit"));
                list.add(item);
            }
            // 批量存SQLite
            new SQLiteHelper(MainActivity.this).batchSaveTaggedList(list);
            // 绑定数据到自定义列表
            TaggedListAdapter adapter = new TaggedListAdapter(MainActivity.this, list);
            lstTaggedList.setAdapter(adapter);

            // 关闭资源
            rs.close();
            statement.close();
        } catch (SQLException e) {
            Toast.makeText(MainActivity.this, e.getMessage(), Toast.LENGTH_LONG).show();
        }
    }
});

按ID查询的按钮逻辑完全一致，注意不要用字符串拼接SQL，避免注入风险，改成参数占位符写法：

PreparedStatement statement = connect.prepareStatement("EXEC SP_TaggedList ?");
statement.setString(1, edtid.getText().toString());

列表点击事件同步修改，直接取实体对象即可：

lstTaggedList.setOnItemClickListener((parent, view, position, id) -> {
    TaggedItem selected = (TaggedItem) parent.getItemAtPosition(position);
    Toast.makeText(MainActivity.this, "选中：" + selected.getMrCode(), Toast.LENGTH_LONG).show();
});

注意事项 #

• 你当前用StrictMode绕过主线程网络请求限制的写法仅适合调试，正式环境必须把SQL Server连接、查询逻辑放到子线程执行，否则网络稍慢就会ANR闪退，查询完成后再切回主线程更新UI
• 数据库连接、ResultSet、PreparedStatement用完必须关闭，否则会出现内存泄漏
• 列表项布局可以按需增减字段，只要在适配器的getView方法里对应赋值即可

内容的提问来源于stack exchange，提问作者Ryan Baltazar