安卓有时候需要用到本地数据库存储数据,用到数据库就会用到增删改查的方法,本文主要讲本地多表查询的语句。
需求是:查询一条服务的签名,根据航班表(ACCA_FLIGHT)中的_flight_seq字段和服务表(ACCA_SERVICE)中的_link_flight_id字段查询,然后判断_operationState字段不为空,并且排序,如果相同则符合条件,把查询出来的数据添加到自定义的list集合中。
/**
* 查询服务签名,根据航班fligth_seq查询,并显示签名预览
*
* @param flightSeq 航班的唯一标识
* @return list集合 包涵所有服务信息
*/
public List<EyeInfo.FlightDataBean.SignInfoBean> selectServiceSign(String flightSeq) {
List<EyeInfo.FlightDataBean.SignInfoBean> signList = new ArrayList<>();
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = db.rawQuery("select a._item_name ,a._item_value,a._end_time,a._server_sign,a._service_seq,a._start_time from ACCA_SERVICE as a INNER JOIN ACCA_FLIGHT as b ON b._flight_seq = a._link_flight_id where a._link_flight_id = ? and a._operationState is not ? order by a._update_time desc ", new String[]{flightSeq,"D"});
while (cursor.moveToNext()) {
EyeInfo.FlightDataBean.SignInfoBean signInfoBean = new EyeInfo.FlightDataBean.SignInfoBean();
//判断,如果签名字段为空的, 就不把这条服务信息添加的list集合中
if (cursor.getString(cursor.getColumnIndex("_server_sign")) != null) {
signInfoBean.setEnd_Time(cursor.getString(cursor.getColumnIndex("_end_time")));
signInfoBean.setItem_Name(cursor.getString(cursor.getColumnIndex("_item_name")));
signInfoBean.setItem_Value(cursor.getString(cursor.getColumnIndex("_item_value")));
signInfoBean.setServer_Sign(cursor.getString(cursor.getColumnIndex("_server_sign")));
signInfoBean.setService_Seq(cursor.getString(cursor.getColumnIndex("_service_seq")));
signInfoBean.setStart_Time(cursor.getString(cursor.getColumnIndex("_start_time")));
signList.add(signInfoBean);
}
}
db.close();
return signList;
}
记录一下。