数据库存储—SQLite

最新推荐文章于 2025-06-06 20:13:40 发布

xxz2645746142

最新推荐文章于 2025-06-06 20:13:40 发布

阅读量227

点赞数

CC 4.0 BY-SA版权

分类专栏： android

本文链接：https://blog.youkuaiyun.com/xxz2645746142/article/details/80683234

android 专栏收录该内容

21 篇文章

订阅专栏

1.为什么要用SQLite

平时所接触到的数据都有一个共性，数据量大，数据结构复杂用Sharedprefences只是简单的用xml存储，容易出错，效率低下

2.什么是SQLite

是android提供的数据存储的方式之一，专门为嵌入式设备所设计的轻量级数据库；
不需要用户名，密码，可以直接使用
不是Android所独有的，为嵌入式设备设计的轻量级数据库
创建数据表

create table product(
                 主键                     自增长
id  integer primary key  autoincrement not null ,
name text,
price real,
description text,
pic_url text
);

3.如何创建数据库和数据表
1.新建类继承SQLiteOpenHelper
2.实现构造方法
3.重写OnCreate()方法
4.重写onUpdategrade()方法
5.实例化SQLiteOpen的Helper子类对象

package com.example.xiaozhen.webapplication;

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

/**
 * Created by xiaozhen on 2018/6/13.
 */

public class MySQLiteHelper extends SQLiteOpenHelper {
    private String sql="create table student(" +
            "id integer primary key autoincrement not null," +
            "name text," +
            "age integer" +
            ")";

    public MySQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //创建数据库，只需创建一次
             sqLiteDatabase.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
//修改数据在这里修改
    }
}

实现增删改查，添加相应的方法
4.如何添加数据

 private void addstudent() {
        //得到编辑框用户所输入的name和age
      String name=  nameET.getText().toString();
       int age= Integer.parseInt(ageET.getText().toString());
             //创建values
        ContentValues values=new ContentValues();
        //里面的字段是表中的列名
        values.put("name",name);
        values.put("age",age);
        //调用刚刚写的子类
        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getWritableDatabase();
        //实现插入调用insert方法，里面有三个参数，第一个是表名，第二个是，第三个是values
        db.insert("student",null,values);
        Log.e("selectstudent",name+"-"+age+"-");


    }

5.如何删除数据

  private void delectstudent() {

        String name=  nameET.getText().toString();
        int age= Integer.parseInt(ageET.getText().toString());

        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getReadableDatabase();
        db.delete("student","name=?",new String[]{name});



    }

6.如何修改数据

 private void updatestudent() {
        String name=  nameET.getText().toString();
        int age= Integer.parseInt(ageET.getText().toString());

        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getReadableDatabase();
        ContentValues values=new ContentValues();
        values.put("name","zyx");
        db.update("student",values,"name=? and age=?",new String[]{name,age+""});
    }

7.如何查询数据

 private void selectstudent() {
        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getReadableDatabase();

        Cursor cursor=db.query("student",null,null,null,null,null,null);
        //一定要添加不然会游标越界报错
        cursor.moveToFirst();
        mysqlList.clear();
       do{
           int id=cursor.getInt(cursor.getColumnIndex("id"));
           String name=cursor.getString(cursor.getColumnIndex("name"));
           int age=cursor.getInt(cursor.getColumnIndex("age"));

           MySQL s=new MySQL(name,id,age);
           mysqlList.add(s);
           Log.e("selectstudent",name+"-"+age+"-");
        }while (cursor.moveToNext());
        sqladapter=new SQLAdapter(SQLActivity.this,mysqlList);
        listView.setAdapter(sqladapter);
    }

利用list view将表中的数据显示在布局下面,需要用到适配器，list view，和实体类这三样东西将数据添加进去
首先要创建一个item布局，

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="match_parent"
    android:layout_height="match_parent">
    <TextView
        android:text="id"
        android:id="@+id/tv1"
        android:layout_weight="1"
        android:layout_width="0dp"
        android:layout_height="wrap_content" />
    <TextView
        android:text="age"
        android:id="@+id/tv2"
        android:layout_weight="1"
        android:layout_width="0dp"
        android:layout_height="wrap_content" />
    <TextView
        android:text="name"
        android:id="@+id/tv3"
        android:layout_weight="1"
        android:layout_width="0dp"
        android:layout_height="wrap_content" />
</LinearLayout>

在activity布局文件中创建list view，放置数据表中的name，id，age

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_sql"
    android:layout_width="match_parent"
    android:layout_height="match_parent"

    tools:context="com.example.xiaozhen.webapplication.SQLActivity">
<EditText
    android:id="@+id/name_et"
    android:layout_width="match_parent"
    android:layout_height="50dp" />
    <EditText
        android:layout_below="@id/name_et"
        android:id="@+id/age_et"
        android:layout_width="match_parent"
        android:layout_height="50dp" />
    <Button
        android:text="添加"
        android:layout_below="@id/age_et"
        android:id="@+id/add_student"
        android:layout_width="match_parent"
        android:layout_height="50dp" />
    <Button
        android:text="修改"
        android:layout_below="@id/add_student"
        android:id="@+id/update_student"
        android:layout_width="match_parent"
        android:layout_height="50dp" />
    <Button
        android:text="删除"
        android:layout_below="@id/update_student"
        android:id="@+id/delete_student"
        android:layout_width="match_parent"
        android:layout_height="50dp" />
    <Button
        android:text="查询"
        android:layout_below="@id/delete_student"
        android:id="@+id/select_student"
        android:layout_width="match_parent"
        android:layout_height="50dp" />

    <ListView
        android:layout_below="@id/select_student"
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="match_parent">

    </ListView>
</RelativeLayout>

接着是实体类的创建，需要得到姓名，年龄，id这三个字段，所以实体类有三个属性

package com.example.xiaozhen.webapplication.entity;

/**
 * Created by xiaozhen on 2018/6/13.
 */

public class MySQL {
    private String name;
    private int id;
    private int age;

    public MySQL(){

    }
    public MySQL(String name, int id, int age) {
        this.name = name;
        this.id = id;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

然后用刚刚的增删改查四个方法，适配器部分都用备注的形式高亮显示，这里四个方法就不详写了

package com.example.xiaozhen.webapplication;

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.Button;
import android.widget.EditText;
import android.widget.ListView;

import com.example.xiaozhen.webapplication.adapter.SQLAdapter;
import com.example.xiaozhen.webapplication.entity.MySQL;

import java.util.ArrayList;
import java.util.List;

public class SQLActivity extends AppCompatActivity implements View.OnClickListener{
private EditText nameET,ageET;
private Button add_student,select_student,update_student,delect_student;

    //适配器的三样东西
    private SQLAdapter sqladapter;
    private List<MySQL> mysqlList=new ArrayList<>();
    private ListView listView;

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

    private void init() {
        nameET= (EditText) findViewById(R.id.name_et);
        ageET= (EditText) findViewById(R.id.age_et);
        add_student= (Button) findViewById(R.id.add_student);
        select_student= (Button) findViewById(R.id.select_student);
        update_student= (Button) findViewById(R.id.update_student);
        delect_student= (Button) findViewById(R.id.delete_student);
        add_student.setOnClickListener(this);
        select_student.setOnClickListener(this);
        update_student.setOnClickListener(this);
        delect_student.setOnClickListener(this);
        listView= (ListView) findViewById(R.id.listView);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()){
            case R.id.add_student:
                addstudent();
                break;
            case R.id.select_student:
                selectstudent();
                break;
            case R.id.update_student:
                updatestudent();
                break;
            case R.id.delete_student:
                delectstudent();
                break;
        }
    }

    private void delectstudent() {

        String name=  nameET.getText().toString();
        int age= Integer.parseInt(ageET.getText().toString());

        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getReadableDatabase();
        db.delete("student","name=?",new String[]{name});



    }

    private void updatestudent() {
        String name=  nameET.getText().toString();
        int age= Integer.parseInt(ageET.getText().toString());

        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getReadableDatabase();
        ContentValues values=new ContentValues();
        values.put("name","zyx");
        db.update("student",values,"name=? and age=?",new String[]{name,age+""});
    }

    private void selectstudent() {
        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getReadableDatabase();

        Cursor cursor=db.query("student",null,null,null,null,null,null);
        //一定要添加不然会游标越界报错
        cursor.moveToFirst();
        //每次循环都要将mysqlList清零，不然会数据重叠
        mysqlList.clear();
       do{
           int id=cursor.getInt(cursor.getColumnIndex("id"));
           String name=cursor.getString(cursor.getColumnIndex("name"));
           int age=cursor.getInt(cursor.getColumnIndex("age"));
            //将查询到的数据添加到实体类中去
           MySQL s=new MySQL(name,id,age);
           //为mysqlList填充所得到的数据
           mysqlList.add(s);
           Log.e("selectstudent",name+"-"+age+"-");
           //这俩步一定要放在循环外面写
          //实例化适配器
        sqladapter=new SQLAdapter(SQLActivity.this,mysqlList);
        //绑定适配器
        listView.setAdapter(sqladapter);
    }

    private void addstudent() {
      String name=  nameET.getText().toString();
       int age= Integer.parseInt(ageET.getText().toString());

        ContentValues values=new ContentValues();
        values.put("name",name);
        values.put("age",age);
        MySQLiteHelper mysqliteHelper=new MySQLiteHelper(this,"student_db",null,1);
        SQLiteDatabase db=mysqliteHelper.getWritableDatabase();
        db.insert("student",null,values);
        Log.e("selectstudent",name+"-"+age+"-");
    }

}