android SQLiteDatebase 实践

android 平台下建立的一个数据库小实例。实现了数据的增、删、改、通过对话框的形式去修改、删除数据

MdbHelper类:

package training.android.com;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class MdbHelper {
	private SQLiteDatabase mdb;
	private sqldataHelper mdbhelp;
	private final Context cxt;
	public static final String KeyRowID = "_id";
	public static final String KeyRowname = "StudentName";
	public static final String KeyRowAge = "StudentAge";
	public static final String KeyRowClass = "StudentClass";
	public static final String KeyRowSchool = "StudentSchool";
	private static final String TableName = "Student";//

	public MdbHelper(Context ct) {
		this.cxt = ct;
	}

	public MdbHelper open() throws SQLException {
		mdbhelp = new sqldataHelper(cxt, 1);
		setMdb(mdbhelp.getWritableDatabase());
		return this;

	}

	public void close() {
		mdbhelp.close();
	}

	public void InsertStudent(int studnetid, String studentname,
			String studentage, String studentclass, String studentschool) {
		ContentValues values = new ContentValues();
		values.put(KeyRowID, studnetid);
		values.put(KeyRowname, studentname);
		values.put(KeyRowAge, studentage);
		values.put(KeyRowClass, studentclass);
		values.put(KeyRowSchool, studentschool);
		mdb.insert(TableName, null, values);
	}

	public void UpdateStudent(int studentid, String studentname,
			String studentage, String studentclass, String studentschool) {
		ContentValues values = new ContentValues();
		values.put(KeyRowname, studentname);
		values.put(KeyRowAge, studentage);
		values.put(KeyRowClass, studentclass);
		values.put(KeyRowSchool, studentschool);
		mdb.update(TableName, values, KeyRowID + "=" + studentid, null);
	}

	public Cursor simpleStudent(long studentid) throws SQLException {
		return mdb.query(TableName, new String[] { KeyRowID, KeyRowname,
				KeyRowAge, KeyRowClass, KeyRowSchool }, KeyRowID + "="
				+ studentid, null, null, null, null);
		// return mdb.query(TableName, new String[]{KeyRowID,
		// KeyRowname,KeyRowAge,KeyRowClass,KeyRowSchool},
		// KeyRowID+"="+studentid, null, null, null, null);
		// return mdb.query(TableName, new String[]{KeyRowID,
		// KeyRowname,KeyRowAge,KeyRowClass,KeyRowSchool},
		// KeyRowID+"="+studentid, null, null, null, null);
	}
	public boolean deletestudent(long rowId) {
		  return mdb.delete(TableName, KeyRowID + "=" + rowId, null) > 0;               //(4)
		}
	public Cursor fetchMyTableById(long rowId) throws SQLException {
		return mdb.query(TableName, new String[] { KeyRowID, KeyRowname,
				KeyRowAge, KeyRowClass, KeyRowSchool }, KeyRowID + "=" + rowId,
				null, null, null, null);
	}

	public Cursor QueryStudentALl() {
		return mdb.query(TableName, new String[] { KeyRowID, KeyRowname,
				KeyRowAge, KeyRowClass, KeyRowSchool }, null, null, null, null,
				null, null);

	}

	public void setMdb(SQLiteDatabase mdb) {
		this.mdb = mdb;
	}

	public SQLiteDatabase getMdb() {
		return mdb;
	}

}

SQLiteOpenHelper继承:

package training.android.com;


import android.content.Context;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class sqldataHelper extends SQLiteOpenHelper {
	private static final String Databasename="SmsStudent";
	private static final String TableName = "Student";//
	public static final String KeyRowID = "_id";
	public static final String KeyRowname = "StudentName";
	public static final String KeyRowAge = "StudentAge";
	public static final String KeyRowClass = "StudentClass";
	public static final String KeyRowSchool = "StudentSchool";
	private static final String StudentTableSql = "CREATE TABLE IF NOT EXISTS "
			+ TableName + "(" + KeyRowID + " INTEGER PRINAMARY KEY," + KeyRowname
			+ " VARCHAR," + KeyRowAge + " VARCHAR," + KeyRowClass + " VARCHAR,"
			+ KeyRowSchool + " VARCHAR);";
	private static final String UpdateTableGradSql = "DROP TABLE IF EXISTS"
			+ TableName;
	public sqldataHelper(Context context, int version) {
		super(context, Databasename, null, version);
		// TODO Auto-generated constructor stub
	}

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

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL(UpdateTableGradSql);
		onCreate(db);
	}
	
}

页面代码：

package training.android.com;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.DialogInterface.OnClickListener;
import android.database.Cursor;
import android.os.Bundle;
import android.view.LayoutInflater;
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;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;
//创建人：周昕
//创建时间：2010-4-1
public class studentdatabase extends Activity {
	private MdbHelper mdbhelper;
	public static final String KeyRowID = "_id";
	public static final String KeyRowname = "StudentName";
	public static final String KeyRowAge = "StudentAge";
	public static final String KeyRowClass = "StudentClass";
	public static final String KeyRowSchool = "StudentSchool";
	private EditText studentname;
	private EditText studentage;
	private EditText studentclass;
	private EditText studentschool;
	private String name;
	private String age;
	private String Class;
	private String School;
    private int Studentid;
	// private static final String TableName = "Student";//
	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);

		Binddata();
	}

	public boolean onCreateOptionsMenu(Menu menu) {
		super.onCreateOptionsMenu(menu);
		menu.add("新增");
		return true;
	}

	public boolean onOptionsItemSelected(MenuItem items) {
		super.onOptionsItemSelected(items);
		switch (items.getItemId()) {
		case 0:
			// Toast.makeText(this, "新增", Toast.LENGTH_SHORT).show();
			LayoutInflater factory = LayoutInflater.from(this);
			View myview = factory.inflate(R.layout.addstundet, null);
			studentname = (EditText) myview
					.findViewById(R.id.insertstudentname);
			studentage = (EditText) myview.findViewById(R.id.insertstudentage);
			studentclass = (EditText) myview
					.findViewById(R.id.insertstudentclass);
			studentschool = (EditText) myview
					.findViewById(R.id.insertstudentschool);
			OnClickListener click1 = new OnClickListener() {
				@Override
				public void onClick(DialogInterface dialog, int which) {
					// TODO Auto-generated method stub
					String name = studentname.getText().toString();
					String age = studentage.getText().toString();
					String sclass = studentclass.getText().toString();
					String school = studentschool.getText().toString();
					mdbhelper.open();
					Cursor firstcurss = mdbhelper.QueryStudentALl();
					int max;
					int temp = 0;
					while (firstcurss.moveToNext()) {

						max = firstcurss.getInt(0);
						if (temp < max)
							temp = max;
					}
					int maxIntID = temp;
					mdbhelper.InsertStudent(maxIntID + 1, name, age, sclass,
							school);
					Binddata();
				}
			};
			OnClickListener click2 = new OnClickListener() {

				@Override
				public void onClick(DialogInterface dialog, int which) {
					// TODO Auto-generated method stub

				}
			};
			AlertDialog newstudentdialog = new AlertDialog.Builder(this)
					.create();
			newstudentdialog.setView(myview);

			newstudentdialog.setButton("确定", click1);
			newstudentdialog.setButton2("取消", click2);
			newstudentdialog.show();
			break;
		}
		return false;

	}

	public void Binddata() {
		mdbhelper = new MdbHelper(this);
		mdbhelper.open();
		String[] student = new String[] { KeyRowID, KeyRowname, KeyRowAge,
				KeyRowClass, KeyRowSchool };
		int[] arraystudent = new int[] { R.id.studentid, R.id.studentname,
				R.id.studentage, R.id.studentclass, R.id.studenschool };
		Cursor curs = mdbhelper.QueryStudentALl();
		startManagingCursor(curs);
		SimpleCursorAdapter ad = new SimpleCursorAdapter(this,
				R.layout.list_row, curs, student, arraystudent);
		// setListAdapter(ad);如果继承ListActivity
		ListView list = (ListView) findViewById(R.id.list);
		list.setAdapter(ad);

		OnItemClickListener clicklisterner = new OnItemClickListener() {

			@Override
			public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
					long arg3) {
				// TODO Auto-generated method stub
				Studentid= arg2 + 1;
				Cursor simplestuent = mdbhelper.fetchMyTableById(Studentid);
				while (simplestuent.moveToNext()) {
					name = simplestuent.getString(1);
					age = simplestuent.getString(2);
					Class = simplestuent.getString(3);
					School = simplestuent.getString(4);
				}
				LayoutInflater factory = LayoutInflater.from(studentdatabase.this);
				View myview = factory.inflate(R.layout.addstundet, null);
				studentname = (EditText) myview
						.findViewById(R.id.insertstudentname);
				studentage = (EditText) myview
						.findViewById(R.id.insertstudentage);
				studentclass = (EditText) myview
						.findViewById(R.id.insertstudentclass);
				studentschool = (EditText) myview
						.findViewById(R.id.insertstudentschool);
				studentname.setText(name);
				studentage.setText(age);
				studentclass.setText(Class);
				studentschool.setText(School);
//				Toast.makeText(studentdatabase.this, "您当前选择的编号为：" + Studentid,
//						Toast.LENGTH_SHORT).show();
				OnClickListener click1 = new OnClickListener() {
					@Override
					public void onClick(DialogInterface dialog, int which) {
						// TODO Auto-generated method stub
						String name = studentname.getText().toString();
						String age = studentage.getText().toString();
						String sclass = studentclass.getText().toString();
						String school = studentschool.getText().toString();
						mdbhelper.open();
						mdbhelper.UpdateStudent(Studentid, name, age, sclass, school);
						Binddata();
					}
				};
				
				OnClickListener click2 = new OnClickListener() {

					@Override
					public void onClick(DialogInterface dialog, int which) {
						// TODO Auto-generated method stub

					}
				};
				OnClickListener click3=new OnClickListener() {
					
					@Override
					public void onClick(DialogInterface dialog, int which) {
						// TODO Auto-generated method stub
						mdbhelper.open();
						if(mdbhelper.deletestudent(Studentid))
						{
							Toast.makeText(studentdatabase.this, "编号为"+Studentid+"学生删除成功", Toast.LENGTH_SHORT).show();
							Binddata();
						}
					}
				};
				AlertDialog newstudentdialog = new AlertDialog.Builder(studentdatabase.this)
						.create();
				newstudentdialog.setView(myview);
				newstudentdialog.setButton("更新", click1);
				newstudentdialog.setButton2("取消", click2);
				newstudentdialog.setButton3("删除", click3);
				newstudentdialog.show();

			}
		};
		list.setOnItemClickListener(clicklisterner);
		// mdbhelper.close();
	}
}

mail.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">
	<ListView android:id="@+id/list" android:layout_width="fill_parent"
		android:layout_height="wrap_content" />
</LinearLayout>

addstundet.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="wrap_content">

	<TableLayout android:id="@+id/TableLayout01"
		android:layout_width="wrap_content" android:layout_height="wrap_content">
		<TableRow android:id="@+id/TableRow01" android:orientation="horizontal"
			android:layout_width="wrap_content" android:layout_height="wrap_content">

			<TextView android:id="@+id/txtname" android:layout_width="100px"
				android:layout_marginLeft="20px" android:layout_height="wrap_content"
				android:text="姓名"></TextView>
			<EditText android:id="@+id/insertstudentname"
				android:layout_width="150px" android:layout_height="wrap_content"
				android:text=""></EditText>
		</TableRow>
		<TableRow android:id="@+id/TableRow02" android:layout_width="wrap_content"
			android:layout_height="wrap_content">
			<TextView android:id="@+id/txtage" android:layout_width="100px"
				android:layout_marginLeft="20px" android:layout_height="wrap_content"
				android:text="年龄"></TextView>
			<EditText android:id="@+id/insertstudentage"
				android:layout_width="150px" android:layout_height="wrap_content"
				android:text=""></EditText>
		</TableRow>
		<TableRow android:id="@+id/TableRow03" android:layout_width="wrap_content"
			android:layout_height="wrap_content">
			<TextView android:id="@+id/txtclass" android:layout_width="100px"
				android:layout_marginLeft="20px" android:layout_height="wrap_content"
				android:text="班级"></TextView>
			<EditText android:id="@+id/insertstudentclass"
				android:layout_width="150px" android:layout_height="wrap_content"
				android:text=""></EditText>
		</TableRow>
		<TableRow android:id="@+id/TableRow03" android:layout_width="wrap_content"
			android:layout_height="wrap_content">
			<TextView android:id="@+id/txtschool" android:layout_width="100px"
				android:layout_marginLeft="20px" android:layout_height="wrap_content"
				android:text="学校"></TextView>
			<EditText android:id="@+id/insertstudentschool"
				android:layout_width="150px" android:layout_height="wrap_content"
				android:text=""></EditText>
		</TableRow>
		<TableRow android:id="@+id/TableRow03" android:orientation="horizontal"
			android:layout_width="wrap_content" android:layout_height="wrap_content">
			
			</TableRow>
	</TableLayout>
</LinearLayout>

list_row.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="wrap_content">
	<TextView 
	android:text="学生编号" 
	android:id="@+id/studentid"
	android:layout_width="fill_parent" 
	android:layout_height="wrap_content">
	</TextView>
	<LinearLayout
	xmlns="http://schemas.android.com/apk/res/android"
	android:orientation="horizontal"
	android:layout_width="fill_parent"
	android:layout_height="wrap_content"
	>
   <TextView
   android:id="@+id/studentname"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:text="学生姓名"
     android:layout_marginLeft="20px"
   >
   </TextView>
   <TextView android:id="@+id/studentage"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:text="学生年纪"
     android:layout_marginLeft="20px"
   ></TextView>
   <TextView
   android:id="@+id/studentclass"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:text="学生班级"
   android:layout_marginLeft="20px"
   >
   </TextView>
   <TextView android:id="@+id/studenschool"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:text="学生学校"
     android:layout_marginLeft="20px"
   ></TextView>
	</LinearLayout>
</LinearLayout>

评论

1 楼 danny3413350 2011-02-04  

大大 請問一下 我執行以下程式碼

public class sql extends SQLiteOpenHelper
{
private final static String DATEBASE_TABLE = "todo_db";
private final static int DATEBASE_VERSION = 1;
private final static String TABLE_NAME = "table_name";
public final static String TEXT_ID = "_id";
public final static String TEXT_NAEM = "text_name";
public final static String TEXT_PHONE = "text_phone";
public final static String TEXT_EMAIL = "text_email";


public sql(Context context) {
super(context, DATEBASE_TABLE , null, DATEBASE_VERSION);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
String sql = "CREATE TABLE " + TABLE_NAME + " ("
+ TEXT_ID + " INTEGER primary key, "
+ TEXT_NAEM + " text, "
+ TEXT_PHONE + " text, "
+ TEXT_EMAIL + " text)";
db.execSQL(sql);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(sql);
onCreate(db);
}

public Cursor select() {
// TODO Auto-generated method stub
SQLiteDatabase db = this.getReadableDatabase();
Cursor coursor = db.query(DATEBASE_TABLE, null, null, null, null, null, null);
return coursor;
}

}


然後 發生了 02-04 14:59:43.898: ERROR/AndroidRuntime(843): Caused by: android.database.sqlite.SQLiteException: no such table: datebase_table: , while compiling: SELECT * FROM datebase_table

的問題

要怎麼改進呢