一、创建数据表
public class SQLiteUtils extends SQLiteOpenHelper {
//user数据表
public static final String TB_USER = "user";
public SQLiteUtils(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* 数据库创建
*/
@Override
public void onCreate(SQLiteDatabase db) {
//创建user表
String person = "create table IF NOT EXISTS "+TB_USER+"(username varchar(12),password varchar(18));";
db.execSQL(person);
}
/**
* 数据库升级,可以不加任何语句
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// db.execSQL("DROP TABLE IF EXISTS " + TB_USER);
// onCreate(db);
}
}
二、对数据表进行增删改查
sqlite数据库添加数据的方法:
public long insert(String table, String nullColumnHack, ContentValues values);
table表名nullColumnHack可选参数。当参数values为空时,可指定字段设置为null值;当参数values不为空时,一般设置为null值。values要插入的字段,以键值对形式存储返回值返回插入成功的行数,若插入失败返回值为-1
//数据库名称
private static final String DB_NAME = "test";
/**
* 添加数据:添加成功返回true,添加失败返回false
*/
public Boolean dataAdd(Context context, String username,String password) {
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("username", username);
values.put("password", password);
long insert = database.insert(SQLiteUtils.TB_USER, null, values);
return insert != (-1);
}
sqlite数据库删除数据的方法:
public int delete(String table, String whereClause, String[] whereArgs);
table表名whereClause条件语句,要指定的参数使用占位符’?’whereArgs条件语句中占位符’?’的值返回值删除的行数,删除失败返回值为0
/**
*删除数据:删除成功返回true,删除失败返回false
*/
public Boolean dataDrop(Context context,String username){
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
int delete = database.delete(SQLiteUtils.TB_USER, "username = ?", new String[]{username});
return delete >= 1;
}
sqlite数据库查询数据的方法:
public Cursor query(String table, String[] columns, String selection,
String[] selectionArgs, String groupBy, String having,
String orderBy)
table表名columns要查询的字段,若设置为null值则返回所有字段selection查询条件,要指定的参数使用占位符’?’;若无查询条件,为null值selectionArgs查询条件语句中占位符’?’的值;若无查询条件,为null值groupBy指定分组方式;若无分组,为null值having指定having条件;若无条件,为null值oederBy指定排序方式;若无排序方式,为null值返回值查询结果集
/**
*查询数据
*/
public List dataQuery(Context context){
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
Cursor cursor = database.query(SQLiteUtils.TB_USER, null, null, null, null, null,null);
cursor.moveToFirst();
List user = new ArrayList<>();
while (!cursor.isAfterLast()){
String username = cursor.getString(0);
String password = cursor.getString(1);
user.add(new User(username,password));
cursor.moveToNext();
}
cursor.close();
return user;
}
sqlite数据库更新数据的方法:
public int update(String table, ContentValues values, String whereClause, String[] whereArgs);
table
表名values要更新的字段及字段值whereClause条件语句,要指定的参数使用占位符’?’whereArgs条件语句中占位符’?’的值返回值更新影响的数据条数,无更新返回0
/**
*更新数据:修改成功返回true,修改失败返回false
*/
public Boolean dataUpdate(Context context,String username,String password){
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("password",password);
int update = database.update(SQLiteUtils.TB_USER, values, "username = ?", new String[]{username});
return update != 0;
}
三、实例
User.java
public class User {
private String username;
private String password;
public User() {
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
SQLiteUtils.java
public class SQLiteUtils extends SQLiteOpenHelper {
//user数据表
public static final String TB_USER = "user";
public SQLiteUtils(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* 数据库创建
*/
@Override
public void onCreate(SQLiteDatabase db) {
//创建user表
String person = "create table IF NOT EXISTS "+TB_USER+"(username varchar(12),password varchar(18));";
db.execSQL(person);
}
/**
* 数据库升级
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TB_USER);
onCreate(db);
}
}
Operation.java
public class Operation {
//数据库名称
private static final String DB_NAME = "test";
/**
* 添加数据:添加成功返回true,添加失败返回false
*/
public Boolean dataAdd(Context context, String username,String password) {
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("username", username);
values.put("password", password);
long insert = database.insert(SQLiteUtils.TB_USER, null, values);
return insert != (-1);
}
/**
*删除数据:删除成功返回true,删除失败返回false
*/
public Boolean dataDrop(Context context,String username){
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
int delete = database.delete(SQLiteUtils.TB_USER, "username = ?", new String[]{username});
return delete >= 1;
}
/**
*查询数据
*/
public List<user> dataQuery(Context context){
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
Cursor cursor = database.query(SQLiteUtils.TB_USER, null, null, null, null, null,null);
cursor.moveToFirst();
List<user> user = new ArrayList<>();
while (!cursor.isAfterLast()){
String username = cursor.getString(0);
String password = cursor.getString(1);
user.add(new User(username,password));
cursor.moveToNext();
}
cursor.close();
return user;
}
/**
*更新数据:修改成功返回true,修改失败返回false
*/
public Boolean dataUpdate(Context context,String username,String password){
SQLiteUtils utils = new SQLiteUtils(context, DB_NAME, null, 1);
SQLiteDatabase database = utils.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("password",password);
int update = database.update(SQLiteUtils.TB_USER, values, "username = ?", new String[]{username});
return update != 0;
}
}
</user></user>
MainActivity.java
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Intent_Onclick();
}
private void Intent_Onclick() {
LinearLayout LayoutAdd = findViewById(R.id.view_add);
LinearLayout LayoutQuery = findViewById(R.id.view_query);
Button mBtnViewAdd = findViewById(R.id.add);
Button mBtnViewQuery = findViewById(R.id.query);
Button mBtnAdd = findViewById(R.id.add_add);
Button mBtnDrop = findViewById(R.id.add_drop);
Button mBtnUpdate = findViewById(R.id.add_update);
mBtnViewAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
LayoutAdd.setVisibility(View.VISIBLE);
LayoutQuery.setVisibility(View.GONE);
}
});
mBtnViewQuery.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
LayoutAdd.setVisibility(View.GONE);
LayoutQuery.setVisibility(View.VISIBLE);
query();
}
});
mBtnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
add();
}
});
mBtnDrop.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
drop();
}
});
mBtnUpdate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
update();
}
});
}
private void add() {
EditText mEtUsername = findViewById(R.id.add_username);
EditText mEtPassword = findViewById(R.id.add_password);
String username = mEtUsername.getText().toString();
String password = mEtPassword.getText().toString();
Operation operation = new Operation();
Boolean flag = operation.dataAdd(MainActivity.this, username, password);
if (flag) {
Toast.makeText(MainActivity.this, "添加成功", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(MainActivity.this, "添加失败", Toast.LENGTH_SHORT).show();
}
}
private void drop() {
EditText mEtUsername = findViewById(R.id.add_username);
String username = mEtUsername.getText().toString();
Operation operation = new Operation();
Boolean flag = operation.dataDrop(MainActivity.this, username);
if (flag) {
Toast.makeText(MainActivity.this, "删除成功", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(MainActivity.this, "删除失败", Toast.LENGTH_SHORT).show();
}
}
private void query() {
Operation operation = new Operation();
List users = null;
users = operation.dataQuery(MainActivity.this);
LinearLayout LayoutUsername = findViewById(R.id.query_username);
LinearLayout LayoutPassword = findViewById(R.id.query_password);
for (int i = 0; i < users.size(); i++) {
EditText username = new EditText(this);
username.setText(users.get(i).getUsername());
username.setGravity(View.TEXT_ALIGNMENT_CENTER);
username.setLayoutParams(new ViewGroup.LayoutParams(LinearLayout.LayoutParams.MATCH_PARENT, LinearLayout.LayoutParams.MATCH_PARENT));
LayoutUsername.addView(username);
EditText password = new EditText(this);
password.setText(users.get(i).getPassword());
password.setGravity(View.TEXT_ALIGNMENT_CENTER);
password.setLayoutParams(new ViewGroup.LayoutParams(LinearLayout.LayoutParams.MATCH_PARENT, LinearLayout.LayoutParams.MATCH_PARENT));
LayoutPassword.addView(password);
}
}
private void update() {
EditText mEtUsername = findViewById(R.id.add_username);
EditText mEtPassword = findViewById(R.id.add_password);
String username = mEtUsername.getText().toString();
String password = mEtPassword.getText().toString();
Operation operation = new Operation();
Boolean flag = operation.dataUpdate(MainActivity.this, username, password);
if (flag) {
Toast.makeText(MainActivity.this, "更新成功", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(MainActivity.this, "更新失败", Toast.LENGTH_SHORT).show();
}
}
}
activity_main.xml
Original: https://blog.csdn.net/m0_54130475/article/details/125540324
Author: 爱笑的小李子
Title: Android 操作本地 SQLite数据库
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/815247/
转载文章受原作者版权保护。转载请注明原作者出处!