Android之轻量数据库SQLite

不念不忘少年蓝@ 2022-07-12 13:15 204阅读 0赞

### SQLite学习 ###

一个朋友问我SQLite运用的怎么样 我就给他说了一下步骤。  
我就有回顾了一下 顺便再练习练习 但是实际写起来 还真有点手生啦！我就抓紧敲了起来  
以下就是源码:

#### MySqlite ####

package com.example.android_sqlite;
    
    import android.content.Context;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.widget.Toast;
    /* 创建Sqlite帮助类 */
    public class MySqlite extends SQLiteOpenHelper { 
        public static final String CREATE_BOOK = "create table Book("
                + "id integer primary key autoincrement, " +
                "author text, " +
                "price real, " +
                "pages integer, " +
                "name text)";
        private Context mContext;
        public static final String CREATE_CATEGORY = "create table Category(" +
                "id integer primary key autoincrement, " +
                "category_name text, " +
                "category_code integer)";
    
        public MySqlite(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
            mContext = context;
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
       //创建方法
            db.execSQL(CREATE_BOOK);
            db.execSQL(CREATE_CATEGORY);
            Toast.makeText(mContext, "建表成功", Toast.LENGTH_SHORT).show();
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int i, int i1) {
       //升级方法
            //如果表已经存在就删除 重新调用oncreate方法
            db.execSQL("drop table if exists Book");
            db.execSQL("drop table if exists Category");
            onCreate(db);
        }
    }

#### MainActivity ####

package com.example.android_sqlite;
    
    import android.content.ContentValues;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.util.Log;
    import android.view.View;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.ListView;
    import android.widget.SimpleAdapter;
    import android.widget.Toast;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Objects;
    
    /* android内置数据库sqlite的使用 步骤： 1.创建数据库使用SqliteOpenHelp帮助类（创建、升级） 2.通过ContentValues对象取出数据库的数据 */
    public class MainActivity extends AppCompatActivity implements View.OnClickListener { 
    
        private Button btcreat;
        private MySqlite mySqlite;
        private Button Add_data, Delete_data, retr_data, Updata;
        private ListView listView;
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            creatSql();
            initView();
        }
    
        private void creatSql() {
            //初始化数据库 填写需要创建的数据库的名字 游标用null先代替 版本是1
            mySqlite = new MySqlite(this, "BookStore.db", null, 2);
        }
    
        private void initView() {
            btcreat = (Button) findViewById(R.id.btcreat);
            btcreat.setOnClickListener(this);
            Add_data = (Button) findViewById(R.id.Add_data);
            Add_data.setOnClickListener(this);
            Delete_data = (Button) findViewById(R.id.Delete_data);
            Delete_data.setOnClickListener(this);
            retr_data = (Button) findViewById(R.id.retr_data);
            retr_data.setOnClickListener(this);
            Updata = (Button) findViewById(R.id.Updata);
            Updata.setOnClickListener(this);
            listView = (ListView) findViewById(R.id.lv);
        }
    
        @Override
        public void onClick(View view) {
            //创建数据库帮助对象
            SQLiteDatabase db = mySqlite.getWritableDatabase();
            //添加数据对象
            ContentValues values = new ContentValues();
            switch (view.getId()) {
                case R.id.btcreat:
                    //调用getWritableDatabase方法会创建数据库
                    mySqlite.getWritableDatabase();
                    Toast.makeText(this, "创建数据库", Toast.LENGTH_SHORT).show();
                    break;
                case R.id.Add_data:
                    //开始添加第一条数据
                    values.put("name", "HELLO ANDROID");
                    values.put("author", "X-X-X");
                    values.put("pages", "448");
                    values.put("price", "47.00");
                    db.insert("Book", null, values);
                    values.clear();
                    //插入第二条数据
                    values.put("name", "爱上编程");
                    values.put("author", "X1-X1-X1");
                    values.put("pages", "500");
                    values.put("price", "60.00");
                    db.insert("Book", null, values);
                    break;
                case R.id.Delete_data:
                    db.delete("Book", "pages > ?", new String[]{
       "449"});
                    break;
                case R.id.retr_data:
                    //数据的查询返回的是cursor对象 从cursor中得到数据
                    Cursor cursor = db.query("Book", null, null, null, null, null, null);
                    //遍历cursor对象
                    if (cursor.moveToFirst()) {
                        do {
                            String name = cursor.getString(cursor.getColumnIndex("name"));
                            String author = cursor.getString(cursor.getColumnIndex("author"));
                            int pages = cursor.getInt(cursor.getColumnIndex("pages"));
                            double price = cursor.getDouble(cursor.getColumnIndex("price"));
                            List<Map<String, Object>> list = new ArrayList<>();
                            Map<String, Object> map = new HashMap<>();
                            map.put("姓名", name);
                            map.put("作者", author);
                            map.put("页数", pages);
                            map.put("价格", price);
                            list.add(map);
                            SimpleAdapter simpleAdapter = new SimpleAdapter(this, list, R.layout.item_layout,
                                    new String[]{
       "姓名", "作者", "页数", "价格"},
                                    new int[]{R.id.name, R.id.author, R.id.pages, R.id.price});
                            listView.setAdapter(simpleAdapter);
                        } while (cursor.moveToNext());
                    }
                    break;
                case R.id.Updata:
                    values.put("pages", "500");
                    db.update("Book", values, "author = ?", new String[]{
       "X-X-X"});
                    break;
            }
        }
    }

#### activity\_main ####

<?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        xmlns:tools="http://schemas.android.com/tools"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        tools:context="com.example.android_sqlite.MainActivity">
    
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:gravity="center"
            android:text="Android之Sqlite数据库"
            android:textColor="@android:color/black" />
    
        <View
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="@android:color/holo_blue_bright" />
    
        <Button
            android:id="@+id/btcreat"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="创建数据库" />
    
        <Button
            android:id="@+id/Add_data"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="插入数据" />
    
        <Button
            android:id="@+id/Delete_data"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="删除数据" />
    
        <Button
            android:id="@+id/retr_data"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="查询数据" />
    
        <Button
            android:id="@+id/Updata"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="更新数据" />
    
        <ListView
            android:id="@+id/lv"
            android:layout_width="match_parent"
            android:layout_height="match_parent" />
    </LinearLayout>

#### item\_layout ####

<?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:id="@+id/name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    
        <TextView
            android:id="@+id/author"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    
        <TextView
            android:id="@+id/pages"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    
        <TextView
            android:id="@+id/price"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

粘贴以上源码就可以运行啦！  
效果图就不上了，手机没有root不能录屏。