创建数据库类实现 增删改查功能

第一步创建数据库辅助类

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

/**
 * Created by han shan on 2016/5/25.
 */
public class DBOpenHelper extends SQLiteOpenHelper{
    DBOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);   //必须通过super调用父类构造函数
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS person ("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "name VARCHAR(20),"
                + "age SMALLINT)");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS person");
        onCreate(db);
    }

}

2.创建数据类型Person类

public class Person {
    public int id;
    public String name;
    public int age;

    public Person() {
    }

    public Person(String name, int age) {
        this.name = name;
        this.age = age;
    }
}

3.Main中执行创建数据库

case R.id.btn :
    //创建辅助类对象
    DBOpenHelper helper = new DBOpenHelper(getApplicationContext(), "test.db", null,DB_VERSION);
    //调用getWritableDatabase()或getReadableDatabase()才会真正创建或打开
    SQLiteDatabase db=helper.getWritableDatabase();
    db.close(); //操作完成后关闭数据库连接
    Toast.makeText(getApplicationContext(), "数据库创建成功", Toast.LENGTH_SHORT).show();
    break;

4.增

case R.id.btn1:
    DBOpenHelper helper1 = new DBOpenHelper(getApplicationContext(), "test.db", null,DB_VERSION);
    SQLiteDatabase db1=helper1.getWritableDatabase();
    Person person = new Person();
    person.name = "wustzz";
    person.age = 39;
    db1.execSQL("INSERT INTO person VALUES (NULL, ?, ?)",
            new Object[ ] { person.name, person.age } );

    db1.close();    //数据操作完毕一定要记得关闭数据库连接
    Toast.makeText(getApplicationContext(), "记录添加成功", Toast.LENGTH_SHORT).show();
    break;

5.删

  case R.id.btn2:
                DBOpenHelper helper2 = new DBOpenHelper(getApplicationContext(), "test.db", null,DB_VERSION);
                SQLiteDatabase db2=helper2.getWritableDatabase();
//删除数据
                db2.execSQL( "Delete from person where age<?", new Object[ ]{ "40" } );
                db2.close();    //关闭数据库连接
                Toast.makeText(getApplicationContext(), "记录删除成功", Toast.LENGTH_SHORT).show();
                break;

6.改

      case R.id.btn3:
                DBOpenHelper helper3 = new DBOpenHelper(getApplicationContext(), "test.db", null,DB_VERSION);
                SQLiteDatabase db3=helper3.getWritableDatabase();
//更新数据
                db3.execSQL("Update person set age=? where name like ?",
                        new Object[ ]{ 20,"wust%" } );
                db3.close();    //关闭数据库连接
                Toast.makeText(getApplicationContext(), "记录修改成功", Toast.LENGTH_SHORT).show();
                break;

7.查

case R.id.btn4:
    DBOpenHelper helper4 = new DBOpenHelper(getApplicationContext(), "test.db", null,DB_VERSION);
    SQLiteDatabase db4=helper4.getWritableDatabase();
    Cursor cursor = db4.rawQuery( "SELECT * FROM person where age>?",  new String[]{"10"} );

    TextView tv=(TextView)findViewById(R.id.tv);   //简单用TextView显示数据
    tv.setText("查询到"+cursor.getCount()+"条记录(当前数据库版本号="+DB_VERSION+")");
    while (cursor.moveToNext()) {
        int id = cursor.getInt(cursor.getColumnIndex("id") );
        String name = cursor.getString(cursor.getColumnIndex("name") );
        int age = cursor.getInt(cursor.getColumnIndex("age") );
        tv.setText(tv.getText()+"\n"+"id="+id+",name="+name+",age="+age);
    }
    cursor.close();   //关闭cursor
    db4.close();  //关闭数据库连接

main_layoutl布局：

<Button
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="数据库创建"
    android:id="@+id/btn" />

<Button
    android:layout_width="350dp"
    android:layout_height="wrap_content"
    android:text="增加记录"
    android:id="@+id/btn1"
    android:layout_gravity="right" />

<Button
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="删除记录"
    android:id="@+id/btn2" />

<Button
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="修改记录"
    android:id="@+id/btn3"
    android:layout_gravity="center_horizontal" />

<Button
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="查询记录"
    android:id="@+id/btn4"
    android:layout_gravity="center_horizontal" />

<TextView
    android:layout_width="350dp"
    android:layout_height="wrap_content"
    android:text="New Text"
    android:id="@+id/tv"
    android:layout_weight="0.85" />