我有一个来自SQL Server存储的过程的ArrayList，我想将所有数据插入SQLite数据库。请在下面查看我的代码

Question

我能够从SQL Server获取数据，但是我不知道该怎么办，请帮助我或任何想法如何将数据从Arraylist插入到SQLITE数据库中，然后将其插入自定义ListView。

................................................................................. ................................................................................

connectionhelper.java

package com.example.storedprocedures;

import android.annotation.SuppressLint;
import android.os.StrictMode;
import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectionHelper {
    Connection con;
    String ip = "***.***.*.***";
    String port = "*****";
    String classes = "net.sourceforge.jtds.jdbc.Driver";
    String database = "CentralizedDB";
    String username = "******";
    String password = "********";
    String url = "jdbc:jtds:sqlserver://"+ip+":"+port+"/"+database;

    @SuppressLint("NewApi")
    public Connection conclass(){
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Connection connection = null;
        try{
            Class.forName(classes);
            connection = DriverManager.getConnection(url, username, password);
        }catch(Exception exception){
            Log.e("Error:", exception.getMessage());
        }
        return connection;
    }
}

mainActivity.java

package com.example.storedprocedures;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import android.os.Bundle;

import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {
    TextView lblheader;
    Button btnviewall,btnview;
    ListView lstTaggedList;
    EditText edtid;

    Connection connect;
    ResultSet rs;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        lblheader = (TextView) findViewById(R.id.lblheader);
        lstTaggedList = (ListView) findViewById(R.id.lstTaggedList);
        btnviewall = (Button) findViewById(R.id.btnviewall);
        btnview = (Button) findViewById(R.id.btnview);
        edtid = (EditText) findViewById(R.id.edtid);


        ConnectionHelper connectionHelper = new ConnectionHelper();
        connect = connectionHelper.conclass();
        btnviewall.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                try {

                    PreparedStatement statement = connect.prepareStatement("EXEC SP_TaggedList");
                    final ArrayList list = new ArrayList();
                    rs = statement.executeQuery();
                    while (rs.next()) {
                        list.add(rs.getString("mrCode"));
                        list.add(rs.getString("docId_branch"));
                        list.add(rs.getString("brchName"));
                        list.add(rs.getString("industry_type"));
                        list.add(rs.getString("specialization_id"));
                        list.add(rs.getString("uName"));
                        list.add(rs.getString("class_id"));
                        list.add(rs.getString("max_visit"));
                    }
                    ArrayAdapter adapter = new ArrayAdapter(MainActivity.this,
                            android.R.layout.simple_list_item_1, list);
                    
                } catch (SQLException e) {
                    Toast.makeText(MainActivity.this, e.getMessage().toString(),
                            Toast.LENGTH_LONG).show();
                }
            }
        });

        btnview.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                try {

                    PreparedStatement statement = connect.prepareStatement("EXEC SP_TaggedList '"+edtid.getText().toString()+"'");
                    final ArrayList list = new ArrayList();
                    rs = statement.executeQuery();
                    while (rs.next()) {
                        list.add(rs.getString("mrCode"));
                        list.add(rs.getString("docId_branch"));
                        list.add(rs.getString("brchName"));
                        list.add(rs.getString("industry_type"));
                        list.add(rs.getString("specialization_id"));
                        list.add(rs.getString("uName"));
                        list.add(rs.getString("class_id"));
                        list.add(rs.getString("max_visit"));
                    }
                    ArrayAdapter adapter = new ArrayAdapter(MainActivity.this,
                            android.R.layout.simple_list_item_1, list);



                } catch (SQLException e) {
                    Toast.makeText(MainActivity.this, e.getMessage().toString(),
                            Toast.LENGTH_LONG).show();
                }
            }
        });
        lstTaggedList.setOnItemClickListener(new OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                                    int position, long id) {
                // TODO Auto-generated method stub

                String item = lstTaggedList.getItemAtPosition(position).toString();
                Toast.makeText(MainActivity.this, item + " selected", Toast.LENGTH_LONG).show();
            }
        });
    }
}

Answer 1

使用Android上的SQLITE，使用SQLite数据库的通常方法是拥有扩展sqliteopenhelper的类。

sqliteopenhelper如果数据不存在的情况，请调用ongreate方法将空的sqlitedatabase不完全空，因为sqlite_master and android_metadata表将存在，但会存在于用户表将关注）。然后，您可以使用Execsql方法为每个表（或其他组件，例如索引，查看触发器）创建表。

您还可以包括其他迎合Crud操作以访问数据的方法。

这是驱动ListView的数据库的简单演示。

第一个 databasehelper 即扩展了 sqliteopenhelper ： -

class DatabaseHelper extends SQLiteOpenHelper {

   public static final String DATABASE_NAME = "the_database.db";
   public static final int DATABASE_VERSION = 1;

   private static volatile DatabaseHelper INSTANCE; /* singleton instance of DatabaseHelper */
   private SQLiteDatabase db;

   /* Constructor (not publicly available) */
   private DatabaseHelper(Context context) {
      super(context,DATABASE_NAME,null,DATABASE_VERSION);
      db = this.getWritableDatabase();
   }

   /* Get singleton instance of DatabaseHelper */
   public static DatabaseHelper getInstance(Context context) {
      if (INSTANCE==null) {
         INSTANCE = new DatabaseHelper(context);
      }
      return INSTANCE;
   }

   public static final String TABLE_NAME = "the_table";
   public static final String COLUMN_MRCODE = BaseColumns._ID; /* Cursor Adapter must have _id column */
   public static final String COLUMN_DOCID_BRANCH = "docid_branch";
   public static final String COLUMN_BRCHNAME = "brchname";
   public static final String COLUMN_INDUSTRY_TYPE = "industry_type";
   public static final String COLUMN_SPECIALIZATION_ID = "specilization_id";
   public static final String COLUMN_UNAME = "uname";
   public static final String COLUMN_CLASS_ID = "class_id";
   public static final String COLUMN_MAX_VISIT = "max_visit";
   private static final String CRTSQL_THE_TABLE =
           "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
                   COLUMN_MRCODE + " INTEGER PRIMARY KEY" +
                   "," + COLUMN_DOCID_BRANCH + " INTEGER " +
                   "," + COLUMN_BRCHNAME + " TEXT " +
                   "," + COLUMN_INDUSTRY_TYPE + " TEXT " +
                   "," + COLUMN_SPECIALIZATION_ID + " INTEGER " +
                   "," + COLUMN_UNAME + " TEXT " +
                   "," + COLUMN_CLASS_ID + " INTEGER " +
                   "," + COLUMN_MAX_VISIT + " INTEGER " +
                   ")";
   private static final String CRTSQL_DOCID_BRANCH_INDEX =
           "CREATE INDEX IF NOT EXISTS " + COLUMN_DOCID_BRANCH + "_" + TABLE_NAME + "_idx001 " +
                   " ON " + TABLE_NAME + "(" +
                   COLUMN_DOCID_BRANCH +
                   ")";

   /* MUST be overridden (not much use if not used) */
   @Override
   public void onCreate(SQLiteDatabase db) {
      db.execSQL(CRTSQL_THE_TABLE);
      db.execSQL(CRTSQL_DOCID_BRANCH_INDEX);
   }

   /* MUST be overridden but doesn't necessarily have to do anything*/
   @Override
   public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

   }

   /* CRUD */

   public long insert(Long mrcode, long docid_branch, String brchname, String industry_type, String uname, long class_id, long max_visit ) {
      ContentValues cv = new ContentValues();
      if (mrcode != null) {
         cv.put(COLUMN_MRCODE,mrcode);
      }
      cv.put(COLUMN_DOCID_BRANCH,docid_branch);
      if (brchname != null) {
         cv.put(COLUMN_BRCHNAME,brchname);
      }
      if (industry_type != null) {
         cv.put(COLUMN_INDUSTRY_TYPE,industry_type);
      }
      if (uname != null) {
         cv.put(COLUMN_UNAME,uname);
      }
      cv.put(COLUMN_CLASS_ID,class_id);
      cv.put(COLUMN_MAX_VISIT,max_visit);
      return db.insert(TABLE_NAME,null,cv);
   }

   public Cursor getAllTheTableRows() {
      return db.query(TABLE_NAME,null,null,null,null,null,null);
   }
   @SuppressLint("Range")
   public long getNumberOfRowsInTheTable() {
      long rv=0;
      String output_column = "row_count";
      Cursor csr = db.query(TABLE_NAME,new String[]{"count(*) AS " + output_column},null,null,null,null,null);
      if (csr.moveToFirst()) {
         rv = csr.getLong(csr.getColumnIndex(output_column));
      }
      csr.close(); /* should always close cursors when done with them */
      return rv;
   }
}

• 的类

  • 有1个名为 the_table

  的表

  • 在docid_branch列上有一个索引
  • 所有组件名称均根据单个硬编码名称定义。

• 沿着您的代码可以看到的内容具有列。

• 已经使用了单身方法

• 已包括一些基本的CRUD方法，以允许插入和提取数据。

为了证明使用数据库的使用和驾驶listView的手段（通过Cursoradapter），然后 Main Activitive ： -

public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbHelper;
    ListView lv;
    SimpleCursorAdapter sca;
    Cursor csr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = DatabaseHelper.getInstance(this);
        lv = this.findViewById(R.id.lv);
        if (dbHelper.getNumberOfRowsInTheTable() < 1) {
            addSomeTestData();
        }
        setOrRefreshListView();
    }

    /*
        Whenever the ListView is to be changed (including initially)
        this can be called.
     */
    private void setOrRefreshListView() {
        csr = dbHelper.getAllTheTableRows(); /* gets the latest data from the database */
        if (sca == null) {
            sca = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_2,
                    csr,
                    new String[]{DatabaseHelper.COLUMN_UNAME,DatabaseHelper.COLUMN_INDUSTRY_TYPE}, /* The column names in the Cursor */
                    new int[]{android.R.id.text1,android.R.id.text2},0); /* The respective id's in the ListView's layout */
            lv.setAdapter(sca);
        } else {
            sca.swapCursor(csr);
        }
    }

    private void addSomeTestData() {
        dbHelper.insert(001L,111,"Branch001","MINING","FRED",1,10);
        dbHelper.insert(null,222,"Branch002","HOSPITALITY","MARY",6,1);
        dbHelper.insert(null,333,"Branch003","REFINING","JANE",5,5);
    }
}

• 注意，对于Brevity

请

来说然后运行： -

然后使用应用检查： -

• 可以看到的bee specialization_id是无效的，这是因为insert> insert in <<代码> databasehelper 类省略了对列的考虑。

再次使用应用程序检查，但使用自定义查询： -

• ​
• ​

我如何将数据从我的arraylist插入到sqlite数据库

因此，使用上述数据，您只需要获取数据库helper的实例，然后在循环浏览阵列时调用插入方法。

• 这不是最好的解决方案，因为每个插入都在自己的交易中完成。