十八、十九、二十数据库案例（学生信息管理）

使用sqlite工具和Android代码的方式建立SQLite数据库。在完成建立数据库工作后，编程实现基本的数据库操作功能，包括数据的添加、删除、修改和更新。
学生信息管理案例:
①创建新工程
②创建一个包com.imau.dao 用来写数据库
名字 DBHelper
③再创建一个包com.imau.entity
名字为student
④再建一个com.imau.controller
名字为studentcontroller
⑥创建常量包：处理表的字段 com.imau.const
名字为const

实体student.java

package com.imau.entity
//与数据表对应：sql对象
public class  student{
//主键
private int_id;
//姓名
private String sname;
//学号
private String sno;
//性别
private String sex;

public Student（int_id,String sname,String sno,String sex;）{
this._id=_id;
this.sname=sname;
this.sno=sno;
this.sex=sex;
}  

public Student（）{
}
public int get_id(){
return_id;
}
public String getSname(){
return sname;
}
public String getSno(){
return sno;
}
public String getSex(){
return sex;
}
@Override
public String toString(){
return "Student{"+
"_id="+_id+
",sname='"+sname+'\''+
",sno='"+sno+'\''+
",sex='"+sex+'\''+
'}';
}


}

数据库代码DBHelper.java

package com.imau.entity
import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBAdapter {
private final Context context;
private SQLiteDatabase db;
 private DBHelper dbHelper;
 private final static String DB_TABLE="studentinfo";
private final static String DB_NAME="student.db";
   private final static int DB_VERSION=1;
private final static String KEY_ID="id";
private final static String KEY_SNO="sno";
private final static String KEY_SNAME="sname";
   private final static String KEY_CLASSES="classes";
   public DBAdapter(Context _cContext) {
   // TODO Auto-generated constructor stub
   context = _cContext;
      }
      //关闭数据库的连接
    public void close()
    {
    if(db!=null)
        {
            db.close();
            db=null;
        }
    }
    public void open() throws SQLiteException
  {
       dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
       try{ 
              db = dbHelper.getWritableDatabase();
            }catch(Exception e)
            {
               db  = dbHelper.getReadableDatabase();
            }
   }
   //插入数据
   public long insert(Student s)
   {
   ContentValues cv = new ContentValues();
        cv.put(KEY_SNO,s.getSno() );
        cv.put(KEY_SNAME, s.getSname());
        cv.put(KEY_CLASSES, s.getClasses());
        return db.insert(DB_TABLE, null, cv);
   }
//查询所有的数据
   public Student[] findAllStudent()
   {
   Cursor c = db.query(DB_TABLE, null, null, null, null,null, null)
   //       while (c.moveToNext()) {
//          System.out.println(c.getString(c.getColumnIndex("sno")));
//          
//      }
return  convertToStudent(c);
   }
public long deleteAllStudent()
  {
  return    db.delete(DB_TABLE, null, null);

   }
   //这种方发比较好能够防止sql注入
   public long deleteBySno(String sno)
  {
      return db.delete(DB_TABLE, "sno=?", new String[]{sno});
  }
  
   //这种方式其实不太好存在sql注入的问题，但是一般的情况下是可以使用的
   public long deleteBYId(int id)
 { 
 return db.delete(DB_TABLE, "id="+id, null);
 }
   public Student[] 
   findStudentsBySno(String sno)
{
     Cursor c = db.query(DB_TABLE, null, "sno=?", new String[]{sno}, null, null, null);
     return  convertToStudent(c);
 }
 public long updateOneStudentById(int id,Student s)
{
     ContentValues cv = new ContentValues();
     cv.put(KEY_SNO, s.getSno());
     cv.put(KEY_SNAME, s.getSname());
     cv.put(KEY_CLASSES, s.getClasses());

  return db.delete(DB_TABLE, cv, "id="+id, null);
  }
 public Student[] findOneStudentByID(int ID)
 {
 
 Cursor  c = db.query(DB_TABLE, null, "id="+ID, null, null, null, null);
 
return convertTOStudent（c）;
}
//转换函数
public Student[] convertToStudent(Cursor c)
   {
   int resultsCount = c.getCount();
   if(resultsCount==0||!c.moveToFirst())
   {
   return null
   }
 Student []stu = new Student[resultsCount];  
 for (int i = 0; i < stu.length; i++) {
  stu[i] = new Student();
String sno  = c.getString(c.getColumnIndex("sno"));
String sname = c.getString(c.getColumnIndex("sname"));
String sex = c.getString(c.getColumnIndex("sex"));
stu[i].id=c.getInt(0);
           stu[i].setSno(sno);
           stu[i].setSname(sname);
          stu[i].setClasses(sex);
          
c.moveToNext(); 
        }
        return stu;
        }
  //内部的静态类用于创建数据库和建立数据库链接
  private   static class DBHelper extends SQLiteOpenHelper{
  public DBHelper(Context context, String name, CursorFactory factory, int version) {
            super(context, name, factory, version);
            // TODO Auto-generated constructor stub
        }
        private static final String SQL="CREATE TABLE studentinfo ("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "sno TEXT DEFAULT NONE,"
                + "sname TEXT DEFAULT NONE,"
                + "classes TEXT DEFAULT NONE"
                + ")";
                @Override
          public void onCreate(SQLiteDatabase db) 
         {
            // TODO Auto-generated method stub
            db.execSQL(SQL);
        }
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS"+DB_TABLE);
        oncreate（db）;
        }
       }
      }
   

查洵:

public void queryAll(View view) {
StringBuffer buffer=new Str ingBuffer() ; 
//查洵
Cursor cr = db. query( table: " user", columns: null, selection: null, selectionArgs: null, groupBy; null, having: null, orderBy: null) ;
//结果遍历
//判断是否有数据
if (cr!=null) {
while(cr. moveToNext() {
//取值
int id=cr. getInt ( columnIndex: 0) ;
String name=cr. getString( columnIndex: 1);
int age=cr. getInt ( columnIndex: 2) ;
buffer. append(id) ;
buffer. append(", ");
buffer. append (name) ;
buffer. append(",");
buffer. append(age) ;
buffer. append(" \n");
}
}
//关闭游标
cr. close() ;
db. close() ;

添加：

public void add(View view) {
//insert into table 0) values 0);
ContentValues values=new ContentValues() ;
values. put(" name", "CCC");
db. insert( table:”user", nullColumnHack: null, values);
}

删除：

public void delete(View view) {
db=he lper. getWri tableDatabase() ;
int row= db. delete( table: "user", whereClause:”age = ?” , new String[]{"20"});
System. out. println(" row-==”+row);
db. close();

修改：

public void update(View view) {
db=helper. getWritableDatabase();
ContentValues values=new ContentValues();
values. put(" name", "DD");
db. update( table:" user' , values, whereClause: age= ?" , new String[]{"28' });
db. close();

效果如图：