android SQLiteDatebase 实践

来源：互联网发布：比尔克软件编辑：程序博客网时间：2024/06/10 03:29

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

MdbHelper类:

view plain
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继承:

view plain
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);  
    }  
      
}  

页面代码：

view plain
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:

view plain
<?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:

view plain
<?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:

view plain
<?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>  