Android SQLite数据库增删改查和事务控制

在Android平台上，集成了一个嵌入式关系型数据库—SQLite.

1、SQLite支持 NULL、INTEGER、REAL（浮点数字）、TEXT(字符串文本)和BLOB(二进制对象)数据类型，虽然它支持的类型虽然只有五种，但实际上sqlite也接受varchar(n)、char(n)、decimal(p,s) 等数据类型，只不过在运算或保存时会转成对应的五种数据类型。

2、和JavaScript类似, SQLite最大的特点是你可以保存任何类型的数据到任何字段中，无论这列声明的数据类型是什么。例如：可以在Integer字段中存放字符串，或者在布尔型字段中存放浮点数，或者在字符型字段中存放日期型值。

3、但有一种情况例外：定义为INTEGER PRIMARY KEY的字段只能存储64位整数， 当向这种字段中保存除整数以外的数据时，将会产生错误。

 

4、SQLite可以支持90%的标准SQL.

 

一、下面是对SQLite数据库的增删改查

model类: User.java

 

package com.lk.model;

public class User {
    private int id;
    private String name;
    private String email;
    private double account;
    
    public User(int id, String name, String email, double account) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.account = account;
    }
    
    
    public User(String name, String email, double account) {
        super();
        this.name = name;
        this.email = email;
        this.account = account;
    }


    public User() {
        super();
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    
    public double getAccount() {
        return account;
    }


    public void setAccount(double account) {
        this.account = account;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", email=" + email
                + ", account=" + account + "]";
    }

    
}

 

 SQLHelper.java

package com.lk.util;

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


public class SQLHelper extends SQLiteOpenHelper {

    /**
     * 创建一个名为userManager的数据库(只有当调用了getWritableDatabase()或getReadableDatabase()方法才会正真创建数据库)
     * @param context
     * @param version
     */
    public SQLHelper(Context context, int version) {
        super(context, "userManager.db", null, version);
    }

    /**
     * 当数据库第一次被创建时调用，可以在里面做一些初始化数据库的工作
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table user(id integer primary key autoincrement, name varchar(64), email varchar(64))");
        
    }

    /**
     * 版本号发生变化时被调用
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("alter table user add column account double");
    }

}

UserService.java

package com.lk.service;

import java.util.ArrayList;
import java.util.List;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.lk.model.User;
import com.lk.util.SQLHelper;

public class UserService {
    private SQLHelper sqlHelper;
    
    /**
     * 保存一个用户
     * @param user
     * @return
     */
    public int save(User user) {
        SQLiteDatabase sqlLiteDatabase = sqlHelper.getWritableDatabase();
        sqlLiteDatabase.execSQL("insert into user(name, email, account) values(?, ?, ?)", 
                new Object[]{user.getName(), user.getEmail(), user.getAccount()});
        Cursor cursor = sqlLiteDatabase.rawQuery("select last_insert_rowid()", null);
        int id = -1;
        if(cursor.moveToFirst()) {
            id = cursor.getInt(0);
        }
        cursor.close();
        sqlLiteDatabase.close();
        return id;
    }
    
    /**
     * 根据id删除一个用户
     * @param id
     */
    public void deleteById(int id) {
        SQLiteDatabase sqlLiteDatabase = sqlHelper.getWritableDatabase();
        sqlLiteDatabase.execSQL("delete from user where id = ?", new Object[]{id});
        sqlLiteDatabase.close();
    }
    
    /**
     * 更新一个用户
     * @param user
     */
    public void updateUser(User user) {
        SQLiteDatabase sqlLiteDatabase = sqlHelper.getWritableDatabase();
        sqlLiteDatabase.execSQL("update user set name = ?, email = ?, account = ? where id = ?", 
                new Object[]{user.getName(), user.getEmail(), user.getAccount(), user.getId()});
        sqlLiteDatabase.close();
    }
    
    /**
     * 根据id查询用户
     * @param id
     * @return
     */
    public User findById(int id) {
        User user = null;
        SQLiteDatabase sqlLiteDatabase = sqlHelper.getReadableDatabase();
        Cursor cursor = sqlLiteDatabase.rawQuery("select * from user where id = ?", new String[]{String.valueOf(id)});
        if(cursor.moveToFirst()) {
            user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndex("id")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setEmail(cursor.getString(cursor.getColumnIndex("email")));
            user.setAccount(cursor.getDouble(cursor.getColumnIndex("account")));
        }
        cursor.close();
        sqlLiteDatabase.close();
        return user;
    }
    
    /**
     * 分页查询
     * @param offset
     * @param recordCount
     * @return
     */
    public List<User> findUserByPage(int offset, int recordCount) {
        List<User> users = new ArrayList<User>();
        SQLiteDatabase sqlLiteDatabase = sqlHelper.getReadableDatabase();
        Cursor cursor = sqlLiteDatabase.rawQuery("select * from user order by id limit ?,?", new String[]{String.valueOf(offset), String.valueOf(recordCount)});
        while(cursor.moveToNext()) {
            User user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndex("id")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setEmail(cursor.getString(cursor.getColumnIndex("email")));
            user.setAccount(cursor.getDouble(cursor.getColumnIndex("account")));
            users.add(user);
        }
        cursor.close();
        sqlLiteDatabase.close();
        return users;
    }
    
    /**
     * 得到记录总数
     * @return
     */
    public int count() {
        SQLiteDatabase sqlLiteDatabase = sqlHelper.getReadableDatabase();
        Cursor cursor = sqlLiteDatabase.rawQuery("select count(*) from user", null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        sqlLiteDatabase.close();
        return count;
    }

    public SQLHelper getSqlHelper() {
        return sqlHelper;
    }

    public void setSqlHelper(SQLHelper sqlHelper) {
        this.sqlHelper = sqlHelper;
    }

}

 

 

 除了自己写SQL外，SQLiteDatabase对象还提供了insert(), delete(), update()和query() API来操作数据库，但其本质上还是拼接SQL.

 

二、SQLite事务

 下面是一个转账的例子

    /**
     * SQlite事务的使用
     * @param from
     * @param to
     * @param change
     */
    public void changeAccount(User from, User to, double change) {
        SQLiteDatabase sqLiteDatabase = sqlHelper.getWritableDatabase();
        try {
            sqLiteDatabase.beginTransaction();
            sqLiteDatabase.execSQL("update user set account = account - ? where id = ?", new Object[]{change, from.getId()});
            sqLiteDatabase.execSQL("update user set account = account + ? where id = ?", new Object[]{change, to.getId()});
            sqLiteDatabase.setTransactionSuccessful();  //设置标志位true
        } finally {
            sqLiteDatabase.endTransaction();  //这个方法既能rollback又能commit，具体是由一个标志位来判别，标志位为true时提交，false时rollback。默认情况下rollback
            sqlHelper.close();
        }
    }