半期考试之 SQLite操作

半期考试 之 SQLite操作

要求：

• 三个EditView，分别输入信息；
• 点击下方“添加”按钮，将输入的信息写入SQLite数据库中去，添加成功后，下方listview进行数据的展示；
• 点击下方“修改”按钮，可修改选定的信息（EditView显示相关信息）；
• 点击下方“删除”按钮，可删除选定的信息（listview进行同步的删除）；

xml文件

<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" >

    <EditText
        android:id="@+id/activity_main_etName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textPersonName" >
        <requestFocus />
    </EditText>

    <EditText
        android:id="@+id/activity_main_etPhone"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="phone" />

    <EditText
        android:id="@+id/activity_main_etAge"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="number" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:gravity="center" >

        <Button
            android:id="@+id/activity_main_btnAdd"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="添加" />

        <Button
            android:id="@+id/activity_main_btnModi"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="修改" />

        <Button
            android:id="@+id/activity_main_btnDel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="删除" />
    </LinearLayout>
    <ListView
        android:id="@+id/activity_main_lstUser"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </ListView>
</LinearLayout>

java

public class MainActivity extends Activity implements OnClickListener, OnItemClickListener {

    private EditText etName, etPhone, etAge;
    private Button btnAdd, btnModi, btnDel;
    private ListView lstUser;
    private ArrayList<HashMap<String, Object>> alUser;
    private PeopleUtil pUtil;

    private int SelectedId = 0;

    SimpleAdapter adapter;
    //UserAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        etName = (EditText) this.findViewById(R.id.activity_main_etName);
        etPhone = (EditText) this.findViewById(R.id.activity_main_etPhone);
        etAge = (EditText) this.findViewById(R.id.activity_main_etAge);

        btnAdd = (Button) this.findViewById(R.id.activity_main_btnAdd);
        btnModi = (Button) this.findViewById(R.id.activity_main_btnModi);
        btnDel = (Button) this.findViewById(R.id.activity_main_btnDel);

        btnAdd.setOnClickListener(this);
        btnModi.setOnClickListener(this);
        btnDel.setOnClickListener(this);

        pUtil = new PeopleUtil(this);
        alUser = pUtil.getList();

        lstUser = (ListView) this.findViewById(R.id.activity_main_lstUser);
        adapter = new SimpleAdapter(this, alUser, R.layout.li_user, new String[]{"UserName", "UserPhone", "UserAge"}, new int[]{R.id.li_user_tvName, R.id.li_user_tvPhone, R.id.li_user_tvAge});
        //adapter=new UserAdapter(this,alUser);
        //为ListView添加数据
        lstUser.setAdapter(adapter);
        lstUser.setOnItemClickListener(this);
    }

    @Override
    public void onClick(View arg0) {
        // TODO Auto-generated method stub
        switch (arg0.getId()) {
            case R.id.activity_main_btnAdd:
                HashMap<String, String> item1 = new HashMap<String, String>();
                item1.put("UserId", "0");
                item1.put("UserName", etName.getText().toString());
                item1.put("UserPhone", etPhone.getText().toString());
                item1.put("UserAge", etAge.getText().toString());
                pUtil.Save(item1);
                Toast.makeText(this, "添加成功", Toast.LENGTH_LONG).show();
                break;
            case R.id.activity_main_btnModi:
                if (SelectedId == 0) {
                    Toast.makeText(this, "请选择要修改的用户", Toast.LENGTH_LONG).show();
                    return;
                }
                HashMap<String, String> item2 = new HashMap<String, String>();
                item2.put("UserId", SelectedId + "");
                item2.put("UserName", etName.getText().toString());
                item2.put("UserPhone", etPhone.getText().toString());
                item2.put("UserAge", etAge.getText().toString());
                pUtil.Save(item2);
                Toast.makeText(this, "修改成功", Toast.LENGTH_LONG).show();
                break;
            case R.id.activity_main_btnDel:
                if (SelectedId == 0) {
                    Toast.makeText(this, "请选择要删除的用户", Toast.LENGTH_LONG).show();
                    return;
                }
                pUtil.Delete(SelectedId);
                break;
        }
        SelectedId = 0;
        alUser = pUtil.getList();
        adapter = new SimpleAdapter(this, alUser, R.layout.li_user, new String[]{"UserName", "UserPhone", "UserAge"}, new int[]{R.id.li_user_tvName, R.id.li_user_tvPhone, R.id.li_user_tvAge});
        //adapter=new UserAdapter(this,alUser);
        lstUser.setAdapter(adapter);
        //adapter.notifyDataSetChanged();
    }

    @Override
    public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
        HashMap<String, Object> item = alUser.get(arg2);
        SelectedId = Integer.valueOf(item.get("UserId").toString());
        etName.setText(item.get("UserName").toString());
        etPhone.setText(item.get("UserPhone").toString());
        etAge.setText(item.get("UserAge").toString());
    }
}

数据库操作类:

public class DbHelper {
	//定义数据成员
	//定义数据库的名称
	private static final String DB_NAME = "people.db";
	//定义当前的版本
	private static final int DB_VERSION = 1;
	//定义表的名称
	public static final String DB_TABLE = "peopleinfo";
	//定义列名
	public static final String KEY_ID = "UserId";
	public static final String KEY_NAME = "UserName";
	public static final String KEY_PHONE = "UserTelPhone";
	public static final String KEY_AGE = "UserAge";

	//数据库操作相应成员的引用变量
	private SQLiteDatabase db;
	private final Context context;
	private DBOpenHelper dbOpenHelper;

	//定义一个DBOpenHelper类继承至SQLiteOpenHelper实现数据库的创建修改等操作
	private static class DBOpenHelper extends SQLiteOpenHelper {

		//定义创建表的sql语句
		private static final String DB_CREATE = "create table " + DB_TABLE
				+ " (" + KEY_ID + " integer primary key autoincrement, "
				+ KEY_NAME + " varchar not null, " + KEY_PHONE + " varchar,"
				+ KEY_AGE + " integer);";
		
		public DBOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, DB_NAME, factory, version);
			// TODO Auto-generated constructor stub
		}
		
		@Override
		public void onCreate(SQLiteDatabase arg0) {
			// TODO Auto-generated method stub
			arg0.execSQL(DB_CREATE);
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
			onCreate(db);
		}
		
		@Override
		public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			super.onDowngrade(db, oldVersion, newVersion);
		}
	}

	//DbHelper构造方法需要传递一个Context参数
	public DbHelper(Context _context) {
		context = _context;
	}
	//打开数据库
	public void open() throws SQLiteException {
		//实例化一个DbOpenHelper对象
		dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, DB_VERSION);
		try {
			db = dbOpenHelper.getWritableDatabase();
		} catch (SQLiteException ex) {
			db = dbOpenHelper.getReadableDatabase();
		}
	}
	//关闭数据库
	public void close() {
		if (db != null) {
			db.close();
			db = null;
		}
	}
	
	public long insert(String table, String nullColumnHack, ContentValues values) {
		return db.insert(table, nullColumnHack, values);
	}
	
	public long update(String table, ContentValues values, String whereClause,
			String[] whereArgs) {
		return db.update(table, values, whereClause, whereArgs);
	}
	
	public long delete(String table, String whereClause, String[] whereArgs) {
		return db.delete(table, whereClause, whereArgs);
	}

	public Cursor query(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy) {
		return db.query(table, columns, selection, selectionArgs, groupBy,
				having, orderBy);
	}
}

实体操作类：

public class PeopleUtil {
	
	private Context _Context;
	private DbHelper helper;
	//构造函数需要传递Context参数
	public PeopleUtil(Context _Context)
	{
		this._Context=_Context;
		helper = new DbHelper(_Context);
		helper.open();
	}
	//保存数据
	public long Save(HashMap<String,String> people)
	{
		ContentValues value=new ContentValues();
		value.put(DbHelper.KEY_NAME, people.get("UserName"));
		value.put(DbHelper.KEY_PHONE, people.get("UserPhone"));
		value.put(DbHelper.KEY_AGE, people.get("UserAge"));
		long count=0;
		if(Integer.valueOf(people.get("UserId").toString())==0)
		{
			count=helper.insert(DbHelper.DB_TABLE, DbHelper.KEY_ID, value);
		}
		else
		{
			count=helper.update(DbHelper.DB_TABLE, value, DbHelper.KEY_ID+"=?", new String[]{String.valueOf(people.get("UserId"))});
		}
		return count;
	}
	//删除数据
	public long Delete(int _UserId)
	{
		return helper.delete(DbHelper.DB_TABLE, DbHelper.KEY_ID+"=?", new String[]{String.valueOf(_UserId)});
	}
	//获取所有数据
	public ArrayList<HashMap<String,Object>> getList()
	{
		ArrayList<HashMap<String,Object>> alUser=new ArrayList<HashMap<String,Object>>();
		//通过query方法查找数据
		Cursor c = helper.query(DbHelper.DB_TABLE, new String[]{DbHelper.KEY_ID,DbHelper.KEY_NAME,DbHelper.KEY_PHONE,DbHelper.KEY_AGE}, null, null, null, null, null);
		//通过moveToNext遍历数据，指定到对应的行
		while(c.moveToNext())
    	{
    		//实例化一个HashMap
			//通过Cursor的getXXX方法获取对应列的数据
			HashMap<String,Object> item=new HashMap<String,Object>();
			item.put("UserId", c.getInt(0));
			item.put("UserName", c.getString(1));
			item.put("UserPhone", c.getString(2));
			item.put("UserAge", c.getInt(3));
			//将hashMap对象添加到ArrayList中
			alUser.add(item);
		}
		//关闭Cursor
    	c.close();
    	c=null;
		return alUser;
	}
	//退出时关闭数据库
	@Override
	protected void finalize() throws Throwable {
		// TODO Auto-generated method stub
		helper.close();
		super.finalize();
	}
}

运行结果：