首先:本文主要参考郭霖《第一行代码》持久化存储数据
一、准备工作:
由于真机未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,"+"author text,"
+"price real,"+"pages integer,"+"name text)";//primary key 主键,autoincrement 自增长
在onCreate里面执行上面的string,进行sql操作。创建成功,弹一个toast:create succeeded。
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(CREATE_BOOK);
Toast.makeText(context,"Create succeeded",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,"+"author text,"
+"price real,"+"pages integer,"+"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, 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);
sqLiteDatabase.execSQL(CREATE_CATEGORY);//新增一条执行语句
Toast.makeText(context,"Create succeeded",Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, 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,"BookStore.db",null,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,"BookStore.db",null,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",null,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","id=?",new String[]{"1"});
}
});
效果如下:
sqlite> select * from book;
2|haibo.xiong|12.5|1|learnSQLite
查询应该算是最复杂的。但是并不是在android中复杂,而是sql语句复杂。不打算多说。
在android代码中只是多了一条:Cursor cursor = db.query("book",null,null,null,null,null,null);
返回结果是一个cursor。通过下述方法可逐条取出。
select_database.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = myDatabaseHelper.getWritableDatabase();
Cursor cursor = db.query("book",null,null,null,null,null,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",null,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。