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语句一个程序员的必备技能哦,不要一味的依赖.