Android数据库SQLite的简单使用

本是古典何须时尚 2022-08-08 14:56 253阅读 0赞

Android数据库SQLite的简单使用：
    
    根据扣丁课堂的教程学习

源码地址：http://download.csdn.net/detail/ash_zheng/9117207
    
    
    首先可以创建一个类 用来表示数据库：
    package com.ashzheng.sqlitetest.sqlite;
    
    import android.provider.BaseColumns;
    
    /**
     * Created by Administrator on 2015/9/17.
     *
     * 元数据的定义
     */
    
    //不被继承
    public final class PetMetData {
    
        //不允许被实例化，私有构造方法
        private PetMetData(){}
    
        //表
        public static abstract class DogTable implements BaseColumns{
            public static final String TABLE_NAME = "dog";
            public static final String NAME = "name";
            public static final String AGE = "age";
        }
    }
    
    
    然后可以新建类 用来表示一个实体，方便后续的操作：
    package com.ashzheng.sqlitetest.sqlite;
    
    /**
     * Created by Administrator on 2015/9/17.
     */
    public class Dog {
    
        private int id;
        private String name;
        private int age;
    
        public Dog() {
    
        }
    
        public Dog(String name, int age) {
            this.name = name;
            this.age = age;
        }
    
        public Dog(int id, String name, int age) {
    
            this.id = id;
            this.name = name;
            this.age = age;
        }
    
        public int getAge() {
            return age;
        }
    
        public void setAge(int age) {
            this.age = age;
        }
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        @Override
        public String toString() {
            return "Dog{" +
                    "age=" + age +
                    ", id=" + id +
                    ", name='" + name + '\'' +
                    '}';
        }
    }
    
    
    
    
    然后新建一个 继承自SQLiteOpenHelper的助手类：
    package com.ashzheng.sqlitetest.sqlite;
    
    import android.content.Context;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    
    /**
     * Created by Administrator on 2015/9/17.
     */
    public class DataBaseHelper extends SQLiteOpenHelper{
    
        private static final String DB_NAME = "pet.db";
        private static final int VERSION = 1;
    
        private static final String CREAT_TABLE = "CREATE TABLE dog(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT, age INTEGER)";
        private static final String DROP_TABLE = "DROP TABLE IF EXISTS dog";
    
        public DataBaseHelper(Context context) {
            super(context, DB_NAME, null, VERSION);
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
    
            db.execSQL(CREAT_TABLE);
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
            db.execSQL(DROP_TABLE);
            db.execSQL(CREAT_TABLE);
        }
    }
    
    
    
    
    然后新建一个DataBaseAdapter的类用来操作（增删改查等）数据库：
    package com.ashzheng.sqlitetest.sqlite;
    
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    
    import java.util.ArrayList;
    
    /**
     * Created by Administrator on 2015/9/17.
     */
    public class DataBaseAdapter {
    
        private DataBaseHelper dbHelper;
    
        public DataBaseAdapter(Context context) {
            dbHelper = new DataBaseHelper(context);
        }
    
        public void add(Dog dog) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
    
            ContentValues values = new ContentValues();
            values.put(PetMetData.DogTable.NAME, dog.getName());
            values.put(PetMetData.DogTable.AGE, dog.getAge());
    
            //null表示可以为空的列 的列
            db.insert(PetMetData.DogTable.TABLE_NAME, null, values);
            db.close();
        }
    
        public void delete(int id) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
    
            String whereClause = PetMetData.DogTable._ID + "=?";
            String[] whereArgs = {String.valueOf(id)};
    
            //表名，条件，条件值
            db.delete(PetMetData.DogTable.TABLE_NAME, whereClause, whereArgs);
            db.close();
        }
    
        public void update(Dog dog) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
    
            ContentValues values = new ContentValues();
            values.put(PetMetData.DogTable.NAME, dog.getName());
            values.put(PetMetData.DogTable.AGE, dog.getAge());
    
            String whereClause = PetMetData.DogTable._ID + "=?";
            String[] whereArgs = {String.valueOf(dog.getId())};
    
            db.update(PetMetData.DogTable.TABLE_NAME, values, whereClause, whereArgs);
            db.close();
        }
    
        public Dog findById(int id) {
    
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            String[] colums = {PetMetData.DogTable._ID, PetMetData.DogTable.NAME, PetMetData.DogTable.AGE};
    
            //是否去除重复记录，表名，要查询的列，查询条件，查询条件的值，分组条件，分组条件的值，排序，分页条件
            Cursor cursor = db.query(true, PetMetData.DogTable.TABLE_NAME, colums, PetMetData.DogTable._ID + "=?",
                    new String[]{String.valueOf(id)}, null, null, null, null);
    
            Dog dog = null;
            if (cursor.moveToNext()) {
                dog = new Dog();
                dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable._ID)));
                dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetData.DogTable.NAME)));
                dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable.AGE)));
            }
    
            return dog;
    
        }
    
        public ArrayList<Dog> findAll() {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            String[] colums = {PetMetData.DogTable._ID, PetMetData.DogTable.NAME, PetMetData.DogTable.AGE};
    
            //是否去除重复记录，表名，要查询的列，查询条件，查询条件的值，分组条件，分组条件的值，排序，分页条件
            Cursor cursor = db.query(true, PetMetData.DogTable.TABLE_NAME, colums, null, null, null, null, null, 
    
    null);
    
            ArrayList<Dog> dogs = new ArrayList<>();
            Dog dog = null;
            while (cursor.moveToNext()) {
                dog = new Dog();
                dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable._ID)));
                dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetData.DogTable.NAME)));
                dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable.AGE)));
                dogs.add(dog);
            }
    
            return dogs;
        }
    }
    
    
    这样就完成了，下面可以进行测试了
    我在布局中加了几个按钮
    <Button
            android:id="@+id/button"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:layout_alignParentTop="true"
            android:onClick="addClick"
            android:text="增" />
    
        <Button
            android:id="@+id/button2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:layout_below="@+id/button"
            android:onClick="deleteClick"
            android:text="删" />
    
        <Button
            android:id="@+id/button3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:layout_below="@+id/button2"
            android:onClick="updateClick"
            android:text="改" />
    
        <Button
            android:id="@+id/button4"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:layout_below="@+id/button3"
            android:onClick="findByIdClick"
            android:text="查一条" />
    
        <Button
            android:id="@+id/button5"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:layout_below="@+id/button4"
            android:onClick="findAllClick"
            android:text="查所有" />
    
    
    对应的：
    package com.ashzheng.sqlitetest;
    
    import android.app.Activity;
    import android.os.Bundle;
    import android.view.View;
    
    import com.ashzheng.sqlitetest.sqlite.DataBaseAdapter;
    import com.ashzheng.sqlitetest.sqlite.Dog;
    
    import java.util.ArrayList;
    
    public class MainActivity extends Activity {
    
        DataBaseAdapter dataBaseAdapter;
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
    
            dataBaseAdapter = new DataBaseAdapter(this);
        }
    
        public void addClick(View v) {
    
            Dog dog = new Dog("one", 5);
            dataBaseAdapter.add(dog);
            System.out.println("666666   " + "已添加！！！");
        }
    
        public void deleteClick(View v) {
            dataBaseAdapter.delete(1);
            System.out.println("666666   " + "已删除id为1的！！！");
        }
    
        public void updateClick(View v) {
    
            Dog dog = new Dog(1, "two", 10);
            dataBaseAdapter.update(dog);
            System.out.println("666666   " + "已更新！！！");
        }
    
        public void findByIdClick(View v) {
    
            Dog dog;
            dog = dataBaseAdapter.findById(1);
            System.out.println("666666   " + dog.toString());
        }
    
        public void findAllClick(View v) {
    
            ArrayList<Dog> dogs;
            dogs = dataBaseAdapter.findAll();
    
            int count = dogs.size();
            for (int i = 0; i < count; i++) {
                System.out.println("666666  " + dogs.get(i).toString());
            }
        }
    }
    
    如上
    详情可看视频http://www.codingke.com/course/167/learn#lesson/509