AndroidSQLite数据库创建和使用实战（一）

14-03-10 来源：[db:作者]

收藏我要投稿

废话不多说，Android撑死算个入门选手，刚好有点时间，随之倒腾整理下Android数据库操作。码农只需要代码和原理，理论从代码中自己感悟。小二，上代码，弄。

功能基本包含了数据库基本功能，至于运行后人机操作界面只是随便写了个布局方便测试而已。

com/jesse/dbasetest1/DbAdapter.java（用来管理数据库，简单的例子嘛，规范啥的就免了，重在说理）。

package com.jesse.dbasetest1;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
 * @author Jesse
 * just for myself learn android SQLite.
 */
public class DbAdapter {
	public static final String TAG = DbAdapter.class.getSimpleName();
	private Context mContext;
	
	public static final String KEY_ID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_AGE = "age";
	public static final String KEY_SEX = "sex";

	public static final String DBASE_NAME = "persons";
	public static final String DBASE_TABLE = "base_info_table";
	public static final int DBASE_VERSION = 1;
	
	public static final String DBASE_CREATE = "create table base_info_table "
											+ "(_id integer primary key autoincrement, "
											+ "name text not null, "
											+ "age text not null, "									+ "sex text not null);";
	
	private DataBaseHelper mBaseHelper;
	private SQLiteDatabase mDb;
	
	public DbAdapter(Context context)
	{
		this.mContext = context;
		mBaseHelper = new DataBaseHelper(mContext, DBASE_NAME, null, DBASE_VERSION);
	}
	
	public DbAdapter openDbase()
	{
		mDb = mBaseHelper.getWritableDatabase();
		return this;
	}
	
	public void closeDbase()
	{
		mBaseHelper.close();
	}
	
	public long insertPersonInfo(PersonInfo person)
	{
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(KEY_NAME, person.name);
		initialValues.put(KEY_AGE, person.age);
		initialValues.put(KEY_SEX, person.sex);
		
		return mDb.insert(DBASE_TABLE, null, initialValues);
	}
	
	public boolean deletePersonInfo(long rowId)
	{
		return (mDb.delete(DBASE_TABLE, KEY_ID + "=" + rowId, null) > 0);
	}
	
	public Cursor getAllPersonInfo()
	{
		return mDb.query(DBASE_TABLE, new String[]{KEY_ID,
													KEY_NAME,
													KEY_AGE,
													KEY_SEX}, 
													null, null, null, null, null);
	}
	
	public Cursor getPersonInfo(long rowId)
	{
		Cursor mCursor = mDb.query(DBASE_TABLE, new String[]{KEY_ID,
													KEY_NAME,
													KEY_AGE,
													KEY_SEX}, 
													KEY_ID + "=" + rowId,
													null, null, null, null);
		if (null != mCursor)
		{
			mCursor.moveToFirst();
		}
		
		return mCursor;
	}
	
	public boolean updatePersonInfo(long rowId, PersonInfo person)
	{
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(KEY_NAME, person.name);
		initialValues.put(KEY_AGE, person.age);
		initialValues.put(KEY_SEX, person.sex);
		
		return (mDb.update(DBASE_TABLE, initialValues, KEY_ID + "=" + rowId, null) > 0);
	}
	//-----------------------------------------------------------------
	private class DataBaseHelper extends SQLiteOpenHelper{

		public DataBaseHelper(Context context, String name,
				CursorFactory factory, int version) 
		{
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(DBASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			Log.w(TAG, "Upgrading database from version " + oldVersion
					+ " to " + newVersion + ", which will destroy all old data");
			
			db.execSQL("DROP TABLE IF EXISTS titles");
			onCreate(db);
		}
	}
}

com/jesse/dbasetest1/MainActivity.java（不解释，看不懂说明Android比我还次，哈哈）。

package com.jesse.dbasetest1;

import android.os.Bundle;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity {
	private Context mContext;
	private EditText mNameEdTxt;
	private EditText mAgeEdTxt;
	private EditText mSexEdTxt;
	private Button mAddBtn;
	
	private EditText mRmIdEdTxt;
	private Button mRemoveBtn;
	
	private EditText mPersonIdEdTxt;
	private TextView mOneText;
	private Button mGetOneBtn;

	private EditText mIdUpEdTxt;
	private EditText mNameUpEdTxt;
	private EditText mAgeUpEdTxt;
	private EditText mSexUpEdTxt;
	private Button mUpBtn;
	
	private TextView mAllText;
	private Button mGetAllBtn;
	
	private DbAdapter mDbAdapter;
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
		initData();
	}
	
	private void initData()
	{
		mContext = MainActivity.this;
		
		mNameEdTxt = (EditText) this.findViewById(R.id.person_name);
		mAgeEdTxt = (EditText) this.findViewById(R.id.person_age);
		mSexEdTxt = (EditText) this.findViewById(R.id.person_sex);
		mAddBtn = (Button) this.findViewById(R.id.add_btn);
		
		mRmIdEdTxt = (EditText) this.findViewById(R.id.person_id);
		mRemoveBtn = (Button) this.findViewById(R.id.remove_btn);
		
		mPersonIdEdTxt = (EditText) this.findViewById(R.id.get_person_id);
		mOneText = (TextView) this.findViewById(R.id.one_person_info);
		mGetOneBtn = (Button) this.findViewById(R.id.get_one_btn);
		
		mIdUpEdTxt = (EditText) this.findViewById(R.id.id_up); 
		mNameUpEdTxt = (EditText) this.findViewById(R.id.person_name_up);
		mAgeUpEdTxt = (EditText) this.findViewById(R.id.person_age_up);
		mSexUpEdTxt = (EditText) this.findViewById(R.id.person_sex_up);
		mUpBtn = (Button) this.findViewById(R.id.update_btn);
		
		mAllText = (TextView) this.findViewById(R.id.all_person_info);
		mGetAllBtn = (Button) this.findViewById(R.id.get_all_btn);
		
		mAddBtn.setOnClickListener(addBtnClickListener);
		mRemoveBtn.setOnClickListener(removeBtnClickListener);
		mGetOneBtn.setOnClickListener(getOneBtnClickListener);
		mUpBtn.setOnClickListener(upBtnClickListener);
		mGetAllBtn.setOnClickListener(getAllBtnClickListener);
		
		mDbAdapter = new DbAdapter(mContext);
		mDbAdapter.openDbase();
	}
	
	OnClickListener addBtnClickListener = new OnClickListener() {
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			PersonInfo tempPerson = new PersonInfo();
			tempPerson.name = mNameEdTxt.getText().toString();
			tempPerson.age = mAgeEdTxt.getText().toString();
			tempPerson.sex = mSexEdTxt.getText().toString();
			
			if ((mNameEdTxt.getText()!=null)&&(mAgeEdTxt.getText()!=null)&&(mSexEdTxt.getText()!=null))
			{
				if (mDbAdapter.insertPersonInfo(tempPerson) >= 0)
				{
					Toast.makeText(mContext, "Add data to dbase successfull!", Toast.LENGTH_LONG).show();
				}
			}
			else
			{
				Toast.makeText(mContext, "Please resure your input data is full ok?", Toast.LENGTH_LONG).show();
			}
		}
	};
	
	OnClickListener removeBtnClickListener = new OnClickListener() {
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			if (mRmIdEdTxt.getText()!=null)
			{
				if (mDbAdapter.deletePersonInfo(Integer.parseInt(mRmIdEdTxt.getText().toString())) == true)
				{
					Toast.makeText(mContext, "Remove data from dbase successfull!", Toast.LENGTH_LONG).show();
				}
				else
				{
					Toast.makeText(mContext, "Remove data from dbase failed!", Toast.LENGTH_LONG).show();
				}
			}
			else
			{
				Toast.makeText(mContext, "Please resure your input id is full ok?", Toast.LENGTH_LONG).show();
			}
		}
	};
	
	OnClickListener getOneBtnClickListener = new OnClickListener() {
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			if (mPersonIdEdTxt.getText()!=null)
			{
				int idInPut = Integer.parseInt(mPersonIdEdTxt.getText().toString());
				Cursor cursor = mDbAdapter.getPersonInfo(idInPut);
				if (cursor.moveToFirst() == true)
				{
					PersonInfo personInfo = new PersonInfo();
					String id = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_ID));
					personInfo.name = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_NAME));
					personInfo.age = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_AGE));
					personInfo.sex = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_SEX));
					
					cursor.close();
					String str = "No:"+id+",name:"+personInfo.name+",age:"+personInfo.age+",sex:"+personInfo.sex;
					mOneText.setText(str);
				}
				else
				{
					Toast.makeText(mContext, "Get One data from dbase failed!", Toast.LENGTH_LONG).show();
				}
			}
			else
			{
				Toast.makeText(mContext, "Please resure your input id is full ok?", Toast.LENGTH_LONG).show();
			}
		}
	};
	
	OnClickListener upBtnClickListener = new OnClickListener() {
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			PersonInfo tempPerson = new PersonInfo();
			tempPerson.name = mNameUpEdTxt.getText().toString();
			tempPerson.age = mAgeUpEdTxt.getText().toString();
			tempPerson.sex = mSexUpEdTxt.getText().toString();
			
			if (mDbAdapter.updatePersonInfo(Integer.parseInt(mIdUpEdTxt.getText().toString()), tempPerson) == true)
			{
				Toast.makeText(mContext, "Update data to dbase successfull!", Toast.LENGTH_LONG).show();
			}
			else
			{
				Toast.makeText(mContext, "Update data to dbase falied!", Toast.LENGTH_LONG).show();
			}
		}
	};
	
	OnClickListener getAllBtnClickListener = new OnClickListener() {
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			Cursor cursor = mDbAdapter.getAllPersonInfo();
			if (cursor.moveToFirst() == true)
			{
				String str = "";
				
				for (int index=0; index<cursor.getCount(); index++)
				{
					PersonInfo temp = new PersonInfo();
					String id = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_ID));
					temp.name = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_NAME));
					temp.age = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_AGE));
					temp.sex = cursor.getString(cursor.getColumnIndex(DbAdapter.KEY_SEX));
					
					str = str + "No:"+id+",name:"+temp.name+",age:"+temp.age+",sex:"+temp.sex + "\n";
					cursor.moveToNext();
				}
				cursor.close();
				
				mAllText.setText(str);
			}
			else
			{
				Toast.makeText(mContext, "Get All data from dbase failed!", Toast.LENGTH_LONG).show();
			}
		}
	};
	
	@Override
	protected void onDestroy() {
		// TODO Auto-generated method stub
		super.onDestroy();
		mDbAdapter.closeDbase();
	}
}

main_activity_layout.xml（不解释，看不懂说明Android比我还次，哈哈）。

<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:background="@drawable/bg"
    android:gravity="center"
    android:orientation="vertical"
    tools:context=".MainActivity" >

    <TextView
        android:id="@+id/title"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Jesse Android SQLite Person Info"
        android:textColor="@android:color/holo_red_light"
        android:textSize="30sp" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Sina微博昵称：工匠若水"
        android:textColor="@android:color/black"
        android:textSize="15sp" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginRight="100dp"
        android:layout_marginTop="10dp"
        android:orientation="horizontal" >

        <EditText
            android:id="@+id/person_name"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="name"
            android:inputType="text"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <EditText
            android:id="@+id/person_age"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="age"
            android:inputType="number"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <EditText
            android:id="@+id/person_sex"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="sex"
            android:inputType="text"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <Button
            android:id="@+id/add_btn"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Add"
            android:textColor="@android:color/holo_blue_dark"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginRight="100dp"
        android:layout_marginTop="40dp"
        android:orientation="horizontal" >

        <EditText
            android:id="@+id/person_id"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="id"
            android:inputType="number"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <TextView
            android:id="@+id/temp"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="2" />

        <Button
            android:id="@+id/remove_btn"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Remove"
            android:textColor="@android:color/holo_blue_dark"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginRight="100dp"
        android:layout_marginTop="40dp"
        android:orientation="horizontal" >

        <EditText
            android:id="@+id/get_person_id"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="id"
            android:inputType="text"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <TextView
            android:id="@+id/one_person_info"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="2"
            android:background="@android:color/white"
            android:text="wait get person info!"
            android:textSize="20sp" />

        <Button
            android:id="@+id/get_one_btn"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Get One"
            android:textColor="@android:color/holo_blue_dark"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginRight="100dp"
        android:layout_marginTop="40dp"
        android:orientation="horizontal" >

        <EditText
            android:id="@+id/id_up"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="id of update"
            android:inputType="text"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <EditText
            android:id="@+id/person_name_up"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="new name"
            android:inputType="text"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <EditText
            android:id="@+id/person_age_up"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="new age"
            android:inputType="number"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <EditText
            android:id="@+id/person_sex_up"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="new sex"
            android:inputType="text"
            android:singleLine="true"
            android:textColorHint="@android:color/darker_gray"
            android:textSize="20sp" />

        <Button
            android:id="@+id/update_btn"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Update"
            android:textColor="@android:color/holo_blue_dark"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginRight="100dp"
        android:layout_marginTop="40dp"
        android:orientation="horizontal" >

        <TextView
            android:id="@+id/all_person_info"
            android:layout_width="0dp"
            android:layout_height="150dp"
            android:layout_weight="3"
            android:background="@android:color/white"
            android:text="wait get all person info!"
            android:textSize="20sp" />

        <Button
            android:id="@+id/get_all_btn"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Get ALL"
            android:textColor="@android:color/holo_blue_dark"
            android:textSize="20sp" />
    </LinearLayout>

</LinearLayout>

好了，至于去哪找运行后的数据库文件和上哪用啥工具查看，自己想办法吧，不解释，你懂的，easy!

好了，看懂了逐行代码的话，一运行，嗖一下，说明你已经入门鸟！没看懂逐行直接运行说明你不太适合屌丝程序猿这个行业唉，哈哈。

点击复制链接与好友分享!回本站首页