Android SQLite数据库
SQLite数据库
SQLiteDatabase //管理操作数据库
管理
openDatabase //打开
openOrCreateDatabase //打开或创建
操作 增删改查
execSQL //执行SQL语句
insert //增
update //改
delete //删
query //查
rawQuery //sql语句查
事物
beginTransaction //开始
endTransaction //结束
Cursor //查询结果
move
SQLiteOpenHelper //管理数据库和版本更新
onCreate //第一次创建数据库回调
onUpgrade //数据库版本更新时回调
getReadableDatabase //读的方式打开
getWritableDatabase //写的方式打开
close //关闭所有打开的SQLiteDatabase
Demo1 插入和查询数据
public class DBTest extends Activity { SQLiteDatabase db; Button bn = null; ListView listView; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); initTitle(); initData(); initView(); } public void initTitle() { } public void initData() { db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString() + "/my.db3", null); try { db.execSQL("create table news_inf(_id integer" + " primary key autoincrement," + " news_title varchar(50)," + " news_content varchar(255))"); } catch (Exception e) { Log.i("TAG", "e---" + e); } } public void initView() { listView = (ListView) findViewById(R.id.show); bn = (Button) findViewById(R.id.ok); queryData(); bn.setOnClickListener(new OnClickListener() { @Override public void onClick(View source) { String title = ((EditText) findViewById(R.id.title)).getText().toString(); String content = ((EditText) findViewById(R.id.content)).getText().toString(); try { // 插入数据 insertData(db, title, content); queryData(); } catch (SQLiteException se) { } } }); } // 插入数据 private void insertData(SQLiteDatabase db, String title, String content) { // 执行插入语句 db.execSQL("insert into news_inf values(null , ? , ?)", new String[] { title, content }); } // 查询数据 private void queryData() { Cursor cursor = db.rawQuery("select * from news_inf", null); SimpleCursorAdapter adapter = new SimpleCursorAdapter(DBTest.this, R.layout.line, cursor, new String[] { "news_title", "news_content" }, new int[] { R.id.my_title, R.id.my_content }, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER); listView.setAdapter(adapter); } @Override public void onDestroy() { super.onDestroy(); // 退出程序时关闭SQLiteDatabase if (db != null && db.isOpen()) { db.close(); } } }
Demo2 单词本
public class Dict extends Activity implements View.OnClickListener{ MyDatabaseHelper dbHelper; Button insert = null; Button search = null; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); initTitle(); initData(); initView(); } public void initTitle() { } public void initData() { // 创建MyDatabaseHelper对象，指定数据库版本为1，此处使用相对路径即可， // 数据库文件自动会保存在程序的数据文件夹的databases目录下。 dbHelper = new MyDatabaseHelper(this, "myDict.db3", 1); } public void initView() { insert = (Button) findViewById(R.id.insert); search = (Button) findViewById(R.id.search); insert.setOnClickListener(this); search.setOnClickListener(this); } @Override public void onClick(View v) { switch (v.getId()) { case R.id.insert:// 插入 String word = ((EditText) findViewById(R.id.word)).getText().toString(); String detail = ((EditText) findViewById(R.id.detail)).getText().toString(); insertData(dbHelper.getReadableDatabase(), word, detail); break; case R.id.search:// 搜索 // 获取用户输入 String key = ((EditText) findViewById(R.id.key)).getText().toString(); // 执行查询 Cursor cursor = dbHelper.getReadableDatabase().rawQuery( "select * from dict where word like ? or detail like ?", new String[] { "%" + key + "%", "%" + key + "%" }); Bundle data = new Bundle(); data.putSerializable("data", converCursorToList(cursor)); Intent intent = new Intent(Dict.this, ResultActivity.class); intent.putExtras(data); startActivity(intent); break; default: break; } } //插入 private void insertData(SQLiteDatabase db, String word, String detail) { db.execSQL("insert into dict values(null , ? , ?)", new String[] { word, detail }); Toast.makeText(Dict.this, "添加生词成功！", 8000).show(); } //查询结果转集合 protected ArrayList<Map<String, String>> converCursorToList(Cursor cursor) { ArrayList<Map<String, String>> result = new ArrayList<Map<String, String>>(); // 遍历Cursor结果集 while (cursor.moveToNext()) { // 将结果集中的数据存入ArrayList中 Map<String, String> map = new HashMap<String, String>(); // 取出查询记录中第2列、第3列的值 map.put("word", cursor.getString(1)); map.put("detail", cursor.getString(2)); result.add(map); } return result; } @Override public void onDestroy() { super.onDestroy(); // 退出程序时关闭MyDatabaseHelper里的SQLiteDatabase if (dbHelper != null) { dbHelper.close(); } } }
public class MyDatabaseHelper extends SQLiteOpenHelper { final String CREATE_TABLE_SQL = "create table dict(_id integer primary " + "key autoincrement , word , detail)"; public MyDatabaseHelper(Context context, String name, int version) { super(context, name, null, version); } @Override public void onCreate(SQLiteDatabase db) { // 第一次使用数据库时自动建表 db.execSQL(CREATE_TABLE_SQL); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { System.out.println("--------onUpdate Called--------" + oldVersion + "--->" + newVersion); } }
public class ResultActivity extends Activity { List<Map<String, String>> list; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.popup); initTitle(); initData(); initView(); } public void initTitle() { } public void initData() { list = (List<Map<String, String>>) getIntent().getExtras().getSerializable("data"); } public void initView() { ListView listView = (ListView) findViewById(R.id.show); SimpleAdapter adapter = new SimpleAdapter(ResultActivity.this, list, R.layout.line, new String[] { "word", "detail" }, new int[] { R.id.word, R.id.detail }); listView.setAdapter(adapter); } }
