13 SQLiteOpenHelper SQLiteDatabase详解
创建数据库:
1. 创建一个类继承SQLiteOpenHelper
2. 创建继承对象 new SQLiteOpenHelper()
3. 用创建的对象获取可写或者可读的SQLiteDatabase
4. 用得到的SQLiteDatabase执行sql语句
* 手动写sql语句
* 调用内置的sql语句API
步骤1详解:
*一个继承的SQLiteOpenHelper类```java package com.fmy.sql.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class MyOpenHelper extends SQLiteOpenHelper { static final String DPNAME = "info.db"; static final int VERSION = 1; public MyOpenHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } public MyOpenHelper(Context context) { super(context, DPNAME, null, VERSION); } @Override public void onCreate(SQLiteDatabase db) { Log.e("fmy", "被创建了"); } @Override public void onOpen(SQLiteDatabase db) { // TODO Auto-generated method stub super.onOpen(db); Log.e("fmy", "数据库被打开了"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } } ```
- 步骤2详解:
*创建继承对象 new SQLiteOpenHelper()
MyOpenHelper myOpenHelper = new MyOpenHelper(this);- 步骤3详解:
*用创建的对象获取可写或者可读的SQLiteDatabase
SQLiteDatabase db = myOpenHelper.getReadableDatabase();- 用得到的SQLiteDatabase执行sql语句
- 手动写sql语句执行
package com.fmy.sql;
import com.fmy.sql.db.MyOpenHelper;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
public void create(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "创建表");
db.execSQL("create table if not exists person(_id INTEGER primary key autoincrement,name varchar,age int)");
db.close();
myOpenHelper.close();
}
public void insert(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "创建表");
String sql = "insert into person (name,age) values()";
for (int i = 0; i < 50; i++) {
db.execSQL("insert into person (name,age) values(" + "'张三" + i + "'" + "," + i + ")");
Log.e("fmy", "创建表");
}
db.close();
myOpenHelper.close();
}
public void update(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "跟新表");
String sql = "UPDATE person SET name = '李四' WHERE _id=1";
db.execSQL(sql);
db.close();
myOpenHelper.close();
}
public void delete(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "创建表");
String sql = "DELETE FROM person WHERE _id=2";
db.execSQL(sql);
db.close();
myOpenHelper.close();
}
public void select(View v) {
Log.e("fmy", "查询");
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
String sql = "select *from person";
Cursor cursor = db.rawQuery(sql,null);
while (cursor.moveToNext()){
String name = cursor.getString(cursor.getColumnIndex("name"));
Log.e("fmy", "查询"+name);
}
db.close();
myOpenHelper.close();
}
}
```
* API 增删改查
```java
package com.fmy.dbapi;
import com.fmy.dbapi.db.MyOpenHelper;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
public void create(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "创建表");
db.execSQL("create table if not exists person(_id INTEGER primary key autoincrement,name varchar,age int)");
db.close();
myOpenHelper.close();
}
public void insert(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "插入表");
String sql = "insert into person (name,age) values()";
ContentValues value = new ContentValues();
for (int i = 0; i < 50; i++) {
value.put("name", "张三" + i);
long insert = db.insert("person", null, value);
Log.e("fmy", "插入表" + insert);
}
db.close();
myOpenHelper.close();
}
public void update(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
ContentValues values = new ContentValues();
values.put("name", "李四");
int update = db.update("person", values, "_id=?", new String[] { "1" });
Log.e("fmy", "跟新表" + update);
db.close();
myOpenHelper.close();
}
public void delete(View v) {
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Log.e("fmy", "创建表");
db.delete("person", "_id=?", new String[] { "2" });
db.close();
myOpenHelper.close();
}
public void select(View v) {
Log.e("fmy", "查询");
MyOpenHelper myOpenHelper = new MyOpenHelper(this);
SQLiteDatabase db = myOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", null, null, null, null, null, null, "1,2");
while (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
Log.e("fmy", "查询" + name);
}
db.close();
myOpenHelper.close();
}
}
