SQLiteOpenHelper与SQLiteDatabase

SQLiteOpenHelper与SQLiteDatabase

　　实现对SQLite数据库的简单操作,增删改查

SQL:

package com.example.sql_demo;

import android.Manifest;
import android.app.Activity;
import android.content.pm.PackageManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.os.Environment;
import android.support.annotation.NonNull;
import android.support.v4.app.ActivityCompat;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.TextUtils;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.CursorAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;

import java.io.File;
import java.security.Permission;

public class MainActivity extends AppCompatActivity {
    private static final String TAG = "MainActivity";
    public static final int REQUESTCODE = 1;
    String[] permision = {
            Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };
    private SQLiteDatabase sqLiteDatabase;
    private EditText et_name;
    private EditText et_age;
    private EditText et_id;
    private RadioGroup radioGroup;
    private RadioButton rb_man;
    private RadioButton rb_woman;
    private String sex ="男";
    private String name,age,id;
    private ListView listView;
    private TextView result;
    private SimpleCursorAdapter cursorAdapter;
    private View v;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        et_name = findViewById(R.id.et_name);
        et_age = findViewById(R.id.et_age);
        et_id = findViewById(R.id.et_id);
        radioGroup = findViewById(R.id.rg_sex);
        rb_man = findViewById(R.id.rb_man);
        rb_woman = findViewById(R.id.rb_woman);
        listView = findViewById(R.id.listView);
        v = getLayoutInflater().inflate(R.layout.header,null);
        listView.addHeaderView(v);
        radioGroup.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(RadioGroup radioGroup, int i) {
                if (i == R.id.rb_woman){
                    sex = "女";
                }else{
                    sex = "男";
                }
            }
        });
        /**
         * 数据库辅助类
         * 参数1 上下文  参数2 1.如果只有一个数据库名称,那么这个数据库就在app的私有路径下
         *                    2.如果带路径,那么数据库就会在指定路径下
         * 参数3 游标工厂 null会选择默认
         * 参数4 版本号 控制升级
         */
        if (TextUtils.equals(Environment.getExternalStorageState(),Environment.MEDIA_MOUNTED))
        {
            if (Build.VERSION.SDK_INT > Build.VERSION_CODES.LOLLIPOP_MR1) {
                for (String str : permision) {
                    if (ActivityCompat.checkSelfPermission(this, str) != PackageManager.PERMISSION_GRANTED) {
                        ActivityCompat.requestPermissions(this, permision, REQUESTCODE);
                    }
                }

            }
        }
        String path = Environment.getExternalStorageDirectory() + File.separator + "stu2.db";
        SQLiteOpenHelper helper = new SQLiteOpenHelper(this,path,null,1) {
            @Override
            public void onCreate(SQLiteDatabase sqLiteDatabase) {
                //创建方法
//                    Toast.makeText(MainActivity.this,"数据库创建",Toast.LENGTH_LONG).show();
                Log.e(TAG, "onCreate: " + "数据创建成功");
                String sql = "create table test2(" + "_id integer primary key autoincrement," + "name varchar(20) not null," +
                        "age integer," + "sex varchar(2) not null)" ;
                //创建数据库
                sqLiteDatabase.execSQL(sql);
                /**
                 * execSQL()  数据表的创建 数据修改 删除 添加
                 * rawQuery() 数据库查询
                 */

            }

            @Override
            public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
                //升级方法
                Toast.makeText(MainActivity.this,"数据库创建",Toast.LENGTH_LONG).show();
            }
        };
        //获取数据库操作对象  会判断指定数据库是否存在 1.存在 打开 2.不存在 创建   如果数据版本较高,则执行升级方法
        sqLiteDatabase = helper.getReadableDatabase();


    }
    public void onButtonClick(View view) {
        id = et_id.getText().toString();
        name = et_name.getText().toString();
       age = et_age.getText().toString();

        switch (view.getId()){
            case R.id.in_button:
                listView.removeHeaderView(v);
                //第一种写法直接加入参数
                /*String sql = "insert into stu(name,age,sex) values ('"+ et_name.getText().toString()+"',"+ et_age.getText().toString()+","+ sex+")";
                sqLiteDatabase.execSQL(sql);*/
                //第二种写法,加入数组
                String sql = "insert into test2(name,age,sex) values (?,?,?)";
                sqLiteDatabase.execSQL(sql,new String[]{name, age,sex});
                break;
            case R.id.sea_button:
                //select * from test where ...
                String sql2 = "select * from test2";
                if (!id.isEmpty()){
                    sql2 += " where _id=" + id;
                }
                //查询结果
                Cursor cursor = sqLiteDatabase.rawQuery(sql2,null);
                //参数2 适配器布局  参数3 数据库列名    参数4 对应资源id  参数 5  int类型 自动更新
                cursorAdapter = new SimpleCursorAdapter(this, R.layout.listview_item,cursor,
                        new String[]{"_id","name","age","sex"},
                        new int[]{R.id.id_item,R.id.name_item,R.id.age_item,R.id.sex_item}
                        ,CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);

                if (cursorAdapter.isEmpty()){
                    Toast.makeText(MainActivity.this,"数据不存在",Toast.LENGTH_LONG).show();
                }else {
                    listView.setAdapter(cursorAdapter);
                }
                break;
            case R.id.del_button:
                //删除
                String sql3 = "delete from test2 where _id=?";
                sqLiteDatabase.execSQL(sql3,new String[]{id});
                break;
            case R.id.upl__button:
                if (name.isEmpty()||age.isEmpty()){
                    Toast.makeText(MainActivity.this,"姓名和年龄不能为空",Toast.LENGTH_LONG).show();
                    break;
                }
                String sql4 = "update test2 set name=?, age=? ,sex=? where _id=?";
                sqLiteDatabase.execSQL(sql4,new String[]{name,age,sex,id});
                break;
        }
        et_id.setText("");
        et_name.setText("");
        et_age.setText("");

    }

    @Override
    public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
        super.onRequestPermissionsResult(requestCode, permissions, grantResults);
      /*  if (requestCode == REQUESTCODE){

        }*/

    }
}

直接使用SQLiteDatabase提供的方法进行增删改查操作

package com.example.sql_demo;

import android.Manifest;
import android.app.Activity;
import android.content.ContentValues;
import android.content.pm.PackageManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.os.Environment;
import android.support.annotation.NonNull;
import android.support.v4.app.ActivityCompat;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.TextUtils;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.CursorAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;

import java.io.File;
import java.security.Permission;

public class Main2Activity extends AppCompatActivity {
    private static final String TAG = "MainActivity";
    public static final int REQUESTCODE = 1;
    String[] permision = {
            Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };
    private SQLiteDatabase sqLiteDatabase;
    private EditText et_name;
    private EditText et_age;
    private EditText et_id;
    private RadioGroup radioGroup;
    private RadioButton rb_man;
    private RadioButton rb_woman;
    private String sex ="男";
    private String name,age,id;
    private ListView listView;
    private TextView result;
    private SimpleCursorAdapter cursorAdapter;
    private View v;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        et_name = findViewById(R.id.et_name);
        et_age = findViewById(R.id.et_age);
        et_id = findViewById(R.id.et_id);
        radioGroup = findViewById(R.id.rg_sex);
        rb_man = findViewById(R.id.rb_man);
        rb_woman = findViewById(R.id.rb_woman);
        listView = findViewById(R.id.listView);
        v = getLayoutInflater().inflate(R.layout.header,null);
        listView.addHeaderView(v);
        radioGroup.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(RadioGroup radioGroup, int i) {
                if (i == R.id.rb_woman){
                    sex = "女";
                }else{
                    sex = "男";
                }
            }
        });
        /**
         * 数据库辅助类
         * 参数1 上下文  参数2 1.如果只有一个数据库名称,那么这个数据库就在app的私有路径下
         *                    2.如果带路径,那么数据库就会在指定路径下
         * 参数3 游标工厂 null会选择默认
         * 参数4 版本号 控制升级
         */
        if (TextUtils.equals(Environment.getExternalStorageState(),Environment.MEDIA_MOUNTED))
        {
            if (Build.VERSION.SDK_INT > Build.VERSION_CODES.LOLLIPOP_MR1) {
                for (String str : permision) {
                    if (ActivityCompat.checkSelfPermission(this, str) != PackageManager.PERMISSION_GRANTED) {
                        ActivityCompat.requestPermissions(this, permision, REQUESTCODE);
                    }
                }

            }
        }
        String path = Environment.getExternalStorageDirectory() + File.separator + "stu2.db";
        SQLiteOpenHelper helper = new SQLiteOpenHelper(this,path,null,1) {
            @Override
            public void onCreate(SQLiteDatabase sqLiteDatabase) {
                //创建方法
//                    Toast.makeText(MainActivity.this,"数据库创建",Toast.LENGTH_LONG).show();
                Log.e(TAG, "onCreate: " + "数据创建成功");
                String sql = "create table test2(" + "_id integer primary key autoincrement," + "name varchar(20) not null," +
                        "age integer," + "sex varchar(2) not null)" ;
                //创建数据库
                sqLiteDatabase.execSQL(sql);
                /**
                 * execSQL()  数据表的创建 数据修改 删除 添加
                 * rawQuery() 数据库查询
                 */

            }

            @Override
            public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
                //升级方法
                Toast.makeText(Main2Activity.this,"数据库创建",Toast.LENGTH_LONG).show();
            }
        };
        //获取数据库操作对象  会判断指定数据库是否存在 1.存在 打开 2.不存在 创建   如果数据版本较高,则执行升级方法
        sqLiteDatabase = helper.getReadableDatabase();


    }
    public void onButtonClick(View view) {
        id = et_id.getText().toString();
        name = et_name.getText().toString();
        age = et_age.getText().toString();

        switch (view.getId()){
            case R.id.in_button:
                /**
                 * 在SqLiteDatabase类下提供了四个操作数据库的方法  可以不用写sql语句
                 * insert  添加    delete  删除
                 * update  修改    query   查询
                 */
                //相当于HashMap  key  value
                ContentValues values = new ContentValues();
                //参数1 要插入数据的列名  参数2  插入的数据
                values.put("name",name);
                values.put("age",age);
                values.put("sex",sex);

                //参数1 数据库表名
                // 参数2 可以为空的列  自动补全 null
                // 参数3 ContentValues类对象(插入数据)
                //返回数据表示当前添加数据的id编号
                long id2 = sqLiteDatabase.insert("test2",null,values);
                break;
            case R.id.sea_button:
                /**
                 * 参数1  表名   参数2 所查询的列名 {"name","age"} 查询所有传入null或{"*"}
                 * 参数3 参数4  表示查询条件  例如"name=?and age=?",new String[]{"HEJUN","23"}
                 * 参数5 分组
                 * 参数6 通过having去除不符合条件的组
                 * 参数7 排序
                 * --聚合函数:用于统计数据的函数
                 --count（列名）统计总数
                 统计各个年龄段的人数 按年龄分组 年龄大于18 降序排序
                 select count(*),age from test2 group by age having age>18 order by age desc
                 */
                Cursor cursor = sqLiteDatabase.query("test2",null,null,null,null,null,null);
                SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,R.layout.listview_item,cursor,
                        new String[]{"_id","name","age","sex"},
                        new int[]{R.id.id_item,R.id.name_item,R.id.age_item,R.id.sex_item},
                        CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
                if (adapter.isEmpty()){
                    Toast.makeText(this,"数据不存在",Toast.LENGTH_LONG).show();
                }else {
                    listView.setAdapter(adapter);
                }
                break;
            case R.id.del_button:
                //返回数据是删除数据的数目
                int count = sqLiteDatabase.delete("test2","_id=?",new String[]{id});
                if (count < 0){
                    Toast.makeText(this,"数据不存在",Toast.LENGTH_LONG).show();
                }else {
                    Toast.makeText(this,"删除成功",Toast.LENGTH_LONG).show();
                }
                break;
            case R.id.upl__button:
                ContentValues values1 = new ContentValues();
                values1.put("name",name);
                values1.put("age",age);
                values1.put("sex",sex);
                sqLiteDatabase.update("test2",values1,"_id=?",new String[]{id});
                break;
        }
        et_id.setText("");
        et_name.setText("");
        et_age.setText("");

    }

    @Override
    public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
        super.onRequestPermissionsResult(requestCode, permissions, grantResults);
      /*  if (requestCode == REQUESTCODE){

        }*/

    }
}

　　尽管SQLiteDatabase提供了轻便的操作方法,但SQL语句一个程序员的必备技能哦,不要一味的依赖.