Android SQLite常用命令：SQLite增删改查。

之前经常写数据库，但一般用框架，但有的时候也不适合用，还是最基础的SQLite最实用，本博文简单介绍一下数据库的增、删、改、查的简单操作。

                              start

首先简单的说一下基本语句，然后是我自己写的Demo，如果需要可以去看看：

增：

db.execSQL("INSERT INTO person VALUES(null, ?, ?, ?)", new Object[]{person.name, person.age, person.info});

删：

 db.delete("person", "age >= ?", new String[]{String.valueOf(person.age)});

改：

        ContentValues cv = new ContentValues();
        cv.put("age", person.age);
        db.update("person", cv, "name = ?", new String[]{person.name});

查：

        Cursor c = db.rawQuery("SELECT * FROM person", null);
        while (c.moveToNext()) {
            Person querys = new Person();
            querys._id = c.getInt(c.getColumnIndex("_id"));
            querys.name = c.getString(c.getColumnIndex("name"));
            querys.age = c.getInt(c.getColumnIndex("age"));
            querys.info = c.getString(c.getColumnIndex("info"));
            persons.add(querys);
        }
        c.close();

效果图：

Demo:

step1: 写一个类，使其继承SQLiteOpenHelper，来创建数据库、创建表以及更新升级数据库

package com.god.mysqlite;

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

/**
 * @author David  create on 2016/9/28  16:21.
 * @email david.forever.god@gmail.com
 * Learn from yesterday, live for today, hope for tomorrow.
 */
public class DBHelper extends SQLiteOpenHelper {

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

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);//创建数据库
    }

    //创建表
    private String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS person" +
            "(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, " +
            "age INTEGER, info TEXT)";

    //数据库第一次被创建时onCreate会被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE);//执行创建表的语句
    }

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

step2:在你需要操作的地方，提前做好初始化(我直接在MainActivity里初始化了)，然后就可以开始执行你想要的增删改查等操作，需要注意，在onDestroy里释放DB:

package com.god.mysqlite;

import android.content.ContentValues;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;

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

public class MainActivity extends AppCompatActivity {


    private ListView listView;
    private ArrayList<Person> persons;

    private DBHelper helper;
    private SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        listView = (ListView) findViewById(R.id.listView);

        //初始化
        helper = new DBHelper(this);
        db = helper.getWritableDatabase();

        initData();
    }

    // 增   最近看了眼幻城，感觉还是有些失望，当年看小说的时候，感觉很不错！
    public void add(View view) {
        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();    //结束事务
        }
    }

    // 删  我们删掉年龄大于30岁的那些神们！
    public void delete(View view) {
        Person person = new Person();
        person.age = 30;
        db.delete("person", "age >= ?", new String[]{String.valueOf(person.age)});
    }

    // 改  我们将梨落的年龄改掉！
    public void update(View view) {
        Person person = new Person();
        person.name = "梨落";
        person.age = 150;

        ContentValues cv = new ContentValues();
        cv.put("age", person.age);
        db.update("person", cv, "name = ?", new String[]{person.name});
    }

    // 查  查询所有的成员
    public void query(View view) {
        ArrayList<Person> persons = new ArrayList<Person>();
        Cursor c = db.rawQuery("SELECT * FROM person", null);
        while (c.moveToNext()) {
            Person querys = new Person();
            querys._id = c.getInt(c.getColumnIndex("_id"));
            querys.name = c.getString(c.getColumnIndex("name"));
            querys.age = c.getInt(c.getColumnIndex("age"));
            querys.info = c.getString(c.getColumnIndex("info"));
            persons.add(querys);
        }
        c.close();
        DbAdapter dbAdapter = new DbAdapter(persons);
        listView.setAdapter(dbAdapter);
    }

    // 清空表，方便再次操作查看变化。
    public void dropTable(View view) {
        db.execSQL("delete from person");
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        //记得在最后一个Activity关闭时，需要释放DB
        db.close();
    }

    private class DbAdapter extends BaseAdapter {

        private List<Person> datas;

        public DbAdapter(List<Person> persons) {
            this.datas = persons;
        }

        @Override
        public int getCount() {
            return datas.size();
        }

        @Override
        public Object getItem(int position) {
            return datas.get(position);
        }

        @Override
        public long getItemId(int position) {
            return position;
        }

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            ViewHolder viewHolder;
            if (convertView == null) {
                convertView = LayoutInflater.from(MainActivity.this).inflate(R.layout.list_item, null);
                viewHolder = new ViewHolder(convertView);
                convertView.setTag(viewHolder);
            } else {
                viewHolder = ((ViewHolder) convertView.getTag());
            }
            viewHolder.item_name.setText(datas.get(position).name);
            viewHolder.item_age.setText(String.valueOf(datas.get(position).age));
            viewHolder.item_con.setText(datas.get(position).info);
            return convertView;
        }
    }

    class ViewHolder {
        private TextView item_name;
        private TextView item_age;
        private TextView item_con;

        public ViewHolder(View view) {
            item_name = ((TextView) view.findViewById(R.id.item_name));
            item_age = ((TextView) view.findViewById(R.id.item_age));
            item_con = ((TextView) view.findViewById(R.id.item_content));
        }
    }

    private void initData() {
        persons = new ArrayList<Person>();
        Resources res = getResources();
        Person person1 = new Person("卡索", 200, res.getString(R.string.kasuo));
        Person person2 = new Person("樱空释", 180, res.getString(R.string.yingkongshi));
        Person person3 = new Person("梨落", 20, res.getString(R.string.liluo));
        Person person4 = new Person("艳炟", 160, res.getString(R.string.yanda));
        Person person5 = new Person("岚裳", 160, res.getString(R.string.lanshang));

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

                           end

@Auther: David

@email ：david.forever.god@gmail.com

Learn from yesterday, live for today, hope for tomorrow.