SQlite数据库
简介
提起数据库,那种类可太多了Oracle、MySQL、SQLSever、DB2...
接下来我们要学习的是Android SDK自带的数据库SQlite,发音:[sk'laɪt] 。
SQlite数据库特点也很多:轻量级、跨平台、多语言接口等。
相关接口、类
一.SQLiteOpenHelper
A helper class to manage database creation and version management.
说明:用来用来管理数据库(增、删、改、查)创建和版本更新的
相关方法
- getWritableDatabase()
- getReadableDatabase()
二.SQLiteDatabase
Exposes methods to manage a SQLite database.
相关方法
方式一
- execSQL(String SQL):执行增、删、改的操作
- rawQuery(String SQL):执行查的操作
方式二 - insert()
- delete()
- update()
- query()
三.Cursor
This interface provides random read-write access to the result set returned by a database query.
常用方法
- moveToNext()
- getInt()
- getString()
- getColumnIndex()
- close()
四.事务
常用方法
- beginTransaction()
- setTransactionSuccessful()
- endTransaction()
使用
public void addAll(){
ArrayList<User> users1 = new ArrayList<>();
users1.add(new User("小红",20,1,new Date()));
users1.add(new User("小花",21,1,new Date()));
users1.add(new User("小白",22,0,new Date()));
database.beginTransaction();
for(User u: users1){
ContentValues values= new ContentValues();
values.put("uname",u.name);
values.put("age",u.age);
values.put("sex",u.sex);
values.put("birthday", String.valueOf(u.birthday));
database.insert("userInfo",null,values);
}
database.setTransactionSuccessful();
database.endTransaction();
}
五.详细代码
1.继承SQLiteOpenHelper重写方法
public class MyDbHelper extends SQLiteOpenHelper {
public MyDbHelper(@Nullable Context context) {
super(context, "user.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE userInfo(uid integer PRIMARY KEY autoincrement,uname varchar(8),age INT,sex int,birthday date);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2.获得SQLiteDatabase对象操作数据
public class DatabaseActivity extends AppCompatActivity {
SQLiteDatabase database;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_database);
MyDbHelper helper = new MyDbHelper(this);
database = helper.getWritableDatabase();
}
public void insert(){
String[] info = new String[]{"20","0","2020-6-11"};
//方式一
database.execSQL("INSERT INTO userInfo(uname,age,sex,birthday)VALUES(?,?,?,?)",info);
//方式二
ContentValues values = new ContentValues();
values.put("age",25);
values.put("sex",1);
values.put("birthday","2020-6-11");
values.put("uname","戴鑫");
database.insert("userInfo",null,values);
}
public void delete(){
String[] strings = new String[]{"20","1"};
//方式一
database.execSQL("DELETE FROM userInfo WHERE age=?",strings);
//方式二
database.delete("userInfo","age=? or sex=?",strings);
}
public void update(){
String sql = "update userInfo set age = ? where age = ? or uname=?";
String[] strings = new String[]{"25","戴鑫"};
//方式一
database.execSQL(sql,strings);
ContentValues contentValues = new ContentValues();
contentValues.put("age",35);
contentValues.put("uname","戴金");
//方式二
database.update("userInfo",contentValues,"age=? or uname=?",strings);
}
public void select(){
//方式一
Cursor cursor = database.rawQuery("select * from userInfo",null);
//方式二
Cursor cursor1 = database.query("userInfo",null,null,null,null,null,null);
while (cursor.moveToNext()){
int id = cursor.getInt(cursor.getColumnIndex("uid"));
String name = cursor.getString(cursor.getColumnIndex("uname"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
int sex = cursor.getInt(cursor.getColumnIndex("sex"));
String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
Log.d("amy","--"+id+name+age+sex+birthday);
}
}
}
六.UserDAO封装
/**
* 操作数据库的工具类
*/
public class UserDAO {
SQLiteDatabase database;
String table;
private static UserDAO userDAO = null;
private UserDAO(){
}
public static UserDAO getInstance(SQLiteDatabase database,String table){
if(userDAO == null){
userDAO = new UserDAO(database,table);
return userDAO;
}
else
return userDAO;
}
private UserDAO(SQLiteDatabase database,String table){
this.database = database;
this.table = table;
}
public long add(User user){
ContentValues values = new ContentValues();
values.put("uname",user.name);
values.put("age",user.age);
values.put("sex",user.sex);
values.put("birthday",user.birthday.toString());
return database.insert(table,null,values);
}
/**
* 批量增加的时候一般用到事务
* @param users
*/
public void addAll(ArrayList<User> users){
//开启事务
database.beginTransaction();
for(User user:users){
ContentValues values = new ContentValues();
values.put("uname",user.name);
values.put("age",user.age);
values.put("sex",user.sex);
values.put("birthday",user.birthday.toString());
database.insert(table,null,values);
}
//设置事务成功
database.setTransactionSuccessful();
//结束事务
database.endTransaction();
}
public void delete(User u){
database.delete(table,"uid=?",new String[]{u.id+""});
}
public void updateUser(User user){
ContentValues values = new ContentValues();
values.put("uname",user.name);
database.update(table,values,"uid = ?",new String[]{user.id+""});
}
public void select(){
Cursor cursor = database.query(table,null,null,null,null,null,null);
while (cursor.moveToNext()){
//cursor.getInt(0);
int id = cursor.getInt(cursor.getColumnIndex("uid"));
String name = cursor.getString(cursor.getColumnIndex("uname"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
String sex = cursor.getString(cursor.getColumnIndex("sex"));
String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
Log.d("amy",id+"-"+name+"-"+age+"-"+sex+"-"+birthday);
}
}
}
User类
public class User {
public int id;
public String sex;
public String name;
public Date birthday;
public int age;
public User(String name ,int age,String sex, Date birthday) {
this.sex = sex;
this.name = name;
this.birthday = birthday;
this.age = age;
}
public User(){
}
}