Android:SqliteDatabase 数据库的简单使用

• 其实做Android这么久还是对数据库这一块比较陌生，在实际项目中，没有搞过数据库这一块。对数据库总是有一些惧怕。
• 不小心看到慕课网有对数据库的讲解，于是根据讲解写了一个简单的Demo。
• 主要是熟悉一下sql语句，进行数据库的创建，表的创建，对表进行增删改查数据的操作。以及对SQLiteOpenHelper的简单使用。
• 代码很少，且不涉及布局文件，所有的信息通过log去查看就好。
• 首先是Activity的代码：

package com.duck.moocdatabase;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends Activity {

    private static final String STUDB = "studb";
    private static boolean DELETE = true;
    private static boolean UPDATE = true;
    private static boolean INSERT = false;
    private static boolean INSERT2 = false;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // 创建第1张表 usertb
        // opTableUsertb();
        // 创建第二张表 stutb
        // opTableStutb();
        // 创建第3张表 ducktb
        OpenDbHelper dbHelper = new OpenDbHelper(this, "duckInfo.db", null, 1);
        SQLiteDatabase db = dbHelper.getWritableDatabase();

        try {
            Cursor cursor = db.query("fuckmetb", null, "_id>?",
                    new String[] { "0" }, null, null, "name");
            if (cursor != null) {
                String[] columnNames = cursor.getColumnNames();
                while (cursor.moveToNext()) {
                    // 获取一条数据里面所有的信息
                    for (String columnName : columnNames) {
                        Log.d("System.out",
                                columnName
                                        + "***"
                                        + cursor.getString(cursor
                                                .getColumnIndex(columnName)));
                    }
                }
                cursor.close();
            }
            db.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void opTableStutb() {
        SQLiteDatabase db = openOrCreateDatabase("user.db", MODE_PRIVATE, null);
        String create = "create table if not exists studb(_id integer primary key autoincrement, name text not null, sex text not null, age integer not null)";
        db.execSQL(create);

        ContentValues values = new ContentValues();
        if (INSERT2) {
            values.clear();
            values.put("name", "Doctor");
            values.put("sex", "male");
            values.put("age", 36);
            db.insert(STUDB, null, values);// 1

            values.clear();
            values.put("name", "Teacher");
            values.put("sex", "male");
            values.put("age", 28);
            db.insert(STUDB, null, values);// 2

            values.clear();
            values.put("name", "Husband");
            values.put("sex", "female");
            values.put("age", 36);
            db.insert(STUDB, null, values);// 3

            values.clear();
            values.put("name", "Duck");
            values.put("sex", "female");
            values.put("age", 45);
            db.insert(STUDB, null, values);// 4

            values.clear();
            values.put("name", "Cat");
            values.put("sex", "female");
            values.put("age", 26);
            db.insert(STUDB, null, values);// 5

            INSERT2 = false;
        }

        if (UPDATE) {
            values.clear();
            values.put("sex", "boy");
            db.update(STUDB, values, "_id>?", new String[] { "3" });// 将所有的id>3的人的sex
                                                                    // 该为 boy
            UPDATE = false;
        }

        if (DELETE) {
            values.clear();
            db.delete(STUDB, "name like ?", new String[] { "%usb%" });// 删除name字段包含"usb"的数据的信息。
        }
        Cursor cursor = db.rawQuery("select * from STUDB", null);
        if (cursor != null) {
            while (cursor.moveToNext()) {
                int id = cursor.getInt(cursor.getColumnIndex("_id"));
                String name = cursor.getString(cursor.getColumnIndex("name"));
                int age = cursor.getInt(cursor.getColumnIndex("age"));
                String sex = cursor.getString(cursor.getColumnIndex("sex"));
                Log.v("System.out", "id=" + id + " | name=" + name + " | age="
                        + age + " | sex=" + sex);
            }
            cursor.close();
        }
        System.out.println("============================");

        Cursor query = db.query(STUDB, null, "_id>?", new String[] { "0" },
                null, null, "age", null);
        if (query != null) {
            while (query.moveToNext()) {
                String[] columnNames = query.getColumnNames();
                for (String columnName : columnNames) {
                    String value = query.getString(query
                            .getColumnIndex(columnName));
                    Log.v("System.out", columnName + "=" + value);
                }
            }
            query.close();
        }
        db.close();
    }

    private void opTableUsertb() {
        // 1. 获取数据库对象
        SQLiteDatabase db = openOrCreateDatabase("user.db", MODE_PRIVATE, null);
        // 2.
        String createTable = "create table if not exists usertb(_id integer primary key autoincrement, name text not null, age integer not null, sex text not null)";
        db.execSQL(createTable);

        if (INSERT) {
            String insert1 = "insert into usertb(name,age,sex) values('ZhangSan','20','female')";
            db.execSQL(insert1);
            String insert2 = "insert into usertb(name,age,sex) values('LeeSi','19','female')";
            db.execSQL(insert2);
            String insert3 = "insert into usertb(name,age,sex) values('WangWu','21','male')";
            db.execSQL(insert3);
        }
        String select = "select * from usertb";
        Cursor cursor = db.rawQuery(select, null);
        if (cursor != null) {
            // cursor.moveToFirst();
            while (cursor.moveToNext()) {
                int id = cursor.getInt(cursor.getColumnIndex("_id"));
                String name = cursor.getString(cursor.getColumnIndex("name"));
                int age = cursor.getInt(cursor.getColumnIndex("age"));
                String sex = cursor.getString(cursor.getColumnIndex("sex"));
                Log.i("System.out", "id=" + id + " | name=" + name + " | age="
                        + age + " | sex=" + sex);
            }
            cursor.close();
        }
        db.close();
    }

}

• Activity中，主要就是进行的数据库的两种方式的创建，数据库表的创建，增删改查的操作。
• 然后是SqilteOpenHelper 的简单使用，利用该类进行数据库表的创建。

package com.duck.moocdatabase;

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

public class OpenDbHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {                 
        String createTable = "create table if not exists fuckmetb(_id integer primary key autoincrement, name text not null, age integer not null)";
        db.execSQL(createTable);
        String insertOneData = "insert into fuckmetb(name,age) values('YaZi',28)";
        db.execSQL(insertOneData);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

以上代码运行ok。
* 关于数据库，还是了解甚少。
* 项目就不上传了，拷贝以上代码就可以直接运行。