Android SQLite

<pre name="code" class="java">package com.jia.mdatabase;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.widget.Toast;public class MyDatabaseHelper extends SQLiteOpenHelper {public static final String CREATE_BOOK = "create table book ("+ " id integer primary key autoincrement," + " author text,"+ " price real," + " pages integer," + " name text)";public static final String CREATE_CATEGORY = "create table Category("+ " id integer primary key autoincrement," + " category_name text,"+ " category_code integer)";private Context mContext;public MyDatabaseHelper(Context context, String name,CursorFactory factory, int version) {super(context, name, factory, version);mContext = context;}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubdb.execSQL(CREATE_BOOK);db.execSQL(CREATE_CATEGORY);Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_LONG).show();}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("drop table if exists Book");db.execSQL("drop table if exists Category");onCreate(db);}}

package com.jia.mdatabase;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;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.Toast;public class MainActivity extends Activity {private MyDatabaseHelper dbHelper;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 2);Button createDatabase = (Button) findViewById(R.id.create_database);createDatabase.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {dbHelper.getWritableDatabase();}});Button addData = (Button) findViewById(R.id.add_data);addData.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();ContentValues values = new ContentValues();// 开始组装第一条数据values.put("name", "The DaVinci Code");values.put("author", "Yao Jiawei");values.put("pages", 454);values.put("price", 16.96);db.insert("Book", null, values);// 插入第一条数据values.clear();// 开始组装第二条数据values.put("name", "The Lost Symbol");values.put("author", "Dan Brown");values.put("pages", 510);values.put("price", 19.95);db.insert("Book", null, values);// 插入第二天数据}});Button updateData = (Button) findViewById(R.id.update_data);updateData.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put("price", 10.99);db.update("Book", values, "name=?",new String[] { "The DaVinci Code" });Toast.makeText(MainActivity.this, "更改成功", Toast.LENGTH_LONG).show();}});Button deleteData = (Button) findViewById(R.id.delete_data);deleteData.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();db.delete("Book", "pages>?", new String[] { "500" });}});Button queryData = (Button) findViewById(R.id.query_data);queryData.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();Cursor cursor = db.query("Book", null, null, null, null, null,null);if (cursor.moveToFirst()) {do {// 遍历Cursor对象,取出数据并打印String name = cursor.getString(cursor.getColumnIndex("name"));String author = cursor.getString(cursor.getColumnIndex("author"));int pages = cursor.getInt(cursor.getColumnIndex("pages"));double price = cursor.getDouble(cursor.getColumnIndex("price"));Log.d("jia", "book name---->" + name);Log.d("jia", "book author---->" + author);Log.d("jia", "book pages---->" + pages);Log.d("jia", "book price---->" + price);} while (cursor.moveToNext());}cursor.close();}});Button replaceData = (Button) findViewById(R.id.replace_data);replaceData.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();db.beginTransaction();// 开启事物try {db.delete("Book", null, null);/*if (true) {// 在这里手动抛出一个异常,让事务失败throw new NullPointerException();}*/ContentValues values = new ContentValues();values.put("name", "Game of Thrones");values.put("author", "George Martin");values.put("pages", 720);values.put("price", 20.85);db.insert("Book", null, values);db.setTransactionSuccessful();// 事务已经执行成功} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {db.endTransaction();// 结束事务}}});}}

<pre name="code" class="java">package org.crazyit.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;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);}@Overridepublic void onCreate(SQLiteDatabase db){// 第一次使用数据库时自动建表db.execSQL(CREATE_TABLE_SQL);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){System.out.println("--------onUpdate Called--------"+ oldVersion + "--->" + newVersion);}}

package org.crazyit.db;import java.util.ArrayList;import java.util.HashMap;import java.util.Map;import android.app.Activity;import android.content.Intent;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;import android.widget.Toast;public class Dict extends Activity{MyDatabaseHelper dbHelper;Button insert = null;Button search = null;@Overridepublic void onCreate(Bundle savedInstanceState){super.onCreate(savedInstanceState);setContentView(R.layout.main);// 创建MyDatabaseHelper对象，指定数据库版本为1，此处使用相对路径即可，// 数据库文件自动会保存在程序的数据文件夹的databases目录下。dbHelper = new MyDatabaseHelper(this, "myDict.db3", 1);insert = (Button) findViewById(R.id.insert);search = (Button) findViewById(R.id.search);insert.setOnClickListener(new OnClickListener(){@Overridepublic void onClick(View source){// 获取用户输入String word = ((EditText) findViewById(R.id.word)).getText().toString();String detail = ((EditText) findViewById(R.id.detail)).getText().toString();// 插入生词记录insertData(dbHelper.getReadableDatabase(), word, detail);// 显示提示信息Toast.makeText(Dict.this, "添加生词成功！", 8000).show();}});search.setOnClickListener(new OnClickListener(){@Overridepublic void onClick(View source){// 获取用户输入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对象Bundle data = new Bundle();data.putSerializable("data", converCursorToList(cursor));// 创建一个IntentIntent intent = new Intent(Dict.this, ResultActivity.class);intent.putExtras(data);// 启动ActivitystartActivity(intent);}});}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;}private void insertData(SQLiteDatabase db, String word, String detail){// 执行插入语句db.execSQL("insert into dict values(null , ? , ?)", new String[] {word, detail });}@Overridepublic void onDestroy(){super.onDestroy();// 退出程序时关闭MyDatabaseHelper里的SQLiteDatabaseif (dbHelper != null){dbHelper.close();}}}

/** * */package org.crazyit.db;import java.util.List;import java.util.Map;import android.app.Activity;import android.content.Intent;import android.os.Bundle;import android.widget.ListView;import android.widget.SimpleAdapter;public class ResultActivity extends Activity{@Overridepublic void onCreate(Bundle savedInstanceState){super.onCreate(savedInstanceState);setContentView(R.layout.popup);ListView listView = (ListView) findViewById(R.id.show);Intent intent = getIntent();// 获取该intent所携带的数据Bundle data = intent.getExtras();// 从Bundle数据包中取出数据@SuppressWarnings("unchecked")List<Map<String, String>> list = (List<Map<String, String>>)data.getSerializable("data");// 将List封装成SimpleAdapterSimpleAdapter adapter = new SimpleAdapter(ResultActivity.this, list,R.layout.line, new String[] { "word", "detail" }, new int[] {R.id.word, R.id.detail });// 填充ListViewlistView.setAdapter(adapter);}}

package org.crazyit.db;import android.app.Activity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteException;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.CursorAdapter;import android.widget.EditText;import android.widget.ListView;import android.widget.SimpleCursorAdapter;public class DBTest extends Activity{SQLiteDatabase db;Button bn = null;ListView listView;@Overridepublic void onCreate(Bundle savedInstanceState){super.onCreate(savedInstanceState);setContentView(R.layout.main);// 创建或打开数据库（此处需要使用绝对路径）db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString()+ "/my.db3", null); // ①listView = (ListView) findViewById(R.id.show);bn = (Button) findViewById(R.id.ok);bn.setOnClickListener(new OnClickListener(){@Overridepublic 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);Cursor cursor = db.rawQuery("select * from news_inf", null);inflateList(cursor);}catch (SQLiteException se){// 执行DDL创建数据表db.execSQL("create table news_inf(_id integer"+ " primary key autoincrement,"+ " news_title varchar(50),"+ " news_content varchar(255))");// 执行insert语句插入数据insertData(db, title, content);// 执行查询Cursor cursor = db.rawQuery("select * from news_inf", null);inflateList(cursor);}}});}private void insertData(SQLiteDatabase db, String title, String content) //②{// 执行插入语句db.execSQL("insert into news_inf values(null , ? , ?)", new String[] {title, content });}private void inflateList(Cursor cursor){// 填充SimpleCursorAdapterSimpleCursorAdapter 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);}@Overridepublic void onDestroy(){super.onDestroy();// 退出程序时关闭SQLiteDatabaseif (db != null && db.isOpen()){db.close();}}}