SQLite、Cursor简单使用

本文完成数据库创建，查询，增加，删除操作。理论就不这么说了，就是那几步，先看效果:

添加权限
SD读写权限：android.permission.WRITE_EXTERNAL_STORAGE
继承SQLiteOpenHelper

/**
 * @project DBDemo
 * @ClassName DBOpenHelper.java
 * @Description 这里表名是写死的，实际使用中不要这样
 * @author xugang
 * @date 2015-12-21 下午1:18:12
 */
public class DBOpenHelper extends SQLiteOpenHelper {

    private final static String dbName = "mydb.db";
    public final static String tableName = "student";
    // 数据库版本
    private static final int VERSION = 1;
    //建学生表，主键自增长
    String sql = "Create Table " + tableName + "(userId INTEGER Primary Key AUTOINCREMENT,userName varchar(32),age int(32),time varchar(32))";

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

    public DBOpenHelper(Context context, String name, int version) {
        this(context, name, null, version);
    }

    public DBOpenHelper(Context context) {
        this(context, dbName, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //数据库更新,简单点可以直接删除表
    //但是推荐将旧表数据导入到新表后再删除
    }
}

获取SQLiteDatabase，进行增删改查操作

package com.example.dbdemo;

import java.util.Random;

import com.example.dbdemo.db.core.DBOpenHelper;

import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity implements OnClickListener, OnItemClickListener {

    private DBOpenHelper dbOpenHelper;
    //操作数据库
    private SQLiteDatabase db;
    private Button btnAdd;
    private Button btnSelect;
    private Button btnDel;
    private ListView lv;
    private MyAdapter myAdapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        init();
    }

    private void init() {
        btnAdd = (Button) findViewById(R.id.btnAdd);
        btnDel = (Button) findViewById(R.id.btnDel);
        btnSelect = (Button) findViewById(R.id.btnSelect);
        lv = (ListView) findViewById(R.id.lv);

        btnDel.setEnabled(false);

        btnAdd.setOnClickListener(this);
        btnSelect.setOnClickListener(this);
        btnDel.setOnClickListener(this);
        lv.setOnItemClickListener(this);

        dbOpenHelper = new DBOpenHelper(this);
        db = dbOpenHelper.getWritableDatabase();
    }

    private int[] ages = { 12, 18, 21, 43, 12, 43, 65, 67 };
    private String[] names = { "Jack", "Tom", "Linar", "Bann", "Ajax", "Rain", "Sun" };
    private Student student;
    private Cursor cursor = null;

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
        case R.id.btnAdd:
        //增加一条数据到表中
            ContentValues contentValues = new ContentValues();
            contentValues.put("userName", names[new Random().nextInt(7)]);
            contentValues.put("age", ages[new Random().nextInt(8)]);
            contentValues.put("time", String.valueOf(System.currentTimeMillis()));
            db.insertOrThrow(DBOpenHelper.tableName, null, contentValues);
            break;
        case R.id.btnSelect:
        //查询表，展现在Adapter中
            cursor = db.rawQuery("select * from student", null);
            myAdapter = new MyAdapter(this, cursor);
            lv.setAdapter(myAdapter);

            break;

        case R.id.btnDel:
        //删除一条数据
            if (student != null) {
                db.delete(DBOpenHelper.tableName, " time = ?", new String[] { student.getTime() });
                btnDel.setEnabled(false);
            }
            break;

        default:
            break;
        }
    }

    @Override
    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
        btnDel.setEnabled(true);
        student = (Student) parent.getItemAtPosition(position);
    }
}

Adapter

package com.example.dbdemo;

import android.content.Context;
import android.database.Cursor;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;

/**
 * @project DBDemo
 * @ClassName MyAdapter.java
 * @Description 填充ListView Adapter
 * @author xugang
 * @date 2015-12-21 下午4:14:00
 */
public class MyAdapter extends BaseAdapter {

    private Cursor cursor;
    private LayoutInflater inflater;

    public MyAdapter(Context context, Cursor cursor) {
        inflater = LayoutInflater.from(context);
        this.cursor = cursor;
    }

    @Override
    public int getCount() {
        return cursor.getCount();
    }

    @Override
    public Object getItem(int position) {
        if (cursor.moveToPosition(position)) {
            Student student = new Student(cursor.getString(cursor.getColumnIndexOrThrow("userName")), cursor.getInt(cursor.getColumnIndexOrThrow("age")), cursor.getString(cursor.getColumnIndexOrThrow("time")));
            return student;
        }
        return null;
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View view, ViewGroup parent) {
        ViewHolder holder;
        if (view == null) {
            holder = new ViewHolder();
            view = inflater.inflate(R.layout.item, parent, false);
            holder.tvTime = (TextView) view.findViewById(R.id.tvTime);
            holder.tvUserAge = (TextView) view.findViewById(R.id.tvUserAge);
            holder.tvUserName = (TextView) view.findViewById(R.id.tvUserName);
            view.setTag(holder);
        } else {
            holder = (ViewHolder) view.getTag();
        }
        Student student = (Student) getItem(position);
        if (student != null) {
            holder.tvUserName.setText("用户名:" + student.getUserName());
            holder.tvUserAge.setText("年龄:" + student.getAge());
            holder.tvTime.setText("添加时间:" + student.getTime());
        }
        return view;
    }

    private class ViewHolder {
        TextView tvUserName;
        TextView tvUserAge;
        TextView tvTime;
    }

}

实体类Student

package com.example.dbdemo;

/**
 * @project DBDemo
 * @ClassName Student.java
 * @Description 实体类
 * @author xugang
 * @date 2015-12-21 下午2:55:06
 */
public class Student {

    private String userName;
    private int age;
    private String time;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    @Override
    public String toString() {
        return "Student [userName=" + userName + ", age=" + age + ", time=" + time + "]";
    }

    public Student() {
        super();
    }

    public Student(String userName, int age, String time) {
        super();
        this.userName = userName;
        this.age = age;
        this.time = time;
    }

}

这里的增加和删除数据都要再次查询才可以刷新，在Android 3.0引入了Loaders机制，就不用这么麻烦了，这个下次在研究吧。