SQLiteDatabaseTest

package com.hyz;import android.app.Activity;import android.content.ContentUris;import android.content.ContentValues;import android.content.Context;import android.content.Intent;import android.database.Cursor;import android.os.Bundle;import android.util.Log;import android.view.Menu;import android.view.MenuItem;import android.view.View;import android.view.ViewGroup;import android.widget.AdapterView;import android.widget.BaseAdapter;import android.widget.EditText;import android.widget.ListView;import android.widget.TextView;import android.widget.Toast;import android.widget.AdapterView.OnItemClickListener;public class SQLiteDatabaseTest extends Activity implements OnItemClickListener{private Cursor cursor;private EditText BookName;private EditText Author;private ListView bookslist;private int BOOK_ID = 0;protected final static int MENU_ADD=Menu.FIRST;protected final static int MENU_DELETE=Menu.FIRST+1;protected final static int MENU_UPDATE=Menu.FIRST+2;protected final static int MENU_QUERY=Menu.FIRST+3;private String bookname;private String author;    public void onCreate(Bundle savedInstanceState)     {        super.onCreate(savedInstanceState);       Log.w("db", "SQLiteDatabaseTest-->onCreate()");        setContentView(R.layout.main);            BookName = (EditText)findViewById(R.id.bookname);    Author = (EditText)findViewById(R.id.author);    bookslist = (ListView)findViewById(R.id.bookslist);        queryAll();    }   @Overridepublic void onItemClick(AdapterView<?> parent, View view, int position, long id) {Log.w("db", "SQLiteDatabaseTest-->onItemClick()");cursor.moveToPosition(position);//移至第position行BOOK_ID = cursor.getInt(0);//得到第0列BookName.setText(cursor.getString(1));//得到第1列Author.setText(cursor.getString(2));//得到第2列}@Overridepublic boolean onCreateOptionsMenu(Menu menu){super.onCreateOptionsMenu(menu);Log.w("db", "SQLiteDatabaseTest-->onCreateOptionsMenu()");menu.add(Menu.NONE, MENU_ADD, 0, "ADD");menu.add(Menu.NONE,MENU_DELETE,0,"DELETE");menu.add(Menu.NONE, MENU_UPDATE, 0, "UPDATE");menu.add(Menu.NONE, MENU_QUERY, 0, "QUERY");return true;}@Overridepublic boolean onOptionsItemSelected(MenuItem item){super.onOptionsItemSelected(item);Log.w("db", "SQLiteDatabaseTest-->onOptionsItemSelected()");switch(item.getItemId()){case MENU_ADD:add();break;case MENU_DELETE:delete();break;case MENU_UPDATE:update();break;case MENU_QUERY:query();break;}return true;} //初始化视图界面,将全部数据列出来    public void queryAll()    {    Log.w("db", "SQLiteDatabaseTest-->queryAll()");        cursor = getContentResolver().query(BooksDB.CONTENT_URI, null, null, null, null);      bookslist.setAdapter(new BookListAdapter(this,cursor));    bookslist.setOnItemClickListener(this);    }    //按姓名或作者关键字查询单个记录    public void query()    {    Log.w("db", "SQLiteDatabaseTest-->query()");  bookname = BookName.getText().toString();author = Author.getText().toString();if(bookname.equals("")&&author.equals("")){queryAll();}else{cursor = getContentResolver().query(BooksDB.CONTENT_URI, null, BooksDB.BOOK_NAME + "=? or " +  BooksDB.BOOK_AUTHOR + "=?", new String[]{bookname , author}, null);bookslist.setAdapter(new BookListAdapter(this,cursor));bookslist.setOnItemClickListener(this);}BookName.setText("");Author.setText("");Toast.makeText(this, "查找成功", Toast.LENGTH_LONG).show();        }private void add(){Log.w("db", "SQLiteDatabaseTest-->add()");bookname = BookName.getText().toString();author = Author.getText().toString();if(bookname.equals("")||author.equals("")){return;}ContentValues values = new ContentValues(2);values.put(BooksDB.BOOK_NAME, bookname);values.put(BooksDB.BOOK_AUTHOR, author);getContentResolver().insert(BooksDB.CONTENT_URI, values);//下面几句可以在更改的同时更新list//Log.w("db", ""+cursor.getPosition());cursor.getPosition()=插入数据位置-1//cursor.moveToFirst();移至第一条记录，cursor.getPosition()=0cursor.requery();//Log.w("db", ""+cursor.getPosition());cursor.getPosition()=-1//cursor.requery();和bookslist.invalidateViews();需要一起使用bookslist.invalidateViews();BookName.setText("");Author.setText("");Toast.makeText(this,"添加成功", Toast.LENGTH_LONG).show();}private void delete(){Log.w("db", "SQLiteDatabaseTest-->delete()");if(BOOK_ID==0){return;}getContentResolver().delete(ContentUris.withAppendedId(BooksDB.CONTENT_URI, BOOK_ID), null, null);cursor.requery();bookslist.invalidateViews();BookName.setText("");Author.setText("");Toast.makeText(this,"删除成功", Toast.LENGTH_LONG).show();}private void update(){Log.w("db", "SQLiteDatabaseTest-->update()");bookname = BookName.getText().toString();author = Author.getText().toString();ContentValues values = new ContentValues();values.put(BooksDB.BOOK_NAME, bookname);values.put(BooksDB.BOOK_AUTHOR, author);if(bookname.equals("")||author.equals("")){return;}getContentResolver().update(ContentUris.withAppendedId(BooksDB.CONTENT_URI, BOOK_ID), values, null, null);cursor.requery();bookslist.invalidateViews();//及时更新ListView里的内容，否则点击更新   内容不会变，其实内容在数据库中已经变了BookName.setText("");Author.setText("");Toast.makeText(this, "更新成功", Toast.LENGTH_LONG).show();}public class BookListAdapter extends BaseAdapter{private Context mContext;private Cursor mCursor;public BookListAdapter(Context context, Cursor cursor) {Log.w("db", "SQLiteDatabaseTest-->BookListAdapter-->BookListAdapter()");mContext = context;mCursor = cursor;}@Overridepublic int getCount() {Log.w("db", "SQLiteDatabaseTest-->BookListAdapter-->getCount()");Log.w("db", ""+mCursor.getCount());return mCursor.getCount();}@Overridepublic Object getItem(int position){Log.w("db", "SQLiteDatabaseTest-->BookListAdapter-->getItem()");// TODO Auto-generated method stubreturn null;}@Overridepublic long getItemId(int position){// TODO Auto-generated method stubLog.w("db", "SQLiteDatabaseTest-->BookListAdapter-->getItemId()");return 0;}//position表示将显示的是第几行，covertView是从布局文 件中inflate来的布局。@Overridepublic View getView(int position, View convertView, ViewGroup parent) {Log.w("db", "SQLiteDatabaseTest-->BookListAdapter-->getView()");TextView mTextView = new TextView(mContext);mCursor.moveToPosition(position);mTextView.setText(mCursor.getString(1)+"____"+mCursor.getString(2));return mTextView;}}}

package com.hyz;import android.content.ContentProvider;import android.content.ContentUris;import android.content.ContentValues;import android.content.UriMatcher;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteQueryBuilder;import android.net.Uri;import android.util.Log;public class MyProvider extends ContentProvider{private BooksDB mbooksDB;private static final UriMatcher sMatcher;private static final int BOOKS = 1;private static final int BOOKS_ID = 2;// 传入匹配码如果大于0表示匹配根路径或传入-1，即常量UriMatcher.NO_MATCH表示不匹配根路径// addURI()方法是用来增加其他URI匹配路径的：// 第一个参数代表传入标识ContentProvider的AUTHORITY字符串// 第二个参数是要匹配的路径，#代表任意数字，另外还可以用*来匹配任意文本// 第三个参数必须传入一个大于零的匹配码，用于match()方法对相匹配的URI返回相对应的匹配码static {sMatcher = new UriMatcher(UriMatcher.NO_MATCH);sMatcher.addURI("com.hyz", "books", BOOKS);sMatcher.addURI("com.hyz", "books/#", BOOKS_ID);}/* * 每当ContentProvider启动时都会回调onCreate()方法。 * 此方法主要执行一些ContentProvider初始化 的工作，返回true表示初始化成功，返回false则初始化失败。 */@Overridepublic boolean onCreate() {Log.w("db", "MyProvider-->onCreate()");//项目首先调用此函数用来创建数据库mbooksDB = new BooksDB(getContext());return true;}/* * * 该方法用于返回当前Url所代表数据的MIME类型  * 如果操作的数据属于集合类型，那么MIME类型字符串应该以vnd.android.cursor.dir/开头  * 例如：要得到所有person记录的Uri为content://org.wp.provider.personprovider/person  * 那么返回的MIME类型字符串应该为："vnd.android.cursor.dir/person"  * 如果要操作的数据属于单一数据，那么MIME类型字符串应该以vnd.android.cursor.item/开头  * 例如：得到id为10的person记录，Uri为content://org.wp.provider.personprovider/person/10  * 那么返回的MIME类型字符串应该为："vnd.android.cursor.item/person" *  */@Overridepublic String getType(Uri uri) {Log.w("db", "MyProvider-->getType()");switch (sMatcher.match(uri)){case 1:return "vnd.android.cursor.dir/books";case 2:return "vnd.android.cursor.item/books";default:throw new IllegalArgumentException("Unknown URI " + uri);}}/* * 用于数据的删除，返回的是所影响数据的数目，首先利用数据库辅助对象获取一个SQLiteDatabase对象 * 然后根据传入Uri用sMatcher进行匹配，对单个数据或数据集进行删除或修改。notifyChange()方法 * 用来通知注册在次URI上的观察者（observer）数据发生了改变。 * */@Overridepublic int delete(Uri uri, String selection, String[] selectionArgs) {Log.w("db", "MyProvider-->delete()");SQLiteDatabase db = mbooksDB.getWritableDatabase();int count;switch(sMatcher.match(uri)){case BOOKS:  count = db.delete(BooksDB.TABLE_NAME, selection, selectionArgs);break;case BOOKS_ID: count = db.delete(BooksDB.TABLE_NAME, BooksDB.BOOK_ID + "=?", new String[]{ContentUris.parseId(uri)+""});break;        default:                 throw new UnsupportedOperationException("Cannot update URL: ");  }    return count;}@Overridepublic Uri insert(Uri uri, ContentValues values) {Log.w("db", "MyProvider-->insert()");    //插入操作要对所有的字段插入，如果带有参数，则报异常        if (sMatcher.match(uri) != BOOKS)        {            throw new IllegalArgumentException("Cannot insert into URL: " + uri);        }        SQLiteDatabase db = mbooksDB.getWritableDatabase();        long rowId = db.insert(BooksDB.TABLE_NAME, null, values);        return ContentUris.withAppendedId(BooksDB.CONTENT_URI, rowId);      }@Overridepublic Cursor query(Uri uri, String[] projection, String selection,String[] selectionArgs, String sortOrder) {        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();        int match = sMatcher.match(uri);        switch (match)         {        //1 、直接查询表中的所有数据            case BOOKS:            Log.w("db", "MyProvider-->query(1)");                qb.setTables("books_table");                break;            //2 、根据ID查询            case BOOKS_ID:              Log.w("db", "MyProvider-->query(2)");                qb.setTables(BooksDB.TABLE_NAME);                qb.appendWhere("book_id=");                //拿到uri后面的第一个参数并将它赋给数据库字段"_id";                qb.appendWhere(uri.getPathSegments().get(1));                break;            default:                throw new IllegalArgumentException("Unknown URL " + uri);        }        //3 、根据指定条件查询数据并返回cursor        SQLiteDatabase db = mbooksDB.getReadableDatabase();        Cursor ret = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);        return ret;}@Overridepublic int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {Log.w("db", "MyProvider-->update()");SQLiteDatabase db = mbooksDB.getWritableDatabase();int count ;     switch(sMatcher.match(uri)){case BOOKS:  count = db.update(BooksDB.TABLE_NAME, values, selection , selectionArgs);break;case BOOKS_ID: count = db.update(BooksDB.TABLE_NAME, values , BooksDB.BOOK_ID + "=?",new String[]{ uri.getPathSegments().get(1)});break;               default:                 throw new UnsupportedOperationException("Cannot update URL: ");     }        return count;     }}

package com.hyz;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.net.Uri;import android.util.Log;public class BooksDB extends SQLiteOpenHelper {private final static String DATABASE_NAME = "books.db";private final static int DATABASE_VERSION = 1;public final static String TABLE_NAME = "books_table";public final static String BOOK_ID = "book_id";public final static String BOOK_NAME = "book_name";public final static String BOOK_AUTHOR = "book_author";public static final Uri CONTENT_URI = Uri.parse("content://com.hyz/books");public BooksDB(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);Log.w("db", "BooksDB-->BooksDB()");}/**  * 如果数据库文件不存在，只有oncreate()被调用（该方法在创建数据库时被调用一次）。 * 如果数据库文件存在，会调用onupdate()方法升级数据库，并更新版本号。 */@Overridepublic void onCreate(SQLiteDatabase db) {Log.w("db", "BooksDB-->onCreate()");String sql = "create table "+TABLE_NAME+" ("+BOOK_ID+" INTEGER primary key autoincrement, "+BOOK_NAME+" text,"+BOOK_AUTHOR+" text);";db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){Log.w("db", "BooksDB-->onUpgrade()");String sql = "drop table if exists" + TABLE_NAME;db.execSQL(sql);onCreate(db);}}

main.xml

<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:orientation="vertical"    android:layout_width="fill_parent"    android:layout_height="fill_parent"    ><TextView      android:layout_width="fill_parent"     android:layout_height="wrap_content"     android:text="书名"    />    <EditText     android:id="@+id/bookname"     android:layout_width="fill_parent"    android:layout_height="wrap_content"/><TextView      android:layout_width="fill_parent"     android:layout_height="wrap_content"     android:text="作者"    />    <EditText     android:id="@+id/author"     android:layout_width="fill_parent"    android:layout_height="wrap_content"/>         <ListView     android:id="@+id/bookslist"     android:layout_width="fill_parent"    android:layout_height="wrap_content"/>   </LinearLayout>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"      package="com.hyz"      android:versionCode="1"      android:versionName="1.0">    <application android:icon="@drawable/icon" android:label="@string/app_name">        <activity android:name=".SQLiteDatabaseTest"                  android:label="@string/app_name">            <intent-filter>                <action android:name="android.intent.action.MAIN" />                <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>                <provider android:name="MyProvider" android:authorities="com.hyz" />            </application></manifest>

更多相关文章

随机推荐