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机制，就不用这么麻烦了，这个下次在研究吧。