SqLite的基本使用

1、布局

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  3.     xmlns:tools="http://schemas.android.com/tools"
  4.     android:layout_width="match_parent"
  5.     android:layout_height="wrap_content"
  6.     android:orientation="vertical"
  7.     tools:context="lpc.com.project641.MainActivity">
  9.     <Button
  10.         android:id="@+id/create"
  11.         android:text="创建数据库"
  12.         android:layout_width="wrap_content"
  13.         android:layout_height="wrap_content"
  14.         android:layout_gravity="center_horizontal" />
  16.     <Button
  17.         android:id="@+id/add"
  18.         android:text="添加数据"
  19.         android:layout_width="wrap_content"
  20.         android:layout_height="wrap_content"
  21.         android:layout_gravity="center_horizontal" />
  23.     <Button
  24.         android:id="@+id/update"
  25.         android:text="更新数据"
  26.         android:layout_width="wrap_content"
  27.         android:layout_height="wrap_content"
  28.         android:layout_gravity="center_horizontal" />
  30.     <Button
  31.         android:id="@+id/delete"
  32.         android:text="删除数据"
  33.         android:layout_width="wrap_content"
  34.         android:layout_height="wrap_content"
  35.         android:layout_gravity="center_horizontal" />
  37.     <Button
  38.         android:id="@+id/query"
  39.         android:text="查询数据"
  40.         android:layout_width="wrap_content"
  41.         android:layout_height="wrap_content"
  42.         android:layout_gravity="center_horizontal" />
  43. </LinearLayout>


2、mydatahelper

  1. package lpc.com.project641;
  3. import android.content.Context;
  4. import android.database.sqlite.SQLiteDatabase;
  5. import android.database.sqlite.SQLiteOpenHelper;
  6. import android.widget.Toast;
  8. /**
  9.  * 自定义DatabHelper，可以定义sql语句，继承SQLiteOpenHelper，需要重写构造方法，还有onCreate和
  10.  * onUpgrade方法。
  11.  */
  12. public class MyDatabHelper extends SQLiteOpenHelper {
  14.     //创建表语句
  15.     public static final String CREATE_BOOK = "create table Book(" +
  16.             "id integer primary key autoincrement," +
  17.             "author text," +
  18.             "price real," +
  19.             "pages integer," +
  20.             "name text)";
  22.     //创建表语句
  23.     public static final String CREATE_CATAGORY = "create table Catagory(" +
  24.             "id integer primary key autoincrement," +
  25.             "category_name text," +
  26.             "category_code integer)";
  28.     //创建表语句
  29.     public static final String CREATE_CATAGORY1 = "create table Catagory1(" +
  30.             "id integer primary key autoincrement," +
  31.             "category_name1 text," +
  32.             "category_code1 integer)";
  34.     private Context mContext;
  36.     public MyDatabHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
  37.         super(context, name, factory, version);
  38.         mContext = context;
  39.     }
  41.     //执行sql语句
  42.     @Override
  43.     public void onCreate(SQLiteDatabase db) {
  44.         db.execSQL(CREATE_BOOK);
  45.         db.execSQL(CREATE_CATAGORY);
  46.         db.execSQL(CREATE_CATAGORY1);
  47.         Toast.makeText(mContext,"创建成功",Toast.LENGTH_SHORT).show();
  48.     }
  50.     //升级或改写数据时，执行此逻辑，
  51.     @Override
  52.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  53.         db.execSQL("drop table if exists Book");
  54.         db.execSQL("drop table if exists Catagory");
  55.         db.execSQL("drop table if exists Catagory1");
  56.         onCreate(db);
  57.     }
  58. }

3、MainActivity

  1. package lpc.com.project641;
  3. import android.content.ContentValues;
  4. import android.database.Cursor;
  5. import android.database.sqlite.SQLiteDatabase;
  6. import android.support.v7.app.AppCompatActivity;
  7. import android.os.Bundle;
  8. import android.util.Log;
  9. import android.view.View;
  10. import android.widget.Button;
  12. public class MainActivity extends AppCompatActivity {
  14.     //创建一个MyDatabHelper 对象
  15.     private MyDatabHelper dbHelper;
  16.     @Override
  17.     protected void onCreate(Bundle savedInstanceState) {
  18.         super.onCreate(savedInstanceState);
  19.         setContentView(R.layout.activity_main);
  20.         //设置dbHelper对象的4个参数，
  21.         /**
  22.          * this             当前对象
  23.          * BookStore.db     数据库名字
  24.          * null             自定义的Cursor 一般传入null
  25.          * 3                版本号，进行数据更新时，版本号要比上一个版本号高
  26.          * */
  27.         dbHelper = new MyDatabHelper(this,"BookStore.db",null,3);
  28.         //获取4个按钮
  29.         Button create = (Button) findViewById(R.id.create);
  30.         Button add = (Button) findViewById(R.id.add);
  31.         Button update = (Button) findViewById(R.id.update);
  32.         Button delete = (Button) findViewById(R.id.delete);
  33.         Button query = (Button) findViewById(R.id.query);
  34.         Button shiwu = (Button) findViewById(R.id.shiwu);
  35.         //创建数据库
  36.         create.setOnClickListener(new View.OnClickListener() {
  37.             @Override
  38.             public void onClick(View v) {
  39.                 dbHelper.getWritableDatabase();
  40.             }
  41.         });
  42.         //添加数据
  43.         add.setOnClickListener(new View.OnClickListener() {
  44.             @Override
  45.             public void onClick(View v) {
  46.                 SQLiteDatabase db = dbHelper.getWritableDatabase();
  47.                 ContentValues values = new ContentValues();
  48.                 values.put("name","刘朋程");
  49.                 values.put("author","lpc");
  50.                 values.put("pages",454);
  51.                 values.put("price",16.96);
  52.                 db.insert("Book",null,values);
  53.                 values.clear();
  54.                 values.put("name","李莉");
  55.                 values.put("author","lili");
  56.                 values.put("pages",555);
  57.                 values.put("price",19.95);
  58.                 db.insert("Book",null,values);
  59.             }
  60.         });
  61.         //升级数据
  62.         update.setOnClickListener(new View.OnClickListener() {
  63.             @Override
  64.             public void onClick(View v) {
  65.                 SQLiteDatabase db = dbHelper.getWritableDatabase();
  66.                 ContentValues values = new ContentValues();
  67.                 values.put("price",11.11);
  68.                 db.update("Book",values,"name=?",new String[]{"刘朋程"});
  69.             }
  70.         });
  71.         //删除数据
  72.         delete.setOnClickListener(new View.OnClickListener() {
  73.             @Override
  74.             public void onClick(View v) {
  75.                 SQLiteDatabase db = dbHelper.getWritableDatabase();
  76.                 db.delete("Book","pages > ?",new String[]{"500"});
  77.             }
  78.         });
  79.         //查询数据
  80.         query.setOnClickListener(new View.OnClickListener() {
  81.             @Override
  82.             public void onClick(View v) {
  83.                 SQLiteDatabase db = dbHelper.getWritableDatabase();
  84.                 Cursor cursor = db.query("Book",null,null,null,null,null,null);
  85.                 if (cursor.moveToNext()){
  86.                     do {
  87.                         String name = cursor.getString(cursor.getColumnIndex("name"));
  88.                         String author = cursor.getString(cursor.getColumnIndex("author"));
  89.                         String pages = cursor.getString(cursor.getColumnIndex("pages"));
  90.                         String price = cursor.getString(cursor.getColumnIndex("price"));
  92.                         Log.d("TAG", "name is " + name);
  93.                         Log.d("TAG", "author is " + author);
  94.                         Log.d("TAG", "pages is " + pages);
  95.                         Log.d("TAG", "price is " + price);
  96.                     }while (cursor.moveToNext());
  97.                 }
  98.                 cursor.close();
  99.             }
  100.         });
  101.         /**
  102.          * 功能说明：事务的基本使用
  103.          * 事务的特性可以保证让某一系列的操作要么全部完成，要不全不完成，
  104.          * 期间如果发生 异常，就不会完成事务。
  105.          * */
  106.         shiwu.setOnClickListener(new View.OnClickListener() {
  107.             @Override
  108.             public void onClick(View v) {
  109.                 SQLiteDatabase db = dbHelper.getWritableDatabase();
  110.                 db.beginTransaction();
  111.                 try {
  112.                     db.delete("Book", null, null);
  113.                     ContentValues values = new ContentValues();
  114.                     values.put("name", "中通快递");
  115.                     values.put("author", "kuaidi");
  116.                     values.put("pages", 720);
  117.                     values.put("price", 20.85);
  118.                     db.insert("Book", null, values);
  119.                     db.setTransactionSuccessful();
  120.                 }catch (Exception e){
  121.                     e.printStackTrace();
  122.                 } finally {
  123.                     db.endTransaction();
  124.                 }
  125.             }
  126.         });
  127.     }
  128. }





posted @ 2016-01-13 09:48  liupengcheng  阅读(238)  评论(0)    收藏  举报