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">
  8. <Button
  9. android:id="@+id/create"
  10. android:text="创建数据库"
  11. android:layout_width="wrap_content"
  12. android:layout_height="wrap_content"
  13. android:layout_gravity="center_horizontal" />
  14. <Button
  15. android:id="@+id/add"
  16. android:text="添加数据"
  17. android:layout_width="wrap_content"
  18. android:layout_height="wrap_content"
  19. android:layout_gravity="center_horizontal" />
  20. <Button
  21. android:id="@+id/update"
  22. android:text="更新数据"
  23. android:layout_width="wrap_content"
  24. android:layout_height="wrap_content"
  25. android:layout_gravity="center_horizontal" />
  26. <Button
  27. android:id="@+id/delete"
  28. android:text="删除数据"
  29. android:layout_width="wrap_content"
  30. android:layout_height="wrap_content"
  31. android:layout_gravity="center_horizontal" />
  32. <Button
  33. android:id="@+id/query"
  34. android:text="查询数据"
  35. android:layout_width="wrap_content"
  36. android:layout_height="wrap_content"
  37. android:layout_gravity="center_horizontal" />
  38. </LinearLayout>


2、mydatahelper

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

3、MainActivity

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





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