Anroid Sqlite 本地数据库

最新推荐文章于 2024-01-19 13:15:27 发布

跑掉的安眠曲

最新推荐文章于 2024-01-19 13:15:27 发布

阅读量125

点赞数

分类专栏： android进阶之路文章标签： sqlite android 数据库

本文链接：https://blog.csdn.net/yineng7758258/article/details/129304785

版权

android进阶之路专栏收录该内容

24 篇文章 0 订阅

订阅专栏

该代码示例展示了如何在Android中使用SQLiteOpenHelper扩展类进行数据库的基本操作，包括创建表、插入数据、删除数据、更新数据和查询数据。通过EditText输入姓名或其他条件，实现对名为mySQLite.db的数据库中student表的CRUD操作。

摘要由CSDN通过智能技术生成

连接数据库，增删改查语句

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "mySQLite.db";
    private static final String TABLE_NAME_STUDENT = "student";

    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 db) {
        db.execSQL(CREATE_TABLE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    //增加数据
    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());

        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) {
        List<Student> list = new ArrayList<>();

        SQLiteDatabase db = getWritableDatabase();
        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);
                list.add(student);

            }
            cursor.close();
        }
        return list;

    }
    //查询所以数据
    public List<Student> queryAllFromDb() {
        List<Student> list = new ArrayList<>();

        SQLiteDatabase db = getWritableDatabase();
        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);
                list.add(student);

            }
            cursor.close();
        }
        return list;

    }
}

0查询数据:

public class QueryActivity extends AppCompatActivity {


    private EditText etName;
    private TextView tv_result;

    private  MySQLiteOpenHelper mySQLiteOpenHelper;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_query);
        etName = findViewById(R.id.et_name);
        tv_result = 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);

    }

    private void showData( List<Student> students ){
        StringBuilder stringBuilder = new StringBuilder();

        for (Student student : students) {

            stringBuilder.append("姓名：");
            stringBuilder.append(student.getName());

            stringBuilder.append(" 学号：");
            stringBuilder.append(student.getNumber());

            stringBuilder.append(" 性别：");
            stringBuilder.append(student.getGender());

            stringBuilder.append(" 分数：");
            stringBuilder.append(student.getScore()+"\n");


        }
        tv_result.setText(stringBuilder.toString());

    }

}

1新增数据：

public class InsertActivity extends AppCompatActivity {

    private EditText et_name;
    private EditText et_number;
    private RadioButton rb_man;
    private RadioButton rb_woman;
    private EditText et_score;

    private MySQLiteOpenHelper mySQLiteOpenHelper;


    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_insert);
        initView();
        mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
    }

    private void initView() {
        et_name = findViewById(R.id.et_name);
        et_number = findViewById(R.id.et_number);
        rb_man = findViewById(R.id.rb_man);
        rb_woman = findViewById(R.id.rb_woman);
        et_score = findViewById(R.id.et_score);
    }
    public void insert(View view){
       String name = et_name.getText().toString().trim();
       String number = et_number.getText().toString().trim();
       String score = et_score.getText().toString().trim();

        String gender = "";
        if (rb_man.isChecked()){
            gender = "男";
        }
        if (rb_woman.isChecked()){
            gender = "女";
        }

        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.toastShort(this,"添加成功!");
        }else{
            ToastUtil.toastShort(this,"添加失败!");
        }

    }
}

2删除数据：

public class DeleteActicity extends AppCompatActivity {

    private EditText etName;

    private  MySQLiteOpenHelper mySQLiteOpenHelper;


    @Override
    protected void onCreate(@Nullable 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,"删除失败");

        }
    }

}

3修改数据：

public class UpdateActivity extends AppCompatActivity {


    private EditText et_name;
    private EditText et_number;
    private RadioButton rb_man;
    private RadioButton rb_woman;
    private EditText et_score;

    private MySQLiteOpenHelper mySQLiteOpenHelper;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_update);
        initView();
        mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
    }

    private void initView() {
        et_name = findViewById(R.id.et_name);
        et_number = findViewById(R.id.et_number);
        rb_man = findViewById(R.id.rb_man);
        rb_woman = findViewById(R.id.rb_woman);
        et_score = findViewById(R.id.et_score);
    }
    public void update(View view){
        String name = et_name.getText().toString().trim();
        String number = et_number.getText().toString().trim();
        String score = et_score.getText().toString().trim();

        String gender = "";
        if (rb_man.isChecked()){
            gender = "男";
        }
        if (rb_woman.isChecked()){
            gender = "女";
        }

        Student student = new Student();
        student.setName(name);
        student.setNumber(number);
        student.setGender(gender);
        student.setScore(score);

        //更新数据库中
        long rowId = mySQLiteOpenHelper.updateData(student);
        if (rowId != -1){
            ToastUtil.toastShort(this,"更新成功!");
        }else{
            ToastUtil.toastShort(this,"更新失败!");
        }

    }
}

activity_query

<?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"
    >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="10dp"
        >

        <TextView
            style="@style/MyTextStyle"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:text="姓名" />
        <EditText
            android:id="@+id/et_name"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:layout_marginLeft="5dp"
            />
    </LinearLayout>


    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询"
        style="@style/MyBtnStyle"
        android:onClick="query"
        />

    <TextView
        style="@style/MyTextStyle"
        android:id="@+id/tv_result"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        android:gravity="left"
       />
</LinearLayout>

activity_main

<?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=".MainActivity">


    <Button
        style="@style/MyBtnStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="insertData"
        android:text="添加数据" />

    <Button
        style="@style/MyBtnStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="deleteData"
        android:text="删除数据" />

    <Button
        style="@style/MyBtnStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="updateData"
        android:text="修改数据" />

    <Button
        style="@style/MyBtnStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="queryData"
        android:text="查询数据" />

</LinearLayout>

activity_insert

<?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"
    >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="10dp"
        >

        <TextView
            style="@style/MyTextStyle"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:text="姓名" />
        <EditText
            android:id="@+id/et_name"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:layout_marginLeft="5dp"
            />
    </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:text="学号"
            android:inputType="number"
            android:maxLines="1"
            style="@style/MyTextStyle"
            />
        <EditText
            android:id="@+id/et_number"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:inputType="number"
            android:layout_marginLeft="5dp"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="10dp"
        android:gravity="center_vertical"
        >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="性别"
            style="@style/MyTextStyle"
            />

        <RadioGroup
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:gravity="center"
            >
            <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="20dp"
                />
        </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:text="分数"
            style="@style/MyTextStyle"
            />
        <EditText
            android:id="@+id/et_score"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:inputType="number"
            android:maxLines="1"
            android:layout_marginLeft="5dp"
            />
    </LinearLayout>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="保存"
        style="@style/MyBtnStyle"
        android:onClick="insert"
        />
</LinearLayout>

activity_delete

<?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">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="10dp"
        >

        <TextView
            style="@style/MyTextStyle"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:text="姓名" />
        <EditText
            android:id="@+id/et_name"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:layout_marginLeft="5dp"
            />
    </LinearLayout>



    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除"
        style="@style/MyBtnStyle"
        android:onClick="delete"
        />
</LinearLayout>

activity_update

<?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"
    >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="10dp"
        >

        <TextView
            style="@style/MyTextStyle"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:text="姓名" />
        <EditText
            android:id="@+id/et_name"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:layout_marginLeft="5dp"
            />
    </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:text="学号"
            android:inputType="number"
            android:maxLines="1"
            style="@style/MyTextStyle"
            />
        <EditText
            android:id="@+id/et_number"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:inputType="number"
            android:layout_marginLeft="5dp"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="10dp"
        android:gravity="center_vertical"
        >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="性别"
            style="@style/MyTextStyle"
            />

        <RadioGroup
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:gravity="center"
            >
            <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="20dp"
                />
        </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:text="分数"
            style="@style/MyTextStyle"
            />
        <EditText
            android:id="@+id/et_score"
            android:layout_width="match_parent"
            style="@style/MyEditStyle"
            android:inputType="number"
            android:maxLines="1"
            android:layout_marginLeft="5dp"
            />
    </LinearLayout>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="更新"
        style="@style/MyBtnStyle"
        android:onClick="update"
        />
</LinearLayout>