SQLite数据库

SQLite数据介绍

SQLite命令行操作

注: 可以使用sqliteStudio去实现可视化操作
http://sqlitestudio.pl/

定义数据库的元数据

我们先定义元数据, 包括表名和相应表数据, 在后期使用

PetMetaData.java

/**
 * 数据库元数据的定义
 * Created by Admin on 2016/5/22.
 * 不能继承, 并且不能实例化
 */
public final class PetMetaData {

    private PetMetaData(){};
    //表
    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";
    }
}

使用SQLiteOpenHelper创建数据库

示例代码:
DatabaseHelper

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 = "DROP TABLE IF EXISTS dog";

    //    private static
    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);
        //再创建
        db.execSQL(CREATE_TABLE_DOG);
    }
}

增删改查

实现增删改查, 代码如下

DatabaseAdapter.java

/**
 * 增删改查操作
 * Created by Admin on 2016/5/22.
 */
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(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());
        //表名, 可以为null的列名(用于保证SQl语句的合法性), 内容
        db.insert(PetMetaData.DogTable.TABLE_NAME, null, values);
        //关闭
        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 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())};

        db.update(PetMetaData.DogTable.TABLE_NAME, values, whereClause, whereArgs);


    }
    //根据ID查询单个记录
    public Dog findById(int id){

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};
        String selection = PetMetaData.DogTable._ID + "=?";
        String[] selectionArgs = {  String.valueOf(id)};
        //                是否去除重复值, 表名, 查询的咧, 条件, 条件的值, 分组, 分组的条件, 排序, 排序条件, 分页
        Cursor c = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, selection, selectionArgs, 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> 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 = 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;
    }
}

MainActivity.java

/**
 * 该工程用来学习SQLite数据库
 */
public class MainActivity extends AppCompatActivity {
    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(5, "测试文本2");
        dbAdapter.add(dog);
    }

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

    public void updateClick(View view) {
        Dog dog = new Dog(1, "测试文本", 5);
        dbAdapter.update(dog);

    }

    public void findByIdClick(View view) {
        Dog dog = dbAdapter.findById(3);
        Toast.makeText(MainActivity.this, dog.toString(), Toast.LENGTH_SHORT).show();
    }

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

使用原生sql操作

使用事务