1.简单创建一个数据库和删除一个数据库
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/btn_database_create"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="创建数据库"
android:textColor="@color/black"
android:textSize="17sp" />
<Button
android:id="@+id/btn_database_delete"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="删除数据库"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>
<TextView
android:id="@+id/tv_database"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:paddingLeft="5dp"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>
package com.tiger.chapter06;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;
public class DatabaseActivity extends AppCompatActivity implements View.OnClickListener {
private String mDatabaseName;
private TextView tv_database;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_database);
findViewById(R.id.btn_database_create).setOnClickListener(this);
findViewById(R.id.btn_database_delete).setOnClickListener(this);
tv_database = findViewById(R.id.tv_database);
//生成一个测试数据库的完整路径
mDatabaseName = getFilesDir() + "/test.db";
}
@Override
public void onClick(View v) {
if (R.id.btn_database_create == v.getId()) {
//创建或打开数据库。数据库如果不存在就创建它,如果存在就打开它
//上下文就是可以获取 公共的东西 的对象 ，达成共识的
/**
* 1. 数据库创建路径
* 2. 设置为私有的 外界应用访问不了
* 3. 游标工厂 做数据库查询的
*/
SQLiteDatabase db = openOrCreateDatabase(mDatabaseName, Context.MODE_PRIVATE, null);
String desc = String.format("数据库%s创建%s", db.getPath(), (db != null) ? "成功" : "失败");
tv_database.setText(desc);
} else {
//删除数据库
//把路径传进去
boolean b = deleteDatabase(mDatabaseName);
String desc = String.format("数据库%s删除%s",mDatabaseName, b ? "成功" : "失败");
tv_database.setText(desc);
}
}
}2.SQLiteOpenHelper
1.封装类
package com.tiger.chapter06.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
import com.tiger.chapter06.entity.User;
import java.util.ArrayList;
import java.util.List;
public class UserDBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "user.db";
private static final int DB_VERSION = 1;
private static final String TABLE_NAME = "user_info";
private static UserDBHelper mHelper = null;
private SQLiteDatabase mRDB = null;
private SQLiteDatabase mWDB = null;
private UserDBHelper(@Nullable Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
//利用单例模式获取数据库帮助其的唯一实例 没有那么大的并发量 所以不用加锁
public static UserDBHelper getInstance(Context context) {
if (mHelper == null) {
mHelper = new UserDBHelper(context);
}
return mHelper;
}
//打开数据库的读连接
public SQLiteDatabase openReadLink() {
if (mRDB == null || !mRDB.isOpen()) {
mRDB = mHelper.getReadableDatabase();
}
return mRDB;
}
//打开数据库的写连接
public SQLiteDatabase openWriteLink() {
if (mWDB == null || !mWDB.isOpen()) {
mWDB = mHelper.getWritableDatabase();
}
return mWDB;
}
//关闭数据库连接
public void closeLink() {
if (mRDB != null && mRDB.isOpen()) {
mRDB.close();
mRDB = null;
}
if (mWDB != null && mWDB.isOpen()) {
mWDB.close();
mWDB = null;
}
}
//创建数据库,执行建表语句
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
" name VARCHAR NOT NULL," +
" age INTEGER NOT NULL," +
" height LONG NOT NULL," +
" weight FLOAT NOT NULL," +
" married INTEGER NOT NULL);";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public long insert(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
// 执行插入记录动作，该语句返回插入记录的行号
// 如果第三个参数values 为Null或者元素个数为0， 由于insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录，
// 为了满足SQL语法的需要， insert语句必须给定一个字段名 ，如：insert into person(name) values(NULL)，
// 倘若不给定字段名 ， insert语句就成了这样： insert into person() values()，显然这不满足标准SQL的语法。
// 如果第三个参数values 不为Null并且元素的个数大于0 ，可以把第二个参数设置为null 。
long result = mWDB.insert(TABLE_NAME, null, values);
return result;
}
public long deleteByName(String name) {
//删除所有
// mWDB.delete(TABLE_NAME,"1=1",null);
//返回影响的行数
return mWDB.delete(TABLE_NAME, "name=?", new String[]{name});
}
public long update(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
//返回被影响的行数
long result = mWDB.update(TABLE_NAME, values, "name=?", new String[]{user.name});
return result;
}
public List<User> queryAll() {
List<User> list = new ArrayList<>();
//执行记录查询 ，该语句返回结果集游标
Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null, null);
//循环取出游标指向的每条记录
while (cursor.moveToNext()){
User user = new User();
user.id = cursor.getInt(0);
user.name = cursor.getString(1);
user.age = cursor.getInt(2);
user.height = cursor.getLong(3);
user.weight = cursor.getFloat(4);
//SQLite没有布尔型，用0表示false，用1表示true
user.married = (cursor.getInt(5) == 0) ? false : true;
list.add(user);
}
return list;
}
public List<User> queryByName(String name) {
List<User> list = new ArrayList<>();
//执行记录查询 ，该语句返回结果集游标
Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?", new String[]{name}, null, null, null, null);
//循环取出游标指向的每条记录
while (cursor.moveToNext()){
User user = new User();
user.id = cursor.getInt(0);
user.name = cursor.getString(1);
user.age = cursor.getInt(2);
user.height = cursor.getLong(3);
user.weight = cursor.getFloat(4);
//SQLite没有布尔型，用0表示false，用1表示true
user.married = (cursor.getInt(5) == 0) ? false : true;
list.add(user);
}
return list;
}
}
2.User实体类
package com.tiger.chapter06.entity;
public class User {
public int id; // 序号
public String name; // 姓名
public int age; // 年龄
public long height; // 身高
public float weight; // 体重
public boolean married; // 婚否
public User(){
}
public User(String name, int age, long height, float weight, boolean married) {
this.name = name;
this.age = age;
this.height = height;
this.weight = weight;
this.married = married;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", height=" + height +
", weight=" + weight +
", married=" + married +
'}';
}
}
3.Activity
package com.tiger.chapter06;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.CheckBox;
import android.widget.EditText;
import com.tiger.chapter06.database.UserDBHelper;
import com.tiger.chapter06.entity.User;
import com.tiger.chapter06.utils.ToastUtlis;
import java.util.List;
public class SQLiteHelperActivity extends AppCompatActivity implements View.OnClickListener {
private EditText et_name;
private EditText et_age;
private EditText et_height;
private EditText et_weight;
private CheckBox ck_married;
private UserDBHelper mHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite_helper);
et_name = findViewById(R.id.et_name);
et_age = findViewById(R.id.et_age);
et_height = findViewById(R.id.et_height);
et_weight = findViewById(R.id.et_weight);
ck_married = findViewById(R.id.ck_married);
findViewById(R.id.btn_save).setOnClickListener(this);
findViewById(R.id.btn_delete).setOnClickListener(this);
findViewById(R.id.btn_update).setOnClickListener(this);
findViewById(R.id.btn_query).setOnClickListener(this);
}
@Override
protected void onStart() {
super.onStart();
mHelper = UserDBHelper.getInstance(this);
//打开数据库帮助器的读写连接
mHelper.openReadLink();
mHelper.openWriteLink();
}
@Override
protected void onStop() {
super.onStop();
mHelper.closeLink();
}
@Override
public void onClick(View v) {
String name = et_name.getText().toString();
String age = et_age.getText().toString();
String height = et_height.getText().toString();
String weight = et_weight.getText().toString();
User user = null;
if (v.getId() == R.id.btn_save){
// 以下声明一个用户信息对象，并填写它的各字段值
user = new User(name,
Integer.parseInt(age),
Long.parseLong(height),
Float.parseFloat(weight),
ck_married.isChecked());
if (mHelper.insert(user) > 0) {
ToastUtlis.show(this, "添加成功");
}
}else if (v.getId() == R.id.btn_delete){
if (mHelper.deleteByName(name) > 0) {
ToastUtlis.show(this, "删除成功");
}
}else if (v.getId() == R.id.btn_update){
user = new User(name,
Integer.parseInt(age),
Long.parseLong(height),
Float.parseFloat(weight),
ck_married.isChecked());
if (mHelper.update(user) > 0) {
ToastUtlis.show(this, "修改成功");
}
}else if (v.getId() == R.id.btn_query){
List<User> list = mHelper.queryByName(name);
list.stream().forEach(u->{
Log.d("JMJ",u.toString());
});
}
}
}4.布局
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="5dp">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="40dp"
android:orientation="horizontal">
<TextView
android:id="@+id/tv_name"
android:layout_width="wrap_content"
android:layout_height="match_parent"
android:gravity="center"
android:text="姓名："
android:textColor="@color/black"
android:textSize="17sp" />
<EditText
android:id="@+id/et_name"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_marginTop="3dp"
android:layout_marginBottom="3dp"
android:layout_weight="1"
android:background="@drawable/edit_select"
android:hint="请输入姓名"
android:inputType="text"
android:maxLength="12"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="40dp"
android:orientation="horizontal">
<TextView
android:id="@+id/tv_age"
android:layout_width="wrap_content"
android:layout_height="match_parent"
android:gravity="center"
android:text="年龄："
android:textColor="@color/black"
android:textSize="17sp" />
<EditText
android:id="@+id/et_age"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_marginTop="3dp"
android:layout_marginBottom="3dp"
android:layout_weight="1"
android:background="@drawable/edit_select"
android:hint="请输入年龄"
android:inputType="number"
android:maxLength="2"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="40dp"
android:orientation="horizontal">
<TextView
android:id="@+id/tv_height"
android:layout_width="wrap_content"
android:layout_height="match_parent"
android:gravity="center"
android:text="身高："
android:textColor="@color/black"
android:textSize="17sp" />
<EditText
android:id="@+id/et_height"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_marginTop="3dp"
android:layout_marginBottom="3dp"
android:layout_weight="1"
android:background="@drawable/edit_select"
android:hint="请输入身高"
android:inputType="numberDecimal"
android:maxLength="5"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="40dp"
android:orientation="horizontal">
<TextView
android:id="@+id/tv_weight"
android:layout_width="wrap_content"
android:layout_height="match_parent"
android:gravity="center"
android:text="体重："
android:textColor="@color/black"
android:textSize="17sp" />
<EditText
android:id="@+id/et_weight"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_marginTop="3dp"
android:layout_marginBottom="3dp"
android:layout_weight="1"
android:background="@drawable/edit_select"
android:hint="请输入体重"
android:inputType="numberDecimal"
android:maxLength="5"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>
<CheckBox
android:id="@+id/ck_married"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:checked="false"
android:gravity="center"
android:text="已婚"
android:textColor="@color/black"
android:textSize="17sp" />
<Button
android:id="@+id/btn_save"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="添加"
android:textColor="@color/black"
android:textSize="17sp" />
<Button
android:id="@+id/btn_delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除"
android:textColor="@color/black"
android:textSize="17sp" />
<Button
android:id="@+id/btn_update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="修改"
android:textColor="@color/black"
android:textSize="17sp" />
<Button
android:id="@+id/btn_query"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询"
android:textColor="@color/black"
android:textSize="17sp" />
</LinearLayout>3.事务管理
public long insert(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
// 执行插入记录动作，该语句返回插入记录的行号
// 如果第三个参数values 为Null或者元素个数为0， 由于insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录，
// 为了满足SQL语法的需要， insert语句必须给定一个字段名 ，如：insert into person(name) values(NULL)，
// 倘若不给定字段名 ， insert语句就成了这样： insert into person() values()，显然这不满足标准SQL的语法。
// 如果第三个参数values 不为Null并且元素的个数大于0 ，可以把第二个参数设置为null 。
// long result = mWDB.insert(TABLE_NAME, null, values);
try {
mWDB.beginTransaction();
mWDB.insert(TABLE_NAME,null,values);
// int i =1/0;
mWDB.insert(TABLE_NAME,null,values);
mWDB.setTransactionSuccessful();//成功就提交
}catch (Exception e){
e.printStackTrace();
}finally {
mWDB.endTransaction();//关闭事务 如果没有提交的话就回滚
}
return 1;
}
4. 数据库版本升级
package com.tiger.chapter06.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import androidx.annotation.Nullable;
import com.tiger.chapter06.entity.User;
import java.util.ArrayList;
import java.util.List;
public class UserDBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "user.db";
private static final int DB_VERSION = 2;
private static final String TABLE_NAME = "user_info";
private static UserDBHelper mHelper = null;
private SQLiteDatabase mRDB = null;
private SQLiteDatabase mWDB = null;
private UserDBHelper(@Nullable Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
//利用单例模式获取数据库帮助其的唯一实例 没有那么大的并发量 所以不用加锁
public static UserDBHelper getInstance(Context context) {
if (mHelper == null) {
mHelper = new UserDBHelper(context);
}
return mHelper;
}
//打开数据库的读连接
public SQLiteDatabase openReadLink() {
if (mRDB == null || !mRDB.isOpen()) {
mRDB = mHelper.getReadableDatabase();
}
return mRDB;
}
//打开数据库的写连接
public SQLiteDatabase openWriteLink() {
if (mWDB == null || !mWDB.isOpen()) {
mWDB = mHelper.getWritableDatabase();
}
return mWDB;
}
//关闭数据库连接
public void closeLink() {
if (mRDB != null && mRDB.isOpen()) {
mRDB.close();
mRDB = null;
}
if (mWDB != null && mWDB.isOpen()) {
mWDB.close();
mWDB = null;
}
}
//创建数据库,执行建表语句
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
" name VARCHAR NOT NULL," +
" age INTEGER NOT NULL," +
" height LONG NOT NULL," +
" weight FLOAT NOT NULL," +
" married INTEGER NOT NULL);";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//版本发送改变的时候会执行里面的方法
Log.d("jmj",oldVersion+"");
Log.d("jmj",newVersion+"");
String sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN phone VARCHAR;";
db.execSQL(sql);
sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN password VARCHAR;";
db.execSQL(sql);
}
public long insert(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
// 执行插入记录动作，该语句返回插入记录的行号
// 如果第三个参数values 为Null或者元素个数为0， 由于insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录，
// 为了满足SQL语法的需要， insert语句必须给定一个字段名 ，如：insert into person(name) values(NULL)，
// 倘若不给定字段名 ， insert语句就成了这样： insert into person() values()，显然这不满足标准SQL的语法。
// 如果第三个参数values 不为Null并且元素的个数大于0 ，可以把第二个参数设置为null 。
// long result = mWDB.insert(TABLE_NAME, null, values);
try {
mWDB.beginTransaction();
mWDB.insert(TABLE_NAME,null,values);
// int i =1/0;
mWDB.insert(TABLE_NAME,null,values);
mWDB.setTransactionSuccessful();//成功就提交
}catch (Exception e){
e.printStackTrace();
}finally {
mWDB.endTransaction();//关闭事务 如果没有提交的话就回滚
}
return 1;
}
public long deleteByName(String name) {
//删除所有
// mWDB.delete(TABLE_NAME,"1=1",null);
//返回影响的行数
return mWDB.delete(TABLE_NAME, "name=?", new String[]{name});
}
public long update(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
//返回被影响的行数
long result = mWDB.update(TABLE_NAME, values, "name=?", new String[]{user.name});
return result;
}
public List<User> queryAll() {
List<User> list = new ArrayList<>();
//执行记录查询 ，该语句返回结果集游标
Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null, null);
//循环取出游标指向的每条记录
while (cursor.moveToNext()){
User user = new User();
user.id = cursor.getInt(0);
user.name = cursor.getString(1);
user.age = cursor.getInt(2);
user.height = cursor.getLong(3);
user.weight = cursor.getFloat(4);
//SQLite没有布尔型，用0表示false，用1表示true
user.married = (cursor.getInt(5) == 0) ? false : true;
list.add(user);
}
return list;
}
public List<User> queryByName(String name) {
List<User> list = new ArrayList<>();
//执行记录查询 ，该语句返回结果集游标
Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?", new String[]{name}, null, null, null, null);
//循环取出游标指向的每条记录
while (cursor.moveToNext()){
User user = new User();
user.id = cursor.getInt(0);
user.name = cursor.getString(1);
user.age = cursor.getInt(2);
user.height = cursor.getLong(3);
user.weight = cursor.getFloat(4);
//SQLite没有布尔型，用0表示false，用1表示true
user.married = (cursor.getInt(5) == 0) ? false : true;
list.add(user);
}
return list;
}
}
