安卓菜鸟的进阶之路-SQLite数据库存储

利用SQLiteOpenHelper类对数据库进行管理

1.创建或打开数据库：

            getReadableDatabase():当数据库不可写入的时候，以只读方式打开数据库

            getWriteableDatabase:当数据库不可写入时将出现异常

           用法：新建一个类继承自SQLiteOpenHelper,用于执行SQL语句

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

/**
 * Created by 15534 on 2018/4/3.
 */

public class MyDataBaseHelper extends SQLiteOpenHelper {
    public static final String CREAT_BOOK = "create table Book("
            + "id integer primary key autoincrement,"
            +"author text,"
            +"price real,"
            +"pages integer,"
            +"name text)";
   
    private Context mContext;
    public MyDataBaseHelper(Context context, String name,
                            SQLiteDatabase.CursorFactory factory,int version){
        super(context,name,factory,version);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREAT_BOOK);//执行SQL语句
      
    }
    @Override
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){//用于升级数据库
        
    }

}

        创建该类的实例：

        private MyDatabaseHelper dbHelper;

        dbHelper = new MyDatabaseHelper(this,"BookStore.db",null,1)//1为版本号

         dbHelper.getWriteablebase()

2.升级数据库

            

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

/**
 * Created by 15534 on 2018/4/3.
 */

public class MyDataBaseHelper extends SQLiteOpenHelper {
    public static final String CREAT_BOOK = "create table Book("
            + "id integer primary key autoincrement,"
            +"author text,"
            +"price real,"
            +"pages integer,"
            +"name text)";
    public static final String CREAT_CATEGORY = "create table Category("
            + "id integer primary key autoincrement,"
            +"category_name text,"
            +"category_code integer)";
    private Context mContext;
    public MyDataBaseHelper(Context context, String name,
                            SQLiteDatabase.CursorFactory factory,int version){
        super(context,name,factory,version);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREAT_BOOK);//执行SQL语句
        db.execSQL(CREAT_CATEGORY);
        Toast.makeText(mContext, "Creat succeeded", Toast.LENGTH_SHORT).show();
    }
    @Override
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){//用于升级数据库
        db.execSQL("drop table if exists Book");//需删除原表以后重新调用onCreat()建立表，否则会报错
        db.execSQL("drop table if exists Category");
        onCreate(db);
    }

}

3.添加数据

SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name","The Da Vinci Code");
values.put("author","Cyl");
values.put("pages","454");
values.put("price","16.39");
db.insert("Book",null,values);
values.clear();
values.put("name","python");
values.put("author","Cyl");
values.put("pages","454");
values.put("price","16.39");
db.insert("Book",null,values);

4.更新数据

           将插入语句的最后一条改为db.updata("Book",values,"name = ?",new String[]{"Cyl"})//String[]用于存放name表达式右边的值

5.删除

            SQLiteDatabase db = dbHelper.getWritableDatabase();

            db.delete("Book","page>?",new String[]{"500"})

6.查询数据

            

SQLiteDatabase db = dbHelper.getWritableDatabase();
Cursor cursor = db.query("Book",null,null,null,null,null,null);
if(cursor.moveToFirst()){
    do{
        String name = cursor.getString(cursor.getColumnIndex("name"));
        queryButton.setText(name);
    }while (cursor.moveToNext());
}
cursor.close();

7.以上增删改查操作均可通过直接使用SQL语句完成

            用法与新建数据库相同

            添加、更新、删除：db.execSQLt("SQL语句")

            查询：db.rawQuery("SQL语句")

利用LitePal操作数据库

1.配置LitePal

            在app/build.grade 文件dependencies闭包中添加

            

compile 'org.litepal.android:core:1.3.2'

           在main目录下新建assets目录(Directory）,在assets目录下新建litepal.xml文件

            文件内容
            

<?xml version="1.0" encoding="utf-8"?>
    <litepal>
    <dbname value = "BookStore"></dbname>//数据库名
    <version value = "1"></version>//版本号
    <list>
        
    </list>
</litepal>

            在AndroidMainifest.xml文件<application>中配置

            

android:name="org.litepal.LitePalApplication"

2.创建数据库

            新建Book类//Book将成为数据库的一个表，类中的变量为表中的变量

import org.litepal.crud.DataSupport;

/**
 * Created by 15534 on 2018/4/4.
 */

public class Book extends DataSupport {
    private int id;
    private String author;
    private double price;
    private int pages;
    private String name;

    public int getId() {
        return id;
    }

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

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getName() {
        return name;
    }

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

    public int getPages() {

        return pages;
    }

    public void setPages(int pages) {
        this.pages = pages;
    }

    public String getAuthor() {

        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }
}

            在litepal.xml中添加

                        

 <list>
        <mapping class = "com.example.a15534.litepal.Book"></mapping>//表名，Book为一个javaBean
    </list>

            执行新建操作

            Connnector.getDatabase();

3.更新操作

            添加列：在Book.class中直接添加即可

            添加表：新建类，然后配置

            完成上面两种操作后需要在litepal.xml文件中修改版本号

4.添加数据

            

Book book = new Book();
book.setName("python");
book.setAuthor("Cyl");
book.setPages(125);
book.setPrice(15.66);
book.save();

5.更新

            法1：与添加数据相同，修改数值即可

            法2：

            Book book = new Book();

             book.setPrice(14.95);

            book.updateAll("name = ? and author = ?","Python","Cyl");

6.删除

            DataSupport.deleteAll(Book.class,"price < ?","15")

7.查询

            查询全部

           

List<Book> books = DataSupport.findAll(Book.class);
for(Book book:books){
    upData.setText(book.getName());
}

             指定列查询

   

List<Book> books = DataSupport.select("name","author").find(Book.class);

            查询第一行

List<Book> books = DataSupport.findFirst(Book.class);

            查询最后一行

List<Book> books = DataSupport.findLasr(Book.class);

            添加约束条件查询

List<Book> books = DataSupport.where("page > ?","400").find(Book.class);

            排序

List<Book> books = DataSupport.order("price desc").find(Book.class);//desc 降序  asc(默认)升序

            指定查询结果数量

          

List<Book> books = DataSupport.limit(3).find(Book.class);

            指定查询结果偏移量

List<Book> books = DataSupport.limit(3).offset(1).find(Book.class);//从第二条开始查询

   以上查询方式可以进行组合