【达内课程】SQLite（二）中现成的增删改查方法

除了使用sql语句，SQLiteDatabase中提供了一个现成的方法来完成我们刚才所有的操作

例如，新增数据

 private void callInsert(){
        String table = "users";
        String nullColumnHack = null;
        ContentValues values = new ContentValues();
        values.put("name","小猪佩奇");
        values.put("age",4);
        values.put("phone","40122739");
        values.put("email","[e-mail]");
        long id= db.insert(table,nullColumnHack,values);
        Log.d("SQLITE","新增加的数据id："+id);

        //如果想要增加多条数据，应该new ContentValues(),或者调用values.clear()方法清空数据，再put值
        values = new ContentValues();
        values.put("name","大白");
        values.put("age",3);
        db.insert(table,nullColumnHack,values);
    }

这个方法返回新增的id，如果不成功，返回-1

第二个参数，是防止程序崩溃的一个解决方案，因为我们第三个参数是一个完全正常的参数，所以第二个参数我们可以随便写

insert()方法的本质也是在为我们拼接了一个sql语句，如果我们第三个参数为null，会出现INSERT INTO users () VALUES ();的错误sql，这种情况下，第二个参数就会起作用，我们建议它的值写成一个字段的值，例如name，那么程序内部拼接出来的sql就是INSERT INTO users (name) VALUES (NULL);虽然没有意义，但是能保证程序不出错

所以第二个参数，仅当第三个参数没有有效值时有效

删除数据

private void callDelete(){
        String name = "Lily";
        int age = 50;
        String table = "users";
        String whereClause = "age>"+age+" OR name='"+name+"'";
        String[] whereArgs = null;
        int affectedRows = db.delete(table,whereClause,whereArgs);
        Log.d("SQL","受影响的行数"+affectedRows);
    }

可以优化为

private void callDelete(){
        String name = "Lily";
        int age = 50;
        String table = "users";
        String whereClause = "age>? OR name=?";
        String[] whereArgs = {age+"",name};
        int affectedRows = db.delete(table,whereClause,whereArgs);
        Log.d("SQL","受影响的行数"+affectedRows);
    }

不管是什么类型的都可以改为？

删除后的数据

查询

查询语句栗子：

private void callQuery(){
        //查询数据
        String table = "users";
        String[] columns = {"name","age","phone","email"};//查询的字段列表
        String selection = null;//查询的where字句
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = null;
        //处理数据
        Cursor c = db.query(table,columns,selection,selectionArgs,groupBy,having,orderBy);
        if(c.moveToFirst())
        String name = c.getString(0);
        int age = c.getInt(1);
        String phone = c.getString(2);
        String email = c.getString(3);
        Log.d("SQL","name:"+name+" age:"+age+" phone:"+phone+" email:"+email);
        c.close();
        }
    }

查看日志

D/SQL: name:小猪佩奇 age:4 phone:[phone] email:[e-mail]

如果想查询下一条，可以修改刚才的程序

......
Cursor c = db.query(table,columns,selection,selectionArgs,groupBy,having,groupBy);
c.moveToFirst();
c.moveToNext();
String name = c.getString(0);
......

查看日志

D/SQL: name:小明 age:20 phone:10086 email:[e-mail]

一旦我们指定了列

String[] columns = {"name","age","phone","email"};//查询的字段列表

c.getString(0)中0，1…是确定的了，如果再增加列，这些序号是要重新调整的，因此我们可以根据字段名获取index

String name = c.getString(c.getColumnIndex("name"));

栗子：使用CursorAdapter显示数据查询结果

activity_main

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/main_layout"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <ListView
        android:id="@+id/lv_contacts"
        android:layout_width="match_parent"
        android:layout_height="match_parent"/>

</LinearLayout>

item_contact

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/main_layout"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:padding="10dp">

    <TextView
        android:id="@+id/tv_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="名字 "/>
    <TextView
        android:id="@+id/tv_age"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="15 "/>
    <TextView
        android:id="@+id/tv_phone"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="10086 "/>
    <TextView
        android:id="@+id/tv_email"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="[e-mail] "/>


</LinearLayout>

MainActivity

package com.example.a00xiaoyugmailcom.myapplication;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class MainActivity extends AppCompatActivity {
    private SQLiteDatabase db;

    private ListView listView;
    private SimpleCursorAdapter simpleCursorAdapter;
    private Cursor c;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //创建数据库
        createDatabase();
        //创建数据表
        createTable();
        //增加数据
        insertData();
        //删除数据
        //deleteData();
        //更新数据
        //updateData();
        //新*增加数据
        //callInsert();
        //新*删除
        //callDelete();
        //新*查询
        c = callQuery();
        String[] from = {"name","age","phone","email"};
        int[] to = {R.id.tv_name,R.id.tv_age,R.id.tv_phone,R.id.tv_email};
        simpleCursorAdapter = new SimpleCursorAdapter(this,R.layout.item_contact,c,from,to,0);

        listView = (ListView) findViewById(R.id.lv_contacts);
        listView.setAdapter(simpleCursorAdapter);
    }

    private void callInsert(){
        String table = "users";
        String nullColumnHack = null;
        ContentValues values = new ContentValues();
        values.put("name","小粉");
        values.put("age",18);
        values.put("phone","12222");
        values.put("email","[e-mail]");
        long id= db.insert(table,nullColumnHack,values);
        Log.d("SQLITE","新增加的数据id："+id);

        values.clear();
        values.put("name","大白");
        values.put("age",3);
        db.insert(table,nullColumnHack,values);
    }

    private void callDelete(){
        String name = "Lily";
        int age = 50;
        String table = "users";
        String whereClause = "age>? OR name=?";
        String[] whereArgs = {age+"",name};
        int affectedRows = db.delete(table,whereClause,whereArgs);
        Log.d("SQL","受影响的行数"+affectedRows);
    }

    private Cursor callQuery(){
        //查询数据
        String table = "users";
        String[] columns = {"_id","name","age","phone","email"};//查询的字段列表
        String selection = null;//查询的where字句
        String[] selectionArgs = null;
        String groupBy = null;
        String having = null;
        String orderBy = null;
        //处理数据
        Cursor c = db.query(table,columns,selection,selectionArgs,groupBy,having,orderBy);
        return c;
    }

    private void updateData() {
        String sql = "UPDATE users SET age = 22 WHERE name = 'Lily'";
        db.execSQL(sql);
    }

    private void deleteData() {
        String sql = "DELETE FROM users WHERE age > 26";
        db.execSQL(sql);
        Log.d("SQL","删除");
    }

    private void insertData() {
        String sql1 = "INSERT INTO users "+
                "(name,age,phone,email) "+
                "values "+
                "('Lily',40,'13333333333','[e-mail]')";
        db.execSQL(sql1);
        String sql2 = "INSERT INTO users "+
                "(name,age,phone,email) "+
                "values "+
                "('James',55,'17712345678','[e-mail]')";
        db.execSQL(sql2);
    }

    private void createTable() {
        String sql = "CREATE TABLE users ("+
                "_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
                "name VARCHAR(10) UNIQUE NOT NULL, "+
                "age INTEGER, "+
                "phone CHAR(11) UNIQUE, "+
                "email VARCHAR(32) UNIQUE"+
                ")";
        db.execSQL(sql);
    }

    private void createDatabase() {
        String name = "test.db";
        int mode = MODE_PRIVATE;
        SQLiteDatabase.CursorFactory cursorFactory = null;
        db = openOrCreateDatabase(name,mode,cursorFactory);
    }
}

把之前的程序卸载，重新运行程序

注意，使用CursorAdapter，在创建表时需要新增“_id”字段。名称固定

查询时也需要查询出此字段才可以