一、 SQLite 是一个开源的嵌入式关系数据库。
二、特点:零配置、可移植、紧凑、简单、可靠
三、支持五种数据类型:NULL 、 INTEGER 、 REAL 、 TEXT 和 BLOB 数据类型。
四、我主要将就是数据库的操作方法
1、写一个类继承SQLiteOpenHelper。
public class MySqliteHelper extends SQLiteOpenHelper {
private final static String name = "test.db";
private final static int version = 1;
//进行数据库的创建
public MySqliteHelper(Context context) {
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
//在数据库中创建User表
String createsql = "create table User(username varchar(20) primary key,password varchar(20));";
db.execSQL(createsql);
}
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
}
}
3、进行数据库的数据操作
1、首先要对MySqliteHelper和SQLiteDatabase声明和初始化。
private MySqliteHelper mysqliHelper;
private SQLiteDatabase db;
// 对SqliteOpenHelper以及SQLiteDatabase进行初始化
mysqliHelper = new MySqliteHelper(SqliteActivity.this);
db = mysqliHelper.getReadableDatabase();
2、有两种方法,第一种是执行sqlite语句( db.execSQL())。第二种使用SQLiteDatabase的方法去操作(db.inser())
第一种:
增(insert)
String addsql = "insert into User (username,password) values(?,?)";
db.execSQL(addsql, new Object[] { usernamestr, usernamestr });
删(delete)
String deletesql = "delete from User where username =
'"+ usernamestr + "';";
db.execSQL(deletesql);
改(updata)
String updatasql = "update User set password =
'" + passwordstr+ "'
where username =
'" + usernamestr + "';";
db.execSQL(updatasql);
查(query)
StringBuffer result = new StringBuffer();
String querysql = "select * from user";
Cursor cursor = db.rawQuery(querysql, null);
if (cursor.moveToFirst()) {
for (int i = 0; i < cursor.getCount(); i++) {
cursor.moveToPosition(i);
String name = cursor.getString(cursor
.getColumnIndex("username"));
String pwd = cursor.getString(cursor
.getColumnIndex("password"));
result.append("name : ").append(name).append(" pwd : ")
.append(pwd).append("\n");
}
}
第二种:
增(insert)
ContentValues cv = new ContentValues();
cv.put("username", username.getText().toString().trim());
cv.put("password", password.getText().toString().trim());
db.insert("User",null, cv);
删(delete)
db.delete("User", "username = ?",
new String[] { username.getText().toString().trim() });
db.close();
改(updata)
ContentValues cv = new ContentValues();
cv.put("username","LQ");
db.update("User", values, "password=?", new String[]{"123"});
查(query)
Cursor cursor = db.query("person", new String[]{"personid,name,age"}, "name like ?", new String[]{"%xxxx%"}, null, null, "personid desc", "1,2");
while (cursor.moveToNext()) {
int personid = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
String name = cursor.getString(1);//获取第二列的值
int age = cursor.getInt(2);//获取第三列的值
}
cursor.close();
db.close();
五、使用事务操作SQLite数据库
SQLiteDatabase db = ....;
db.beginTransaction();//开始事务
try {
db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"xxxx", 4});
db.execSQL("update person set name=? where personid=?", new Object[]{"xxxx", 1});
db.setTransactionSuccessful();//调用此方法会在执行到endTransaction() 时提交当前事务,如果不调用此方法会回滚事务
} finally {
db.endTransaction();//由事务的标志决定是提交事务,还是回滚事务
}
db.close();