Android Sqlite简单使用

最新推荐文章于 2024-10-10 15:40:58 发布

lishuqi_

最新推荐文章于 2024-10-10 15:40:58 发布

阅读量1.1k

点赞数 1

文章标签： Android Sqlite简单使用

本文链接：https://blog.csdn.net/lishuqi_/article/details/80391433

版权

1.新建一个SqlDBHelper类继承SQLiteOpenHelper用来创建数据库

public class SqlDBHelper extends SQLiteOpenHelper {

// 数据库名字
private static String name = "lsqTest.db";
// 版本
private static int version = 1;

//表名
public static final String TABLE_NAME_USER = "User";
public static final String TABLE_NAME_ORDE = "Order";

/**
* 建数据库
*/
public SqlDBHelper(Context context) {
super(context, name, null, version);
}

/**
* 建表
*/
@Override
public void onCreate(SQLiteDatabase db) {
//Id integer primary autoincrement key id设置为主键自增长
String sql_user = "create table if not exists " + TABLE_NAME_USER + " (Id integer primary autoincrement key, Name text, Age integer)";
db.execSQL(sql_user);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//根据version的改变升级数据库
}

}

2.创建一个实体类UserBean

public class UserBean {
private int id;
private String name;
private int age;

public UserBean() {
}

public UserBean(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}

public UserBean(String name, int age) {
this.age = age;
this.name = name;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

}

3.创建一个管理类UserDao，这里就用来操作数据库（增删改查）

public class UserDao {
private static final String TAG = "UserDao";
// 列定义
private final String[] USER_COLUMNS = new String[]{"Id", "Name", "Age"};

private Context context;
private SqlDBHelper sqlDBHelper;

public UserDao(Context context) {
this.context = context;
sqlDBHelper = new SqlDBHelper(context);
}

/**
* 判断表中是否有数据
*/
public boolean isDataExist() {
int count = 0;

SQLiteDatabase db = null;
Cursor cursor = null;

try {
db = sqlDBHelper.getWritableDatabase();
// select count(Id) from Orders
cursor = db.query(SqlDBHelper.TABLE_NAME_USER, new String[]{"COUNT(Id)"}, null, null, null, null, null);
db.setTransactionSuccessful();
if (cursor.moveToFirst()) {
count = cursor.getInt(0);
}
if (count > 0) return true;
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}

/**
* 初始化数据
*/
public void initTable() {
SQLiteDatabase db = null;
try {
db = sqlDBHelper.getWritableDatabase();
db.beginTransaction();
db.execSQL("insert into " + SqlDBHelper.TABLE_NAME_USER + " (Name, Age) values ( 'Lsq', 20)");
db.setTransactionSuccessful();
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
}

/**
* 添加用户数据
*/
public boolean addUser(UserBean userBean) {
SQLiteDatabase db = null;
try {
db = sqlDBHelper.getWritableDatabase();
db.beginTransaction();
ContentValues contentValues = new ContentValues();
contentValues.put("Name", userBean.getName());
contentValues.put("Age", userBean.getAge());
db.insertOrThrow(SqlDBHelper.TABLE_NAME_USER, null, contentValues);
return true;
} catch (SQLiteConstraintException e) {
Toast.makeText(context, "主键重复", Toast.LENGTH_SHORT).show();
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}

/**
* 删除用户数据
* 根据id删除
*/
public boolean deleteUser(int id) {
SQLiteDatabase db = null;
try {
db = sqlDBHelper.getWritableDatabase();
db.beginTransaction();
db.delete(SqlDBHelper.TABLE_NAME_USER, "Id = ?", new String[]{String.valueOf(id)});
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}

/**
* 改变用户的数据
*/
public boolean changeUser(UserBean userBean) {
SQLiteDatabase db = null;
try {
db = sqlDBHelper.getWritableDatabase();
db.beginTransaction();
ContentValues contentValues = new ContentValues();
contentValues.put("Name", userBean.getName());
contentValues.put("Age", userBean.getAge());
db.update(SqlDBHelper.TABLE_NAME_USER, contentValues, "id=?", new String[]{String.valueOf(userBean.getId())});
return true;
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}

/**
* 根据id查找用户的数据
*/
public UserBean SearchUser(int id) {
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = sqlDBHelper.getReadableDatabase();
db.beginTransaction();
cursor = db.query(SqlDBHelper.TABLE_NAME_USER, new String[]{"id"}, "id=?", new String[]{String.valueOf(id)}, null, null, null);
db.setTransactionSuccessful();
if (cursor.getCount() > 0) {
UserBean userBean = parseUser(cursor);
return userBean;
}
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.endTransaction();
db.close();
}
}
return null;
}

/**
* 查找所有用户数据
*/
public List<UserBean> searchAllUser() {
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = sqlDBHelper.getReadableDatabase();
db.beginTransaction();
cursor = db.query(SqlDBHelper.TABLE_NAME_USER, USER_COLUMNS, "id=?", null, null, null, null);
db.setTransactionSuccessful();
if (cursor.getCount() > 0) {
List<UserBean> list = new ArrayList<>();
while (cursor.moveToNext()) {
list.add(parseUser(cursor));
}
return list;
}
} catch (Exception e) {

} finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.endTransaction();
db.close();
}
}
return null;
}

/**
* 将查找到的数据转换成Order类
*/
private UserBean parseUser(Cursor cursor) {
UserBean userBean = new UserBean();
userBean.setId(cursor.getInt(cursor.getColumnIndex("Id")));
userBean.setName(cursor.getString(cursor.getColumnIndex("Name")));
userBean.setAge(cursor.getInt(cursor.getColumnIndex("Age")));
return userBean;