·SQLite是安卓内嵌的数据库,它仅占用内存几百kb。
一、数据库的基本操作
·数据库的创建方法:
public class MyHelper extends SQLiteOpenHelper{ //创建一个类继承SQLiteOpenHelper
//构造方法,参数为上下文对象
public MyHelper(Context context){
super(context,"book.db",null,1);//参数1:上下文对象,参数2:数据库名,参数3:游标工厂,参数4:自定义的数据库版本号
}
//第一次创建时调用,所以把创建数据库的语句写在这里。
public void onCreat(SQLiteDatabase db){
db.execSQL("CREATE TABLE information (_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),price INTERGER)");
}
//当数据库的版本号增加时调用
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
//适合写更新数据库的语句
}
}
·添加(插入):
public void insert(String name,String price){
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name",name);
values.put("price",price);
long id = db.inseret("information",null,values);
db.close();
}
·修改(更新):
public int update(String name,String price){
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("price",price);
//参数4:占位符的具体内容
int number = db.update("information",values,"name = ?",new String[]{name});//返回更新行数
db.close();
return number;
}
·删除:
public int delete(long id){
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
int number = db.delete("information","_id=?",new String[]{id+""});//根据id删除
db.close();
return number;
}
·查询:
public boolean delete(long id){
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
Cursor cursor = db.query("information",null,"_id=?",new String[]{id+""},null,null,null);//根据id查询,返回一个行数集合Cursor
boolean result = cursor.moveToNext();
cursor.close();
db.close();
return result;
}
*提示:除了查询以外,增删改还可以使用SQLiteDatabase的execSQL()来执行。比如:
//执行更新
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("update book set price = 100 where name = ?",new String[]{"三毛"});
二、SQLite中的事务
·已转账为例子:
PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper (getContext());
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();//开启事务
try(){
db.execSQL("update person set account = account-1000 where name = ?",new String[]{"wangwu"});
db.execSQL("update person set account = account+1000 where name = ?",new String[]{"zhaosi"});
//设置事务成功,当事务结束时才会提交事务。
db.setTransactionSuccessful();
}catch(Exception e){
Log.i("事务处理失败",e.toString());
}finally{
db.endTransaction();//关闭事务
db.close();
}
三、实例演示使用SQLite数据库
·使用数据库操作账号密码的流程如下:
1、简单写一个操作页面:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:gravity="center"
tools:context=".MainActivity">
<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="账号:"/>
<EditText
android:id="@+id/username"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:width="80dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="密码:"/>
<EditText
android:id="@+id/password"
android:width="80dp"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/bt_insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入"/>
<Button
android:id="@+id/bt_query"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询"/>
<Button
android:id="@+id/bt_update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="更新"/>
<Button
android:id="@+id/bt_delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除"/>
</LinearLayout>
<TextView
android:id="@+id/showText"
android:text="暂无数据"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
</LinearLayout>
2、编写数据库的创建类
package com.sjzc.edu;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class MyHelper extends SQLiteOpenHelper {//继承SQLiteOpenHelper
public MyHelper(Context context) {
super(context, "itcast.db", null, 1);//参数3:游标工厂
}
//创建数据库
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE information (_id INTEGER PRIMARY KEY AUTOINCREMENT,username VARCHAR(20),password VARCHAR(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
3、在activity中进行增删改查的操作(重点)
package com.sjzc.edu;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private MyHelper myHelper;
private EditText et_username;
private EditText et_password;
private TextView showText;
private Button bt_query;
private Button bt_insert;
private Button bt_update;
private Button bt_delete;
private String username;
private String password;
private SQLiteDatabase db;
private ContentValues values;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//创建数据库
myHelper= new MyHelper(this);
initView();//初始化控件
}
private void initView() {
et_username = findViewById(R.id.username);
et_password = findViewById(R.id.password);
showText = findViewById(R.id.showText);
bt_delete = findViewById(R.id.bt_delete);
bt_insert = findViewById(R.id.bt_insert);
bt_query = findViewById(R.id.bt_query);
bt_update = findViewById(R.id.bt_update);
bt_update.setOnClickListener(MainActivity.this);
bt_delete.setOnClickListener(MainActivity.this);
bt_query.setOnClickListener(MainActivity.this);
bt_insert.setOnClickListener(MainActivity.this);
}
@Override
public void onClick(View v) {
switch (v.getId()){
case R.id.bt_insert://添加
username = et_username.getText().toString();
password = et_password.getText().toString();
db = myHelper.getReadableDatabase();//获取可读的数据库对象
values = new ContentValues();
values.put("username",username);
values.put("password",password);
db.insert("information",null,values);
db.close();
Toast.makeText(MainActivity.this,"插入成功",Toast.LENGTH_LONG).show();
break;
case R.id.bt_query://查询
db = myHelper.getReadableDatabase();//获取可读的数据库对象
Cursor cursor = db.query("information", null, null, null, null, null, null);//返回一个结果集
if (cursor.getCount()==0){
//没有查到数据
Toast.makeText(MainActivity.this,"没有查到数据",Toast.LENGTH_LONG).show();
showText.setText("没有查到数据");
}else{
cursor.moveToFirst();
Toast.makeText(MainActivity.this,cursor.getString(1),Toast.LENGTH_LONG).show();
showText.setText("Username:"+cursor.getString(1)+",Password:"+cursor.getString(2));//每行数据有两列,分别为username和password
while (cursor.moveToNext()){
showText.append("\n"+"Username:"+cursor.getString(1)+",Password:"+cursor.getString(2));
}
}
db.close();//用完了要关闭
break;
case R.id.bt_update://更新
username = et_username.getText().toString();
db = myHelper.getReadableDatabase();//获取可读的数据库对象
values = new ContentValues();
values.put("password","987654");//要更新的字段和内容
db.update("information",values,"username = ?",new String[]{username});
db.close();
break;
case R.id.bt_delete://删除
db = myHelper.getReadableDatabase();
db.delete("information",null,null);//删除所有
Toast.makeText(MainActivity.this,"删除成功",Toast.LENGTH_LONG).show();
showText.setText("无数据");
db.close();
break;
}
}
}
4、运行测试