sqlite
发布时间：2020-12-12 23:53:22 所属栏目：百科 来源：网络整理
导读：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; imp
|
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," + " extra char," + " 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," + " 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(),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); 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,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[] arg) { return db.delete(c.getSimpleName(),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,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); } } } （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |