安卓学习-SQLite数据库-基本的增删改查

最新推荐文章于 2021-05-26 01:03:00 发布

weixin_34270606

最新推荐文章于 2021-05-26 01:03:00 发布

阅读量76

点赞数

文章标签：数据库移动开发 java

原文链接：https://my.oschina.net/xiaofeiandroid/blog/652003

版权

2019独角兽企业重金招聘Python工程师标准>>>

案例一：代码包含基本的增删改查，仔细研究代码

PetMetaData.java数据库元数据的定义

package com.example.administrator.sqlite;

import android.provider.BaseColumns;

/**
 * descreption:数据库元数据的定义
 * Created by Administrator on 2016/4/3.
 */
public final class PetMetaData {
    private PetMetaData(){}
    //Dog表的定义.默认 有_ID
    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";

    }
}

DatabaseHelper.java用于创建数据库表

package com.example.administrator.sqlite;

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

/**
 * Created by Administrator on 2016/4/3.
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "pet.db";
    private static final int VERSION = 1;
    private static final String CREATE_TABLE_DOG = "CREATE TABLE dog(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "name TEXT,age INTEGER)";
    private static final String DROP_TABLE_DOG = "DROP TABLE IF EXISTS dog";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    //如果数据库表不存在，那么会调用该方法
    @Override
    public void onCreate(SQLiteDatabase db) {
        //SQLiteDatabase 用于操作数据库的工具类
        db.execSQL(CREATE_TABLE_DOG);
    }

    //升级
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       db.execSQL(DROP_TABLE_DOG);
       db.execSQL(CREATE_TABLE_DOG);
    }
}

Dog.java 普通JavaBean

package com.example.administrator.sqlite;

/**
 * Created by Administrator on 2016/4/3.
 */
public class Dog {
    private int id;
    private String name;
    private int age;

    public Dog() {

    }

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

    public Dog(String name, int age) {

        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 + '\'' +
                '}';
    }
}

DatabaseAdapter.java实现数据库表的增删改查

package com.example.administrator.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 2016/4/3.
 */
public class DatabaseAdapter {
    private DatabaseHelper dbHelper;

    public DatabaseAdapter(Context context) {
        dbHelper = new DatabaseHelper(context);
    }

    //事务处理
    public void transaction() {
        //多个操作使用事务
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.beginTransaction();//开始事务
        try {
            db.execSQL("insert into dog(name,age) values('duang',4)");
            db.execSQL("insert into dog(name,age) values('duang',3)");
            db.setTransactionSuccessful();//用来设置事务的成功标记（true）
        } finally {
            db.endTransaction();//  结束事务，判断事务标记是否为true，如果为true，那么就提交事务，否则回滚（撤销）
        }
        db.close();

    }

    public void rawAdd(Dog dog) {
        String sql = "insert into dog(name,age) values(?,?)";
        Object[] args = {dog.getName(), dog.getAge()};
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL(sql, args);
        db.close();
    }


    //添加操作
    public void add(Dog dog) {
        //获取操作数据库的工具类
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());
        //参数（表明，可以为NULL的列名，ContentValues）
        //合法：insert into dog(name,age)values('',Z);
        //不合法：insert into dog() values();
        db.insert(PetMetaData.DogTable.TABLE_NAME, null, values);
        db.close();
    }

    public void rawDelete(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "delete from dog where _id=?";
        Object[] args = {id};
        db.execSQL(sql, args);
        db.close();
    }


    //删除操作
    public void delete(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String whereClause = PetMetaData.DogTable._ID + "=?";
        String[] whereArgs = {String.valueOf(id)};
        //表名，条件，条件的值
        db.delete(PetMetaData.DogTable.TABLE_NAME, whereClause, whereArgs);
        db.close();
    }


    public void rawUpdate(Dog dog) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "update  dog set name=?,age=? where _id=?";
        Object[] args = {dog.getName(), dog.getAge(), dog.getId()};
        db.execSQL(sql, args);
        db.close();
    }

    //更新操作
    public void update(Dog dog) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());
        String whereClause = PetMetaData.DogTable._ID + "=?";
        String[] whereArgs = {String.valueOf(dog.getId())};
        //表明，ContentValues,条件，条件的值
        db.update(PetMetaData.DogTable.TABLE_NAME, values, whereClause, whereArgs);
        db.close();
    }


    public Dog rawFindById(int id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select _id,name,age from dog where _id=?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(id)});
        Dog dog = null;
        if (c.moveToNext()) {
            dog = new Dog();
            dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
        }
        c.close();
        db.close();
        return dog;
    }

    //根据ID查询单个记录
    public Dog findById(int id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};
        //是否去除重复列记录，表名，要查询的列，查询条件，查询条件的值，分组条件，分组条件的值，排序，分页条件
        Cursor c = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, PetMetaData.DogTable._ID + "=?", 
                new String[]{String.valueOf(id)}, null, null, null, null);
        Dog dog = null;
        if (c.moveToNext()) {
            dog = new Dog();
            dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
        }
        c.close();
        db.close();
        return dog;
    }


    public ArrayList<Dog> rawFindAll() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql = "select _id,name,age from dog";
        Cursor c = db.rawQuery(sql, null);
        ArrayList<Dog> dogs = new ArrayList<Dog>();
        Dog dog = null;
        while (c.moveToNext()) {
            dog = new Dog();
            dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
            dogs.add(dog);
        }
        c.close();
        db.close();
        return dogs;
    }

    //查询所有
    public ArrayList<Dog> findAll() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};
        //是否去除重复列记录，表名，要查询的列，查询条件，查询条件的值，分组条件，分组条件的值，排序，分页条件
        Cursor c = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, null, null, null, null, null, null);
        ArrayList<Dog> dogs = new ArrayList<Dog>();
        Dog dog = null;
        while (c.moveToNext()) {
            dog = new Dog();
            dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
            dogs.add(dog);
        }
        c.close();
        db.close();
        return dogs;
    }

}

activity_main.xml 布局文件

<?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:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.example.administrator.sqlite.MainActivity">


    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="添加数据"
        android:id="@+id/button"
        android:onClick="addClick"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除数据"
        android:onClick="deleteClick"
        android:id="@+id/button2"
        android:layout_below="@+id/button"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="修改数据"
        android:onClick="updateClick"
        android:id="@+id/button3"
        android:layout_below="@+id/button2"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询一个"
        android:onClick="findByIdClick"
        android:id="@+id/button4"
        android:layout_below="@+id/button3"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询所有"
        android:onClick="findAllClick"
        android:id="@+id/button5"
        android:layout_below="@+id/button4"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />
</RelativeLayout>

MainActivity.java 主界面

package com.example.administrator.sqlite;

import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;

import java.util.ArrayList;

public class MainActivity extends ActionBarActivity {
    private DatabaseAdapter dbAdapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbAdapter = new DatabaseAdapter(this);
    }

    public void addClick(View view) {
        Dog dog = new Dog("doge", 5);
        dbAdapter.add(dog);

    }

    public void deleteClick(View view) {
        dbAdapter.delete(1);
    }

    public void updateClick(View view) {
        Dog dog = new Dog(1,"wangwang", 5);
   dbAdapter.update(dog);
    }

    public void findByIdClick(View view) {
        Dog dog=dbAdapter.findById(1);
        System.out.println(dog);

    }

    public void findAllClick(View view) {
        ArrayList<Dog> dogs=dbAdapter.findAll();
        int size=dogs.size();
        for (int i=0;i<size;i++)
        {
            System.out.println(dogs.get(i));
        }
    }

}