SQLiteDatabase的CRUD操作实现

1.新建db项目

2.MySQLiteOpenHelper.java

package com.example.db.impl;

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

import android.database.sqlite.SQLiteOpenHelper;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    // 数据库的名称
    private static String name = "hytc";

    // 数据库的版本号
    private static int version = 2;

    // 构造函数
    public MySQLiteOpenHelper(Context context) {
        super(context, name, null, version);
    }

    /**
     * 第一次创建的时候调用
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("============onCreate===============");
        db.execSQL("CREATE TABLE user (id integer primary key autoincrement, name varchar(20))");

    }

    /**
     * 版本发生变化的时候调用
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("---------------onUpgrade------------------");
        db.execSQL("alter table user add column phone varchar(11)");
    }

}

3.DBTest.java

package com.example.db.test;

import com.example.db.impl.MySQLiteOpenHelper;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase {

    private MySQLiteOpenHelper helper;

    /**
     * 插入数据
     */
    public void testInsert() {
        // 创建MySQLiteOpenHelper对象
        helper = new MySQLiteOpenHelper(getContext());
        // 获取数据库操作对象
        SQLiteDatabase db = helper.getWritableDatabase();
        for (int i = 0; i < 20; i++) {
            // 执行sql语句
            db.execSQL("insert into user(name,phone) values('chj" + i
                    + "','15631293228')");
        }
        // 关闭操作
        db.close();
    }

    public void testupdate() {
        // 创建MySQLOpenHelper对象
        helper = new MySQLiteOpenHelper(getContext());
        // 获取数据库对象
        SQLiteDatabase db = helper.getWritableDatabase();
        // 执行sql语句
        db.execSQL("update user set name=?,phone=? where id=?", new Object[] {
                "bc1", "11000", 2 });

    }

    public void testdelete() {
        helper = new MySQLiteOpenHelper(getContext());
        // 获取数据库操作对象
        SQLiteDatabase db = helper.getWritableDatabase();
        // 执行sql语句
        db.execSQL("delete from user where id=?", new Object[] { 2 });
        // 关闭操作
        db.close();
    }

    public void testselect() {
        helper = new MySQLiteOpenHelper(getContext());
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor c = db.rawQuery("select name,phone,id from user", null);
        // 判断是否有下一个
        while (c.moveToNext()) {
            // c.getColumnName 获取字段的名称 id name phone
            // c.getColumnIndex 获取字段的下标值 从0开始比
            // System.out.println(c.getColumnIndex("id")+","+c.getColumnIndex("name")+","+c.getColumnIndex("phone"));

            int id = c.getInt(c.getColumnIndex("id"));
            String name = c.getString(c.getColumnIndex("name"));
            String phone = c.getString(c.getColumnIndex("phone"));
            System.out.println(id + "," + name + "," + phone);
        }
        // 关闭操作
        db.close();

    }

    public void testselect2() {
        helper = new MySQLiteOpenHelper(getContext());
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor c = db.rawQuery("select name,phone,id from user limit 0,10",
                null);
        // 判断是否有下一个
        while (c.moveToNext()) {

            int id = c.getInt(c.getColumnIndex("id"));
            String name = c.getString(c.getColumnIndex("name"));
            String phone = c.getString(c.getColumnIndex("phone"));
            System.out.println(id + "," + name + "," + phone);
        }
        // 关闭操作
        db.close();

    }

    public void testselect3() {
        helper = new MySQLiteOpenHelper(getContext());
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor c = db.rawQuery(
                "select name,phone,id from user where name=? and phone=?",
                new String[] { "chj2", "15631293228" });
        // 判断是否有下一个
        while (c.moveToNext()) {

            int id = c.getInt(c.getColumnIndex("id"));
            String name = c.getString(c.getColumnIndex("name"));
            String phone = c.getString(c.getColumnIndex("phone"));
            System.out.println(id + "," + name + "," + phone);
        }
        // 关闭操作
        db.close();

    }

}