Android提供了SQLiteDatabase代表一个数据库（底层就是一个数据库文件）,一旦应用程序获得了代表指定数据库的SQLiteDatabase对象，接下来可通过SQLiteDatabase对象来管理、操作数据库。
Android中使用SQLite数据库进行开发时，主要利用SQL语句来进行基本功能实现。掌握如下增删改查语句更利于学习。
创建表create table tableName(id integer primary key autoincrement , name , text , number text);
删除表 drop table if exists tableName;
增加一条数据：insert into tableName values(“小明”,”111”);
删除一条数据：delete from tableName where name=”小明”;
修改一条数据：update tableName set name=”小红” where name=”小明”;
查询一条数据:select*from tableName where name=”小明”;
首页
效果图：
MainActivity.java
package com.example.sqlite_foundation;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
public void insertData(View view){
//进行跳转
Intent intent = new Intent(this,insertActivity.class);
startActivity(intent);
}
public void deleteData(View view){
Intent intent = new Intent(this,DeleteActivity.class);
startActivity(intent);
}
public void updateData(View view){
Intent intent = new Intent(this,UpdateActivity.class);
startActivity(intent);
}
public void queryData(View view){
Intent intent = new Intent(this,QueryActivity.class);
startActivity(intent);
}
}activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:gravity="center"
android:background="@drawable/back"
android:scaleType="matrix"
>
<Button
android:layout_width="300dp"
android:layout_height="60dp"
android:onClick="insertData"
android:text="添加数据"
android:textSize="20sp"
android:textStyle="bold"
android:layout_gravity="center"
android:background="@color/yellow"/>
<Button
android:layout_width="300dp"
android:layout_height="60dp"
android:onClick="deleteData"
android:text="删除数据"
android:textSize="20sp"
android:textStyle="bold"
android:layout_gravity="center"
android:layout_marginTop="20dp"
android:background="@color/yellow"/>
<Button
android:layout_width="300dp"
android:layout_height="60dp"
android:onClick="updateData"
android:text="修改数据"
android:textSize="20sp"
android:textStyle="bold"
android:layout_gravity="center"
android:layout_marginTop="20dp"
android:background="@color/yellow"/>
<Button
android:layout_width="300dp"
android:layout_height="60dp"
android:onClick="queryData"
android:text="查询数据"
android:textSize="20sp"
android:textStyle="bold"
android:layout_gravity="center"
android:layout_marginTop="20dp"
android:background="@color/yellow"/>
</LinearLayout>数据库创建
MySQLiteOpenHerper.java
package com.example.sqlite_foundation;
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.example.sqlite_foundation.bean.Student;
import java.util.ArrayList;
import java.util.List;
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
private static final String DB_NAME="mySQLite.db";
private static final String TABLE_NAME_STUDENT="student";
//SQL语句准备
private static final String CREATE_TABLE_SQL = "create table " + TABLE_NAME_STUDENT + " (id integer primary key autoincrement,name text,number text,gender text,score text);";
//定死参数
public MySQLiteOpenHelper(Context context){
super(context,DB_NAME,null,1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//执行SQL语句
sqLiteDatabase.execSQL(CREATE_TABLE_SQL);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
//插入数据方法
public long insertData(Student student){
//得到一个可写数据库
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
//传值准备数据
values.put("name",student.getName());
values.put("number",student.getNumber());
values.put("gender",student.getGender());
values.put("score",student.getScore());
//把数据插入到数据库中
//返回当前行的id，若为-1则数据插入失败
return db.insert(TABLE_NAME_STUDENT,null,values);
}
//按姓名删除数据方法
public int deleteFromDbByName(String name){
SQLiteDatabase db = getWritableDatabase();
return db.delete(TABLE_NAME_STUDENT,"name like ?",new String[]{name});
}
//修改数据方法
public int updateData(Student student){
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
//传值准备数据
values.put("name",student.getName());
values.put("number",student.getNumber());
values.put("gender",student.getGender());
values.put("score",student.getScore());
return db.update(TABLE_NAME_STUDENT,values,"name like ?",new String[] {student.getName()});
}
//查询数据方法
public List<Student> queryFromDbByName(String name){
SQLiteDatabase db = getWritableDatabase();
List<Student> studentList = new ArrayList<>();
Cursor cursor = db.query(TABLE_NAME_STUDENT, null, "name like ?", new String[]{name}, null, null, null);
if(cursor!=null){
while(cursor.moveToNext()){
String name1 = cursor.getString(cursor.getColumnIndex("name"));
String number = cursor.getString(cursor.getColumnIndex("number"));
String gender = cursor.getString(cursor.getColumnIndex("gender"));
String score = cursor.getString(cursor.getColumnIndex("score"));
Student student = new Student();
student.setName(name1);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
studentList.add(student);
}
cursor.close();
}
return studentList;
}
public List<Student> queryAllFromDb(){
SQLiteDatabase db = getWritableDatabase();
List<Student> studentList = new ArrayList<>();
Cursor cursor = db.query(TABLE_NAME_STUDENT, null, null,null, null, null, null);
if(cursor!=null){
while(cursor.moveToNext()){
String name1 = cursor.getString(cursor.getColumnIndex("name"));
String number = cursor.getString(cursor.getColumnIndex("number"));
String gender = cursor.getString(cursor.getColumnIndex("gender"));
String score = cursor.getString(cursor.getColumnIndex("score"));
Student student = new Student();
student.setName(name1);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
studentList.add(student);
}
cursor.close();
}
return studentList;
}
}
添加数据
效果图：
insertActivity.java
package com.example.sqlite_foundation;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.Toast;
import com.example.sqlite_foundation.bean.Student;
import com.example.sqlite_foundation.util.ToastUtil;
public class insertActivity extends AppCompatActivity {
private EditText etName,etNumber,etScore;
private RadioButton rbMan,rbWoman;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_insert);
initView();
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
private void initView() {
etName = (EditText) findViewById(R.id.et_name);
etNumber = (EditText) findViewById(R.id.et_number);
etScore = (EditText) findViewById(R.id.et_score);
rbMan = (RadioButton) findViewById(R.id.rb_man);
rbWoman = (RadioButton) findViewById(R.id.rb_woman);
}
public void insert(View view){
String name = etName.getText().toString().trim();
String number = etNumber.getText().toString().trim();
String score = etScore.getText().toString().trim();
String gender="";
if(rbMan.isChecked()){
gender="男";
}
if(rbWoman.isChecked()){
gender="女";
}
//构建数据实体Student
Student student = new Student();
student.setName(name);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
//数据插入数据库中
long rowId = mySQLiteOpenHelper.insertData(student);
if(rowId!=-1){
//工具类ToastUtil的使用
ToastUtil.toastShort(this,"添加成功!");
}else{
ToastUtil.toastShort(this,"添加失败!");
}
}
}activity_insert.xml
<?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"
tools:context=".insertActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="姓名"/>
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
android:inputType="number"
style="@style/MyTextStyle"
android:text="学号"/>
<EditText
android:id="@+id/et_number"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="性别"/>
<RadioGroup
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center"
android:layout_marginTop="10dp">
<RadioButton
android:id="@+id/rb_man"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="男"/>
<RadioButton
android:id="@+id/rb_woman"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="女"
android:layout_marginLeft="30dp"/>
</RadioGroup>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="分数"/>
<EditText
android:id="@+id/et_score"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<Button
android:text="保存"
android:onClick="insert"
style="@style/MyButton"
android:background="@drawable/round_button"
android:layout_marginTop="30dp"
/>
</LinearLayout>删除数据
效果图：
DeleteActivity.java
package com.example.sqlite_foundation;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import com.example.sqlite_foundation.util.ToastUtil;
public class DeleteActivity extends AppCompatActivity {
private EditText etName;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_delete);
etName = findViewById(R.id.et_name);
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
public void delete(View view){
String name = etName.getText().toString().trim();
//按姓名从数据库中删除数据
int row = mySQLiteOpenHelper.deleteFromDbByName(name);
if(row>0){
ToastUtil.toastShort(this,"删除成功，删除了"+ row + "条数据!");
}else{
ToastUtil.toastShort(this,"删除失败，没有符合条件的数据!");
}
}
}activity_delete.xml
<?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"
tools:context=".DeleteActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="姓名"/>
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<Button
android:text="删除"
android:onClick="delete"
style="@style/MyButton"
android:background="@drawable/round_button"
android:layout_marginTop="30dp"
/>
</LinearLayout>修改数据
效果图：
UpdateActivity.java
package com.example.sqlite_foundation;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.RadioButton;
import com.example.sqlite_foundation.bean.Student;
import com.example.sqlite_foundation.util.ToastUtil;
public class UpdateActivity extends AppCompatActivity {
private EditText etName,etNumber,etScore;
private RadioButton rbMan,rbWoman;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_update);
initView();
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
private void initView() {
etName = (EditText) findViewById(R.id.et_name);
etNumber = (EditText) findViewById(R.id.et_number);
etScore = (EditText) findViewById(R.id.et_score);
rbMan = (RadioButton) findViewById(R.id.rb_man);
rbWoman = (RadioButton) findViewById(R.id.rb_woman);
}
public void update(View view){
String name = etName.getText().toString().trim();
String number = etNumber.getText().toString().trim();
String score = etScore.getText().toString().trim();
String gender="";
if(rbMan.isChecked()){
gender="男";
}
if(rbWoman.isChecked()){
gender="女";
}
//构建数据实体Student
Student student = new Student();
student.setName(name);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
//更新数据库数据
long rowId = mySQLiteOpenHelper.updateData(student);
if(rowId>0){
//工具类ToastUtil的使用
ToastUtil.toastShort(this,"更新成功!");
}else{
ToastUtil.toastShort(this,"没有数据被更新!");
}
}
}activity_update.xml
<?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"
tools:context=".UpdateActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="姓名"/>
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
android:inputType="number"
style="@style/MyTextStyle"
android:text="学号"/>
<EditText
android:id="@+id/et_number"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="性别"/>
<RadioGroup
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center"
android:layout_marginTop="10dp">
<RadioButton
android:id="@+id/rb_man"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="男"/>
<RadioButton
android:id="@+id/rb_woman"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="女"
android:layout_marginLeft="30dp"/>
</RadioGroup>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="分数"/>
<EditText
android:id="@+id/et_score"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<Button
android:text="更新"
android:onClick="update"
style="@style/MyButton"
android:background="@drawable/round_button"
android:layout_marginTop="30dp"
/>
</LinearLayout>查询数据
效果图：
QueryActivity.java
package com.example.sqlite_foundation;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import com.example.sqlite_foundation.bean.Student;
import com.example.sqlite_foundation.util.ToastUtil;
import java.util.List;
public class QueryActivity extends AppCompatActivity {
private EditText etName;
private TextView tvResult;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_query);
etName = findViewById(R.id.et_name);
tvResult = findViewById(R.id.tv_result);
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
public void query(View view) {
String name = etName.getText().toString().trim();
if(TextUtils.isEmpty(name)){
//如果输入为空，则查询所有数据
List<Student> students = mySQLiteOpenHelper.queryAllFromDb();
showData(students);
return;
}
//按姓名在数据库中查询数据并显示
List<Student> students = mySQLiteOpenHelper.queryFromDbByName(name);
showData(students);
}
//展示数据方法
public void showData(List<Student> students){
// String result="";
// for(Student stu : students){
// result += "姓名:" +stu.getName()+",学号:"+stu.getNumber()+",性别:"+stu.getGender()+",分数:"+stu.getScore()+"\n";
// }
//因为每次String拼接都会new一个新的串，比较消耗性能，可以采用StringBuilder
StringBuilder stringBuilder = new StringBuilder();
for(Student stu : students){
stringBuilder.append("姓名:");
stringBuilder.append(stu.getName());
stringBuilder.append(",学号:");
stringBuilder.append(stu.getNumber());
stringBuilder.append(",性别:");
stringBuilder.append(stu.getGender());
stringBuilder.append(",分数:");
stringBuilder.append(stu.getScore());
stringBuilder.append("\n");
}
tvResult.setText(stringBuilder.toString());
}
}activity_query.xml
<?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"
tools:context=".QueryActivity"
>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
style="@style/MyTextStyle"
android:text="姓名"/>
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="50dp"
android:textSize="18sp"
android:layout_marginLeft="10dp"
android:paddingLeft="5px"
android:background="@drawable/edittext_border"/>
</LinearLayout>
<Button
android:text="查询"
android:onClick="query"
style="@style/MyButton"
android:background="@drawable/round_button"
android:layout_marginTop="30dp"
/>
<TextView
android:id="@+id/tv_result"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textColor="@color/black"
android:textSize="20sp"
android:layout_marginTop="20dp"
android:layout_marginLeft="10dp"
android:gravity="left"/>
</LinearLayout>写在后面
1、频繁使用的Toast可以写一个工具类来简便代码。
package com.example.sqlite_foundation.util;
import android.content.Context;
import android.widget.Toast;
public class ToastUtil {
public static void toastShort(Context context,String msg){
Toast.makeText(context, msg, Toast.LENGTH_SHORT).show();
}
public static void toastLong(Context context,String msg){
Toast.makeText(context, msg, Toast.LENGTH_SHORT).show();
}
}
2、查询全部数据时的简单拼接方法因为每次String拼接都会new一个新串，较消耗性能，可以采取StringBuilder进行替换。
3、查看当前db数据库时可以采用点击菜单栏view->ToolWindows->database inspector进行查看。有些版本过高找不到database inspector，可以找到app inspection打开后运行app，查看。
扫一扫
2230
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
