Android：数据库增删改查、SQLite、SQLiteOpenHelper、openOrCreateDatabase

1. 创建一个SQLiteOpenHelper继承类

public class MySQLiteHelper extends SQLiteOpenHelper
{
    //重写构造方法,可以改为(Context context, int version)只要这两参数
    public MySQLiteHelper(Context context, String name, CursorFactory factory,
            int version)
    {
        super(context, "test.db", null, version);
    }
    //创建表
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL("CREATE TABLE person(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))" );
        db.execSQL("CREATE TABLE student(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))" );
    }
    //升级表（当Database的Version低于当前new里的Version，直接执行下面方法）
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
                                           
        int v = newVersion - oldVersion;
        switch (v)
        {
        case 3:
            db.execSQL("ALTER TABLE person ADD salary3 VARCHAR(20)");
        case 2:
            db.execSQL("ALTER TABLE person ADD salary2 VARCHAR(20)");
        case 1:
            db.execSQL("ALTER TABLE person ADD salary1 VARCHAR(20)");
        default:
            break;
        }
    }
                                       
}

2.使用SQLite增删改查

/**
 *
CREATE TABLE person
(
       personid INTEGER PRIMARY KEY AUTOINCREMENT,
       name VARCHAR(20),
       phone VARCHAR(20)
)
DROP TABLE person  //删除表
INSERT INTO person(name,phone) VALUES ("rongqin","1598088588")  //插入
SELECT * FROM person WHERE name="Andfeel"
UPDATE person SET name="Andfeel" WHERE personid=2   //改
ALTER TABLE person ADD salary      //增加列
DELETE FROM person WHERE name="Andfeel"    //删除
                             
 *
 */
public class MainActivity extends Activity implements OnClickListener
{
                               
    private TextView textView;
    private MySQLiteHelper mDBHelper;
    private MySQLiteHelper bHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
                                   
        findViewById(R.id.btn__create_table).setOnClickListener(this);
        findViewById(R.id.btn_delete_table).setOnClickListener(this);
        findViewById(R.id.btn_alter_table).setOnClickListener(this);
        findViewById(R.id.btn_insert).setOnClickListener(this);
        findViewById(R.id.btn_delete).setOnClickListener(this);
        findViewById(R.id.btn_update).setOnClickListener(this);
        findViewById(R.id.btn_select).setOnClickListener(this);
        textView = (TextView) findViewById(R.id.textView1);
                                   
        //创建数据库
        mDBHelper = new MySQLiteHelper(this, "one.db", null, 3);
        bHelper = new MySQLiteHelper(this, "two.db", null, 4);
    }
                               
    @Override
    public boolean onCreateOptionsMenu(Menu menu)
    {
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
    }
    @Override
    public void onClick(View v)
    {
        switch (v.getId())
        {
        case R.id.btn__create_table:
            btnCreateTable();
            break;
        case R.id.btn_delete_table:
            btnDeleteTable();
            break;
        case R.id.btn_alter_table:
            btnAlterTable();
            break;
        case R.id.btn_insert:
            btnInsert();
            break;
        case R.id.btn_delete:
            btnDelete();
            break;
        case R.id.btn_update:
            btnUpdate();
            break;
        case R.id.btn_select:
            btnSelect();
            break;
                                   
        default:
            break;
        }
    }
    private void btnSelect()//查
    {
        SQLiteDatabase db = mDBHelper.getWritableDatabase();
        SQLiteDatabase db1 = bHelper.getWritableDatabase();
                                   
        Cursor cursor = db.rawQuery("SELECT * FROM person WHERE name = ?", new String[]{"rongqin"});
//        cursor.moveToFirst();
        while(cursor.moveToNext())
        {
            int columnIndex = cursor.getColumnIndex("name");
            String name = cursor.getString(columnIndex);
            int columnIndex2 = cursor.getColumnIndex("phone");
            String phone = cursor.getString(columnIndex2);
                                       
            textView.setText(name + ":" + phone);
                                       
        }
    }
    private void btnUpdate()//改
    {
        SQLiteDatabase db = mDBHelper.getWritableDatabase();
        db.execSQL("UPDATE person SET phone='0592xxxxxx' WHERE personid=5");
    }
    private void btnDelete()//删
    {
        SQLiteDatabase db = mDBHelper.getWritableDatabase();
        db.execSQL("DELETE FROM person WHERE name='Andfeel'");
    }
    private void btnInsert()//增
    {
        SQLiteDatabase db = mDBHelper.getWritableDatabase();
        db.execSQL("INSERT INTO person(name,phone) VALUES ('rongqin',1598088588)");
    }
    private void btnAlterTable()//扩展表
    {
                                   
    }
    private void btnDeleteTable()//删除表
    {
                                   
    }
    private void btnCreateTable()//创建表
    {
                                   
    }
                               
}

注：

1.通过以下方法可以修改数据库存储路径

SQLiteDatabase.openOrCreateDatabase(file, factory)；

context.openOrCreateDatabase(name, mode, factory)；

参考资料：http://blog.csdn.net/wangqilin8888/article/details/7903048

转载于:https://blog.51cto.com/glblong/1216873