参考:《第一行代码》
代码搬运工。
还是按照书上的步骤来吧,先是进行创建数据库训练。安卓内置一款轻量级的关系型数据库SQLite。为了方便我们管理数据库,专门提供了SQLiteOpenHelper这个抽象类,在使用时我们只能借助其子类。
- 它有两个构造方法,我们通常只是使用参数较少的一个,较少的方法有四个参数,分别是,context,数据库名,XXX目前用不到的参数(一般传入null),数据库版本。
- 它有两个抽象方法需要我们进行实现,那就是onCreate()和onUpgrade()方法,第一个方法只有一个参数,SQLiteDataBase参数,后一个方法就有三个参数,SQLiteDataBase,oldVersion,newVersion,
- 它有两个非常重要的实例方法,一个是getReadableDatabase(),和另一个方法getWritableDatabase()方法,都没有参数,它们用于打开或创建一个数据库(若数据库已经存在就打开,不存在就创建)
1,创建数据库的步骤,先生成管理数据库的构建管理数据库的类,通过新建其对象先拿到一个数据库,创建我们用其实例方法进行实例化。
注意在生成SQLiteOpenHelper的子类时,建表语句的规范(不能少了顿号,且权限为public )
建库代码:
package com.example.databasetest;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
public class MainActivity extends Activity {
private Button createButton;
private MyDataBaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=new MyDataBaseHelper(MainActivity.this, "h.db", null, 1);
createButton=(Button) findViewById(R.id.btn_create);
createButton.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
dbHelper.getWritableDatabase();
}
});
}
}
布局:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent">
<Button
android:id="@+id/btn_create"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/create_database"
/>
</LinearLayout>
管理数据库的类
package com.example.databasetest;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
public class MyDataBaseHelper extends SQLiteOpenHelper {
private Context mContext;
public static final String CREATE_BOOK="create table Book ("//建表语句
+"id integer primary key autoincrement,"
+"author text,"
+"price real,"
+"pages integer,"
+"name text)";
public MyDataBaseHelper(Context context, String name,//构造方法
CursorFactory factory, int version) {
super(context, name, factory, version);
mContext=context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);//执行建表语句
Toast.makeText(mContext, "You are beautiful", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
,2,下面是最开始的简单粗暴升级数据库,(能称得上是升级数据库的级别的改变肯定是相当大的,与添加删除数据的小痛小痒区分开),比如书上的例子,给数据库再添加一张表。当然是不能先写个常量的建表语句然后到onCreate()方法中去执行那么简单了,因为已经建立的数据库名称系统是能够检测到的,一旦发现你要建立的数据库已经存在就不会再次执行onCreate()方法,因此新建表的语句是永远得不到执行的。
两种解决办法,一种是卸载程序然后再次运行,这种方法直接否决;另一种是利用onUpgrade()方法,先将已经存在的表删除,再重新执行onCreate()方法进行建表。(删除使用drop语句)
综上:我们需要对数据库管理子类进行改动;
package com.example.databasetest;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
public class MyDataBaseHelper extends SQLiteOpenHelper {
private Context mContext;
public static final String CREATE_BOOK = "create table Book ("// 建表语句
+ "id integer primary key autoincrement,"
+ "author text,"
+ "price real," + "pages integer," + "name text)";
public static final String CREATE_CATEGORY = "create table Category ("
+ "id integer primary key autoincrement," + "category_naem text,"
+ "category_code integer)";
public MyDataBaseHelper(Context context, String name,// 构造方法
CursorFactory factory, int version) {
super(context, name, factory, version);
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);// 执行建表语句
db.execSQL(CREATE_CATEGORY);
Toast.makeText(mContext, "You are beautiful", Toast.LENGTH_SHORT)
.show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("drop table if exists Book");//drop
db.execSQL("drop table if exists Category");
onCreate(db);
}
}
,3,下面就是正式地进行操作了,先是来进行insert()操作,insert()方法有三个参数,分别是表名,不常用参数null(这里是传入null的意思),(ContentValues)values。步骤,先是打开一个数据库,于是拿到一个SQLiteDatabase对象,再new 出一个ContentValues相当于载体,最后在一并insert,要是有多条数据,则先clear()再进行下一条数据的组装。
代码:
package com.example.databasetest;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener{
private Button createButton,adddataButton;
private MyDataBaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=new MyDataBaseHelper(MainActivity.this, "h.db", null, 1);//
createButton=(Button) findViewById(R.id.btn_create);
adddataButton=(Button) findViewById(R.id.btn_adddata);
createButton.setOnClickListener(this);
adddataButton.setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_create:
dbHelper.getWritableDatabase();
break;
case R.id.btn_adddata:
add();
break;
}
}
public void add(){//添加數據
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "xqf");
values.put("name", "How to get a girl?");
values.put("pages", 100000000);
values.put("price",0.05 );
db.insert("Book", null, values);
values.clear();//清除,準備下一條數據的組裝
values.put("author", "wly");
values.put("name", "I hate poor boys");
values.put("price", 10000000000.0);
values.put("pages", 1);
db.insert("Book", null, values);
Toast.makeText(this, "add success!", Toast.LENGTH_SHORT).show();
}
}
布局文件就是添加一个按钮,简单
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<Button
android:id="@+id/btn_create"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/create_database"
/>
<Button
android:id="@+id/btn_adddata"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/add_data"
/>
</LinearLayout>
4,下面进行的是简单数据的更新,比如,将表中的某一条数据更新,早上的白菜三块五一斤,下午却变成了五毛,但菜市场还是那个菜市场!我们来更新,Book表中作者为wly的书名,更新为WLY,,,,,,呵呵呵,,,,。更新也是对数据库的操作,因此有update方法,此方法有四个参数,a为表名,b为(ContentValues)values, c,d分别是对应特征的数据特征。
java部分
package com.example.databasetest;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener{
private Button createButton,adddataButton,updateButton;
private MyDataBaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=new MyDataBaseHelper(MainActivity.this, "h.db", null, 1);//
createButton=(Button) findViewById(R.id.btn_create);
adddataButton=(Button) findViewById(R.id.btn_adddata);
updateButton=(Button) findViewById(R.id.btn_update);
createButton.setOnClickListener(this);
adddataButton.setOnClickListener(this);
updateButton.setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_create:
dbHelper.getWritableDatabase();
break;
case R.id.btn_adddata:
add();
break;
case R.id.btn_update:
update();
break;
}
}
public void add(){//添加數據
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "xqf");
values.put("name", "How to get a girl?");
values.put("pages", 100000000);
values.put("price",0.05 );
db.insert("Book", null, values);
values.clear();//清除,準備下一條數據的組裝
values.put("author", "wly");
values.put("name", "I hate poor boys");
values.put("price", 10000000000.0);
values.put("pages", 1);
db.insert("Book", null, values);
Toast.makeText(this, "add success!", Toast.LENGTH_SHORT).show();
}
public void update(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "WLY");
db.update("Book", values, "name=?", new String[]{"wly"});
//上面语句的意思是把Book表中author为wly的书的author改成WLY
Toast.makeText(this, "update success", Toast.LENGTH_SHORT).show();
}
}
布局也是在上面的基础上加一个Button,
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<Button
android:id="@+id/btn_create"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/create_database"
/>
<Button
android:id="@+id/btn_adddata"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/add_data"
/>
<Button
android:id="@+id/btn_update"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/update_data"
/>
</LinearLayout>
5,下面是删除的test,这里涉及的删除只有两种情况,一种是删除表,一种是删除表中的某一条数据,而这条数据包含很多的属性,前面的操作都是在更新属性,添加数据,对具体内容要借助values(ContentValues)。而这里是进行整条数据的删除,不涉及具体属性等等,因此不借助values,update()方法有三个参数,分别是a表名,b,c对应特征
java代码部分
package com.example.databasetest;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener{
private Button createButton,adddataButton,updateButton,deleteButton;
private MyDataBaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=new MyDataBaseHelper(MainActivity.this, "h.db", null, 1);//
createButton=(Button) findViewById(R.id.btn_create);
adddataButton=(Button) findViewById(R.id.btn_adddata);
updateButton=(Button) findViewById(R.id.btn_update);
deleteButton=(Button) findViewById(R.id.btn_delete);
createButton.setOnClickListener(this);
adddataButton.setOnClickListener(this);
updateButton.setOnClickListener(this);
deleteButton.setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_create:
dbHelper.getWritableDatabase();
break;
case R.id.btn_adddata:
add();
break;
case R.id.btn_update:
update();
break;
case R.id.btn_delete:
delete();
break;
}
}
public void add(){//添加數據
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "xqf");
values.put("name", "How to get a girl?");
values.put("pages", 100000000);
values.put("price",0.05 );
db.insert("Book", null, values);
values.clear();//清除,準備下一條數據的組裝
values.put("author", "wly");
values.put("name", "I hate poor boys");
values.put("price", 10000000000.0);
values.put("pages", 1);
db.insert("Book", null, values);
Toast.makeText(this, "add success!", Toast.LENGTH_SHORT).show();
}
public void update(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "WLY");
db.update("Book", values, "name=?", new String[]{"wly"});
//上面语句的意思是把Book表中author为wly的书的author改成WLY
Toast.makeText(this, "update success", Toast.LENGTH_SHORT).show();
}
public void delete(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
db.delete("Book", "author=?", new String[]{"xqf"});
//刪除作者是xqf的這本書
Toast.makeText(this, "delete success", Toast.LENGTH_SHORT).show();
}
}
布局仍然是添加Button,
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<Button
android:id="@+id/btn_create"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/create_database"
/>
<Button
android:id="@+id/btn_adddata"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/add_data"
/>
<Button
android:id="@+id/btn_update"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/update_data"
/>
<Button
android:id="@+id/btn_delete"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/delete_data"
/>
</LinearLayout>
6,接着就是参数众多的查询操作了,query()方法最少都是七个参数,说实话目前百度到的好像都是一样的解释,没有一个通俗易懂的,。,。我也就只能跟着书上说,查询整个表里的内容了,然后用一个textView显示出来,。,注意查询完后一定要将cursor关闭,
package com.example.databasetest;
import android.R.integer;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener{
private Button createButton,adddataButton,updateButton,deleteButton,queryButton;
private TextView textView;
private MyDataBaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=new MyDataBaseHelper(MainActivity.this, "h.db", null, 1);//
createButton=(Button) findViewById(R.id.btn_create);
adddataButton=(Button) findViewById(R.id.btn_adddata);
updateButton=(Button) findViewById(R.id.btn_update);
deleteButton=(Button) findViewById(R.id.btn_delete);
queryButton=(Button) findViewById(R.id.btn_query);
textView=(TextView) findViewById(R.id.textview);
createButton.setOnClickListener(this);
adddataButton.setOnClickListener(this);
updateButton.setOnClickListener(this);
deleteButton.setOnClickListener(this);
queryButton.setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_create:
dbHelper.getWritableDatabase();
break;
case R.id.btn_adddata:
add();
break;
case R.id.btn_update:
update();
break;
case R.id.btn_delete:
delete();
break;
case R.id.btn_query:
query();
break;
}
}
public void add(){//添加數據
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "xqf");
values.put("name", "How to get a girl?");
values.put("pages", 100000000);
values.put("price",0.05 );
db.insert("Book", null, values);
values.clear();//清除,準備下一條數據的組裝
values.put("author", "wly");
values.put("name", "I hate poor boys");
values.put("price", 10000000000.0);
values.put("pages", 1);
db.insert("Book", null, values);
Toast.makeText(this, "add success!", Toast.LENGTH_SHORT).show();
}
public void update(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("author", "WLY");
db.update("Book", values, "name=?", new String[]{"wly"});
//上面语句的意思是把Book表中author为wly的书的author改成WLY
Toast.makeText(this, "update success", Toast.LENGTH_SHORT).show();
}
public void delete(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
db.delete("Book", "author=?", new String[]{"xqf"});
//刪除作者是xqf的這本書
Toast.makeText(this, "delete success", Toast.LENGTH_SHORT).show();
}
public void query(){
SQLiteDatabase db=dbHelper.getWritableDatabase();
Cursor cursor=db.query("Book",null, null, null, null, null, null);
while(cursor.moveToNext()){
String nameString=cursor.getString(cursor.getColumnIndex("name"));
String authorString=cursor.getString(cursor.getColumnIndex("author"));
double price=cursor.getDouble(cursor.getColumnIndex("price"));
int pages=cursor.getInt(cursor.getColumnIndex("pages"));
textView.setText(authorString+"\n"+nameString+"\n"+price+"\n"+"pages");
Toast.makeText(this, "query success", Toast.LENGTH_SHORT).show();
}
cursor.close();
}
}
布局
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<Button
android:id="@+id/btn_create"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/create_database"
/>
<Button
android:id="@+id/btn_adddata"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/add_data"
/>
<Button
android:id="@+id/btn_update"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/update_data"
/>
<Button
android:id="@+id/btn_delete"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/delete_data"
/>
<Button
android:id="@+id/btn_query"
android:layout_margin="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/query_data"
/>
<TextView
android:id="@+id/textview"
android:layout_width="match_parent"
android:layout_height="wrap_content"
/>
</LinearLayout>
综上,几乎所有的操作都要先拿到SQLiteDatabase对象,而这个对象时借助SQLiteOpenHelper的子类对象得到的。具体的添加什么属性要用到ContentValues,.,.,,.,.,.,,,还有就是要记住所有方法的参数个数及其含义。。。。。。。。。。。。。。。。。。真是难呀!!