一、数据库的基本操作
数据库的创建方法:
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 select(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();
}
三、数据库语句增、删、改、查的方法。
在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;
}
}
}