sqlite

package com.example.specialists.database;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
 * @author luohaoxin
 *
 */
public class Db {
private static final String DATABASE_NAME = "medicine";// 数据库名
private static final String SPECIALIST_CREATE = "create table specialist(id integer primary key, "
+ " username varchar(20) not null, "
+ " password varchar(25) not null, "
+ " name varchar(20) not null, "
+ " sex integer varchar(20) not null, "
+ " id_card varchar(20) not null, "
+ " birthday varchar(15) not null, "
+ " position varchar(15) not null, "
+ " title varchar(15) not null, "
+ " political_status varchar(10) not null, "
+ " speciality varchar(20) not null, "
+ " hospital varchar(20) not null, "
+ " telephone varchar(11) not null, "
+ " cellphone varchar(11) not null, "
+ " device_id varchar(25) not null, "
+ " enabled integer not null default 1);";
private static final String PATIENT_CREATE = "create table patient(id varchar(20) primary key, "
+ " name text not null, "
+ " did integer not null, "
+ " sex text not null, "
+ " birthday text, "
+ " address text, "
+ " telephone text, "
+ " allergic_history text,"
+ " enabled integer not null default 1,"
+ " foreign key ( did ) REFERENCES doctor (id),"
+ " unique (telephone));";
private static final String CASE_CREATE = "create table case_history(id varchar(25) primary key, "
+ " pid text not null, "
+ " date text not null, "
+ " symptom text, "
+ " prescription text, "
+ " isupload integer not null,"
+ " did integer not null, "
+ " extra char, "
+ " enabled integer not null default 1,"
+ " foreign key ( pid ) REFERENCES patient (id),"
+ " foreign key ( did ) REFERENCES doctor (id));";
private static final String IMAGE_CREATE = "create table image(id varchar(25) primary key, "
+ "cid varchar(25) not null, "
+ "isupload int default 0, "
+ "enabled integer not null default 1, "
+ " foreign key ( cid ) REFERENCES case_history (id));";
private static final String COMMENT_CREATE = "create table comment(id varchar(25) primary key, "
+ "cid char not null, "
+ "sid integer not null, "
+ "content char, "
+ "extra char, "
+ "isupload integer default 0, "
+ "isdownload integer default 0, "
+ "enabled integer not null default 1,"
+ " foreign key ( cid ) REFERENCES case_history (id), "
+ " foreign key ( sid ) REFERENCES doctor (id));";

private static final String BETA_CREATE = "create table specialist_beta(id integer primary key, "
+ "version integer, "
+ "time text);";

private static final String CASE_OPERATION_CREATE = "create table case_history_operation(id text primary key, "
+ "code integer not null);";
private static final String IMAGE_OPERATION_CREATE = "create table image_operation(id text primary key, "
+ "code integer not null);";
private static final String SUGGESTION_CREATE = "create table suggestion(id integer primary key, "
+ "content text );";
private final Context context;
private DBOpenHelper dbOpenHelper;
private SQLiteDatabase db;
private static final int DB_VERSION = 5;
public Db(Context _context) {
this.context = _context;

}

// 打开数据库
    public void open() throws SQLException {
dbOpenHelper = new DBOpenHelper(context, DATABASE_NAME, null, DB_VERSION);
try {
db = dbOpenHelper.getWritableDatabase();
//db.execSQL("PRAGMA foreign_keys = ON");
}
catch (SQLiteException ex) {
db = dbOpenHelper.getReadableDatabase();
//db.execSQL("PRAGMA foreign_keys = ON");
}  
}


// 关闭数据库
public void close() {
if (db != null){
db.close();
db = null;
}

}
public long insert(Object entity) {//返回插入行的ID

Class<?> c=entity.getClass();
ContentValues newValues = new ContentValues();
//HashMap<String, Object> hashMap= entity.GetInfo();
Field[] field = c.getDeclaredFields();
try
{
       for (int i = 0; i < field.length; i++) {
           Class<?> type = field[i].getType();
           
           String key=field[i].getName();
           if(type.getName().equals("int"))
           {
            Method method=c.getMethod("get"+key.substring(0,1).toUpperCase()+key.substring(1));
               int value=Integer.parseInt(String.valueOf(method.invoke(entity)));
               newValues.put(key, value);
           }
           if(type.getName().equals("java.lang.String"))
           {
            Method method=c.getMethod("get"+key.substring(0,1).toUpperCase()+key.substring(1));
               String value=(String)method.invoke(entity);
               newValues.put(key, value);
           } 
       }
}
catch(Exception e){Log.i("insert", e.toString());}
   return db.insert(c.getSimpleName(), null, newValues);
}
public<T> ArrayList<T> query(Class<T> c,String where,String[] arg)
{
try
{
ArrayList<String> column=new ArrayList<String>();
Field[] field = c.getDeclaredFields();
String key;
for (int i = 0; i < field.length; i++) {
           key=field[i].getName();
           column.add(key);
}
String []Column=new String[column.size()];
Column=column.toArray(Column);
Cursor cursor =db.query(c.getSimpleName(), Column, where, arg, null, null, null);
return convert_to_entity(c, cursor);
}
catch (Exception e) {
e.printStackTrace();
return new ArrayList<T>();
}

}
public<T> ArrayList<T> query(Class<T> c,Object entity){
ArrayList<Object> result=new ArrayList<Object>();
ArrayList<String> arg=new ArrayList<String>();

String sql="select * from "+c.getSimpleName()+" where ";
String key;
Object value;
Field[] field = c.getDeclaredFields();
try
{
       for (int i = 0; i < field.length; i++) {
           Class<?> type = field[i].getType();
           
           key=field[i].getName();
           if(type.getName().equals("int"))
           {
            Method method=c.getMethod("get"+key.substring(0,1).toUpperCase()+key.substring(1));
               value=Integer.parseInt(method.invoke(entity).toString());
               sql=sql+key+"=? and ";
               arg.add(value.toString());
               
           }
           if(type.getName().equals("java.lang.String"))
           {
            Method method=c.getMethod("get"+key.substring(0,1).toUpperCase()+key.substring(1));
               value=method.invoke(entity).toString();
               sql=sql+key+"=? and ";
               arg.add(value.toString());
           } 
       }
}
catch(Exception e){Log.i("insert", e.toString());}
String []Arg=new String[arg.size()];
Arg=arg.toArray(Arg);
sql=sql.substring(0, sql.length()-5);
Cursor cursor =db.rawQuery(sql, Arg);
return convert_to_entity(c, cursor);
}


/**Convenience method for updating rows in the database.
* @param table
* @param values
* @param whereClause
* @param whereArgs
* @return 受影响行数
*/
public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
{
return db.update(table, values, whereClause, whereArgs);
}
public int update(Object entity)
{
Object id=new Object();//用于记录下id
Class<?> c=entity.getClass();
ContentValues newValues = new ContentValues();
//HashMap<String, Object> hashMap= entity.GetInfo();
Field[] field = c.getDeclaredFields();
try
{
       for (int i = 0; i < field.length; i++) {
           Class<?> type = field[i].getType();
           
           String key=field[i].getName();
           if(type.getName().equals("int"))
           {
            Method method=c.getMethod("get"+key.substring(0,1).toUpperCase()+key.substring(1));
               int value=Integer.parseInt(String.valueOf(method.invoke(entity)));
               if(key.equals("id")) 
               {id=value;continue;}
               newValues.put(key, value);
               
           }
           if(type.getName().equals("java.lang.String"))
           {
            Method method=c.getMethod("get"+key.substring(0,1).toUpperCase()+key.substring(1));
               String value=(String)method.invoke(entity);
               if(key.equals("id")) 
               {id=value;continue;}
               newValues.put(key, value);
           } 
       }
}
catch(Exception e){Log.i("update", e.toString());}
String[]arg=new String[1];
arg[0]=id.toString();
return db.update(c.getSimpleName(), newValues, "id=?", arg);
}
public int insert_or_update(Object entity)//返回1表示插入成功，返回2表示更新成功，-1表示出现错误
{
if(update(entity)!=0) return 2;
if(insert(entity)!=-1) return 1;
return -1;
}
public int delete(Class<?> c,String where,String[] arg)
{
return db.delete(c.getSimpleName(), where, arg);
}
public<T> ArrayList<T> convert_to_entity(Class<T> c,Cursor cursor)
{
Field[] field = c.getDeclaredFields();
String key;
ArrayList<T> result=new ArrayList<T>();
cursor.moveToFirst();
int aaa=cursor.getCount();

for(int i=0;i<cursor.getCount();i++)
{
try
{
T object=c.newInstance();
for (int j = 0; j < field.length; j++) {
           Class<?> type = field[j].getType();
           key=field[j].getName();
           if(type.getName().equals("int"))
           {
           
            Method method=c.getMethod("set"+key.substring(0,1).toUpperCase()+key.substring(1),int.class);
               method.invoke(object,cursor.getInt(cursor.getColumnIndex(key)));
               //sqlite数据库的表的列名只能是小写，所以有些原本大写
           }
           if(type.getName().equals("java.lang.String"))
           {
            Method method=c.getMethod("set"+key.substring(0,1).toUpperCase()+key.substring(1),String.class);
               method.invoke(object,cursor.getString(cursor.getColumnIndex(key)));
           } 
       }
result.add(object);

}
catch(Exception e){Log.i("query", e.toString());}
cursor.moveToNext();
}
cursor.close();
return result;
}
private static class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context, String name, CursorFactory factory, int version) {
   super(context, name, factory, version);
 }


@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(SPECIALIST_CREATE);
db.execSQL(PATIENT_CREATE);
db.execSQL(CASE_CREATE);
db.execSQL(IMAGE_CREATE);
db.execSQL(COMMENT_CREATE);
db.execSQL(BETA_CREATE);
db.execSQL(CASE_OPERATION_CREATE);
db.execSQL(IMAGE_OPERATION_CREATE);
db.execSQL(SUGGESTION_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS doctor");
db.execSQL("DROP TABLE IF EXISTS patient");
db.execSQL("DROP TABLE IF EXISTS case_history");
db.execSQL("DROP TABLE IF EXISTS image");
db.execSQL("DROP TABLE IF EXISTS comment");
db.execSQL("DROP TABLE IF EXISTS message");
db.execSQL("DROP TABLE IF EXISTS beta");
db.execSQL("DROP TABLE IF EXISTS patient_operation");
db.execSQL("DROP TABLE IF EXISTS case_history_operation");
db.execSQL("DROP TABLE IF EXISTS image_operation");
db.execSQL("DROP TABLE IF EXISTS message_operation");
db.execSQL("DROP TABLE IF EXISTS suggestion");
onCreate(db);
}
}


}