android studio 连接SQLite数据库并实现增删改查功能

功能代码及调试代码

package com.example.bankappdemo;import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.ListView;
import android.widget.Toast;import androidx.appcompat.app.AppCompatActivity;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;import pl.com.salsoft.sqlitestudioremote.SQLiteStudioService;public class CreatAccountActivity extends AppCompatActivity {private EditText mCreatConfirmPasswordEditText;private EditText mCreatPassWordEditText;private EditText mMconfirmPasswordEditText;private Button mReturnbutton;private Button mConfirmMagbutton;private DBHelper mDBHelper;private static final String DB_NAME = "Account.db";//数据库名private static final String TABLE_NAME = "User";//表名private static final String COLUMN_ID = "id";private static final String TAG = "CreatAccountActivity";public void isNameExist(int id) {//判断用户名是否重复try {SQLiteStudioService.instance().start(this);DBHelper helper = new DBHelper(CreatAccountActivity.this);SQLiteDatabase db = helper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from User where ID=?", new String[]{"id"});cursor.moveToLast();if (cursor.getCount() <= 0) {Toast.makeText(CreatAccountActivity.this, "用户不存在", Toast.LENGTH_SHORT).show();}} catch (Exception e) {e.printStackTrace();}Toast.makeText(CreatAccountActivity.this, "用户存在", Toast.LENGTH_SHORT).show();}//插入数据到数据库public void addAccountSQlite(String name, String pwd, int id) {SQLiteStudioService.instance().start(this);DBHelper helper = new DBHelper(CreatAccountActivity.this);SQLiteDatabase db = helper.getReadableDatabase();String sql = "insert into User values(" + id + ',' + name + ',' + pwd + ',' + 0.0 + ")";db.execSQL(sql);db.close();Toast.makeText(CreatAccountActivity.this, "账户创建成功", Toast.LENGTH_SHORT).show();}//删除账户public void delAllAccount(int id) {SQLiteStudioService.instance().start(this);DBHelper helper = new DBHelper(CreatAccountActivity.this);SQLiteDatabase db = helper.getWritableDatabase();db.delete(TABLE_NAME, "ID=" + id, null);//db.close();Toast.makeText(CreatAccountActivity.this, "删除所有账户成功", Toast.LENGTH_SHORT).show();}//更新数据public void updateData(String name, String pwd, int id, Double money) {SQLiteStudioService.instance().start(this);DBHelper helper = new DBHelper(CreatAccountActivity.this);SQLiteDatabase db = helper.getWritableDatabase();String sql = "update User set name = " + name + " ,password = " + pwd + " ,money = " + money + " where id = " + id;db.execSQL(sql);Toast.makeText(CreatAccountActivity.this, "更新账户信息成功", Toast.LENGTH_SHORT).show();}//根据ID查找数据public void query(int id) {SQLiteStudioService.instance().start(this);DBHelper helper = new DBHelper(CreatAccountActivity.this);SQLiteDatabase db = helper.getWritableDatabase();String rawQuerySql = "select * from " + TABLE_NAME + " where " + COLUMN_ID + " = " + id + " or " + COLUMN_ID + " = 2";Log.e(TAG, rawQuerySql);/***   Cursor cursor = helper.getWritableDatabase().rawQuery(rawQuerySql, null);*   int resultCounts = cursor.getCount();* 通过执行SQL查询语句获取结果集的行数* 通过helper.getWritableDatabase()方法获取可写的SQLiteDatabase对象* 使用rawQuery()方法执行查询语句，将结果保存在Cursor对象中* 通过调用cursor.getCount()方法获取结果集的行数，将结果保存在resultCounts变量中* 最后，可以根据resultCounts的值来判断查询结果的行数*/Cursor cursor = helper.getWritableDatabase().rawQuery(rawQuerySql, null);int resultCounts = cursor.getCount();//遍历每一行for (int i = 0; i < resultCounts; i++) {//得到的cursor的初始位置是指向第一条记录的前一个位置的（-1），避免出现下标越界异常，使用moveToNext:移动游标到下一条记录，也就是第一条记录cursor.moveToNext();Account stAccount = new Account();int nId = cursor.getInt(0);//ID是第一列String strUsername = cursor.getString(1);//名字是第二列String strPassword = cursor.getString(2);//密码是第三列Double dMoney = cursor.getDouble(3);//余额是第四列stAccount.setId(nId);stAccount.setUserName(strUsername);stAccount.setPassWord(strPassword);stAccount.setMoney(dMoney);Log.e(TAG, stAccount.toString());}Toast.makeText(CreatAccountActivity.this, "信息查询成功", Toast.LENGTH_SHORT).show();}protected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.creataccount);mConfirmMagbutton = findViewById(R.id.confirmMagbutton);mConfirmMagbutton.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View view) {//调试信息String name = ((EditText) findViewById(R.id.creatUserNameEditText)).getText().toString();String pwd = ((EditText) findViewById(R.id.creatPassWordEditText)).getText().toString();int id = 3;//ContentValues values = new ContentValues();// addAccountSQlite(name, pwd, id);//  delAllAccount(1);updateData("2","2",2,6000.00);// query(3);//  isNameExist(1);}});}
}

DBHelper类

package com.example.bankappdemo;import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;public class DBHelper extends SQLiteOpenHelper {private static final String TAG = "DBHelper";private Context context;//上下文环境private static final String DB_NAME = "Account.db";//数据库名private static final int DB_VERSION = 1;//数据库版本号private static final String TABLE_NAME = "User";//表名private Context mContext;// 列名称private static final String COLUMN_ID = "id";private static final String COLUMN_NAME = "name";private static final String COLUMN_PASSWORD = "password";private static final String COLUMN_MONEY = "money";public DBHelper(Context context) {super(context, DB_NAME, null, DB_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {// 创建数据库表String createTableQuery = "create table if not exists " + TABLE_NAME + "(" + COLUMN_ID +" int primary key, " + COLUMN_NAME + " varchar, " + COLUMN_PASSWORD + " varchar, " + COLUMN_MONEY + " double)";Log.e(TAG, "建表语句：" + createTableQuery);db.execSQL(createTableQuery);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 升级数据库版本if (oldVersion < newVersion) {// 执行升级操作，例如添加新的表或修改表结构}}}