安卓开发之SQLite增删改查

• MySqliteHelper

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class MySqliteHelper extends SQLiteOpenHelper {
    //构造方法，对应的该方法就是用来创建数据库的
    //构造方法里面有四个参数,1.上下文，2.数据库名字 3.游标工厂，一般没什么用直接传null 4.数据库版本号
    //建库
    public MySqliteHelper(Context context) {
        super(context, "user.db", null, 1);
    }

    //建表
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //通过SQLiteDatabase建表
        //create table user(_id INTEGER primary key autoincrement,username VARCHAR2(20),password VARCHAR2(20))
        sqLiteDatabase.execSQL("create table user(_id INTEGER primary key autoincrement,username VARCHAR2(20),password VARCHAR2(20))");
    }

    //升级方法
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int old_version, int new_version) {

    }
}

• userDAO

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

//dao数据库访问对象
public class UserDao {

    private SQLiteDatabase sqLiteDatabase;

    public UserDao(Context context) {
        MySqliteHelper mySqliteHelper = new MySqliteHelper(context);
        sqLiteDatabase=mySqliteHelper.getWritableDatabase();
    }

    //插入用户的方法
    public boolean insertUser(String username,String password){

//        sqLiteDatabase.execSQL("insert into......v....");
        //插入数据进数据库表
        //1.表名字 2.表示如果发现将要插入的行为为空行时，会将这个列名的值设为null 3.要插入数据的Map集合
        //insert into user values(1,"zhangsan",""123456);
        ContentValues contentValues = new ContentValues();
        contentValues.put("username",username);
        contentValues.put("password",password);
        //返回值可以作为标识符，如果返回-1则标识插入失败
        long id =   sqLiteDatabase.insert("user",null,contentValues);

        return id!=-1;
    }

    /**查询用户
     * select * from user where username=xxx and password = xxx ;
     * @return
     */
    public boolean querryUser(String username,String password){

        //1.表明 2，返回的需要查询的列的名字集合 3，
//       Cursor cursor =  sqLiteDatabase.query("user",null,null,null,null,null,null,null);
//        List<String> cols = new ArrayList<>();
//        //游标的遍历，如果有下一行记录返回true，否则发挥false
//       while (cursor.moveToNext()){
//           //cursor.getString(1)根据列的索引获取值
//           String name = cursor.getString(1);
//           cols.add(name);
//       }
//        for (int i = 0; i < cols.size(); i++) {
//            Log.e("TAG",cols.get(i));
//        }
         
       Cursor cursor =  sqLiteDatabase.query(
       		"user", //1.表名 
       		null, //2，返回的需要查询的列的名字集合
     		"username = ? and password = ?", //3，查询的条件语句，值用？代替
       		new String[]{username,password}, // 4，第三个参数查询条件的？值的集合
       		null, //
       		null, //
       		null, //
       		null); //
       return  cursor.moveToNext();

    }

    /*
     update user set passsword=xxxxxxxx where username=xxxx and password=xxxx
     */
    public  boolean updateUser(String username,String oldPassword,String newPassword){

        ContentValues contentValues = new ContentValues();
        contentValues.put("password",newPassword);

        int number=sqLiteDatabase.update(
        "user",
        contentValues,
        "username = ? and password = ?",
        new String[]{username,oldPassword});


        return number>0;

    }

    /*
     delete from user where xxx=fff
     */
    public boolean deleteUser(String username){

        int number =sqLiteDatabase.delete(
        "user",
        "username=?",
        new String[]{username});
        return number>0;
    }
}

• MainActivity

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    private EditText etUsername;
    private EditText etPassword;
    private Button btnRegist;
    private Button btnLogin;
    private Button btnChange;
    private Button btnDelete;
    private UserDao userDao;
    private EditText etNewPwd;

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

//        MySqliteHelper mySqliteHelper = new MySqliteHelper(this);
        userDao = new UserDao(this);
//        userDao.querryUser("",""); //select *

        etUsername = findViewById(R.id.et_username);
        etPassword = findViewById(R.id.et_password);
        etNewPwd = findViewById(R.id.et_new_password);
        btnRegist = findViewById(R.id.btn_regist);
        btnLogin = findViewById(R.id.btn_login);
        btnChange = findViewById(R.id.btn_change);
        btnDelete = findViewById(R.id.btn_delete);

        btnRegist.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String username = etUsername.getText().toString();
                String password = etPassword.getText().toString();
                if (userDao.insertUser(username,password)){
                    Toast.makeText(MainActivity.this,"插入成功",Toast.LENGTH_LONG).show();
                }else {
                    Toast.makeText(MainActivity.this,"插入失败",Toast.LENGTH_LONG).show();
                }
            }
        });
        btnLogin.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String username = etUsername.getText().toString();
                String password = etPassword.getText().toString();
                if (userDao.querryUser(username,password)){
                    Toast.makeText(MainActivity.this,"登录成功",Toast.LENGTH_LONG).show();
                }else {
                    Toast.makeText(MainActivity.this,"登录失败",Toast.LENGTH_LONG).show();
                }
            }
        });
        btnChange.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String username = etUsername.getText().toString();
                String oldpPassword = etPassword.getText().toString();
                String newPwd=etNewPwd.getText().toString();

                if (userDao.updateUser(username,oldpPassword,newPwd)){
                    Toast.makeText(MainActivity.this,"修改成功",Toast.LENGTH_LONG).show();
                }else {
                    Toast.makeText(MainActivity.this,"修改失败",Toast.LENGTH_LONG).show();
                }
            }
        });
        btnDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String username = etUsername.getText().toString();
                if (userDao.deleteUser(username)){
                    Toast.makeText(MainActivity.this,"删除成功",Toast.LENGTH_LONG).show();
                }else {
                    Toast.makeText(MainActivity.this,"删除失败",Toast.LENGTH_LONG).show();
                }
            }
        });
    }
}