安卓开发：数据库操作（详细）

安卓使用的数据库是sqlite

创建方式：新建一个类继承SQLiteOpenHelper

package org.dreamtech.sqlite;

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

public class MyOpenHelper extends SQLiteOpenHelper {

    public MyOpenHelper(Context context) {
        super(context, "dreamtech.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    
    }

}

这里第二个参数：数据库的名字，第三个参数为结果集（游标）写成null即可，最后一个参数为数据库的版本，写1即可

 

在MainActivity中新建实例即可创建数据库：

package org.dreamtech.sqlite;

import android.os.Bundle;
import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        MyOpenHelper myOpenHelper = new MyOpenHelper(getApplicationContext());
        // 获取一个可写的数据库，如果数据库不存在，则新建数据库
        SQLiteDatabase sqLiteDatabase = myOpenHelper.getWritableDatabase();
    }

}

 

好的，运行即可创建数据库成功

 

 

接下来看下MyOpenHelper中的方法：

oncreate：数据库第一次创建时候执行的方法

适合于初始化表结构

示例：

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table info(_id integer primary key autoincrement,name varchar(20))");
    }

 

另一个方法：onupgrade方法：

当数据库版本升级的时候调用（上边设置为1版本，如果升级成2，会运行这里的方法）

示例：这里可以修改表结构

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("alter table info add tel varchar(20)");
    }

 

注意：版本只能升，不能降级

 

接下来做一个增删改查的小案例：

使用原始方法：

package org.dreamtech.sqlite;

import android.os.Bundle;
import android.view.View;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity {

    private MyOpenHelper myOpenHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myOpenHelper = new MyOpenHelper(getApplicationContext());
    }

    // 增
    public void click1(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        db.execSQL("insert into info(name,tel) values(?,?)", new Object[] {
                "zhangsan", "110" });
        db.close();
    }

    // 删
    public void click2(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        db.execSQL("delete from info where name=?", new Object[] { "zhangsan" });
        db.close();
    }

    // 改
    public void click3(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        db.execSQL("update info set tel=? where name=?", new Object[] { "120",
                "zhangsan" });
        db.close();
    }

    // 查
    public void click4(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from info", null);
        if (cursor != null && cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                String name = cursor.getString(1);
                String tel = cursor.getString(2);
                System.out.println(name + "   " + tel);
            }
        }
        db.close();
    }
}

 

确实成功了，但是存在问题：sql语句只要一处出错，将难以发现，浪费大量时间

唯一的优点：多表查询

 

通常操作数据库是采用谷歌封装好的API：

 优点：不易写错，有返回值，方便开发

 缺点：涉及多表操作不便

package org.dreamtech.sqlite;

import android.os.Bundle;
import android.view.View;
import android.widget.Toast;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity {

    private MyOpenHelper myOpenHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myOpenHelper = new MyOpenHelper(getApplicationContext());
    }

    // 增
    public void click1(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", "zhangsan");
        values.put("tel", "110");
        // 返回新行的id
        long insert = db.insert("info", null, values);
        db.close();
        if (insert > 0) {
            Toast.makeText(getApplicationContext(), "add 成功", Toast.LENGTH_LONG)
                    .show();
        } else {
            Toast.makeText(getApplicationContext(), "add 失败", Toast.LENGTH_LONG)
                    .show();
        }
    }

    // 删
    public void click2(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        // 返回值：影响的行数
        int delete = db.delete("info", "name=?", new String[] { "zhangsan" });
        db.close();
        Toast.makeText(getApplicationContext(), "删除了" + delete + "行",
                Toast.LENGTH_LONG).show();
    }

    // 改
    public void click3(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("tel", "666");
        // 返回值：更新了多少行
        int update = db.update("info", values, "name=?",
                new String[] { "zhangsan" });
        db.close();
        Toast.makeText(getApplicationContext(), "更新了" + update + "行",
                Toast.LENGTH_LONG).show();
    }

    // 查
    public void click4(View v) {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();

        Cursor cursor = db.query("info", new String[] { "tel" }, "name=?",
                new String[] { "zhangsan" }, null, null, null);

        if (cursor != null && cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                String tel = cursor.getString(0);
                System.out.println(tel);
            }
        }
        db.close();
    }
}

 

 

事务操作：执行一段逻辑，要么同时成功，要么同时失败

这里写一个逻辑转账的案例

 

package org.dreamtech.transaction;

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

public class MyOpenHelper extends SQLiteOpenHelper {

    public MyOpenHelper(Context context) {
        super(context, "Account.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table info(_id integer primary key autoincrement,name varchar(20),money varchar(20))");
        db.execSQL("insert into info('name','money') values('a','2000')");
        db.execSQL("insert into info('name','money') values('b','5000')");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

}

 

package org.dreamtech.transaction;

import android.os.Bundle;
import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.View;
import android.widget.Toast;

public class MainActivity extends Activity {

    private MyOpenHelper myOpenHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myOpenHelper = new MyOpenHelper(getApplicationContext());
    }

    // 使用事务进行转账
    public void click(View v) {
        SQLiteDatabase db = myOpenHelper.getReadableDatabase();

        // 开启事务
        db.beginTransaction();
        try {
            // 转账逻辑
            db.execSQL("update info set money=money-100 where name=?",
                    new Object[] { "a" });
            // 加入这一行则不会成功
            // int a = 10/0;
            db.execSQL("update info set money=money+100 where name=?",
                    new Object[] { "b" });
            // 表示当前事务成功（自动提交）
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Toast.makeText(getApplicationContext(), "转账失败", Toast.LENGTH_LONG)
                    .show();
        } finally {
            // 关闭事务
            db.endTransaction();
        }

    }
}

 

加入10/0一行的效果：