Android数据库操作,实现登陆、注册、导入sql文件

一：创建保存用户名称与密码的实体类User

package com.example.test;

public class User {
    String name;
    String password;

    public User(String name, String password) {
        this.name = name;
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

二：创建DatabaseHelper继承自SQLiteOpenHelper

package com.example.test;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;

public class DBOpenHelper extends SQLiteOpenHelper {
    private SQLiteDatabase db;

    public DBOpenHelper(Context context) {
        super(context, "quinto", null, 1);
        db = getReadableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS user(" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +
                "password TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS user");
        onCreate(db);
    }

    // 增
    public boolean add(String name, String password){
        boolean isSuccess = false;
        try {
            db.execSQL("INSERT INTO user (name, password) VALUES(?, ?)",new Object[]{name, password});
            isSuccess = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    // 删
    public boolean delete(String name, String password){
        boolean isSuccess = false;
        try {
            db.execSQL("DELETE FROM user WHERE name = '" + name + "' AND password = '" + password + "'");
            isSuccess = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    // 改
    public boolean update(String password){
        boolean isSuccess = false;
        try {
            db.execSQL("UPDATE user SET password = ?",new Object[]{password});
            isSuccess = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    // 查
    public ArrayList<User> query(){
        ArrayList<User> list = new ArrayList<>();
        Cursor cursor = db.query("user",null,null,null,null,null,"name DESC");
        while(cursor.moveToNext()){
            @SuppressLint("Range") String name = cursor.getString(cursor.getColumnIndex("name"));
            @SuppressLint("Range") String password = cursor.getString(cursor.getColumnIndex("password"));
            list.add(new User(name,password));
        }
        cursor.close();
        return list;
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        // 禁用wal
        db.disableWriteAheadLogging();
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        // 禁用wal
        db.disableWriteAheadLogging();
    }
}

execSQL()方法可以执行insert、delete、update和CREATE TABLE之类有更改行为的SQL语句； rawQuery()方法用于执行select语句。

查询语句：select * from 表名 where 条件

插入语句：insert into 表名(字段列表) values(值列表)

更新语句：update 表名 set 字段名=值 where 条件

删除语句：delete from 表名 where 条件

三：登陆 LoginActivity（查询）

package com.example.test;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

import java.util.List;

public class LoginActivity extends AppCompatActivity{
    private EditText editName;
    private EditText editPwd;
    private DBOpenHelper dbOpenHelper;

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

        dbOpenHelper = new DBOpenHelper(LoginActivity.this);
        editName = findViewById(R.id.edit_name);
        editPwd = findViewById(R.id.edit_pwd);
    }

    /**
     * 登录（查询）
     * @param view
     */
    public void Login(View view) {
        String Name = editName.getText().toString().trim();
        String Pad = editPwd.getText().toString().trim();
        if (TextUtils.isEmpty(Name) || TextUtils.isEmpty(Pad)) {
            Toast.makeText(LoginActivity.this, "账号或密码不能为空", Toast.LENGTH_LONG).show();
        } else {
            List<User> list = dbOpenHelper.query();
            boolean isSuccess = false;
            for (User user: list) {
                if(Name.equals(user.getName()) && Pad.equals(user.getPassword())){
                    isSuccess = true;
                }
            }
            if (isSuccess) {
                editName.setText("");
                editPwd.setText("");
                Toast.makeText(LoginActivity.this, "登录成功", Toast.LENGTH_LONG).show();
                Intent intent = new Intent(LoginActivity.this, MainActivity.class);
                intent.putExtra("root", Name);
                intent.putExtra("password", Pad);
                startActivity(intent);
            } else {
                Toast.makeText(LoginActivity.this, "账号或密码错误", Toast.LENGTH_LONG).show();
            }
        }
    }

    /**
     * 跳转注册界面
     * @param view
     */
    public void Register(View view) {
        startActivity(new Intent(LoginActivity.this, RegisterActivity.class));
    }


}

效果：

四：注册 RegisterActivity（查询、增）

package com.example.test;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

import java.util.List;

public class RegisterActivity extends AppCompatActivity{
    private EditText edit_name;
    private EditText edit_pwd;
    private EditText edit_pwd2;
    private DBOpenHelper dbOpenHelper;

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

        dbOpenHelper = new DBOpenHelper(RegisterActivity.this);
        edit_name = findViewById(R.id.edit_name);
        edit_pwd = findViewById(R.id.edit_pwd);
        edit_pwd2 = findViewById(R.id.edit_pwd2);
    }

    /**
     * 注册（查询、增）
     * @param view
     */
    public void Register(View view) {
        String Name = edit_name.getText().toString().trim();
        String Pwd = edit_pwd.getText().toString().trim();
        String Pwd2 = edit_pwd2.getText().toString().trim();
        if (TextUtils.isEmpty(Name) || TextUtils.isEmpty(Pwd) || TextUtils.isEmpty(Pwd2)) {
            Toast.makeText(RegisterActivity.this, "账号和密码不能为空", Toast.LENGTH_SHORT).show();
        } else {
            List<User> list = dbOpenHelper.query();
            boolean isSuccess = false;
            for (User user: list) {
                if(Name.equals(user.getName())){
                    isSuccess = true;
                }
            }
            if (isSuccess) {
                Toast.makeText(RegisterActivity.this, "该用户已存在", Toast.LENGTH_LONG).show();
            } else {
                if (Pwd.equals(Pwd2)) {
                    if (dbOpenHelper.add(Name, Pwd)) {
                        Toast.makeText(RegisterActivity.this, "注册成功", Toast.LENGTH_LONG).show();
                        finish();
                    } else {
                        Toast.makeText(RegisterActivity.this, "注册失败", Toast.LENGTH_LONG).show();
                    }
                } else {
                    Toast.makeText(RegisterActivity.this, "两次密码输入不一致", Toast.LENGTH_LONG).show();
                }
            }
        }
    }

    /**
     * 返回
     * @param view
     */
    public void Back(View view) {
        finish();
    }
}

效果：

五：注销 MainActivity（删）

package com.example.test;

import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;

import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity{
    DBOpenHelper openHelper;
    String root;
    String password;

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

        openHelper = new DBOpenHelper(MainActivity.this);
        root = getIntent().getStringExtra("root");
        password = getIntent().getStringExtra("password");
    }

    /**
     * 跳转修改密码界面
     * @param view
     */
    public void Update(View view) {
        Intent intent = new Intent(MainActivity.this, ChangeActivity.class);
        intent.putExtra("password", password);
        startActivity(intent);
    }

    /**
     * 注销（删）
     * @param view
     */
    public void Logout(View view) {
        AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
        builder.setTitle("!!!");
        builder.setMessage("确定注销账号吗？");
        builder.setPositiveButton("确定", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                if (openHelper.delete(root, password)) {
                    Toast.makeText(MainActivity.this, "注销成功", Toast.LENGTH_LONG).show();
                    finish();
                } else {
                    Toast.makeText(MainActivity.this, "注销失败", Toast.LENGTH_LONG).show();
                }
            }
        });
        builder.setNegativeButton("取消", null);
        AlertDialog dialog = builder.create();
        dialog.show();
    }

    /**
     * 退出
     * @param view
     */
    public void Back(View view) {
        finish();
    }
}

效果：

六：修改密码 ChangeActivity（改）

package com.example.test;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class ChangeActivity extends AppCompatActivity {
    private EditText old_paw;
    private EditText new_paw1;
    private EditText new_paw2;
    private String password;
    private DBOpenHelper helper;

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

        old_paw = findViewById(R.id.old_paw);
        new_paw1 = findViewById(R.id.new_paw1);
        new_paw2 = findViewById(R.id.new_paw2);
        helper = new DBOpenHelper(ChangeActivity.this);
        password = getIntent().getStringExtra("password");
    }

    /**
     * 修改密码（改）
     * @param view
     */
    public void Confirm(View view) {
        String paw1 = old_paw.getText().toString().trim();
        String paw2 = new_paw1.getText().toString().trim();
        String paw3 = new_paw2.getText().toString().trim();
        if (TextUtils.isEmpty(paw1) || TextUtils.isEmpty(paw2) || TextUtils.isEmpty(paw3)) {
            Toast.makeText(ChangeActivity.this, "密码不能为空", Toast.LENGTH_LONG).show();
        } else {
            if (!password.equals(paw1)) {
                Toast.makeText(ChangeActivity.this, "您输入的旧密码错误，请重新输入", Toast.LENGTH_LONG).show();
                old_paw.setText("");
            } else {
                if (!paw2.equals(paw3)) {
                    Toast.makeText(ChangeActivity.this, "您两次输入的新密码不一致，请重新输入", Toast.LENGTH_LONG).show();
                    new_paw1.setText("");
                    new_paw2.setText("");
                } else {
                    if (helper.update(paw2)) {
                        Toast.makeText(ChangeActivity.this, "修改成功", Toast.LENGTH_LONG).show();
                        finish();
                    } else {
                        Toast.makeText(ChangeActivity.this, "修改失败", Toast.LENGTH_LONG).show();
                    }
                }
            }
        }
    }

    /**
     * 返回
     * @param view
     */
    public void Back(View view) {
        finish();
    }
}

效果：

七：导入sql文件

    /**
     * 导入sql文件
     * @param sqlName sql文件名称
     * @param context 上下文对象
     * @param sqlPlan 接口回调
     */
    public void importSql(String sqlName, Context context, SqlIntentPlan sqlPlan) {
        DBOpenHelper helper = new DBOpenHelper(context);
        SQLiteDatabase db = helper.getWritableDatabase();
        BufferedReader bufferedReader = null;
        try {
            bufferedReader = new BufferedReader(new InputStreamReader(context.openFileInput(sqlName)));
            String str;
            int allLine = 0;
            BufferedReader reader = new BufferedReader(new InputStreamReader(context.openFileInput(sqlName)));
            while ((str = reader.readLine()) != null) {
                allLine++;
            }
            int readLineNum = 0;
            StringBuffer buffer = new StringBuffer("");
            while ((str = bufferedReader.readLine()) != null) {
                readLineNum++;
                if (str.indexOf("<html>") > -1) {
                    sqlPlan.onSqlFailed("服务器连接失败，请选择正确的服务器！");
                    return;
                } else {
                    buffer.append(str);
                    if (str.indexOf("; --") > -1) {
                        db.execSQL(buffer.toString());
                        buffer = new StringBuffer("");
                        sqlPlan.SqlPlan(readLineNum * 100 / allLine);
                    }
                }
            }
            db.close();
            sqlPlan.onSqlSuccess(1);
        } catch (IOException e) {
            sqlPlan.onSqlFailed("文件导入错误！");
            Log.e("db-error", e.toString());
            return;
        } catch (SQLException e) {
            sqlPlan.onSqlFailed("导入数据错误!" + e.toString());
            Log.e("db-error", e.toString());
            return;
        }
    }

    public interface SqlIntentPlan {
        // 导入进度
        void  SqlPlan(int a);

        // 导入成功
        void onSqlSuccess(int b);

        // 导入异常
        void onSqlFailed(String e);
    }

原文链接：https://blog.csdn.net/Qxnedy/article/details/122862447