8.2 Android Basic 数据存储 Database SQLite Demo 1

<<SQLiteDemo1.zip>>

 

SQLite Demo1 演示

演示操作SQLite数据库

   

• 新建项目SQLiteDemo1,编辑res/layout/main.xml 布局文件：

  <?xml version="1.0" encoding="utf-8"?>

  <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

  android:orientation="vertical"

  android:layout_width="fill_parent"

  android:layout_height="fill_parent"

  >

  <TextView

  android:layout_width="fill_parent"

  android:layout_height="wrap_content"

  android:text="SQLite 演示"

  />

  <Button android:id="@+id/button3"

  android:layout_width="wrap_content"

  android:layout_height="wrap_content" android:text="插入两条记录" />

  <Button android:id="@+id/button4"

  android:layout_width="wrap_content"

  android:layout_height="wrap_content" android:text="删除一条记录" />

  <Button android:id="@+id/button5"

  android:layout_width="wrap_content"

  android:layout_height="wrap_content" android:text="查询数据库" />

  <Button android:id="@+id/button2"

  android:layout_width="wrap_content"

  android:layout_height="wrap_content" android:text="删除数据表" />

  <Button android:id="@+id/button1"

  android:layout_width="wrap_content"

  android:layout_height="wrap_content" android:text="重新建立数据表" />

  </LinearLayout>

     

  2. 编辑类SQLiteDemo1，定义数据库文件名称、版本号、表名称、列名称。

  public class SQLiteDemo1 extends Activity {

  private static final String DATABASE_NAME= "SQLiteDemo1.db";

  private static final int DATABASE_VERSION= 1;

  private static final String TABLE_NAME = "diary";

  private static final String TITLE = "Title"; //Col1

  private static final String BODY = "Body"; //Col2

     

  定义内部类 DatabaseHelper 继承自SQLiteOpenHelper,

  private static class DatabaseHelper extends SQLiteOpenHelper{

     

  public DatabaseHelper(Context context) {

  super(context, DATABASE_NAME, null, DATABASE_VERSION);

  }

     

  @Override

  public void onCreate(SQLiteDatabase db) {

  String sql = "CREATE TABLE " + TABLE_NAME + "("+TITLE+" text not null, "

  +BODY+" text not null);";

  Log.i("SQLiteDemo1:Create Table", sql);

  db.execSQL(sql);

  }

     

  @Override

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

  // TODO Auto-generated method stub

  }

  }

     

  构造函数初始化数据库信息。重载onCreate()方法使用sql语句创建表。

  在类SQLiteDemo1的onCreate()方法中初始化自定义的DatabseHelper类。

  dbHelper = new DatabaseHelper(this);

     

  定义了4个方法操作表

  //重建表

  private void createTable(){

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE

  + " text not null, " + BODY + " text not null " + ");";

  Log.i("SQLiteDemo1:Create Table", sql);

  try{

  db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);

  db.execSQL(sql);

  setTitle("数据表重建");

  }catch(SQLException ex){

  Log.i("sqlerr:",ex.getMessage());

  setTitle("重建错误");

  }

  }

  //删除表

  private void dropTable(){

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  String sql = "drop table " + TABLE_NAME;

  try {

  db.execSQL(sql);

  setTitle("数据表成功删除：" + sql);

  } catch (SQLException e) {

  setTitle("数据表删除错误");

  }

  }

  //插入数据

  private void insert(){

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  String sql1 = String.format("insert into %s (%s,%s)values('%s','%s')", TABLE_NAME,TITLE,BODY,"第一篇日记标题","第一篇日记内容");

  String sql2 = String.format("insert into %s (%s,%s)values('%s','%s')", TABLE_NAME,TITLE,BODY,"第2篇日记标题","第2篇日记内容");

  try{

  Log.i("insert sql:",sql1);

  Log.i("insert sql:",sql2);

  db.execSQL(sql1);

  db.execSQL(sql2);

  setTitle("插入数据成功");

  }catch(SQLException ex){

  setTitle("插入数据失败");

  }

  }

  //删除数据

  private void delete(){

  try{

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  db.delete(TABLE_NAME, "title='第2篇日记标题'", null);

  setTitle("delete successed");

  }catch(SQLException ex){

  setTitle("delete error");

  }

  }

  //查询数据

  private void search(){

  SQLiteDatabase db = dbHelper.getReadableDatabase();

  String []cols={TITLE,BODY};

  Cursor cur = db.query(TABLE_NAME, cols, null, null, null, null, null);

  int num = cur.getCount();

  setTitle("Counts:"+num);

  }

     

  都是使用的sql语句。

     

     

  完整的代码如下：

  import android.app.Activity;

  import android.content.Context;

  import android.database.Cursor;

  import android.database.SQLException;

  import android.database.sqlite.SQLiteDatabase;

  import android.database.sqlite.SQLiteOpenHelper;

  import android.database.sqlite.SQLiteDatabase.CursorFactory;

  import android.os.Bundle;

  import android.util.Log;

  import android.view.View;

  import android.widget.Button;

     

  public class SQLiteDemo1 extends Activity {

  private Button button1,button2,button3,button4,button5;

  private DatabaseHelper dbHelper;

  private static final String DATABASE_NAME= "SQLiteDemo1.db";

  private static final int DATABASE_VERSION= 1;

  private static final String TABLE_NAME = "diary";

  private static final String TITLE = "Title"; //Col1

  private static final String BODY = "Body"; //Col2

  private static class DatabaseHelper extends SQLiteOpenHelper{

     

  public DatabaseHelper(Context context) {

  super(context, DATABASE_NAME, null, DATABASE_VERSION);

  }

     

  @Override

  public void onCreate(SQLiteDatabase db) {

  String sql = "CREATE TABLE " + TABLE_NAME + "("+TITLE+" text not null, "

  +BODY+" text not null);";

  Log.i("SQLiteDemo1:Create Table", sql);

  db.execSQL(sql);

  }

     

  @Override

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

  // TODO Auto-generated method stub

  }

  }

  @Override

  public void onCreate(Bundle savedInstanceState) {

  super.onCreate(savedInstanceState);

  setContentView(R.layout.main);

  dbHelper = new DatabaseHelper(this);

  init();

  }

  private void init(){

  button1 = (Button)findViewById(R.id.button1);

  button2 = (Button)findViewById(R.id.button2);

  button3 = (Button)findViewById(R.id.button3);

  button4 = (Button)findViewById(R.id.button4);

  button5 = (Button)findViewById(R.id.button5);

  button1.setOnClickListener(new Button.OnClickListener(){

  @Override

  public void onClick(View v) {

  //重建表

  createTable();

  }

  });

  button2.setOnClickListener(new Button.OnClickListener(){

  @Override

  public void onClick(View v) {

  //删除表

  dropTable();

  }

  });

  button3.setOnClickListener(new Button.OnClickListener(){

  @Override

  public void onClick(View v) {

  //插入数据

  insert();

  }

  });

  button4.setOnClickListener(new Button.OnClickListener(){

  @Override

  public void onClick(View v) {

  //删除数据

  delete();

  }

  });

  button5.setOnClickListener(new Button.OnClickListener(){

  @Override

  public void onClick(View v) {

  //查询数据

  search();

  }

  });

  }

  //重建表

  private void createTable(){

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE

  + " text not null, " + BODY + " text not null " + ");";

  Log.i("SQLiteDemo1:Create Table", sql);

  try{

  db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);

  db.execSQL(sql);

  setTitle("数据表重建");

  }catch(SQLException ex){

  Log.i("sqlerr:",ex.getMessage());

  setTitle("重建错误");

  }

  }

  //删除表

  private void dropTable(){

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  String sql = "drop table " + TABLE_NAME;

  try {

  db.execSQL(sql);

  setTitle("数据表成功删除：" + sql);

  } catch (SQLException e) {

  setTitle("数据表删除错误");

  }

  }

  //插入数据

  private void insert(){

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  String sql1 = String.format("insert into %s (%s,%s)values('%s','%s')", TABLE_NAME,TITLE,BODY,"第一篇日记标题","第一篇日记内容");

  String sql2 = String.format("insert into %s (%s,%s)values('%s','%s')", TABLE_NAME,TITLE,BODY,"第2篇日记标题","第2篇日记内容");

  try{

  Log.i("insert sql:",sql1);

  Log.i("insert sql:",sql2);

  db.execSQL(sql1);

  db.execSQL(sql2);

  setTitle("插入数据成功");

  }catch(SQLException ex){

  setTitle("插入数据失败");

  }

  }

  //删除数据

  private void delete(){

  try{

  SQLiteDatabase db = dbHelper.getWritableDatabase();

  db.delete(TABLE_NAME, "title='第2篇日记标题'", null);

  setTitle("delete successed");

  }catch(SQLException ex){

  setTitle("delete error");

  }

  }

  //查询数据

  private void search(){

  SQLiteDatabase db = dbHelper.getReadableDatabase();

  String []cols={TITLE,BODY};

  Cursor cur = db.query(TABLE_NAME, cols, null, null, null, null, null);

  int num = cur.getCount();

  setTitle("Counts:"+num);

  }

  }

     

http://cnblogs.com/xingquan