SQLite 基础知识

1. 创建SQLiteOpenHelper内部类

 //内部类继承  需要自己生成构造器
    class DBHelper extends SQLiteOpenHelper {
        //构造方法
        //name 数据库名
        //version 自己定义的数据库的版本号
        public DBHelper(String name, int version) {

            //需要调用父类的构造方法 写在第一行
            super(MainActivity.this, name, null, version);
        }

        //实现方法  是一个回调方法
        //在创建数据库时调用
        //什么时候创建数据库：连接数据库的时候，如果数据库文件不存在
        //只调用一次
        @Override
        public void onCreate(SQLiteDatabase db) {

            //1.创建数据库的语句
            //构造建表语句
//            创建一张表，表名为user
            String creaTTable = "create table user (_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name varchar,age int)";
            db.execSQL(creaTTable);
//
//            //2.初始化参数 ContentValues
//            ContentValues cv = new ContentValues();
//
//            cv.put("name", "tom");
//            cv.put("age", "20");
//            //返回id long型  如果不成功返回-1
//            //1-表名
//            //2-空列的默认值
//            //3-字段和值的key/value集合
//            Long l = db.insert("user", null, cv);
//            Toast.makeText(MainActivity.this, "id=" + l, Toast.LENGTH_LONG).show();

            //2.初始化数据

        }

        //升级数据库
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        }
    }

（1）、添加数据

public void AddDataBase(View view){

//        DBHelper dh = new DBHelper("mes.db", 1);

        SQLiteDatabase sd = dbHelper.getWritableDatabase();

        ContentValues cv = new ContentValues();

        cv.put("name", "tom");
        cv.put("age", "20");
        //返回id long型  如果不成功返回-1
        //1-表名
        //2-空列的默认值
        //3-字段和值的key/value集合
        Long l = sd.insert("user", null, cv);
        Toast.makeText(MainActivity.this, "id=" + l, Toast.LENGTH_LONG).show();

        Cursor cursor = sd.query("user", new String[] { "_id",
                "name"}, null, null, null, null, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            String  id = cursor.getString(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));

//            Log.i("sdjflsdj","-------------Cursor------------");
//            Log.i("id: ","id===>"+id);
//            Log.i("name: ","name===>"+name);

            System.out.println("-------------Cursor------------");
            System.out.println("id: "+id);
            System.out.println("name: "+name);

        }

    }

（2）、修改数据

//    更新数据
public void UpdateDataBase(View view){


    // 创建一个DatabaseHelper对象
//    DBHelper dbHelper = new DBHelper("mes.db", 1);
    // 得到一个可写的SQLiteDatabase对象
    SQLiteDatabase sqliteDatabase = dbHelper.getWritableDatabase();
    // 创建一个ContentValues对象
    ContentValues values = new ContentValues();
    values.put("name", "zhangsan");
    // 调用update方法
    // 第一个参数String：表名
    // 第二个参数ContentValues：ContentValues对象
    // 第三个参数String：where字句，相当于sql语句where后面的语句，？号是占位符
    // 第四个参数String[]：占位符的值
    //此行的意思是：将user表中id=1 的name的值修改为zhangsan
    sqliteDatabase.update("user", values, "_id=?", new String[] { "1" });
    System.out.println("-----------update------------");

    Cursor cursor = sqliteDatabase.query("user", new String[] { "_id",
            "name"}, null, null, null, null, null);
    // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
    while (cursor.moveToNext()) {
        String  id = cursor.getString(cursor.getColumnIndex("_id"));
        String name = cursor.getString(cursor.getColumnIndex("name"));

//        Log.i("sdjflsdj","-------------Cursor------------");
//        Log.i("id: ","id===>"+id);
//        Log.i("name: ","name===>"+name);

        System.out.println("-------------Cursor------------");
        System.out.println("id: "+id);
        System.out.println("name: "+name);

    }


}

（3）、删除数据

public void DelateDataBase(View view){

        // 创建一个DatabaseHelper对象
//        DBHelper dbHelper = new DBHelper("mes.db", 1);
        // 得到一个可写的SQLiteDatabase对象
        SQLiteDatabase sqliteDatabase = dbHelper.getWritableDatabase();

        //调用SQLiteDatabase对象的delete方法进行删除操作
        //第一个参数String：表名
        //第二个参数String：条件语句
        //第三个参数String[]：条件值
        sqliteDatabase.delete("user", "_id=?", new String[]{"1"});
        System.out.println("----------delete----------");

        Cursor cursor = sqliteDatabase.query("user", new String[] { "_id",
                "name"}, null, null, null, null, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            String  id = cursor.getString(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));

//            Log.i("sdjflsdj","-------------Cursor------------");
//            Log.i("id: ","id===>"+id);
//            Log.i("name: ","name===>"+name);

            System.out.println("-------------Cursor------------");
            System.out.println("id: "+id);
            System.out.println("name: "+name);

        }

    }

(4)、查询数据

//查询数据库

    public void CursorDataBase(View v){

//        DBHelper dbh=new DBHelper("mes.db",1);

        SQLiteDatabase sdb=dbHelper.getReadableDatabase();

        // 调用SQLiteDatabase对象的query方法进行查询，返回一个Cursor对象：由数据库查询返回的结果集对象
        // 第一个参数String：表名
        // 第二个参数String[]:要查询的列名
        // 第三个参数String：查询条件
        // 第四个参数String[]：查询条件的参数
        // 第五个参数String:对查询的结果进行分组
        // 第六个参数String：对分组的结果进行限制
        // 第七个参数String：对查询的结果进行排序

        //此行的意思是：查询user表中id=1数据
//        Cursor cursor = sdb.query("user", new String[] { "_id",
//                "name"}, "_id=?", new String[] { "1" }, null, null, null);
        Cursor cursor = sdb.query("user", new String[] { "_id",
                "name"}, null, null, null, null, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
          String  id = cursor.getString(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));

//            Log.i("sdjflsdj","-------------Cursor------------");
//            Log.i("id: ","id===>"+id);
//            Log.i("name: ","name===>"+name);

            System.out.println("-------------Cursor------------");
            System.out.println("id: "+id);
            System.out.println("name: "+name);

        }

    }

2、创建SQLiteOpenHelper外部类，并使用管理类

（1）、DBHelper类

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

/**
 * Created by Administrator on 2017/1/9.
 */

public class DBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "test.db";
    private static final int DATABASE_VERSION = 1;

    public DBHelper(Context context) {
        //CursorFactory设置为null,使用默认值
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //数据库第一次被创建时onCreate会被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS person" +
                "(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age INTEGER, info TEXT)");
    }

    //如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("ALTER TABLE person ADD COLUMN other STRING");
    }
}

（2）、Person实体类

public class Person {
    public int _id;
    public String name;
    public int age;
    public String info;

    public Person() {
    }

    public Person(String name, int age, String info) {
        this.name = name;
        this.age = age;
        this.info = info;
    }
}

（3）、辅助DBHelper的管理类

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

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

/**
 * Created by Administrator on 2017/1/9.
 */

public class DBManager {
    private DBHelper helper;
    private SQLiteDatabase db;

    public DBManager(Context context) {
        helper = new DBHelper(context);
        //因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
        //所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = helper.getWritableDatabase();
    }

    /**
     * add persons
     * @param persons
     */
    public void add(List<Person> persons) {
        db.beginTransaction();  //开始事务
        try {
            for (Person person : persons) {
                db.execSQL("INSERT INTO person VALUES(null, ?, ?, ?)", new Object[]{person.name, person.age, person.info});
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     * update person's age
     * @param person
     */
    public void updateAge(Person person) {
        ContentValues cv = new ContentValues();
        cv.put("age", person.age);
        db.update("person", cv, "name = ?", new String[]{person.name});
    }

    /**
     * delete old person
     * @param person
     */
    public void deleteOldPerson(Person person) {
        db.delete("person", "age >= ?", new String[]{String.valueOf(person.age)});
    }

    /**
     * query all persons, return list
     * @return List<Person>
     */
    public List<Person> query() {
        ArrayList<Person> persons = new ArrayList<Person>();
        Cursor c = queryTheCursor();
        while (c.moveToNext()) {
            Person person = new Person();
            person._id = c.getInt(c.getColumnIndex("_id"));
            person.name = c.getString(c.getColumnIndex("name"));
            person.age = c.getInt(c.getColumnIndex("age"));
            person.info = c.getString(c.getColumnIndex("info"));
            persons.add(person);
        }
        c.close();
        return persons;
    }

    /**
     * query all persons, return cursor
     * @return  Cursor
     */
    public Cursor queryTheCursor() {
        Cursor c = db.rawQuery("SELECT * FROM person", null);
        return c;
    }

    /**
     * close database
     */
    public void closeDB() {
        db.close();
    }
}

（4）、mainactivity

import android.database.Cursor;
import android.database.CursorWrapper;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MainActivity extends AppCompatActivity {

    private DBManager mgr;
    private ListView listView;

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

        listView = (ListView) findViewById(R.id.listView);
        //初始化DBManager
        mgr = new DBManager(this);
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        //应用的最后一个Activity关闭时应释放DB
        mgr.closeDB();
    }

    public void add(View view) {
        ArrayList<Person> persons = new ArrayList<Person>();

        Person person1 = new Person("Ella", 22, "lively girl");
        Person person2 = new Person("Jenny", 22, "beautiful girl");
        Person person3 = new Person("Jessica", 23, "sexy girl");
        Person person4 = new Person("Kelly", 23, "hot baby");
        Person person5 = new Person("Jane", 25, "a pretty woman");

        persons.add(person1);
        persons.add(person2);
        persons.add(person3);
        persons.add(person4);
        persons.add(person5);

        mgr.add(persons);
    }
//更新数据
    public void update(View view) {
        Person person = new Person();
        person.name = "Jane";
        person.age = 30;
        mgr.updateAge(person);
    }
//删除数据
    public void delete(View view) {
        Person person = new Person();
        person.age = 30;
        mgr.deleteOldPerson(person);
    }
    //查询数据
    public void query(View view) {
        List<Person> persons = mgr.query();
        ArrayList<Map<String, String>> list = new ArrayList<Map<String, String>>();
        for (Person person : persons) {
            HashMap<String, String> map = new HashMap<String, String>();
            map.put("name", person.name);
            map.put("info", person.age + " years old, " + person.info);
            list.add(map);
        }
        SimpleAdapter adapter = new SimpleAdapter(this, list, android.R.layout.simple_list_item_2,
                new String[]{"name", "info"}, new int[]{android.R.id.text1, android.R.id.text2});
        listView.setAdapter(adapter);
    }

    public void queryTheCursor(View view) {
        Cursor c = mgr.queryTheCursor();

        CursorWrapper cursorWrapper = new CursorWrapper(c) {
            @Override
            public String getString(int columnIndex) {
                //将简介前加上年龄
                if (getColumnName(columnIndex).equals("info")) {
                    int age = getInt(getColumnIndex("age"));
                    return age + " years old, " + super.getString(columnIndex);
                }
                return super.getString(columnIndex);
            }
        };
        //确保查询结果中有"_id"列
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2,
                cursorWrapper, new String[]{"name", "info"}, new int[]{android.R.id.text1, android.R.id.text2});
        ListView listView = (ListView) findViewById(R.id.listView);
        listView.setAdapter(adapter);
    }

}