1.SQLite
1:体积小,主要使用于嵌入式设备。
2:跨平台性。
3:使用方便,直接解压便可以使用。
4:多线程操作性能差,对SQL支持不全面。
2.SQLite创建数据库
public class MyOpenHelper extends SQLiteOpenHelper {
public MyOpenHelper(Context context){
this(context,"MySQLite.db",null,1);
}
public MyOpenHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table user(_id integer " +
"primary key autoincrement,name varchar(20)," +
"phone varchar(20))");
System.out.println("on Create");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("alter table user add age integer");
System.out.println("oldVersion" + oldVersion);
System.out.println("newVersion" + newVersion);
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
System.out.println("数据库降级");
}
}
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
}
}
使用上述程序后,会生成数据库文件,一行有两个文件。其中-journal是日志文件,一个是存储数据的文件。
3.SQLite基础增删改查
public void insert(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String sql = "insert into user(name,phone) values ('张三','111111')";
readableDatabase.execSQL(sql);
sql = "insert into user(name,phone) values ('lisi','000000')";
readableDatabase.execSQL(sql);
readableDatabase.close();
}
public void query(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String sql = "select * from user";
Cursor cursor = readableDatabase.rawQuery(sql, null);
while(cursor.moveToNext()){
String name = cursor.getString(1);
System.out.println("name" + name);
String phone = cursor.getString(cursor.getColumnIndex("phone"));
System.out.println("phone" + phone);
}
readableDatabase.close();
}
public void delete(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String sql = "delete from user where _id = 1";
readableDatabase.execSQL(sql);
readableDatabase.close();
}
public void update(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String sql = "update user set name = 'wangwu' where _id = 2";
readableDatabase.execSQL(sql);
readableDatabase.close();
}
4.使用定义好的方法进行数据的增删改查操作
public void delete1(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String table = "user";
String whereClause = "_id = ?";
String[] whereArgs = {"5"};
int delete = readableDatabase.delete(table, whereClause, whereArgs);
Toast.makeText(this, delete + "", Toast.LENGTH_SHORT).show();
readableDatabase.close();
}
public void query1(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String table = "user";
String[] columns = {"name"};
String selection = "_id = ?";
String[] selectionArgs = {"4"};
String groupBy = null;
String having = null;
String orderBy = null;
String limit = null;
Cursor query = readableDatabase.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
while(query.moveToNext()){
String name = query.getString(query.getColumnIndex("name"));
System.out.println(name);
}
query.close();
readableDatabase.close();
}
public void update1(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String table = "user";
ContentValues values = new ContentValues();
values.put("name", "zhaoliu99999");
String whereClause = "_id = ?";
String[] whereArgs = {"4"};
int update = readableDatabase.update(table, values, whereClause, whereArgs);
System.out.print(update);
readableDatabase.close();
}
public void insert1(View v){
SQLiteDatabase readableDatabase = myOpenHelper.getReadableDatabase();
String table = "user";
String nullColumnHack = "name";
ContentValues values = new ContentValues();
values.put("name", "zhaoliu");
values.put("phone", "110");
long num = readableDatabase.insert(table, nullColumnHack, values);
System.out.println("num" + num);
readableDatabase.close();
}
5.事务模板
public void transaction(View v){
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
db.beginTransaction();
try {
int i = 1/0;
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}