SQLite的AI简介:
SQLite是一种流行的开源、轻量级、自包含的关系型数据库管理系统。它被广泛应用于各种应用程序和设备中,因为它简单、可靠且高效。SQLite的一些关键特点包括:
-
自包含性:SQLite是无服务器的,意味着它不需要单独的服务器进程来运行。整个数据库存储在单个磁盘文件中,易于管理和部署。
-
零配置:SQLite不需要任何复杂的设置或配置。它可以立即使用,适用于小型项目或嵌入式系统。
-
跨平台:SQLite兼容各种操作系统,包括Windows、macOS、Linux以及移动平台如Android和iOS。这种跨平台支持使其适用于不同的环境。
-
事务支持:SQLite支持ACID(原子性、一致性、隔离性、持久性)事务,确保数据的完整性和可靠性。
-
占用空间小:SQLite轻量级且占用内存空间小,适用于嵌入式系统、移动应用程序和其他资源受限的环境。
-
开源:SQLite是开源的,可以免费使用,允许开发人员根据公共领域的条款进行修改和分发。
开始练习
实现一个简单的注册登录流程-效果如图:
a、在build.gradle中添加SQLlite依赖库
implementation 'androidx.sqlite:sqlite:2.2.0'
b、继承SQLiteOpenHelper类创建库文件及数据表
onCreate方法只有在第一次创建数据库的时候才会被调用,后续表的操作可在onUpgrade中执行,也可以在Device file Explorer中找到db文件然后删了在执行会重新创建
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "my_database.db";
public static final int DB_VERSION = 2;
public static final String TABLE_NAME = "my_user";
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createTableSql = "CREATE TABLE " + TABLE_NAME + " (" +
"_id TEXT PRIMARY KEY," +
"name TEXT," +
"password TEXT" +
");";
db.execSQL(createTableSql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//版本升级-版本2增加字段
if(newVersion==2){
String alterTableQuery = "ALTER TABLE "+TABLE_NAME+" ADD COLUMN status INTEGER(2) NOT NULL DEFAULT 0";
db.execSQL(alterTableQuery);
}
}
}
b、搞一个简单交互界面
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:gravity="center"
tools:context=".sqlHeaper.OperateDataBaseActivity">
<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="用户:"></TextView>
<EditText
android:id="@+id/editTextTextName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="text" />
</LinearLayout>
<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="密码:"></TextView>
<EditText
android:id="@+id/editTextTextPassword"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="textPassword" />
</LinearLayout>
<Button
android:id="@+id/login"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="用户登录"
tools:layout_editor_absoluteX="127dp"
tools:layout_editor_absoluteY="100dp" />
<Button
android:id="@+id/button5"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="用户注册"
tools:layout_editor_absoluteX="127dp"
tools:layout_editor_absoluteY="217dp" />
<Button
android:id="@+id/button7"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改密码" />
<Button
android:id="@+id/button8"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="用户注销" />
<Button
android:id="@+id/button6"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="获取用户列表"
tools:layout_editor_absoluteX="127dp"
tools:layout_editor_absoluteY="100dp" />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="20dp"
android:textColor="#000"
android:gravity="center"
android:text="" />
</LinearLayout>
d、SQLite增删改查实现注册登录
public class OperateDataBaseActivity extends AppCompatActivity {
private Button button5;
private Button button6;
private Button button7;
private Button button8;
private Button login;
private EditText userName;
private EditText password;
private TextView textView;
private Context myContext;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_operate_data_base);
button5=(Button)findViewById(R.id.button5);
button6=(Button)findViewById(R.id.button6);
button7=(Button)findViewById(R.id.button7);
button8=(Button)findViewById(R.id.button8);
textView=(TextView)findViewById(R.id.textView2);
userName=(EditText)findViewById(R.id.editTextTextName);
password=(EditText)findViewById(R.id.editTextTextPassword);
login=(Button)findViewById(R.id.login);
DatabaseHelper dbHelper = new DatabaseHelper(this);
myContext=this;
button5.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String name = userName.getText().toString();
String userPassword = password.getText().toString();
if("".equals(name)||name==null||"".equals("userPassword")||userPassword==null){
Toast.makeText(myContext,"用户名和密码不能为空",Toast.LENGTH_SHORT).show();
return;
}
// 插入数据
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("_id", UUID.randomUUID().toString());
values.put("name", name);
values.put("password", userPassword);
Long row=db.insert(DatabaseHelper.TABLE_NAME, null, values);
System.out.println(row);
db.close();
}
});
button6.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
// 查询数据
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.query(DatabaseHelper.TABLE_NAME, null, null, null, null, null, null);
String result="";
while (cursor.moveToNext()) {
@SuppressLint("Range")
String id = cursor.getString(cursor.getColumnIndex("_id"));
@SuppressLint("Range")
String name = cursor.getString(cursor.getColumnIndex("name"));
@SuppressLint("Range")
String password = cursor.getString(cursor.getColumnIndex("password"));
@SuppressLint("Range")
int status = cursor.getInt(cursor.getColumnIndex("status"));
// 处理查询结果...
result+=name+":"+password+"\n";
}
textView.setText(result);
db.close();
}
});
button7.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String name = userName.getText().toString();
String userPassword = password.getText().toString();
if("".equals(name)||name==null||"".equals("userPassword")||userPassword==null){
Toast.makeText(myContext,"用户名和密码不能为空",Toast.LENGTH_SHORT).show();
return;
}
// 修改数据
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("password", userPassword);
String whereClause =" name= ?";
String[] whereArgs = {name};
int row=db.update(DatabaseHelper.TABLE_NAME,values,whereClause,whereArgs);
System.out.println(row);
db.close();
}
});
button8.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String name = userName.getText().toString();
if(("".equals(name)||name==null)){
Toast.makeText(myContext,"用户名不能为空",Toast.LENGTH_SHORT).show();
return;
}
// 删除数据
SQLiteDatabase db = dbHelper.getWritableDatabase();
String whereClause =" name= ?";
String[] whereArgs = {name};
int row= db.delete(DatabaseHelper.TABLE_NAME,whereClause,whereArgs);
System.out.println(row);
db.close();
}
});
login.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String name = userName.getText().toString();
String userPassword = password.getText().toString();
if("".equals(name)||name==null||"".equals("userPassword")||userPassword==null){
Toast.makeText(myContext,"用户名和密码不能为空",Toast.LENGTH_SHORT).show();
return;
}
// 验证
SQLiteDatabase db = dbHelper.getReadableDatabase();
String selection="name=? and password=?";
String[] selectionArgs = {name,userPassword};
Cursor cursor = db.query(DatabaseHelper.TABLE_NAME, null, selection, selectionArgs, null, null, null);
String result="";
if(cursor.moveToNext()) {
Intent intent=new Intent(OperateDataBaseActivity.this, MainActivity.class);
intent.putExtra("name",name);
startActivity(intent);
finish();
}else{
Toast.makeText(myContext,"用户名或密码不正确",Toast.LENGTH_SHORT).show();
}
textView.setText(result);
db.close();
}
});
}
}
e、新建一个登录成功页面
public class MainActivity extends AppCompatActivity {
private TextView textView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
textView=(TextView)findViewById(R.id.textView3);
Intent intent = getIntent();
textView.setText("HELLO "+intent.getStringExtra("name")+"!");
}
}
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:gravity="center"
tools:context=".MainActivity">
<TextView
android:id="@+id/textView3"
android:layout_width="240dp"
android:layout_height="113dp"
android:textSize="30dp"
android:text="HELLO WORD!"
tools:layout_editor_absoluteX="104dp"
tools:layout_editor_absoluteY="261dp" />
</LinearLayout>