android studio数据存储建立SQLite数据库实现增删查改

实验目的：

分别使用sqlite3工具和Android代码的方式建立SQLite数据库。在完成建立数据库的工作后，编程实现基本的数据库操作功能，包括数据的添加、删除和更新。

实验要求：

• 1.创建一个学生管理的应用，基本信息包含学生姓名，班级，学号。采用数据库存储这些信息。

• 2.应用应该至少包含信息录入和删除功能。

• 3.数据显示考虑采用ListView。

实验效果：

工程结构：

源代码：

DBAdapter.java

package com.example.shiyan6_sqlite;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class DBAdapter {

private static final String DB_NAME = "student.db";
private static final String DB_TABLE = "peopleinfo";
private static final int DB_VERSION = 1;

public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_BANJI = "banji";
public static final String KEY_XUEHAO = "xuehao";

private SQLiteDatabase db;
private final Context context;
private DBOpenHelper dbOpenHelper;

public DBAdapter(Context _context) {
 context = _context;
}

public void close() {
 if(db !=null)
 {
  db.close();
  db=null;
 }
}

public void open() throws SQLiteException {
 dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, DB_VERSION);
 try {
  db = dbOpenHelper.getWritableDatabase();
 }
 catch (SQLiteException ex) {
  db = dbOpenHelper.getReadableDatabase();
 }
}

public long insert(People people) {
 ContentValues newValues = new ContentValues();
 newValues.put(KEY_NAME, people.Name);
 newValues.put(KEY_BANJI, people.Banji);
 newValues.put(KEY_XUEHAO, people.Xuehao);

return db.insert(DB_TABLE, null, newValues);
}

public People[] queryAllData() {
 Cursor results =  db.query(DB_TABLE, new String[] { KEY_ID, KEY_NAME, KEY_BANJI, KEY_XUEHAO},
   null, null, null, null, null);
 return ConvertToPeople(results);
}

public People[] queryOneData(long id) {
 Cursor results =  db.query(DB_TABLE, new String[] { KEY_ID, KEY_NAME, KEY_BANJI, KEY_XUEHAO},
   KEY_ID + "=" + id, null, null, null, null);
 return ConvertToPeople(results);
}

@SuppressLint("Range")
private People[] ConvertToPeople(Cursor cursor){
 int resultCounts = cursor.getCount();
 if (resultCounts == 0 || !cursor.moveToFirst()){
  return null;
 }
 People[] peoples = new People[resultCounts];
 for (int i = 0 ; i<resultCounts; i++){
  peoples[i] = new People();
  peoples[i].ID = cursor.getInt(0);
  peoples[i].Name = cursor.getString(cursor.getColumnIndex(KEY_NAME));
  peoples[i].Banji = cursor.getString(cursor.getColumnIndex(KEY_BANJI));
  peoples[i].Xuehao = cursor.getString(cursor.getColumnIndex(KEY_XUEHAO));
  cursor.moveToNext();
 }
 return peoples;
}

public long deleteAllData() {
 return db.delete(DB_TABLE, null, null);
}

public long deleteOneData(long id) {
 return db.delete(DB_TABLE,  KEY_ID + "=" + id, null);
}

public long updateOneData(long id , People people){
 ContentValues updateValues = new ContentValues();
 updateValues.put(KEY_NAME, people.Name);
 updateValues.put(KEY_BANJI, people.Banji);
 updateValues.put(KEY_XUEHAO, people.Xuehao);

return db.update(DB_TABLE, updateValues,  KEY_ID + "=" + id, null);
}

private static class DBOpenHelper extends SQLiteOpenHelper {

public DBOpenHelper(Context context, String name, CursorFactory factory, int version) {
  super(context, name, factory, version);
 }

private static final String DB_CREATE = "create table " +
   DB_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
   KEY_NAME+ " text not null, " + KEY_BANJI+ " text not null," + KEY_XUEHAO + " text not null);";

@Override
 public void onCreate(SQLiteDatabase _db) {
  _db.execSQL(DB_CREATE);
 }

@Override
 public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
  _db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
  onCreate(_db);
 }
}
}

People.java

package com.example.shiyan6_sqlite;

public class People {
public int ID = -1;
public String Name;
public String Banji;
public String Xuehao;

@Override
public String toString(){
 String result = "";
 result += "ID：" + this.ID + "，";
 result += "姓名：" + this.Name + "，";
 result += "班级：" + this.Banji + "， ";
 result += "学号：" + this.Xuehao;
 return result;
}
}

MainActivity.java

package com.example.shiyan6_sqlite;

import androidx.appcompat.app.AppCompatActivity;

import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity {

EditText e_xm,e_nl,e_sg,e_id;
   TextView t_1;
   Button b_add,b_allsee,b_clearsee,b_alldel,b_delid,b_seeid,b_updid;
   DBAdapter dbAdapter;
   SQLiteDatabase db;

@Override
   protected void onCreate(Bundle savedInstanceState) {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.activity_main);

e_xm=findViewById(R.id.e_xm);
       e_nl=findViewById(R.id.e_nl);
       e_sg=findViewById(R.id.e_sg);
       b_add=findViewById(R.id.b_add);
       b_allsee=findViewById(R.id.b_allsee);
       b_clearsee=findViewById(R.id.b_clearall);
       b_alldel=findViewById(R.id.b_delall);
       b_delid=findViewById(R.id.b_delid);
       b_seeid=findViewById(R.id.b_seeid);
       b_updid=findViewById(R.id.b_updid);
       e_id=findViewById(R.id.e_id);
       t_1=findViewById(R.id.t_1);
       dbAdapter=new DBAdapter(this);
       dbAdapter.open();

b_add.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               People t=new People();
               t.Name=e_xm.getText().toString();
               t.Banji=e_nl.getText().toString();
               t.Xuehao=e_sg.getText().toString();
               long colunm=dbAdapter.insert(t);
               if (colunm == -1 ){
                   t_1.setText("添加过程错误！");
               } else {
                   t_1.setText("成功添加数据，ID："+String.valueOf(colunm));
               }
           }
       });

b_allsee.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               People [] peoples =dbAdapter.queryAllData();
               if (peoples == null){
                   t_1.setText("数据库中没有数据");
                   return;
               }
               String t="数据库:\n";
               for(int i=0;i<peoples.length;++i){
                   t+=peoples[i].toString()+"\n";
               }
               t_1.setText(t);
           }
       });

b_clearsee.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               t_1.setText("");
           }
       });

b_alldel.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               dbAdapter.deleteAllData();
               t_1.setText("已删除所有数据！");
           }
       });

b_delid.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               int id=Integer.parseInt(e_id.getText().toString());
               long result=dbAdapter.deleteOneData(id);
               String msg = "删除ID为"+e_id.getText().toString()+"的数据" + (result>0?"成功":"失败");
               t_1.setText(msg);
           }
       });

b_seeid.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               int id=Integer.parseInt(e_id.getText().toString());
               People people[]=dbAdapter.queryOneData(id);
               if(people==null){
                   t_1.setText("Id为"+id+"的记录不存在！");
               }
               else{
                   t_1.setText("查询成功：\n"+people[0].toString());
               }
           }
       });

b_updid.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View view) {
               int id=Integer.parseInt(e_id.getText().toString());
               People t=new People();
               t.Name=e_xm.getText().toString();
               t.Banji=e_nl.getText().toString();
               t.Xuehao=e_sg.getText().toString();
               long n=dbAdapter.updateOneData(id,t);
               if (n<0){
                   t_1.setText("更新过程错误！");
               } else {
                   t_1.setText("成功更新数据，"+String.valueOf(n)+"条");
               }
           }
       });
   }

@Override
   protected void onStop() {
       super.onStop();
       dbAdapter.close();
   }
}

来源：https://blog.csdn.net/qq_42641977/article/details/121970828