第三章:手机增 删 查 改(SQLiteOpenHelper)

效果:





























main.xml

<?xml version="1.0" encoding="utf-8"?>
<AbsoluteLayout
android:id="@+id/widget0"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
xmlns:android="http://schemas.android.com/apk/res/android"
>
<EditText
android:id="@+id/text"
android:layout_width="247px"
android:layout_height="wrap_content"
android:textSize="18sp"
android:layout_x="20px"
android:layout_y="10px"
>
</EditText>
<ListView
android:id="@+id/list"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_x="27px"
android:layout_y="73px"
>
</ListView>


</AbsoluteLayout>

list.xml

<?xml version="1.0" encoding="UTF-8"?>
<TextView
	android:id="@+id/text1"
	xmlns:android="http://schemas.android.com/apk/res/android"
	android:layout_width="wrap_content"
	android:layout_height="20px"
	android:textSize="14sp"/>

package sqlite.open.helper.test;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteCursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class SQLiteOpenHelperTest extends Activity {
	private  DataTest data;
	private Cursor myCursor;
	private ListView myListView;
	private EditText myEditText;
	private int _id;
	protected final static int MENU_ADD=Menu.FIRST;
	protected final static int MENU_EDIT=Menu.FIRST+1;
	protected final static int MENU_DELETE=Menu.FIRST+2;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        /**载入main.xml*/
        setContentView(R.layout.main);
        /**载入通过id找到ListView对象*/
        myListView=(ListView)findViewById(R.id.list);
        /**载入通过id找到EditText对象*/
        myEditText=(EditText)findViewById(R.id.text);
        /**实例化DataTest对象传入当前对象*/
        data=new DataTest(this);
        /**取得DataBase数据*/
        myCursor=data.select();
        /**实例SimpleCursorAdapter 并传入myCursor,数据的字段为data_text*/
        SimpleCursorAdapter adapter=new SimpleCursorAdapter(this,R.layout.list,myCursor,new String[]{DataTest.FIELD_TEXT},new int[]{R.id.text1});
        /**设置ListView组件内容*/
        myListView.setAdapter(adapter);
        /**设置ListView行的点击事件*/
        myListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
			@Override
			public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
					long arg3) {
		        /**将myCursor移到所点击的值*/
				myCursor.moveToPosition(arg2);
				/**取得字段id的值*/
				_id=myCursor.getInt(0);
				/**取得字段data_text的值*/
				myEditText.setText(myCursor.getString(1));
			}
		});
      
        myListView.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
			@Override
			public void onItemSelected(AdapterView<?> arg0, View arg1,
					int arg2, long arg3) {
				  /**取得的是SQLiteCursor对象*/
				SQLiteCursor sc=(SQLiteCursor)arg0.getSelectedItem();
				/**取得字段id的值*/
				_id=sc.getInt(0);
				/**取得字段data_text的值*/
				myEditText.setText(sc.getString(1));
			}
			@Override
			public void onNothingSelected(AdapterView<?> arg0) {
			}
		});
        
    }
    /**该方法是判断点击Menu按钮*/
	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		super.onOptionsItemSelected(item);
		switch(item.getItemId()){
			case MENU_ADD:
				this.addData();
				break;
			case MENU_EDIT:
				this.editData();
				break;
			case MENU_DELETE:
				this.deleteData();
				break;
		}
		return true;
	}
	 /**该方法是设置添加自定义Menu*/
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		super.onCreateOptionsMenu(menu);
		menu.add(Menu.NONE,MENU_ADD,0,R.string.add_Menu);
		menu.add(Menu.NONE,MENU_EDIT,0,R.string.edit_Menu);
		menu.add(Menu.NONE,MENU_DELETE,0,R.string.delete_Menu);
		return true;
	}
	
	private void addData(){
		/**输入的是否为空*/
		if(myEditText.getText().toString().equals("")){
			return;
		}
		 /**添加数据到数据库*/
		data.insert(myEditText.getText().toString());
		/**重新查询*/
		myCursor.requery();
		/**刷新ListView*/
		myListView.invalidateViews();
		/**设置EditText组件内容*/
		myEditText.setText("");
		_id=0;
	}

    private void editData(){
    	/**输入的是否为空*/
    	if(myEditText.getText().toString().equals("")){
    		return;
    	}
    	 /**修改数据到数据库*/
    	data.update(_id, myEditText.getText().toString());
    	/**重新查询*/
    	myCursor.requery();
    	/**刷新ListView*/
    	myListView.invalidateViews();
    	/**设置EditText组件内容*/
    	myEditText.setText("");
    	_id=0;
    }
    private void deleteData(){
    	if(_id==0){
    		return;
    	}
    	/**删除数据 */
    	data.delete(_id);
    	/**重新查询*/
    	myCursor.requery();
    	/**刷新ListView*/
    	myListView.invalidateViews();
    	/**设置EditText组件内容*/
    	myEditText.setText("");
    	_id=0;
    }
}

package sqlite.open.helper.test;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DataTest extends SQLiteOpenHelper {
	private final static String DATABASE_NAME="data_db";
	private final static int DATABASE_VERSION=1;
	private final static String TABLE_NAME="data_table";
	public final static String FIELD_id="_id";
	public final static String FIELD_TEXT="data_text";

	public DataTest(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		/**创建一张名为data_text的表*/
		String sql="CREATE TABLE "+TABLE_NAME+" ("+FIELD_id+" INTEGER primary key autoincrement, "+" "+FIELD_TEXT+" text)";
		/**执行SQL*/
		db.execSQL(sql);
	}
	
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql="DROP TABLE IF EXISTS"+TABLE_NAME;
		db.execSQL(sql);
		onCreate(db);
	}
	/**查询数据*/
	public Cursor select(){
		/**
		 * public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
		 * 
		 * table        	The table name to compile the query against.
		 * columns      	A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
		 * selection		A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table
		 * selectionArgs	You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
		 * groupBy			A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
		 * having			A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
		 * orderBy			How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
		 * 
		 * Returns
		 * A Cursor object, which is positioned before the first entry
		 * */
		
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor cursor=db.query(TABLE_NAME, null, null, null, null, null, null);
		return cursor;
	}
	/**添加数据*/
	public long insert(String text){
		SQLiteDatabase db=this.getWritableDatabase();
		/**将添加的数据放入 ContentValues*/
		ContentValues cv=new ContentValues();
		cv.put(FIELD_TEXT, text);
		/**
		 * public long insert (String table, String nullColumnHack, ContentValues values)
		 * 
		 * table			the table to insert the row into
		 * nullColumnHack	SQL doesn't allow inserting a completely empty row, so if initialValues is empty this column will explicitly be assigned a NULL value.
		 * values			this map contains the initial column values for the row. The keys should be the column names and the values the column values。
		 * 
		 * Returns
		 * the row ID of the newly inserted row, or -1 if an error occurred
		 */
		long row=db.insert(TABLE_NAME, null, cv);
		return row;
	}
	/**删除数据*/
	public void delete(int id){
		SQLiteDatabase db=this.getWritableDatabase();
		String where=FIELD_id+"=?";
		String[] whereValue={Integer.toString(id)};
		/**
		 * public int delete (String table, String whereClause, String[] whereArgs)
		 * 
		 * table		the table to delete from
		 * whereClause	the optional WHERE clause to apply when deleting. Passing null will delete all rows.
		 * 
		 * Returns
		 * the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.
		 * */
		db.delete(TABLE_NAME, where, whereValue);
	}
	/**修改数据*/
	public void update(int id,String text){
		SQLiteDatabase db=this.getWritableDatabase();
		String where=FIELD_id+"=?";
		String[] whereValue={Integer.toString(id)};
		/**将添加的数据放入 ContentValues*/
		ContentValues cv=new ContentValues();
		cv.put(FIELD_TEXT, text);
		/**
		 * public int update (String table, ContentValues values, String whereClause, String[] whereArgs)
		 * 
		 * table		the table to update in
		 * values		a map from column names to new column values. null is a valid value that will be translated to NULL
		 * whereClause	the optional WHERE clause to apply when updating. Passing null will update all rows.
		 * 
		 * Returns
		 * the number of rows affected
		 * */
		db.update(TABLE_NAME, cv, where, whereValue);
	}
}