android的ORMLite的sqlite自定义框架
16lz
2021-01-23
android的ORMLite的sqlite自定义框架
android中经常会用到sqlite数据库存储手机本地数据。
在java后台中，也有类似的对象关系映射数据库框架，
例如hibernate、mybatis数据库框架
ORMLite时android中扩展性比较好的一款数据库框架。
准备工作，导入ORMLite：
compile 'com.j256.ormlite:ormlite-android:5.0' compile 'com.j256.ormlite:ormlite-core:5.0'自定义sqlite框架实现效果:
1、 app安装的时候，会初始化数据库
2、 app版本升级后，可以更新android数据库
3、 通过javaBean对象、数据库字段的关系的方式实现对数据库的
增删改查的操作。
实现思路：
通过应用层调用依次调用底层逻辑层的思路实现。
1、dbHelp里实现sqlite的初始化和版本更新功能、调用dao的功能
2、dao层提供不同功能dao的功能与扩展口。
3、实体类层提供不同的与sqlite数据库对应的实体类与扩展口。
实现思路图：
实现代码：
DBHelper.java:
public class DBHelper extends OrmLiteSqliteOpenHelper { private static final String TAG = "DBHelper"; // 数据库名称 private static final String DATABASE_NAME = "steward.db"; // 数据库版本 // 4---v1.0.9 // 5:添加synHead到contactbase v1.0.11 // 6:创建短信签名表 v1.0.15 // 7: private static final int DATABASE_VERSION = 1; private Context ctx; // 数据库连接 private AndroidDatabaseConnection connection = null; private FamilyDao muser; private DeviceDao device; private DeviceStateDao state; private ModelDao model; private ModelInfoDao modelInfo; private RoomDao room; private TerminalDao terminal; private UserDao user; private DeviceTerminalChooseDao choose; private UserFamilyRelationDao relation; private DeviceTerminalRelationDao relations; private VoiceSetDao voices; private AlarmMessageDao mAlarmMessageDao; private ModeVoiceDao mVoices; private CombinationVoiceDao mCombination; private VideoUserDao vUserDao; private AirConditionDaoImpl airCondition; private DBHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); ctx = context; } public static DBHelper dbHelper; public static DBHelper getIntance(Context ctx) { if (dbHelper == null) { dbHelper = new DBHelper(ctx); } return dbHelper; } @Override public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {//LogUtil.i(TAG, "onCreate"); try { // 建表 TableUtils.createTable(connectionSource, Family.class); TableUtils.createTable(connectionSource, DeviceState.class); TableUtils.createTable(connectionSource, Device.class); TableUtils.createTable(connectionSource, Model.class); TableUtils.createTable(connectionSource, ModelInfo.class); TableUtils.createTable(connectionSource, Room.class); TableUtils.createTable(connectionSource, Terminal.class); TableUtils.createTable(connectionSource, User.class); TableUtils.createTable(connectionSource, DeviceTerminalChoose.class); TableUtils.createTable(connectionSource, UserFamilyRelation.class); TableUtils.createTable(connectionSource, DeviceTerminalRelation.class); TableUtils.createTable(connectionSource, VoiceSet.class); TableUtils.createTable(connectionSource, AlarmMessage.class); TableUtils.createTable(connectionSource, ModelVoice.class); TableUtils.createTable(connectionSource, CombinationVoice.class); TableUtils.createTable(connectionSource, Video.class); TableUtils.createTable(connectionSource, AirCondition.class); execSQLByBatch(db);//TableUtils.createTable(connectionSource, BaseDevice.class); } catch (Exception e) {//LogUtil.e(TAG, "创建数据库失败"); throw new RuntimeException(e); } } public static void execSQLByBatch(SQLiteDatabase db) { List list = new ArrayList<>(); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '03')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '04')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '05')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '07')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '0A')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '0E')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '0F')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '01')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '08')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '09')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '0B')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '0C')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '0D')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '10')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '03')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '05')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '07')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '0A')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '0E')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '0F')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '06')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '1C')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '02')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '17')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '13')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D6', '01')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D6', '02')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D6', '10')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D6', '06')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D6', '1C')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D6', '0F')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D2', '02')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CC', '06')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('DA', '17')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('D8', '16')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('DB', '1A')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('DC', '1C')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '1B')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '11')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '12')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '15')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('C9', '15')"); list.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('CF', '04')"); try { db.beginTransaction(); for (String sql : list) { db.execSQL(sql); } db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { db.endTransaction(); } }//private SQLiteDatabase sqLiteDatabase(SQLiteDatabase db){//////List sqls=new ArrayList<>();////sqls.add("INSERT INTO t_device_terminal_static(f_terminal_type,f_device_type) VALUES('" + "C9', '03" +// "')");////dic.execSQLByBatch(sqls);////db.insert("t_device_terminal_static",null,cv);//return db;//} @Override public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) { //如果版本号不一样，重置数据信息 clearData(); execSQLByBatch(db);//LogUtil.i(TAG, "onUpgrade")onUpgrade;//LogUtil.d(TAG, "oldVersion=" + oldVersion + " newVersion=" + newVersion);////int upgradeVersion = oldVersion;//LogUtil.d(TAG, "1 == " + upgradeVersion);//if (1 == upgradeVersion) {//try {//TableUtils.createTable(connectionSource, LinkageList.class);//TableUtils.createTable(connectionSource, Linkage.class);//TableUtils.createTable(connectionSource, StateTemp.class);//TableUtils.createTable(connectionSource, MessageButton.class);//} catch (Exception e) {//e.printStackTrace();//}//upgradeVersion = 2;//}//if (2 == upgradeVersion) {//try {//TableUtils.createTable(connectionSource, StateSlidingWindow.class);//} catch (Exception e) {//e.printStackTrace();//}//upgradeVersion = 3;//} } /** * 读取数据库文件（.sql），并执行sql语句 */ @SuppressWarnings("unused") private void executeSchema(SQLiteDatabase db, String schemaName) { BufferedReader in = null; try { in = new BufferedReader(new InputStreamReader(ctx.getAssets().open("schema/" + schemaName))); String line; String buffer = ""; while ((line = in.readLine()) != null) { buffer += line; if (line.trim().endsWith(";")) { db.execSQL(buffer.replace(";", "")); buffer = ""; } } } catch (IOException e) { Log.e("db-error", e.toString()); } finally { try { if (in != null) in.close(); } catch (IOException e) { Log.e("db-error", e.toString()); } } } public AndroidDatabaseConnection getConnection() { if (connection == null) { connection = new AndroidDatabaseConnection(getWritableDatabase(), true); } return connection; } public FamilyDao getfamilyTableDao() { if (muser == null) { try { muser = new FamilyDaoImpl(ctx, connectionSource, Family.class); } catch (Exception e) { e.printStackTrace(); } } return muser; } public DeviceDao getDeviceTableDao() { if (device == null) { try { device = new DeviceDaoImpl(ctx, connectionSource, Device.class); } catch (Exception e) { e.printStackTrace(); } } return device; } public DeviceStateDao getDeviceStateTableDao() { if (state == null) { try { state = new DeviceStateDaoImpl(ctx, connectionSource, DeviceState.class) { }; } catch (Exception e) { e.printStackTrace(); } } return state; } public ModelDao getModelDao() { if (model == null) { try { model = new ModelDaoImpl(ctx, connectionSource, Model.class) { }; } catch (Exception e) { e.printStackTrace(); } } return model; } public ModelInfoDao getModelInfoDao() { if (modelInfo == null) { try { modelInfo = new ModelInfoDaoImpl(ctx, connectionSource, ModelInfo.class) { }; } catch (Exception e) { e.printStackTrace(); } } return modelInfo; } public RoomDao getRoomTableDao() { if (room == null) { try { room = new RoomDaoImpl(ctx, connectionSource, Room.class) { }; } catch (Exception e) { e.printStackTrace(); } } return room; } public TerminalDao getTerminalTableDao() { if (terminal == null) { try { terminal = new TerminalDaoImpl(ctx, connectionSource, Terminal.class) { }; } catch (Exception e) { e.printStackTrace(); } } return terminal; } public AirConditionDao getAirConditionDao() { if (airCondition == null) { try { airCondition = new AirConditionDaoImpl(ctx, connectionSource, AirCondition.class) { }; } catch (Exception e) { e.printStackTrace(); } } return airCondition; } public UserDao getUserDao() { if (user == null) { try { user = new UserDaoImpl(ctx, connectionSource, User.class) { }; } catch (Exception e) { e.printStackTrace(); } } return user; } public DeviceTerminalChooseDao getDeviceTerminalChooseDao() { if (choose == null) { try { choose = new DeviceTerminalChooseDaoImpl(ctx, connectionSource, DeviceTerminalChoose.class) { }; } catch (Exception e) { e.printStackTrace(); } } return choose; } public UserFamilyRelationDao getUserFamilyRelationDao() { if (relation == null) { try { relation = new UserFamilyRelationDaoImpl(ctx, connectionSource, UserFamilyRelation.class) { }; } catch (Exception e) { e.printStackTrace(); } } return relation; } public DeviceTerminalRelationDao getDeviceTerminalRelationDao() { if (relations == null) { try { relations = new DeviceTerminalRelationDaoImpl(ctx, connectionSource, DeviceTerminalRelation.class) { }; } catch (Exception e) { e.printStackTrace(); } } return relations; } public VoiceSetDao getVoiceSetDao() { if (voices == null) { try { voices = new VoiceSetDaoImpl(ctx, connectionSource, VoiceSet.class); } catch (Exception e) { e.printStackTrace(); } } return voices; } public AlarmMessageDao getAlarmMessageDao() { if (mAlarmMessageDao == null) { try { mAlarmMessageDao = new AlarmMessageDaoImpl(ctx, connectionSource, AlarmMessage.class) { }; } catch (Exception e) { e.printStackTrace(); } } return mAlarmMessageDao; } public ModeVoiceDao getModeVoiceDao() { if (mVoices == null) { try { mVoices = new ModeVoiceDaoImpl(ctx, connectionSource, ModelVoice.class) { }; } catch (Exception e) { e.printStackTrace(); } } return mVoices; } public CombinationVoiceDao getCombinationVoiceDao() { if (mCombination == null) { try { mCombination = new CombinationVoiceDaoImpl(ctx, connectionSource, CombinationVoice.class) { }; } catch (Exception e) { e.printStackTrace(); } } return mCombination; } public VideoUserDao getvUserDao() { if (vUserDao == null) { try { vUserDao = new VideoUserDaoImpl(ctx, connectionSource, Video.class) { }; } catch (Exception e) { e.printStackTrace(); } } return vUserDao; } /** * 清除APP数据,并发送广播 */ public static void clearData() { MainApplication context = MainApplication.getmApplication(); ConnectionSource con = getIntance(context).getConnectionSource(); try {// SharedPreferences sharedPreferences = context.getSharedPreferences("JinXun", 0);// SharedPreferences.Editor editor1 = sharedPreferences.edit();// editor1.clear();// editor1.commit(); boolean bl = true; TableUtils.dropTable(con, Family.class, bl); TableUtils.dropTable(con, DeviceState.class, bl); TableUtils.dropTable(con, Device.class, bl); TableUtils.dropTable(con, Model.class, bl); TableUtils.dropTable(con, ModelInfo.class, bl); TableUtils.dropTable(con, Room.class, bl); TableUtils.dropTable(con, Terminal.class, bl); TableUtils.dropTable(con, User.class, bl); TableUtils.dropTable(con, DeviceTerminalChoose.class, bl); TableUtils.dropTable(con, UserFamilyRelation.class, bl); TableUtils.dropTable(con, DeviceTerminalRelation.class, bl); TableUtils.dropTable(con, VoiceSet.class, bl); TableUtils.dropTable(con, AlarmMessage.class, bl); TableUtils.dropTable(con, ModelVoice.class, bl); TableUtils.dropTable(con, Video.class, bl); TableUtils.dropTable(con, CombinationVoice.class, bl); TableUtils.dropTable(con, AirCondition.class, bl); //创建表 TableUtils.createTable(con, Family.class); TableUtils.createTable(con, DeviceState.class); TableUtils.createTable(con, Device.class); TableUtils.createTable(con, Model.class); TableUtils.createTable(con, ModelInfo.class); TableUtils.createTable(con, Room.class); TableUtils.createTable(con, Terminal.class); TableUtils.createTable(con, User.class); TableUtils.createTable(con, DeviceTerminalChoose.class); TableUtils.createTable(con, UserFamilyRelation.class); TableUtils.createTable(con, DeviceTerminalRelation.class); TableUtils.createTable(con, VoiceSet.class); TableUtils.createTable(con, AlarmMessage.class); TableUtils.createTable(con, ModelVoice.class); TableUtils.createTable(con, Video.class); TableUtils.createTable(con, CombinationVoice.class); TableUtils.createTable(con, AirCondition.class); execSQLByBatch(getIntance(context).getWritableDatabase()); } catch (Exception e) { e.printStackTrace(); } //发送清空数据广播 context.sendBroadcast(new Intent(notifyConstants.REFRESH_CLEAN_DATA_BROADCAST)); }} UserDao.java :
public interface UserDao extends Dao { public void save(User id); public void deleteAll(); public List getAll(); public List getAll2(); public void updateUser(String userId, String userType);} TerminalDao.java:
public interface TerminalDao extends Dao { public void save(Terminal id); /** * 修改终端名称 * * @param terminalList */ public void updateNameAll(List> terminalList); /** * 保存终端集合(包含终端,设备,终端与设备关系表的保存) * * @param terminalList */ public void saveAll(List terminalList); /** * 删除终端集合(包含终端,设备,终端与设备关系表的删除) * * @param terminalList */ public void deleteAll(List terminalList, boolean isAll); /** * 保存终端(包含终端,设备,终端与设备关系表的保存) * * @param terminal */ public void saveTerminalDevice(Terminal terminal); /** * 获取设备对应的终端列表(添加设备时,选择终端) * * @param familyId * @param roomId * @param devType * @return */ public List getTerminalListByType(String familyId, String roomId, String devType); /** * 获取当前房间所有终端列表 * * @param room * @return */ public List getTerminalListByRoom(Room room); /** * 获取当前房间所有终端列表2 * * @param room * @return */ public List getTerminalListByRoom2(Room room); /** * 获取当前房间所有终端列表2 * * @param room * @return */ public List getTerminalListByRoom3(Room room); /** * 获取当前房间所有终端与对应设备列表 * * @param room * @return */ public List getTerminalDevListByRoom(Room room); /** * 获取当前房间所有终端列表(房间列表页面使用) * * @param room * @return */ public List getListByRoom(Room room); /** * 获取当前房间所有终端与对应设备列表(房间列表页面使用) * * @param room * @return */ public List getTerDevListByRoom(Room room); /** * 获取当前房间所有终端与对应设备列表(房间列表页面使用) * * @param room * @return */ public List getTerDevListByRoom2(Room room); /** * 删除房间中所有终端 * * @param terminal * @return */ void deleteRoomTerminal(Terminal terminal); /** * 删除单个终端数据 */ public void deleteTerminal(Terminal terminal); public void updateDate(); //更新下發時候的數據 void updateAllSelcet(List terminalList);} DeviceDao.java:
public interface DeviceDao extends Dao { public void save(Device id); /** * 获取当前房间所有设备列表 * * @param room * @return */ public List getDeviceListByRoom(Room room); /** * 获取模式列表中的设备 * * @param room * @return */ public List getDeviceListByModel(Room room); /** * 修改设备名称 * * @param deviceList */ public void updateNameAll(List> deviceList); /** * 删除设备 * * @param device */ public void deleteDevice(Device device); /** * 更新GD32主动上报的搜素结果 * * @param familyId * @param roomId * @param alarm */ void updateSearch(String familyId, String roomId, String alarm);} UserDaoImpl.java:
public class UserDaoImpl extends BaseDaoImpl implements UserDao { private DBHelper dbHelper; public UserDaoImpl(Context ctx, ConnectionSource connectionSource, Class dataClass) throws Exception { super(connectionSource, dataClass); dbHelper = DBHelper.getIntance(ctx); } @Override public void save(User bean) { // TODO Auto-generated method stub try { create(bean); } catch (Exception e) { e.printStackTrace(); } } @Override public void deleteAll() { SQLiteDatabase db = dbHelper.getWritableDatabase(); //修改SQL语句 String sql = "delete from t_user"; //执行SQL db.execSQL(sql); } @Override public List getAll() { List list = null; SQLiteDatabase db = dbHelper.getReadableDatabase(); Cursor c = db.query("t_user", null, null, null, null, null, null); //查询并获得游标 if (c.moveToFirst()) {//判断游标是否为空 list = new ArrayList<>(); for (int i = 0; i < c.getCount(); i++) { c.move(i);//移动到指定记录 User user = new User(); user.setId(c.getInt(c.getColumnIndex("id"))); user.setUserName(c.getString(c.getColumnIndex("f_user_name"))); user.setUserType(c.getString(c.getColumnIndex("f_user_type"))); user.setUserId(c.getString(c.getColumnIndex("f_user_id"))); user.setPassword(c.getString(c.getColumnIndex("f_user_password"))); user.setUserToken(c.getString(c.getColumnIndex("f_user_token"))); user.setIsextrusion(c.getString(c.getColumnIndex("f_user_isextrusion"))); user.setPhoneId(c.getString(c.getColumnIndex("f_phone_id"))); user.setIsaccredit(c.getString(c.getColumnIndex("f_user_accredit"))); user.setUser_group(c.getString(c.getColumnIndex("f_user_group"))); user.setIsquituser(c.getString(c.getColumnIndex("f_user_isquituser"))); list.add(user); } c.close(); } return list; } @Override public List getAll2() { List list = null; SQLiteDatabase db = dbHelper.getReadableDatabase(); Cursor c = db.query("t_user", null, null, null, null, null, null); //查询并获得游标 if (c.moveToFirst()) {//判断游标是否为空 list = new ArrayList(); for (int i = 0; i < c.getCount(); i++) { c.move(i);//移动到指定记录 User user = new User(); user.setId(c.getInt(c.getColumnIndex("id"))); user.setUserName(c.getString(c.getColumnIndex("f_user_name"))); user.setUserId(c.getString(c.getColumnIndex("f_user_id")));//user.setUserType(c.getString(c.getColumnIndex("f_user_type"))); user.setPassword(c.getString(c.getColumnIndex("f_user_password")));//user.setUserToken(c.getString(c.getColumnIndex("f_user_token"))); list.add(user); } c.close(); } return list; } @Override public void updateUser(String userId, String userType) { }} TerminalDaoImpl.java:
public class TerminalDaoImpl extends BaseDaoImpl implements TerminalDao { private DBHelper dbHelper; protected TerminalDaoImpl(Context context, ConnectionSource connectionSource, Class dataClass) throws SQLException { super(connectionSource, dataClass); dbHelper = DBHelper.getIntance(context); } @Override public void saveAll(List terminalList) { if (terminalList.size() > 0) { for (int i = 0; i < terminalList.size(); i++) { saveTerminalDevice(terminalList.get(i)); } } terminalList.clear(); } @Override public void updateNameAll(List> terminalList) { for (Map map : terminalList) { updateName(map); } } public boolean updateName(Map map) { boolean result = false; SQLiteDatabase database = dbHelper.getWritableDatabase(); String familyId = map.get("familyId"); String roomId = map.get("roomId"); String name = map.get("name"); String terminalAddress = map.get("terminalAddress"); String terminalType = map.get("terminalType"); ContentValues cv = new ContentValues(); cv.put("f_terminal_name", name); if (name.equals("")) { result = false; } else { String[] args = new String[]{familyId, roomId, terminalAddress, terminalType}; int i = database.update("t_terminal", cv, "f_family_id=? and f_room_id=? and f_terminal_address=? and " + "f_terminal_type=?", args); if (i == 1) { result = true; } } return result; } @Override public void saveTerminalDevice(Terminal ter) { //是否已经保存数据 if (!isSaveTer(ter)) { dbHelper.getTerminalTableDao().save(ter); } if (ter.getDevice() != null) { Device dev = ter.getDevice(); if (!isSaveDeve(dev)) { dbHelper.getDeviceTableDao().save(dev); } else { } if (dev.getDeviceState() != null) { if (isSaveDeviSta(dev.getDeviceState())) { update_DeviceSate(dev.getDeviceState()); } else { dbHelper.getDeviceStateTableDao().save(dev.getDeviceState()); } } //终端设备的关系表 DeviceTerminalRelation dtr = new DeviceTerminalRelation(ter.getFamilyId(), ter.getRoomId(), ter.getTerminalAddress(), dev.getDeviceNumber(), dev.getDeviceType(), dev.getDataNumber()); if (isSaveRelations(dtr)) { update_RelationDao(dtr); } else { dbHelper.getDeviceTerminalRelationDao().save(dtr); } } } @Override public void updateAllSelcet(List terminalList) { if (terminalList.size() > 0) { for (int i = 0; i < terminalList.size(); i++) { updateBySelcet(terminalList.get(i)); } } terminalList.clear(); } public void updateBySelcet(Terminal ter) { //是否已经保存数据 if (isSaveTer(ter)) { update_Terminal(ter); } if (ter.getDevice() != null) { Device dev = ter.getDevice(); if (isSaveDeve(dev)) { update_Device(dev); } if (dev.getDeviceState() != null) { update_DeviceSate(dev.getDeviceState()); } } } public void update_Terminal(Terminal ter) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); ContentValues values = new ContentValues(); values.put("f_parameter1", ter.getParameter1()); values.put("f_parameter2", ter.getParameter2()); values.put("f_parameter3", ter.getParameter3()); values.put("f_parameter4", ter.getParameter4());// values.put("f_property", ter.getProperty()); try { db.update("t_terminal", values, "f_terminal_num = ? and f_room_id=? and f_family_id = ?", new String[]{ter .getTerminalNum(), ter.getRoomId(), ter.getFamilyId()}); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } db.endTransaction(); } public void update_Device(Device device) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); ContentValues values = new ContentValues(); values.put("f_device_name", device.getDeviceName()); /**不能恢复下面这行代码*********/ //values.put("f_device_number", device.getDeviceNumber()); try { db.update("t_device", values, "f_family_id = ? and f_room_id = ? and f_device_number = ?", new String[]{device.getFamilyId(), device.getRoomId(), device.getDeviceNumber()}); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } db.endTransaction(); } public void update_DeviceSate(DeviceState deviceState) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); ContentValues values = new ContentValues(); values.put("f_device_state", deviceState.getDeviceState()); values.put("f_fortify_state", deviceState.getFortifyState()); values.put("f_device_onofftimes", deviceState.getOnOffTimes()); values.put("f_device_runTime", deviceState.getRunTime()); values.put("f_update_time", deviceState.getUpdateTime()); values.put("f_sensor_address1", deviceState.getSensorAddress1()); values.put("f_sensor_address2", deviceState.getSensorAddress2()); values.put("f_auto_opt", deviceState.getAutoOpt()); values.put("f_rated_power", deviceState.getRatedPower()); values.put("f_load_power", deviceState.getLoadPower()); values.put("f_temperature", deviceState.getTemperature()); values.put("f_brilliance", deviceState.getBrilliance()); values.put("f_parameter3", deviceState.getParameter3()); values.put("f_parameter4", deviceState.getParameter4()); values.put("f_alarm_type", deviceState.getAlarmType()); values.put("f_alarm_state", deviceState.getAlarmState()); values.put("f_timing_selection", deviceState.getTimingSelection()); try { db.update("t_device_state", values, "f_family_id=? and f_room_id=? and f_device_number = ?", new String[]{deviceState.getFamilyId(), deviceState.getRoomId(), deviceState.getDeviceNumber()}); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } db.endTransaction(); } public void update_RelationDao(DeviceTerminalRelation terminalRelation) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); ContentValues values = new ContentValues(); values.put("f_terminal_address", terminalRelation.getTerminalAddress()); values.put("f_device_number", terminalRelation.getDeviceNumber()); values.put("f_device_type", terminalRelation.getDeviceType()); values.put("f_data_number", terminalRelation.getDataNumber()); try { db.update("t_device_terminal", values, "f_family_id = ? and f_room_id = ? and f_device_number = ?", new String[]{terminalRelation.getFamilyId(), terminalRelation.getRoomId(), terminalRelation .getDeviceNumber()}); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } db.endTransaction(); } public boolean isSaveTer(Terminal ter) { boolean result = false; SQLiteDatabase db = null; Cursor cursor = null; String sql = "select count(*) from t_terminal where f_family_id = ? and f_room_id = ? and f_terminal_address " + "= ? and f_terminal_num = ? and f_terminal_type = ? "; String[] args = new String[]{ter.getFamilyId(), ter.getRoomId(), ter.getTerminalAddress(), ter.getTerminalNum (), ter.getTerminalType()}; try { db = dbHelper.getReadableDatabase(); cursor = db.rawQuery(sql, args); if (cursor.moveToNext()) { int count = cursor.getInt(0); if (count > 0) { result = true; } } } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) cursor.close(); } return result; } public boolean isSaveDeve(Device ter) { boolean result = false; SQLiteDatabase db = null; Cursor cursor = null; String sql = "select count(*) from t_device where " + "f_family_id = ? and f_room_id = ? and f_device_number = ? and f_device_type = ?"; String[] args = new String[]{ter.getFamilyId(), ter.getRoomId(), ter.getDeviceNumber(), ter.getDeviceType()}; try { db = dbHelper.getReadableDatabase(); cursor = db.rawQuery(sql, args); if (cursor.moveToNext()) { int count = cursor.getInt(0); if (count > 0) { result = true; } } } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) cursor.close(); } return result; } public boolean isSaveDeviSta(DeviceState deviceState) { boolean result = false; SQLiteDatabase db = null; Cursor cursor = null; String sql = "select count(*) from t_device_state where" + "f_family_id = ? and f_room_id= ? and f_device_number = ?"; String[] args = new String[]{deviceState.getFamilyId(), deviceState.getRoomId(), deviceState.getDeviceNumber()}; try { db = dbHelper.getReadableDatabase(); cursor = db.rawQuery(sql, args); if (cursor.moveToNext()) { int count = cursor.getInt(0); if (count > 0) { result = true; } } } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) cursor.close(); } return result; } public boolean isSaveRelations(DeviceTerminalRelation dtr) { boolean result = false; SQLiteDatabase db = null; Cursor cursor = null; String sql = "select count(*) from t_device_terminal where" + "f_data_number = ? and f_device_number = ? and f_device_type = ? and f_family_id = ? and f_room_id" + " = ?"; String[] args = new String[]{dtr.getDataNumber(), dtr.getDeviceNumber(), dtr.getDeviceType(), dtr.getFamilyId (), dtr.getRoomId()}; try { db = dbHelper.getReadableDatabase(); cursor = db.rawQuery(sql, args); if (cursor.moveToNext()) { int count = cursor.getInt(0); if (count > 0) { result = true; } } } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) cursor.close(); } return result; } @Override public void save(Terminal id) { try { create(id); } catch (SQLException e) { e.printStackTrace(); } } @Override public List getTerminalListByType(String familyId, String roomId, String devType) { try { List list = new ArrayList(); SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select t_terminal.* from t_terminal left join t_device_terminal_static" + " on t_terminal.f_terminal_type=t_device_terminal_static.f_terminal_type" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " and t_device_terminal_static.f_device_type = ?"; Cursor cursor = database.rawQuery(sql, new String[]{familyId, roomId, devType}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { } return null; } //获取设备的属性和设备关系表 @Override public List getTerminalListByRoom(Room room) { List list = new ArrayList(); try { SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select t_terminal.* ,b.* ,b.id dev_id from t_terminal left join t_device_terminal" + " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" + " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id =?)" + " b" + " on t_device_terminal.f_device_number = b.f_device_number" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " order by t_terminal.f_terminal_num"; Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId(), room.getFamilyId(), room.getRoomId()}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 //设置终端参数 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); //设置设备参数 Device device = new Device(); device.setId(cursor.getInt(cursor.getColumnIndex("dev_id"))); device.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); device.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); device.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("f_device_number"))); device.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type"))); device.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); //device.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); data.setDevice(device); list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { } return list; } @Override public List getTerminalListByRoom2(Room room) { List list = new ArrayList(); try { SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select t_terminal.* ,b.* ,b.id dev_id ,t_device_state.id stateid,t_device_state.* ," + "t_device_terminal.f_device_number deviceNumber from t_terminal left join t_device_terminal" + " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" + " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id =?)" + " b" + " on t_device_terminal.f_device_number = b.f_device_number left join t_device_state on (b" + ".id=t_device_state.f_device_number)" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " order by t_terminal.f_terminal_num"; Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId(), room.getFamilyId(), room.getRoomId()}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 //设置终端参数 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); //设置设备参数 Device device = new Device(); device.setId(cursor.getInt(cursor.getColumnIndex("dev_id"))); device.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); device.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); device.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); device.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("f_device_number"))); device.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type")));// device.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); //设备状态信息 DeviceState deviceState = new DeviceState(); deviceState.setId(cursor.getInt(cursor.getColumnIndex("id"))); deviceState.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); deviceState.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); deviceState.setDeviceNumber(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_device_number"))); deviceState.setDeviceState(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_device_state"))); deviceState.setOnOffTimes(cursor.getInt(cursor.getColumnIndex("t_device_state" + ".f_device_onofftimes"))); deviceState.setRunTime(cursor.getInt(cursor.getColumnIndex("t_device_state.f_device_runTime"))); deviceState.setFortifyState(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_fortify_state"))); deviceState.setVoltage(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_voltage"))); device.setDeviceState(deviceState); data.setDevice(device); list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { } return list; } @Override public List getTerminalListByRoom3(Room room) { List list = new ArrayList(); try { SQLiteDatabase database = dbHelper.getReadableDatabase();// String sql = "select t_terminal.* ,b.* ,b.id dev_id ,t_device_state.id stateid,t_device_state.* ,// t_device_terminal.f_device_number deviceNumber from t_terminal left join t_device_terminal" +// " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" +// " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id// =?) b" +// " on t_device_terminal.f_device_number = b.f_device_number left join t_device_state on// (b.id=t_device_state.f_device_number)" +// " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" +// " order by t_terminal.f_terminal_num"; String sql = "select t_terminal.* ,b.* ,b.id dev_id ,t_device_state.id stateid,t_device_state.* ," + "t_device_terminal.f_device_number deviceNumber from t_terminal left join t_device_terminal" + " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" + " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id =?)" + " b" + " on t_device_terminal.f_device_number = b.f_device_number left join t_device_state on (b" + ".id=t_device_state.f_device_number)" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " order by t_terminal.f_terminal_num"; Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId(), room.getFamilyId(), room.getRoomId()}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 //设置终端参数 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); //设置设备参数 Device device = new Device(); device.setId(cursor.getInt(cursor.getColumnIndex("dev_id"))); device.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); device.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); device.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("deviceNumber"))); device.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type"))); device.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number")));// device.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); //设备状态信息 DeviceState deviceState = new DeviceState(); deviceState.setId(cursor.getInt(cursor.getColumnIndex("stateid"))); deviceState.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); deviceState.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); deviceState.setDeviceNumber(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_device_number"))); deviceState.setDeviceState(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_device_state"))); deviceState.setOnOffTimes(cursor.getInt(cursor.getColumnIndex("t_device_state" + ".f_device_onofftimes"))); deviceState.setRunTime(cursor.getInt(cursor.getColumnIndex("t_device_state.f_device_runTime"))); deviceState.setFortifyState(cursor.getString(cursor.getColumnIndex("t_device_state" + ".f_fortify_state"))); device.setDeviceState(deviceState); data.setDevice(device); list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { } return list; } @Override public List getTerminalDevListByRoom(Room room) { List list = new ArrayList(); try { SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select t_terminal.* ,b.id dev_id ,b.f_device_name,b.f_device_number,b.f_device_type,b" + ".f_data_number from t_terminal left join t_device_terminal" + " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" + " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id =?)" + " b" + " on t_device_terminal.f_device_number = b.f_device_number" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " order by t_terminal.f_terminal_num"; Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId(), room.getFamilyId(), room.getRoomId()}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 //设置终端参数 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); //设置设备参数 Device device = new Device(); device.setId(cursor.getInt(cursor.getColumnIndex("dev_id"))); device.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); device.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); device.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("f_device_number"))); device.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type"))); device.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number")));// device.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); if (device.getDeviceNumber() != null) { data.setDevice(device); } list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { } return list; } @Override public List getListByRoom(Room room) { List list = new ArrayList(); Map map = new HashMap(); map.put("f_family_id", room.getFamilyId()); map.put("f_room_id", room.getRoomId()); try { list = queryForFieldValues(map); if (list == null) { list = new ArrayList(); } } catch (Exception e) { list = new ArrayList(); return list; } return list; } @Override public List getTerDevListByRoom(Room room) { List list = new ArrayList<>(); try { SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select t_terminal.* ,t_device_terminal.f_data_number dn,b.id dev_id ,b.f_device_name,b" + ".f_device_number,b.f_device_type,b.f_search from t_terminal left join t_device_terminal" + " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" + " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id =?)" + " b" + " on t_device_terminal.f_device_number = b.f_device_number" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " order by t_terminal.f_terminal_num"; String sql1 = "select t_terminal.* ,t_device_terminal.f_data_number dn,t_device.id dev_id ,t_device" + ".f_device_name," + "t_device.f_device_number,t_device.f_device_type,t_device.f_search from t_terminal " + " left join t_device_terminal on t_terminal.f_family_id=t_device_terminal.f_family_id" + " and t_terminal.f_room_id=t_device_terminal.f_room_id" + " and t_terminal.f_terminal_address=t_device_terminal.f_terminal_address" + " left join t_device on t_device.f_family_id=t_device_terminal.f_family_id and t_device" + ".f_room_id=t_device_terminal.f_room_id and " + " t_device.f_data_number=t_device_terminal.f_data_number" + " and t_device.f_device_number=t_device_terminal.f_device_number and t_device" + ".f_device_type=t_device_terminal.f_device_type" + " where t_terminal.f_family_id=? and t_terminal.f_room_id=? order by t_terminal" + ".f_terminal_num";// Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId(),// room.getFamilyId(), room.getRoomId()}); Cursor cursor = database.rawQuery(sql1, new String[]{room.getFamilyId(), room.getRoomId()}); String tt = ""; try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 //设置终端参数 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setParameter4(cursor.getString(cursor.getColumnIndex("f_parameter4"))); data.setProperty(cursor.getString(cursor.getColumnIndex("f_property"))); data.setReserve(cursor.getString(cursor.getColumnIndex("f_reserve"))); //设置设备参数 Device device = new Device(); device.setId(cursor.getInt(cursor.getColumnIndex("dev_id"))); device.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); device.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); device.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("f_device_number"))); device.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); device.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type"))); if (device.getDeviceNumber() != null) {//终端关联了设备 data.setDevice(device); //设置资料包序号 device.setDataNumber(cursor.getString(cursor.getColumnIndex("dn"))); } list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { e.printStackTrace(); } return list; } @Override public List getTerDevListByRoom2(Room room) { List list = new ArrayList(); try { SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select t_terminal.* ,t_device_terminal.f_data_number dn,b.id dev_id ,b.f_device_name,b" + ".f_device_number,b.f_device_type from t_terminal left join t_device_terminal" + " on (t_terminal.f_terminal_address=t_device_terminal.f_terminal_address ) left join" + " (select t_device.* from t_device where t_device.f_family_id =? and t_device.f_room_id =?)" + " b" + " on t_device_terminal.f_device_number = b.f_device_number" + " where t_terminal.f_family_id =? and t_terminal.f_room_id =?" + " order by t_terminal.f_terminal_num"; Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId(), room.getFamilyId(), room.getRoomId()}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 //设置终端参数 Terminal data = new Terminal(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setTerminalName(cursor.getString(cursor.getColumnIndex("f_terminal_name"))); data.setTerminalType(cursor.getString(cursor.getColumnIndex("f_terminal_type"))); data.setTerminalNum(cursor.getString(cursor.getColumnIndex("f_terminal_num"))); data.setTerminalAddress(cursor.getString(cursor.getColumnIndex("f_terminal_address"))); data.setWifiChannel(cursor.getString(cursor.getColumnIndex("f_wifi_channel"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); data.setParameter1(cursor.getString(cursor.getColumnIndex("f_parameter1"))); data.setParameter2(cursor.getString(cursor.getColumnIndex("f_parameter2"))); data.setParameter3(cursor.getString(cursor.getColumnIndex("f_parameter3"))); data.setParameter4(cursor.getString(cursor.getColumnIndex("f_parameter4"))); data.setProperty(cursor.getString(cursor.getColumnIndex("f_property"))); data.setReserve(cursor.getString(cursor.getColumnIndex("f_reserve"))); data.setReserve(cursor.getString(cursor.getColumnIndex("f_isonetomany"))); //设置设备参数 Device device = new Device(); device.setId(cursor.getInt(cursor.getColumnIndex("dev_id"))); device.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); device.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); device.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); device.setDeviceNumber(cursor.getString(cursor.getColumnIndex("f_device_number"))); device.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type"))); device.setDataNumber(cursor.getString(cursor.getColumnIndex("dn")));// device.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); //if(device.getDeviceNumber() != null){//终端关联了设备 data.setDevice(device); //设置资料包序号 //data.setDataNumber(cursor.getString(cursor.getColumnIndex("dn"))); // } list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } catch (Exception e) { } return list; } @Override public void deleteAll(List terminalList, boolean isAll) { for (Terminal ter : terminalList) { deleteTerminalDevice(ter, isAll); } } public void deleteTerminalDevice(Terminal ter, boolean isAll) { if (ter.getDevice() != null) { if (isAll == false) { if (!ter.getTerminalType().equals("C9") & !ter.getTerminalType().equals("D6")) { dbHelper.getTerminalTableDao().deleteTerminal(ter); } } else { if (!ter.getTerminalType().equals("C9")) { dbHelper.getTerminalTableDao().deleteTerminal(ter); } } Device dev = ter.getDevice(); //删除设备 dbHelper.getDeviceTableDao().deleteDevice(dev); if (dev.getDeviceState() != null) { dbHelper.getDeviceStateTableDao().deleteDeviceState(ter.getDevice().getDeviceState()); } DeviceTerminalRelation dtr = new DeviceTerminalRelation(ter.getFamilyId(), ter.getRoomId(), ter.getTerminalAddress(), dev.getDeviceNumber(), dev.getDeviceType(), ter.getDataNumber()); //删除终端设备关系表 dbHelper.getDeviceTerminalRelationDao().deleteDeviceTerminal(dtr); } else { String[] args = new String[]{ter.getFamilyId(), ter.getRoomId(), ter.getTerminalAddress(), ter .getTerminalNum()}; SQLiteDatabase writbase = dbHelper.getWritableDatabase(); writbase.delete("t_terminal", "f_family_id=? and f_room_id=? and f_terminal_address=? and " + "f_terminal_num=?", args); } } @Override public void deleteRoomTerminal(Terminal terminal) { String[] args = new String[]{terminal.getFamilyId(), terminal.getRoomId()}; SQLiteDatabase writbase = dbHelper.getWritableDatabase(); int a = writbase.delete("t_terminal", "f_family_id = ? and f_room_id = ? and f_terminal_type !='C9'", args); //同步的时候暂时不要删除设备 int b = writbase.delete("t_device", "f_family_id=? and f_room_id=?", args); int c = writbase.delete("t_device_terminal", "f_family_id=? and f_room_id=?", args); int deviceState = writbase.delete("t_device_state", "f_family_id=? and f_room_id=?", args); } @Override public void deleteTerminal(Terminal terminal) { String[] args = new String[]{terminal.getFamilyId(), terminal.getRoomId(), terminal.getTerminalType(), terminal.getTerminalNum()}; SQLiteDatabase writbase = dbHelper.getWritableDatabase(); writbase.delete("t_terminal", "f_family_id=? and f_room_id=? and f_terminal_type=? and f_terminal_num=?", args); } @Override public void updateDate() { String[] args = new String[]{"C9"}; SQLiteDatabase database = dbHelper.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("f_data_number", "00"); database.update("t_terminal", cv, "f_terminal_type=?", args); }} DeviceDaoImpl.java:
public class DeviceDaoImpl extends BaseDaoImpl implements DeviceDao { private DBHelper dbHelper; public DeviceDaoImpl(Context context, ConnectionSource connectionSource, Class dataClass) throws SQLException { super(connectionSource, dataClass); dbHelper = DBHelper.getIntance(context); } @Override public List getDeviceListByRoom(Room room) { List list = null; Map map = new HashMap(); map.put("f_family_id", room.getFamilyId()); map.put("f_room_id", room.getRoomId()); try { list = queryForFieldValues(map); } catch (Exception e) { return list; } return list; } @Override public void save(Device id) { try { create(id); } catch (SQLException e) { e.printStackTrace(); } } @Override public void updateNameAll(List> terminalList) { for (Map map : terminalList) { updateName(map); } } public boolean updateName(Map map) { // TODO Auto-generated method stub SQLiteDatabase database = dbHelper.getWritableDatabase(); String familyId = map.get("familyId"); String roomId = map.get("roomId"); String name = map.get("name"); String number = map.get("number"); ContentValues cv = new ContentValues(); cv.put("f_device_name", name); String[] args = new String[]{familyId, roomId, number}; int i = database.update("t_device", cv, "f_family_id=? and f_room_id=? and f_device_number=?", args); if (i == 1) { return true; } return false; } @Override public void deleteDevice(Device device) { String[] args = new String[]{device.getFamilyId(), device.getRoomId(), device.getDeviceNumber(), device .getDeviceType()}; SQLiteDatabase writbase = dbHelper.getWritableDatabase(); writbase.delete("t_device", "f_family_id=? and f_room_id=? and f_device_number=? and f_device_type=?", args); } @Override public List getDeviceListByModel(Room room) { List list = new ArrayList(); SQLiteDatabase database = dbHelper.getReadableDatabase(); String sql = "select * from t_device where f_family_id =? and f_room_id =?" + " and f_device_type <> '16' and f_device_type <> '1A' "; Cursor cursor = database.rawQuery(sql, new String[]{room.getFamilyId(), room.getRoomId()}); try { while (cursor.moveToNext()) {//遍历游标，调用一次moveToNext，游标向下滚动一次 Device data = new Device(); data.setId(cursor.getInt(cursor.getColumnIndex("id"))); data.setFamilyId(cursor.getString(cursor.getColumnIndex("f_family_id"))); data.setRoomId(cursor.getString(cursor.getColumnIndex("f_room_id"))); data.setDeviceName(cursor.getString(cursor.getColumnIndex("f_device_name"))); data.setDeviceNumber(cursor.getString(cursor.getColumnIndex("f_device_number"))); data.setDataNumber(cursor.getString(cursor.getColumnIndex("f_data_number"))); data.setDeviceType(cursor.getString(cursor.getColumnIndex("f_device_type"))); data.setIsSearch(cursor.getString(cursor.getColumnIndex("f_search"))); list.add(data); } } finally { if (cursor != null) cursor.close(); } return list; } @Override public void updateSearch(String familyId, String roomId, String alarm) { //1011111111111111 for (int j = 0; j < alarm.length(); j++) { char num = alarm.charAt(j); String tem = String.valueOf(num); String isearch = "notrue"; if (tem.equalsIgnoreCase("1")) isearch = "true"; String deviceNum = j + ""; if (deviceNum.length() < 2) deviceNum = "0" + deviceNum; updateByid(familyId, roomId, deviceNum, isearch); } } public void updateByid(String familyId, String roomId, String deviceNum, String isearch) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); ContentValues values = new ContentValues(); values.put("f_search", isearch); try { db.update("t_device", values, "f_family_id = ? and f_room_id = ? and f_device_number = ?", new String[]{familyId, roomId, deviceNum}); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } db.endTransaction(); }} 更多相关文章
- 查看自己Android设备分辨率
- Android：获取设备ID、型号等
- 读取Android设备的MAC地址
- Android获取设备ID号
- 如何在Android和iOS设备上录制游戏？
- adb通过TCP/IP来调试Android设备
- Android 支持不同（本地化、屏幕、版本）设备