|
目录
一、创建数据表
二、对数据表进行增删改查
1、增加数据
2、删除数据
3、查询数据
4、更新数据
三、实例
一、创建数据表
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);
}
}
二、对数据表进行增删改查
1、增加数据
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);
}
2、删除数据
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;
}
3、查询数据
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<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;
}
4、更新数据
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;
}
}
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<User> 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
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/add"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="添加" />
<Button
android:id="@+id/query"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="查询" />
</LinearLayout>
<!-- 添加页面-->
<LinearLayout
android:id="@+id/view_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:layout_marginTop="40dp"
android:orientation="vertical"
android:visibility="visible">
<!-- 用户名-->
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<TextView
android:layout_width="70dp"
android:layout_height="wrap_content"
android:gravity="center"
android:text="用户名:" />
<EditText
android:id="@+id/add_username"
android:layout_width="200dp"
android:layout_height="40dp"
android:background="@drawable/edit"
android:gravity="center"
android:maxLines="1"
android:singleLine="true" />
</LinearLayout>
<!-- 密码-->
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<TextView
android:layout_width="70dp"
android:layout_height="wrap_content"
android:gravity="center"
android:text="密码:" />
<EditText
android:id="@+id/add_password"
android:layout_width="200dp"
android:layout_height="40dp"
android:background="@drawable/edit"
android:gravity="center"
android:maxLines="1"
android:singleLine="true" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/add_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:layout_marginLeft="20dp"
android:text="添加" />
<Button
android:id="@+id/add_update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:layout_marginLeft="20dp"
android:text="更改密码" />
<Button
android:id="@+id/add_drop"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:layout_marginLeft="20dp"
android:text="删除" />
</LinearLayout>
</LinearLayout>
<!-- 查询页面-->
<LinearLayout
android:id="@+id/view_query"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_gravity="center"
android:layout_marginTop="40dp"
android:orientation="vertical"
android:visibility="gone">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:gravity="center"
android:orientation="horizontal">
<LinearLayout
android:layout_width="0dp"
android:layout_gravity="center"
android:gravity="center"
android:layout_height="wrap_content"
android:layout_weight="1">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:gravity="center"
android:text="用户名" />
</LinearLayout>
<LinearLayout
android:layout_width="0dp"
android:layout_gravity="center"
android:gravity="center"
android:layout_height="wrap_content"
android:layout_weight="1">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:gravity="center"
android:text="密码" />
</LinearLayout>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:gravity="center"
android:orientation="horizontal">
<LinearLayout
android:id="@+id/query_username"
android:layout_width="0dp"
android:layout_gravity="center"
android:gravity="center"
android:orientation="vertical"
android:layout_height="wrap_content"
android:layout_weight="1">
</LinearLayout>
<LinearLayout
android:id="@+id/query_password"
android:layout_width="0dp"
android:layout_gravity="center"
android:gravity="center"
android:orientation="vertical"
android:layout_height="wrap_content"
android:layout_weight="1">
</LinearLayout>
</LinearLayout>
</LinearLayout>
</LinearLayout>
|