SQLiteOpenHelper
SQLiteOpenHelper是android提供的一个管理数据库的工具类，可用于管理数据库的创建和版本更新。
一般的用法是创建SQLiteOpenHelper的子类，并扩展它的onCreate(SQLiteDatabase db)和onUpgrade(SQLiteDatabase db,int oldVersion,newVersion)方法。
SQLiteOpenHelper包含如下常用方法：
| sychronized SQLiteDatabase getReadableDatabase() | 以读写的方式打开数据库对应的SQLiteDataBase对象 |
| sychronized SQLiteDatabase getWriteableDataBase() | 以写的方式打开数据库对应的SQLiteDataBase对象 |
| abstract void onCreate(SQLiteDatabase db) | 当第一次创建数据库时回调该方法 |
| abstract void onUpgrade(SQLiteDatabase db,int oldVersion,newVersion) | 当数据库版本更新时回调该方法 |
| sychronized void close() | 关闭所有打开的SQLiteDatabase |
实例如下：
布局文件==》main
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity" >
<EditText
android:id="@+id/edit1"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/edit2"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<Button
android:id="@+id/btnInsert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="添加生词" />
<EditText
android:id="@+id/edit3"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<Button
android:id="@+id/btnQuery"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询" />
</LinearLayout>
result.xml==>
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<ListView
android:id="@+id/lv"
android:layout_width="match_parent"
android:layout_height="match_parent" />
<EditText
android:id="@+id/word"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/detial"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
</LinearLayout>
代码实现==》
package com.example.mysqlite2;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class MyDatabaseHelper extends SQLiteOpenHelper
{
final String CREATE_TABLE_SQL = "create table dict(_id integer primary key autoincrement,word,detial)";
public MyDatabaseHelper(Context context, String name, int version)
{
super(context, name, null, version);
Log.i("swg", "-----------MyDatabaseHelper------------");
}
@Override
public void onCreate(SQLiteDatabase db)
{
// 第一个使用数据库时自动见表
db.execSQL(CREATE_TABLE_SQL);
Log.i("swg", "-----------创建表成功------------");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
System.out
.println("-----------onUpgrade----------" + oldVersion + "-------->" + newVersion);
}
}
package com.example.mysqlite2;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity
{
MyDatabaseHelper dbHelper;
EditText edit1;
EditText edit2;
EditText edit3;
String dbName = "test.db3";
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建MyDatabaseHelper对象，指定数据库版本为1，此处使用相对路径
// 数据库文件会自动保存在程序的数据文件夹下的databases目录下
dbHelper = new MyDatabaseHelper(this, dbName, 1);
edit1 = (EditText) this.findViewById(R.id.edit1);
edit2 = (EditText) this.findViewById(R.id.edit2);
edit3 = (EditText) this.findViewById(R.id.edit3);
Button btnInsert = (Button) this.findViewById(R.id.btnInsert);
Button btnQuery = (Button) this.findViewById(R.id.btnQuery);
btnInsert.setOnClickListener(new MyButtonOnClick());
btnQuery.setOnClickListener(new MyButtonOnClick());
}
private class MyButtonOnClick implements OnClickListener
{
@Override
public void onClick(View v)
{
switch (v.getId())
{
case R.id.btnInsert:
String word = edit1.getText().toString();
String detial = edit2.getText().toString();
Log.i("swg", "insert content==" + word + "=========" + detial);
// 插入生词记录
insertData(dbHelper.getReadableDatabase(), word, detial);
Toast.makeText(MainActivity.this, "添加生词成功", Toast.LENGTH_LONG).show();
break;
case R.id.btnQuery:
String key = edit3.getText().toString();
Log.i("swg", "key==" + key);
// 执行查询
String sql = "select * from dict where word like ? or detial like ? ";
Cursor cursor = dbHelper.getReadableDatabase().rawQuery(sql,
new String[] { "%" + key + "%", "%" + key + "%" });
Bundle data = new Bundle();
data.putSerializable("data", convertCursorToList(cursor));
Intent intent = new Intent(MainActivity.this, ResultActivity.class);
intent.putExtras(data);
startActivity(intent);
break;
}
}
private ArrayList<Map<String, String>> convertCursorToList(Cursor cursor)
{
ArrayList<Map<String, String>> result = new ArrayList<Map<String, String>>();
while (cursor.moveToNext())
{
Map<String, String> map = new HashMap<String, String>();
map.put("word", cursor.getString(1));
map.put("detial", cursor.getString(2));
result.add(map);
}
return result;
}
private void insertData(SQLiteDatabase db, String word, String detial)
{
String sql = "insert into dict values (null,?,?)";
db.execSQL(sql, new String[] { word, detial });
}
}
@Override
public boolean onCreateOptionsMenu(Menu menu)
{
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
@Override
protected void onDestroy()
{
super.onDestroy();
// 退出程序时关闭MyDatabaseHelper里的SQLitedatabase
if (dbHelper != null)
dbHelper.close();
}
}
package com.example.mysqlite2;
import java.util.List;
import java.util.Map;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.widget.ListView;
import android.widget.SimpleAdapter;
public class ResultActivity extends Activity
{
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.result);
ListView lv = (ListView) this.findViewById(R.id.lv);
Intent intent = getIntent();
Bundle data = intent.getExtras();
@SuppressWarnings("unchecked")
List<Map<String, String>> list = (List<Map<String, String>>) data.getSerializable("data");
// 将list封装成SimpleAdapter
SimpleAdapter adapter = new SimpleAdapter(ResultActivity.this, list, R.layout.result,
new String[] { "word", "detial" }, new int[] { R.id.word, R.id.detial });
int count = adapter.getCount();
Log.i("swg", "查到" + count + "条");
lv.setAdapter(adapter);
}
}
注意：AndroidMainfest.xml需要添加==》<activity android:name="com.example.mysqlite2.ResultActivity" android:theme="@android:style/Theme.Dialog"/>
运行效果：
注意：android实现系统自带样式如下方式：
android:theme="@android:style/Theme.Dialog" : Activity显示为对话框模式
android:theme="@android:style/Theme.NoTitleBar" : 不显示应用程序标题栏
android:theme="@android:style/Theme.NoTitleBar.Fullscreen" : 不显示应用程序标题栏，并全屏
android:theme="Theme.Light ": 背景为白色
android:theme="Theme.Light.NoTitleBar" : 白色背景并无标题栏
android:theme="Theme.Light.NoTitleBar.Fullscreen" : 白色背景，无标题栏，全屏
android:theme="Theme.Black" : 背景黑色
android:theme="Theme.Black.NoTitleBar" : 黑色背景并无标题栏
android:theme="Theme.Black.NoTitleBar.Fullscreen" : 黑色背景，无标题栏，全屏
android:theme="Theme.Wallpaper" : 用系统桌面为应用程序背景
android:theme="Theme.Wallpaper.NoTitleBar" : 用系统桌面为应用程序背景，且无标题栏
android:theme="Theme.Wallpaper.NoTitleBar.Fullscreen" : 用系统桌面为应用程序背景，无标题栏，全屏
android:theme="Theme.Translucent : 透明背景
android:theme="Theme.Translucent.NoTitleBar" : 透明背景并无标题
android:theme="Theme.Translucent.NoTitleBar.Fullscreen" : 透明背景并无标题，全屏
android:theme="Theme.Panel ": 面板风格显示
android:theme="Theme.Light.Panel" : 平板风格显示