学会使用SQLite的用法
(一)什么是SQLite?
概念:
①一种轻量级的数据库;
②占用资源低,只需要几百K的内存;
③不需要用户名和密码就可以使用,存在不安全性。
(二)如何使用SQLite?
步骤:
1.创建数据库和数据表
- 新建类继承SQLiteOpenHelper
- 实现构造方法
- 重写onCreate方法
- 重写onUpgrade方法
- 创建数据表语句
2.MainActivity中实例化SQLiteOpenHelper的子类对象
3.根据需求调用getReadableDatabase方法 或 getWritableDatabase方法
(三)SQLite使用实例
创建数据库实现添加数据,查找数据,修改数据以及删除数据。
Step1: 创建数据库和数据表
public class DBHelper extends SQLiteOpenHelper {
//建表语句
private String createSQL = "create table student(" +
"id integer primary key autoincrement not null," +
"name text not null," +
"gender text ," +
"age integer)";
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(createSQL);//创建数据表结构语句
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
//实现升级数据库
}
}
Step2: 实例化SQLiteOpenHelper的子类对象
dbHelper=new DBHelper(this,"SQLName",null,1);
Step3: 添加数据
String name =nameEdit.getText().toString();
//获得SQLiteDatabase对象,读写模式
SQLiteDatabase sqldb1=dbHelper.getWritableDatabase();
//ContentValues类似HashMap,ContentValues只能存简单数据类型,不能存对象
ContentValues values=new ContentValues();
values.put("name",name);//存放数据
sqldb1.insert("student",null,values); //执行插入操作
Step4: 查找数据
//获得SQLiteDatabase对象,读写模式
SQLiteDatabase sqldb2=dbHelper.getReadableDatabase();
//创建游标
Cursor cursor=sqldb2.query("student",new String[]{"id","name","gender","age"},null,null,null,null,null);
cursor.moveToFirst();
do{
String name2=cursor.getString(cursor.getColumnIndex("name"));
Log.e("MAIN",name2);
}while (cursor.moveToNext());
cursor.close();
Step5: 修改数据
String name1 =nameEdit.getText().toString();
String newName=genderEdit.getText().toString();
SQLiteDatabase sqldb3=dbHelper.getWritableDatabase();
ContentValues values3=new ContentValues();
//放进新的数据
values3.put("name",newName);
//参数1:数据表名;参数2:修改的值;参数3:读取的是"name"的那一列;参数4:占位符的填充值
sqldb3.update("student",values3,"name=?",new String[]{name1});
Step6: 删除数据
SQLiteDatabase sqldb4=dbHelper.getWritableDatabase();
String name2 =nameEdit.getText().toString();
sqldb4.delete("student","name=?",new String[]{name2} );
代码演示
DBHelper类
public class DBHelper extends SQLiteOpenHelper {
//建表语句
private String createSQL = "create table student(" +
"id integer primary key autoincrement not null," +
"name text not null," +
"gender text ," +
"age integer)";
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(createSQL);//创建数据表结构语句
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
//实现升级数据库
}
}
MainActivity类
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private EditText nameEdit;
private EditText genderEdit;
private Button addbtn;
private Button findbtn;
private Button changebtn;
private Button deletebtn;
private DBHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
bindID();
dbHelper=new DBHelper(this,"SQLName",null,1);
}
private void bindID() {
nameEdit = findViewById(R.id.main_name_et);
genderEdit = findViewById(R.id.main_gender_et);
addbtn = findViewById(R.id.main_addbtn);
findbtn = findViewById(R.id.main_findbtn);
changebtn = findViewById(R.id.main_changebtn);
deletebtn = findViewById(R.id.main_deletebtn);
addbtn.setOnClickListener(this);
findbtn.setOnClickListener(this);
changebtn.setOnClickListener(this);
deletebtn.setOnClickListener(this);
}
@Override
public void onClick(View view) {
switch (view.getId()) {
case R.id.main_addbtn:
String name =nameEdit.getText().toString();
SQLiteDatabase sqldb1=dbHelper.getWritableDatabase(); //获得SQLiteDatabase对象,读写模式
ContentValues values=new ContentValues();//ContentValues类似HashMap,区别是ContentValues只能存简单数据类型,不能存对象
values.put("name",name);//存放数据
sqldb1.insert("student",null,values); //执行插入操作
break;
case R.id.main_findbtn:
//获得SQLiteDatabase对象,读写模式
SQLiteDatabase sqldb2=dbHelper.getReadableDatabase();
//创建游标
Cursor cursor=sqldb2.query("student",new String[]{"id","name","gender","age"},null,null,null,null,null);
cursor.moveToFirst();
do{
String name2=cursor.getString(cursor.getColumnIndex("name"));
Log.e("MAIN",name2);
}while (cursor.moveToNext());
cursor.close();
break;
case R.id.main_changebtn:
String name1 =nameEdit.getText().toString();
String newName=genderEdit.getText().toString();
SQLiteDatabase sqldb3=dbHelper.getWritableDatabase();
ContentValues values3=new ContentValues();
//放进新的数据
values3.put("name",newName);
//参数1:数据表名;参数2:修改的值;参数3:读取的是"name"的那一列;参数4:占位符的填充值
sqldb3.update("student",values3,"name=?",new String[]{name1});
break;
case R.id.main_deletebtn:
SQLiteDatabase sqldb4=dbHelper.getWritableDatabase();
String name2 =nameEdit.getText().toString();
sqldb4.delete("student","name=?",new String[]{name2} );
break;
}
}
}