Android的SQLite数据库的使用

tundra38

已于 2024-11-01 11:33:15 修改

阅读量448

点赞数 1

分类专栏： Android 文章标签：数据库 android sqlite

于 2023-10-10 09:34:41 首次发布

本文链接：https://blog.csdn.net/tundra38/article/details/133735764

版权

Android 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Android自带的数据库是sqlite，这是一个轻量级的数据库，sqlite官方表示理论存储容量为140TB，虽然目前应该没有那么大容量的手机，但平时测试练习足够使用了。注意这个和我们使用的MySQL数据库不是一个。
Android中的SQLite，支持的存储的数据类型一共有9种：Byte；Long；Short；Integer；Float；Double；String；Boolean；byte[]
下面我们以一个含有编辑框editText，下拉列表Spinner，复选按钮checkbox，单选按钮Radiobutton的页面来测试SQlite的使用方法。
首先构造布局文件如下：

<?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" >
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:height="50px"
            android:text="   姓 名 ： "
            />
        <EditText
            android:id="@+id/et_name"
            android:layout_width="300px"
            android:layout_height="wrap_content"
            android:singleLine="true"
            android:hint="请输入姓名"
            />
    </LinearLayout>
    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:height="50px"
            android:text="   年 龄 ： "
            />
        <EditText
            android:id="@+id/et_age"
            android:layout_width="300px"
            android:layout_height="wrap_content"
            android:inputType="text"
            android:hint="请输入年龄"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:height="50px"
            android:text="   专 业 ： "
            />
        <RadioGroup
            android:id="@+id/radioGroup1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:orientation="vertical"
            >
            <RadioButton
                android:id="@+id/rb1"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="计算机科学与技术"
                />
            <RadioButton
                android:id="@+id/rb2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="软件工程"
                />
            <RadioButton
                android:id="@+id/rb3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="网络工程"
                />
        </RadioGroup>
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:height="50px"
            android:text="   学 历 ： "
            />
        <Spinner
            android:id="@+id/edu"
            android:layout_width="149dp"
            android:layout_height="wrap_content"
            android:entries="@array/zhuanye" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:height="50px"
            android:text="   爱 好 ： "
            />
        <GridLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:columnCount="3"
            android:id="@+id/g1_hobby"
            >
            <CheckBox
                android:id="@+id/cb1"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="阅读"
                />
            <CheckBox
                android:id="@+id/cb2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="旅游"
                />
            <CheckBox
                android:id="@+id/cb3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="发呆"
                />
            <CheckBox
                android:id="@+id/cb4"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="唱歌"
                />
            <CheckBox
                android:id="@+id/cb5"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="编程"
                />
            <CheckBox
                android:id="@+id/cb6"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="运动"
                />
        </GridLayout>
    </LinearLayout>
    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center">
        <Button
            android:id="@+id/bt_login"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="录入"
          
            />
        <Button
            android:id="@+id/bt_reset"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="重置"
          
            />
        <Button
            android:id="@+id/bt_xianshi"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="显示"
            />
        <Button
            android:id="@+id/bt_check"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="查询"
            />
    </LinearLayout>
    <ListView
        android:id="@+id/lsv1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:divider="#f00"
        android:dividerHeight="2dp"
        android:layout_gravity="center"
        />
</LinearLayout>

List列表的单行布局文件如下

<?xml version="1.0" encoding="utf-8" ?>

<!--2.模板-->

<TextView
xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:textSize="20sp"
android:textColor="#009">

</TextView>
对应的acticity的代码如下：

package com.example.myapplication;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import androidx.appcompat.app.AppCompatActivity;

import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.content.Context;
import android.os.Bundle;
import android.app.Activity;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.EditText;
import android.widget.GridLayout;
import android.widget.ListView;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Spinner;
import android.view.View. OnClickListener;
import android.widget.Toast;


import java.util.ArrayList;

public class Activity2 extends AppCompatActivity{

    //声明
    private Context context;

    Spinner sp;
    //声明ListView对象
    ListView lsv;

    RadioGroup zy;

    EditText etname,etage;

    CheckBox cb1,cb2,cb3,cb4,cb5,cb6;
    CheckBox cb[]=new CheckBox[6];

    Button bt1,bt2,btxianshi,btcheck;
    //复选按钮组
    GridLayout g1;

    String name,specialty,shobby,edu,s,single;
    Double age;
    //
    ArrayList<String> list=new ArrayList<String>();;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.app);

        context =this;

        //获取控件对象
        //登记按钮和重置按钮
        bt1 = (Button) findViewById(R.id.bt_login);
        bt2 = (Button) findViewById(R.id.bt_reset);
        btxianshi = (Button) findViewById(R.id.bt_xianshi);
        btcheck = (Button) findViewById(R.id.bt_check);
        //复选框
        cb[0] = (CheckBox) findViewById(R.id.cb1);
        cb[1] = (CheckBox) findViewById(R.id.cb2);
        cb[2] = (CheckBox) findViewById(R.id.cb3);
        cb[3] = (CheckBox) findViewById(R.id.cb4);
        cb[4] = (CheckBox) findViewById(R.id.cb5);
        cb[5] = (CheckBox) findViewById(R.id.cb6);
        g1 = (GridLayout) findViewById(R.id.g1_hobby);
        etname = (EditText) findViewById(R.id.et_name);
        etage = (EditText) findViewById(R.id.et_age);
        zy = (RadioGroup) findViewById(R.id.radioGroup1);
        sp = (Spinner)findViewById(R.id.edu);
        lsv=(ListView)findViewById(R.id.lsv1);

        //登记信息
        bt1.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub

                edu = sp.getSelectedItem().toString();

                shobby="";
                //
                for(int i=0;i<g1.getChildCount() ;i++) {
                    CheckBox cbs = (CheckBox) g1.getChildAt(i);
                    //判断状态
                    if(cb[i].isChecked()) {
                        //因为是多选所以可以+=将多选的字符串连接
                        shobby+=cbs.getText().toString()+"  ";
                        System.out.println(cbs.getText().toString());
                    }
                }

                for(int i=0;i<zy.getChildCount();i++) {
                    RadioButton r = (RadioButton) zy.getChildAt(i);
                    if(r.isChecked()) {
                        specialty = r.getText().toString();
                    }
                }


                name = etname.getText().toString();
                age = Double.parseDouble(etage.getText().toString());

                String s="\n姓名:"+name+"\n年龄:"+age+"\n专业:"+specialty+"\n学历:"+edu+"\n爱好:"+shobby;

                list.add(s);
//增加数据库
                SQL dbHelper = new SQL(Activity2.this, 4);
                SQLiteDatabase database = dbHelper.getWritableDatabase();
               // String sql = "insert into user(name,balance)values('tom',12)";
                //database.execSQL(sql);
                ContentValues values=new ContentValues();
                values.put("name",name);
                values.put("age",age);
                values.put("specialty",specialty);
               values.put("edu",edu);
                values.put("shobby",shobby);
                database.insert("user",null,values);
                dbHelper.close();
                Toast.makeText(Activity2.this, "添加成功", Toast.LENGTH_SHORT).show();

            }
        });

        //重置
        bt2.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                name = etname.getText().toString();
                SQL query_dbHelper = new SQL(Activity2.this, 4);
                SQLiteDatabase query_database = query_dbHelper.getReadableDatabase();
                //select ... from ... where .. group by ... having ... order by ... limit...
                //返回游标，类似于jdbc中的结果集：ResultSet
                query_database.delete( "user","name=? ",new String[]{name});
                //清空编辑框
                etname.setText("");
                etage.setText("");
                //消除单选按钮的被选中状态
                for(int i=0;i<zy.getChildCount();i++) {
                    RadioButton r = (RadioButton) zy.getChildAt(i);
                    if(r.isChecked()) {
                        r.setChecked(false);
                    }
                }
                //清除复选按钮的被选中状态
                for(int i=0;i<g1.getChildCount() ;i++) {
                    CheckBox cbs = (CheckBox) g1.getChildAt(i);
                    cbs.setChecked(false);
                }

                lsv.setAdapter(null);
                sp.setSelection(0);


            }
        });

        btxianshi.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View view) {
                /**
                 * 从list集合中读取数据显示在文本框中
                 * 创建适配器对象
                 */
                ArrayAdapter<String> adapter =
                        new ArrayAdapter<String>(
                                Activity2.this,
                                R.layout.item2,
                                list);
                //关联
                lsv.setAdapter(adapter);

                SQL query_dbHelper = new SQL(Activity2.this, 4);
                SQLiteDatabase query_database = query_dbHelper.getReadableDatabase();
                //select ... from ... where .. group by ... having ... order by ... limit...
                //返回游标，类似于jdbc中的结果集：ResultSet
                Cursor cursor = query_database.query("user", null, null, null, null, null, null, null);
                while(cursor.moveToNext()){//①判断下一行是否还有数据②如果返回true，指针下移
                    @SuppressLint("Range")
                    int id = cursor.getInt(cursor.getColumnIndex("_id"));
                    @SuppressLint("Range")
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    @SuppressLint("Range")
                    double age = cursor.getDouble(cursor.getColumnIndex("age"));
                    @SuppressLint("Range")
                    String specialty = cursor.getString(cursor.getColumnIndex("specialty"));
                   @SuppressLint("Range")
                  String edu = cursor.getString(cursor.getColumnIndex("edu"));
                    @SuppressLint("Range")
                   String shobby = cursor.getString(cursor.getColumnIndex("shobby"));
                    Log.e("TAG", "id = " + id + ",name = " + name + ",age = " + age + ",specialty="+specialty+",edu="+edu+",shobby="+shobby);
                    String s="\n姓名:"+name+"\n年龄:"+age+"\n专业:"+specialty+"\n学历:"+edu+"\n爱好:"+shobby;
                    list.add(s);
                }
                cursor.close();
                query_database.close();
            }
        });
        btcheck.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View view) {
               
                name = etname.getText().toString();
                SQL query_dbHelper = new SQL(Activity2.this, 4);
                SQLiteDatabase query_database = query_dbHelper.getReadableDatabase();
                //select ... from ... where .. group by ... having ... order by ... limit...
                //返回游标，类似于jdbc中的结果集：ResultSet
                Cursor cursor = query_database.rawQuery("select*from user where name like ? ",new String[]{name});
                while(cursor.moveToNext()){//①判断下一行是否还有数据②如果返回true，指针下移
                    @SuppressLint("Range")
                    int id = cursor.getInt(cursor.getColumnIndex("_id"));
                    @SuppressLint("Range")
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    @SuppressLint("Range")
                    double age = cursor.getDouble(cursor.getColumnIndex("age"));
                    @SuppressLint("Range")
                    String specialty = cursor.getString(cursor.getColumnIndex("specialty"));
                    @SuppressLint("Range")
                    String edu = cursor.getString(cursor.getColumnIndex("edu"));
                    @SuppressLint("Range")
                    String shobby = cursor.getString(cursor.getColumnIndex("shobby"));
                    Log.e("TAG", "id = " + id + ",name = " + name + ",age = " + age + ",specialty="+specialty+",edu="+edu+",shobby="+shobby);
                    String single="\n姓名:"+name+"\n年龄:"+age+"\n专业:"+specialty+"\n学历:"+edu+"\n爱好:"+shobby;
                    list2.add(single);
                    ArrayAdapter<String> adapter =
                            new ArrayAdapter<String>(
                                    Activity2.this,
                                    R.layout.item2,
                                    list2);
                    //关联
                    lsv.setAdapter(adapter);
                }
                cursor.close();
                query_database.close();

            }
        });


    }




}

另外添加一个java文件，放数据库

package com.example.myapplication;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQL extends SQLiteOpenHelper {

    public SQL(Context context,int version){
        super(context,"user.db",null,version);
    }
    /**
     *
     * @param context:用于创建数据库的上下文
     * @param name:创建数据库的名称
     * @param factory：用户创建Cursor的工厂
     * @param version：数据库的版本
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql="create table user(_id integer primary key autoincrement,name varchar,age double,specialty varchar,edu varchar,shobby varchar)";
        db.execSQL(sql);
        //先给表中添加一条数据测试
        db.execSQL("insert into user(name,age,specialty,edu,shobby)values('Tom',1000,'computer','computer','read')");
    }
    /***
     * 当数据库升级的时候回调的方法
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}