SQL Android

SQLite是一款轻量级的关系型数据库，它的运算速度非常快，占用资源很少。

一般有以下几个关键步骤：

1.创建数据库

2.创建表

3.操作：增删改查

4.关闭数据库

5.删除表（非必选）

SQLiteOpenHelper是SQLiteDatabse的一个帮助类，用来管理数据的创建和版本更新。一般的用法是定义一个类继承SQLiteOpenHelper，并实现两个回调方法，OnCreate(SQLiteDatabase db)和onUpgrade(SQLiteDatabse, int oldVersion, int newVersion)来创建和更新数据库。

下面新建类MyDatabaseHelper继承自SQLiteOpenHelper，并创建了表people

public class MyDatabaseHelper extends SQLiteOpenHelper {

    public static final String CREATE_PEOPLE = "create table people ("+"id integer primary key autoincrement,"+"name text,"+"age int,"+"price double)";


    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //创建表
        sqLiteDatabase.execSQL(CREATE_PEOPLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

        //删除旧表重新创建
        sqLiteDatabase.execSQL("drop table if exists people");
        onCreate(sqLiteDatabase);
    }
}

下面采用了两种方式来展示增删改查

第一种：纯SQL语句

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "MainActivity";
    private MyDatabaseHelper myDatabaseHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        //增加version 来删除之前的表信息
        myDatabaseHelper = new MyDatabaseHelper(this,"peopleInfo.db",null,9);
        //创建数据库
        myDatabaseHelper.getWritableDatabase();

        //打开数据库
        SQLiteDatabase database = myDatabaseHelper.getWritableDatabase();
        //新增zhangsan 18 11.11
//        ContentValues values = new ContentValues();
//        values.put("name","zhangsan");
//        values.put("age",18);
//        values.put("price",11.11);
//        database.insert("people",null,values);

        database.execSQL("insert into people (name,age,price) values(?,?,?)",new String[] {"zhangsan","18","11.11"});

        //新增lisi 28 22.22
//        values.clear();
//        values.put("name","lisi");
//        values.put("age",28);
//        values.put("price",22.22);
//        database.insert("people",null,values);

        database.execSQL("insert into people (name,age,price) values(?,?,?)",new String[] {"lisi","28","22.22"});


        //新增wangwu 48 44.44
//        values.clear();
//        values.put("name","wangwu");
//        values.put("age",48);
//        values.put("price",44.44);
//        database.insert("people",null,values);

        database.execSQL("insert into people (name,age,price) values(?,?,?)",new String[] {"wangwu","48","44.44"});


        //删除zhangsan信息
//        database.delete("people","name = ?",new String[] {"zhangsan"});

        database.execSQL("delete from people where name = ?",new String[] {"zhangsan"});

        //更改lisi age=38
//        values.clear();
//        values.put("age",38);
//        database.update("people",values,"name = ?",new String[] {"lisi"});

        database.execSQL("update people set age = ? where name = ? ",new String[] {"38","lisi"});


        //查询people表的内容并打印
//        Cursor cursor = database.query("people",null,null,null,null,null,null,null);
        Cursor cursor = database.rawQuery("select * from people",null);
        if (cursor.moveToFirst()){
            do{
                String name = cursor.getString(cursor.getColumnIndex("name"));
                int age = cursor.getInt(cursor.getColumnIndex("age"));
                double price = cursor.getDouble(cursor.getColumnIndex("price"));

                Log.d(TAG, "onCreate: "+name+" "+age+" "+price);

            }while (cursor.moveToNext());

        }
        cursor.close();


    }
}

第二种：Android对象方式

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "MainActivity";
    private MyDatabaseHelper myDatabaseHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        //增加version 来删除之前的表信息
        myDatabaseHelper = new MyDatabaseHelper(this,"peopleInfo.db",null,9);
        //创建数据库
        myDatabaseHelper.getWritableDatabase();

        //打开数据库
        SQLiteDatabase database = myDatabaseHelper.getWritableDatabase();
        //新增zhangsan 18 11.11
        ContentValues values = new ContentValues();
        values.put("name","zhangsan");
        values.put("age",18);
        values.put("price",11.11);
        database.insert("people",null,values);

//        database.execSQL("insert into people (name,age,price) values(?,?,?)",new String[] {"zhangsan","18","11.11"});

        //新增lisi 28 22.22
        values.clear();
        values.put("name","lisi");
        values.put("age",28);
        values.put("price",22.22);
        database.insert("people",null,values);

//        database.execSQL("insert into people (name,age,price) values(?,?,?)",new String[] {"lisi","28","22.22"});


        //新增wangwu 48 44.44
        values.clear();
        values.put("name","wangwu");
        values.put("age",48);
        values.put("price",44.44);
        database.insert("people",null,values);

//        database.execSQL("insert into people (name,age,price) values(?,?,?)",new String[] {"wangwu","48","44.44"});


        //删除zhangsan信息
        database.delete("people","name = ?",new String[] {"zhangsan"});

//        database.execSQL("delete from people where name = ?",new String[] {"zhangsan"});

        //更改lisi age=38
        values.clear();
        values.put("age",38);
        database.update("people",values,"name = ?",new String[] {"lisi"});

//        database.execSQL("update people set age = ? where name = ? ",new String[] {"38","lisi"});


        //查询people表的内容并打印
        Cursor cursor = database.query("people",null,null,null,null,null,null,null);
//        Cursor cursor = database.rawQuery("select * from people",null);
        if (cursor.moveToFirst()){
            do{
                String name = cursor.getString(cursor.getColumnIndex("name"));
                int age = cursor.getInt(cursor.getColumnIndex("age"));
                double price = cursor.getDouble(cursor.getColumnIndex("price"));

                Log.d(TAG, "onCreate: "+name+" "+age+" "+price);

            }while (cursor.moveToNext());

        }
        cursor.close();


    }
}

最终结果

05-28 10:00:11.618 16802-16802/? D/MainActivity: onCreate: lisi 38 22.22
05-28 10:00:11.618 16802-16802/? D/MainActivity: onCreate: wangwu 48 44.44