Android 使用系统 API 操作 SQLite 数据库

如果你没有学过数据库相关的语法,或者不想写数据库语法,可以使用 Android 提供的操作数据库的一些 API 方法

范例

我们写一个 demo 操作 SQLite 数据库 lang.db 来演示 Android 提供的各个 API


  1. 创建一个 空的 Android 项目 cn.twle.android.DoSQLite

  2. 修改 activity_main.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="match_parent" 
        android:orientation="vertical" >
    
        <TextView
            android:layout_width="match_parent" 
            android:layout_height="wrap_content"
            android:text="ID (插入操作会忽略输入的 ID)"/>
    
        <EditText
            android:id="@+id/ms_id"
            android:layout_width="match_parent" 
            android:layout_height="wrap_content"/>
    
        <TextView
            android:layout_width="match_parent" 
            android:layout_height="wrap_content"
            android:text="开发语言,如(python)"/>
    
        <EditText
            android:id="@+id/ms_lang"
            android:layout_width="match_parent" 
            android:layout_height="wrap_content"/>
    
        <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" 
            android:orientation="horizontal" >
    
            <Button 
                android:text="插入"
                android:id="@+id/btn_insert"
                android:layout_weight="1"
                android:layout_width="wrap_content" 
                android:layout_height="wrap_content" />
    
            <Button 
                android:text="查询"
                android:layout_weight="1"
                android:id="@+id/btn_query" 
                android:layout_width="wrap_content" 
                android:layout_height="wrap_content" />
    
            <Button 
                android:text="修改"
                android:layout_weight="1"
                android:id="@+id/btn_update" 
                android:layout_width="wrap_content" 
                android:layout_height="wrap_content" />
    
            <Button 
                android:text="删除"
                 android:layout_weight="1"
                android:id="@+id/btn_delete" 
                android:layout_width="wrap_content" 
                android:layout_height="wrap_content" />
    
        </LinearLayout>
    
        <TextView
            android:layout_width="match_parent" 
            android:layout_height="wrap_content"
            android:text="操作结果"/>
        <TextView
            android:id="@+id/ms_rs"
            android:layout_width="match_parent" 
            android:layout_height="wrap_content" />
    
        <TextView
            android:layout_width="match_parent" 
            android:layout_height="wrap_content"
            android:text="表里的数据"/>
    
        <TextView
            android:id="@+id/ms_all"
            android:layout_width="match_parent" 
            android:layout_height="wrap_content" />
    
    </LinearLayout>
    
  3. MainActivity.java 目录下创建一个 SQLite 帮助类 MsDBOpenHelper.java

    package cn.twle.android.dosqlite;
    
    import android.content.Context;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    
    public class MsDBOpenHelper extends SQLiteOpenHelper {
    
        public MsDBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
                int version) {super(context, name, factory, version); }
    
        @Override
        //数据库第一次创建时被调用
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE lang(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(64))");
    
        }
        //软件版本号发生改变时调用
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("ALTER TABLE rank ADD phone INTEGER '0'");
        }
    }
    
  4. 修改 MainActivity.java

    package cn.twle.android.dosqlite;
    
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.os.Bundle;
    import android.support.v7.app.AppCompatActivity;
    import android.view.View;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.TextView;
    
    public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    
        private TextView ms_rs;
        private TextView ms_all;
    
        private EditText ms_id;
        private EditText ms_lang;
    
        private Context mContext;
        private Button btn_insert;
        private Button btn_query;
        private Button btn_update;
        private Button btn_delete;
        private SQLiteDatabase db;
        private MsDBOpenHelper msDBHelper;
        private StringBuilder sb;
        private int i = 1;
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
    
            mContext = MainActivity.this;
            msDBHelper = new MsDBOpenHelper(mContext, "ms.db", null, 1);
            bindViews();
            queryAll();
        }
    
        private void bindViews() {
            btn_insert = (Button) findViewById(R.id.btn_insert);
            btn_query  = (Button) findViewById(R.id.btn_query);
            btn_update = (Button) findViewById(R.id.btn_update);
            btn_delete = (Button) findViewById(R.id.btn_delete);
    
            ms_id   = (EditText) findViewById(R.id.ms_id);
            ms_lang = (EditText) findViewById(R.id.ms_lang);
    
            ms_rs   = (TextView) findViewById(R.id.ms_rs);
            ms_all  = (TextView) findViewById(R.id.ms_all);
    
            btn_query.setOnClickListener(this);
            btn_insert.setOnClickListener(this);
            btn_update.setOnClickListener(this);
            btn_delete.setOnClickListener(this);
        }
    
        @Override
        public void onClick(View v) {
    
            db = msDBHelper.getWritableDatabase();
            switch (v.getId()) {
                case R.id.btn_insert:
    
                    ContentValues values1 = new ContentValues();
                    values1.put("name",ms_lang.getText().toString());
    
                    //参数依次是:表名,强行插入 null 值得数据列的列名,一行记录的数据
                    db.insert("lang", null, values1);
    
                    ms_rs.setText("插入成功");
    
                    break;
                case R.id.btn_query:
                    sb = new StringBuilder();
                    //参数依次是:表名,列名,where 约束条件,where中占位符提供具体的值,指定 group by 的列,进一步约束
                    //指定查询结果的排序方式
                    Cursor cursor = db.query("lang", null, "id=?", new String[]{ms_id.getText().toString()}, null, null, null);
    
                    if (cursor.moveToFirst()) {
                        do {
                            int pid = cursor.getInt(cursor.getColumnIndex("personid"));
                            String name = cursor.getString(cursor.getColumnIndex("name"));
                            sb.append("id:" + pid + ":" + name);
                        } while (cursor.moveToNext());
                    }
                    cursor.close();
    
                    ms_rs.setText(sb.toString());
                    break;
                case R.id.btn_update:
    
                    ContentValues values2 = new ContentValues();
                    values2.put("name",ms_lang.getText().toString());
                    //参数依次是表名,修改后的值,where条件,以及约束,如果不指定三四两个参数,会更改所有行
                    db.update("lang", values2, "id= ?", new String[]{ms_id.getText().toString()});
    
                    ms_rs.setText("更新成功");
                    break;
                case R.id.btn_delete:
                    //参数依次是表名,以及where条件与约束
                    db.delete("lang", "id = ?", new String[]{ms_id.getText().toString()});
    
                    ms_rs.setText("删除成功");
                    break;
            }
    
            queryAll();
        }
    
        public void queryAll() {
    
            db = msDBHelper.getWritableDatabase();
            sb = new StringBuilder();
            //参数依次是:表名,列名,where 约束条件,where中占位符提供具体的值,指定 group by 的列,进一步约束
            //指定查询结果的排序方式
            Cursor cursor = db.query("lang", null, null, null, null, null, null);
    
            if (cursor.moveToFirst()) {
                do {
                    int pid = cursor.getInt(cursor.getColumnIndex("id"));
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    sb.append("id:" + pid + ":" + name +"\n");
                } while (cursor.moveToNext());
            }
            cursor.close();
    
            ms_all.setText(sb.toString());
        }
    }
    

Android 基础教程

关于   |   FAQ   |   我们的愿景   |   广告投放   |  博客

  简单教程,简单编程 - IT 入门首选站

Copyright © 2013-2022 简单教程 twle.cn All Rights Reserved.