[文件存储]SQLite数据库初尝
首先:本文主要参考郭霖《第一行代码》持久化存储数据 一、准备工作:由于真机未root,用模拟器:(失败) Last login: Mon Sep 19 17:05:27 on ttys000 ebj1831:~ user$ adb shell adb server version (32) doesn't match this client (36); killing... error: could not install *smartsocket* listener: Address already in use ADB server didn't ACK * failed to start daemon * error: cannot connect to daemon查找原因,问题得到解决方案: 再次使用adb shell命令:(成功) ebj1831:~ user$ adb shell root@vbox86p:/ #这样就可以查看data/data文件下的内容:(如下) root@vbox86p:/ # cd data/data/ root@vbox86p:/data/data # ls -l drwxr-x--x u0_a0 u0_a0 2016-02-16 04:27 com.android.backupconfirm drwxr-x--x bluetooth bluetooth 2016-02-16 04:27 com.android.bluetooth drwxr-x--x u0_a18 u0_a18 2016-02-16 04:27 com.android.browser drwxr-x--x u0_a20 u0_a20 2016-02-16 04:27 com.android.calculator2 drwxr-x--x u0_a21 u0_a21 2016-02-16 04:28 com.android.calendar drwxr-x--x u0_a36 u0_a36 2016-02-16 04:27 com.android.camera drwxr-x--x u0_a22 u0_a22 2016-02-16 04:27 com.android.certinstaller drwxr-x--x u0_a2 u0_a2 2016-02-16 04:27 com.android.contacts drwxr-x--x u0_a24 u0_a24 2016-02-16 04:27 com.android.customlocale2 drwxr-x--x u0_a3 u0_a3 2016-02-16 05:01 com.android.defcontainer drwxr-x--x u0_a25 u0_a25 2016-02-16 04:28 com.android.deskclock drwxr-x--x u0_a26 u0_a26 2016-02-16 04:27 com.android.development 二、创建一张SQLite数据库表1)写一个类继承SQLiteOpenHelper public class MyDatabaseHelper extends SQLiteOpenHelper{ private static final String CREATE_BOOK = "create table book (" +"id integer primary key autoincrement,"+"author text," +"price real,"+"pages integer,"+"name text)";//primary key 主键,autoincrement 自增长 private Context context; public MyDatabaseHelper(Context context,String name,SQLiteDatabase.CursorFactory factory,int version) { super(context,name,factory,version); this.context = context; } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { sqLiteDatabase.execSQL(CREATE_BOOK); Toast.makeText(context,"Create succeeded",Toast.LENGTH_SHORT).show(); } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase,int i,int i1) { } }代码很简单:一个string字符串里面包含一个创建表的sql语句。(此处不对sql语句多说) private static final String CREATE_BOOK = "create table book (" +"id integer primary key autoincrement,"+"name text)";//primary key 主键,autoincrement 自增长在onCreate里面执行上面的string,进行sql操作。创建成功,弹一个toast:create succeeded。 public void onCreate(SQLiteDatabase sqLiteDatabase) { sqLiteDatabase.execSQL(CREATE_BOOK); Toast.makeText(context,Toast.LENGTH_SHORT).show(); }2)Activity主方法源码: public class MainActivity extends Activity { private TextView create_database; private MyDatabaseHelper myDatabaseHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); create_database = (TextView)findViewById(R.id.create_database); myDatabaseHelper = new MyDatabaseHelper(this,"BookStore.db",null,1);//数据库名,版本号1 create_database.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { myDatabaseHelper.getWritableDatabase(); } }); } }3)xml布局源码: <?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/create_database" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_alignParentTop="true" android:text="create_database" android:textColor="#999999" android:gravity="center" android:padding="15dp" android:textSize="16sp" /> </RelativeLayout>4)动态效果图: 光toast还不能看出我已经创建成功了一个数据库表。 如何查看呢?通过前期准备中的adb shell命令来看:进入/data/data/com.example.user.learnsqlite 文件夹下: root@vbox86p:/data/data/com.example.user.learnsqlite # ls //创建前 cache files lib root@vbox86p:/data/data/com.example.user.learnsqlite # ls //创建后 cache databases files lib键入sqlite3 BookStore.db得到如下: SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>查看哪些表:.table sqlite>.table android_metadata book 三、升级数据库1)什么时候需要升级数据库? 比如新增字段。比如新增表。都需要升级数据库。 修改后的继承SQLiteOpenHelper类如下: public class MyDatabaseHelper extends SQLiteOpenHelper{ private static final String CREATE_BOOK = "create table book (" +"id integer primary key autoincrement,"+"name text)";//primary key 主键,autoincrement 自增长 private static final String CREATE_CATEGORY = "create table Category (" +"id integer primary key autoincrement,"+"category_name text," +"category_code integer)";//新增一个表 private Context context; public MyDatabaseHelper(Context context,version); this.context = context; } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { sqLiteDatabase.execSQL(CREATE_BOOK); sqLiteDatabase.execSQL(CREATE_CATEGORY);//新增一条执行语句 Toast.makeText(context,int i1) { sqLiteDatabase.execSQL("drop table if exists book");//如果存在则删除 sqLiteDatabase.execSQL("drop table if exists Category");//如果存在则删除 onCreate(sqLiteDatabase);//执行创建 } }代码简单,注释明晰,不多解释。 如何触发升级数据库代码?直接上acitivyt代码就知道了: public class MainActivity extends Activity { private TextView create_database; private MyDatabaseHelper myDatabaseHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); create_database = (TextView)findViewById(R.id.create_database); myDatabaseHelper = new MyDatabaseHelper(this,2);//数据库名,版本号2 create_database.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { myDatabaseHelper.getWritableDatabase(); } }); } }代码不同之处在于版本号使用了2. 查看数据库中的table: sqlite> .table //触发前 android_metadata book sqlite> .table //触发后 Category android_metadata book 四、对数据库中表的操作(增删改查)1)新增一条数据: public class MainActivity extends Activity { private TextView create_database,add_database; private MyDatabaseHelper myDatabaseHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); create_database = (TextView)findViewById(R.id.create_database); add_database = (TextView)findViewById(R.id.add_database); myDatabaseHelper = new MyDatabaseHelper(this,2);//数据库名,版本号1 create_database.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { myDatabaseHelper.getWritableDatabase(); } }); add_database.setOnClickListener(new View.OnClickListener() {//新增表数据代码 @Override public void onClick(View v) { SQLiteDatabase db = myDatabaseHelper.getWritableDatabase();//获取数据库对象,没有则创建,有则获取 ContentValues values = new ContentValues();//组装数据,用键值对 values.put("name","learnSQLite"); values.put("pages","1"); values.put("price","$12.5"); values.put("author","haibo.xiong"); db.insert("book",values);//执行插入语句 } }); } }查看表的数据是否新增: ps:刚尝试了一把真机,发现一个问题:root测试机。(成功root如下)
ebj1831:~ user$ adb shell shell@HM2014813:/ $ su root@HM2014813:/ # but超级坑,小米手机居然sqlite3找不到。也就是说我走到要sqlite3 BookStore.db 过不去。然后3个小时过去了,从网上找了一圈,各种导入sqlite3,最后还是发现版本不一致。说多了,都是泪。总之我最后没有成功导入sqlite3 ,希望大家如果要尝试这个时候,直接用模拟器genymotion,不要往坑里跳。 回到模拟器,查看我插入的数据: adb shell命令按顺序总结如下
1、adb shell 2、su 3、cd /data/data/app项目文件/dababases/ 4、sqlite3 BookStore.db 5、select * from book;效果如下:(点了两次插入,插入了两条数据)
sqlite> select * from book; 1|haibo.xiong|$12.5|1|learnSQLite 2|haibo.xiong|$12.5|1|learnSQLite
直接上代码:写一个按钮的点击事件:
update_database.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("price",10000); db.update("book",values,"id=?",new String[]{"1"}); } }); 很眼熟有木有?换汤不换料罢了。有个地方需要注意:第三个参数,id=?必须要要加上=?,至于为什么之前都是普通的值,因为需要这个加上一个=判断关系,不仅仅=还可以< 或者>号。 结果如下:
sqlite> select * from book; 1|haibo.xiong|10000.0|1|learnSQLite 2|haibo.xiong|12.5|1|learnSQLite
3)删除一条数据
delete_database.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); db.delete("book",new String[]{"1"}); } });效果如下:
sqlite> select * from book; 2|haibo.xiong|12.5|1|learnSQLite
查询应该算是最复杂的。但是并不是在android中复杂,而是sql语句复杂。不打算多说。 在android代码中只是多了一条:Cursor cursor = db.query("book",null); 返回结果是一个cursor。通过下述方法可逐条取出。
select_database.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); Cursor cursor = db.query("book",null); if (cursor.moveToFirst()){ do { String name = cursor.getString(cursor.getColumnIndex("name")); int id = cursor.getInt(cursor.getColumnIndex("id")); int pages = cursor.getInt(cursor.getColumnIndex("pages")); Double price = cursor.getDouble(cursor.getColumnIndex("price")); String author = cursor.getString(cursor.getColumnIndex("author")); Log.d("Log",name+id+pages+price+author); Toast.makeText(getApplicationContext(),name+id+pages+price+author,Toast.LENGTH_SHORT).show(); }while (cursor.moveToNext()); } cursor.close(); } });Log效果如下:(点击查询按钮)
09-20 04:32:35.554 7943-7943/com.example.user.learnsqlite D/Log: learnSQLite2112.5haibo.xiong 09-20 04:32:35.554 7943-7943/com.example.user.learnsqlite D/Log: learnSQLite5112.5haibo.xiong 09-20 04:32:35.558 7943-7943/com.example.user.learnsqlite D/Log: learnSQLite6112.5haibo.xiong 09-20 04:32:35.558 7943-7943/com.example.user.learnsqlite D/Log: learnSQLite7112.5haibo.xiong 09-20 04:32:35.558 7943-7943/com.example.user.learnsqlite D/Log: learnSQLite8112.5haibo.xiong其实到这儿SQLite数据库已经分析完了。 但是,不得不提一句,其实这么做并不符合多数人的习惯。比如我们之前已经熟练掌握了sql语句。用这个方式,会觉得比较繁琐。 例如插入:
SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); // ContentValues values = new ContentValues(); // values.put("name","learnSQLite"); // values.put("pages",1); // values.put("price",12.5); // values.put("author","haibo.xiong"); // db.insert("book",values); db.execSQL("insert into book(name,pages,price,author) values (?,?,?)",new String[]{"learnSql","2","1888","bobo"}); 效果:
sqlite> select * from book; 2|haibo.xiong|12.5|1|learnSQLite 5|haibo.xiong|12.5|1|learnSQLite 6|haibo.xiong|12.5|1|learnSQLite 7|haibo.xiong|12.5|1|learnSQLite 8|haibo.xiong|12.5|1|learnSQLite 9|bobo|1888.0|2|learnSql 例如删除:
SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); db.execSQL("delete from book where id=?",new String[]{"9"});效果:
sqlite> select * from book; 2|haibo.xiong|12.5|1|learnSQLite 5|haibo.xiong|12.5|1|learnSQLite 6|haibo.xiong|12.5|1|learnSQLite 7|haibo.xiong|12.5|1|learnSQLite 8|haibo.xiong|12.5|1|learnSQLite例如更新: SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); db.execSQL("update book set price=? where id=?",new String[]{"1000","8"});效果:
sqlite> select * from book; 2|haibo.xiong|12.5|1|learnSQLite 5|haibo.xiong|12.5|1|learnSQLite 6|haibo.xiong|12.5|1|learnSQLite 7|haibo.xiong|12.5|1|learnSQLite 8|haibo.xiong|1000.0|1|learnSQLite唯一稍有差异的是查询:rawQuery
SQLiteDatabase db = myDatabaseHelper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from book",null);取出效果一样。不再上效果。 over。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |