前段时间项目中遇到的问题，来跟大家来分享一下。
开发环境:
Android Studio
项目框架：
litepal
最终效果：
result.gif
需要了解的sql语句
1.select name from sqlite_master where type='table' order by name 获取app中所有表名
2.pragma table_info(" + name + ") 通过表名获取字段名
服务端中主要的四个方法：
1.获取全部表名：
public List<String> getAllTableName() throws RemoteException {
List<String> result = new ArrayList<>();
Cursor cursor = findBySQL("SELECT name FROM sqlite_master WHERE type='table' order by name");
while (cursor.moveToNext()) {
if (!cursor.getString(cursor.getColumnIndex("name")).trim().equals("android_metadata"))
result.add(cursor.getString(cursor.getColumnIndex("name")));
}
return result;
}
2.通过表名获取表中所有字段
@Override
public List<String> getFieldByName(String name) throws RemoteException {
List<String> result = new ArrayList<>();
Cursor cursor = findBySQL("pragma table_info(" + name + ")");
while (cursor.moveToNext()) {
result.add(cursor.getString(cursor.getColumnIndex("name")));
}
return result;
}
3.通过表名来获取数据
@Override
public Modern getDataByTableName(String tableName) throws RemoteException {
Modern modern = new Modern();
Map<Integer, List<String>> dataMap = new HashMap<>();
List<String> field = new ArrayList<>();
Cursor cursor = DataSupport.findBySQL("pragma table_info(" + tableName + ")");
while (cursor.moveToNext()) {
field.add(cursor.getString(cursor.getColumnIndex("name")));
}
cursor = DataSupport.findBySQL("select *from " + tableName);
int i = 0;
while (cursor.moveToNext()) {
List<String> data = new ArrayList<>();
for (int itme = 0; itme < field.size(); itme++) {
data.add(cursor.getString(cursor.getColumnIndex(field.get(itme))));
}
dataMap.put(i, data);
Log.d("zw", " i = " + i);
i++;
}
modern.setMap(dataMap);
return modern;
}
4.通过表名更新活插入一条新数据
@Override
public void updateData(String tableName, Modern modern) throws RemoteException {
Log.d("zw", "updatedata");
Map<Integer, List<String>> data = modern.getMap();
List<String> field = new ArrayList<>();
Cursor cursor = DataSupport.findBySQL("pragma table_info(" + tableName + ")");
while (cursor.moveToNext()) {
field.add(cursor.getString(cursor.getColumnIndex("name")));//获取表中所有字段名
}
for (int i = 0; i < data.size(); i++) {
cursor = DataSupport.findBySQL("select * from " + tableName + " where id = " + data.get(i).get(0));
if (cursor.moveToNext()) //通过id来判断表中是否已经存在该条数据 如果是 更新 否则插入一条新数据
{
ContentValues values = new ContentValues();
for (int s = 0; s < field.size(); s++) {
values.put(field.get(s), data.get(i).get(s));
}
DataSupport.updateAll(tableName, values, field.get(0) + " = ?", data.get(i).get(0) + "");
} else {
Log.d("zw", "insert into ");
SQLiteDatabase sqLiteDatabase = LitePal.getDatabase();//litepal框架获取database方法
ContentValues value = new ContentValues();
for (int c = 0; c < field.size(); c++) {
value.put(field.get(c), data.get(i).get(c));
}
sqLiteDatabase.insert(tableName, null, value);
}
}
}
5.通过表名和id删除数据
@Override
public void deleteData(String tableName, List<String> id) throws RemoteException {
Log.d("zw", "start deleteData");
for (int i = 0; i < id.size(); i++) {
Log.d("zw", " id " + id.get(i));
DataSupport.deleteAll(tableName, "id = ?", id.get(i));
}
}
客户端主要方法：
1.获取表名在spinner上输出
private void initTableInfo() {
try {
tabNames = mlService.getAllTableName();
if (tabNames != null) {
tabNameAdapter = new StringAdapter(tabNames, getActivity());
spDataTyle.setAdapter(tabNameAdapter);//这里的spDataTyle是一个Spinner控件
spDataTyle.setSelection(0);
}
} catch (RemoteException e) {
e.printStackTrace();
}
}
2.加载表中数据
private Handler handler = new Handler() {
@Override
public void handleMessage(Message msg) {
super.handleMessage(msg);
if (progressDialog != null)
progressDialog.dismiss();
switch (msg.what) {
case SUCESS:
initFiledInfo();
Map<Integer, List<String>> data = modern.getMap();
if (tableAdapter == null) {
tableAdapter = new TableAdapter(getActivity(), data);
} else {
tableAdapter.setDataToView(data);
}
rvData.setAdapter(tableAdapter);
break;
case FAILURE:
showToast("数据加载失败");
break;
case DELETEFAILURE:
break;
case DELETESUCESS:
initTableData();
break;
}
}
};
if (progressDialog == null)
progressDialog = new ProgressDialog(getActivity());
progressDialog.setTitle("数据加载中......");
progressDialog.show();
new Thread() {
@Override
public void run() {
super.run();
try {
filedNames = mlService.getFieldByName(tableName);
modern = mlService.getDataByTableName(tableName);
handler.sendEmptyMessage(SUCESS);
} catch (RemoteException e) {
e.printStackTrace();
handler.sendEmptyMessage(FAILURE);
}
}
}.start();
3.适配器
public class TableAdapter extends RecyclerView.Adapter<TableAdapter.MyViewHolder> {
private Context context;
private Map<Integer, List<String>> data;
private List<String> pos;
private List<Boolean> checks;
public List<String> getPos() {
return pos;
}
public TableAdapter(Context context, Map<Integer, List<String>> data) {
pos = new ArrayList<>();
checks = new ArrayList<>();
this.context = context;
this.data = data;
for (int i = 0; i < data.size(); i++) {
checks.add(false);
}
}
public void setAllChecked(boolean checked) {
pos.clear();
for (int i = 0; i < checks.size(); i++)
checks.set(i, checked);
if (checked) {
for (int c = 0; c < data.size(); c++) {
pos.add(data.get(c).get(0));
}
}
notifyDataSetChanged();
}
public void addNewDataToView() {
List<String> newData = new ArrayList<>();
for (int i = 0; i < data.get(data.size() - 1).size(); i++) {
if (i == 0) {
String id = (Integer.parseInt(data.get(data.size() - 1).get(0)) + 1) + "";
newData.add(id);
} else {
newData.add("");
}
}
data.put(data.size(), newData);
checks.clear();
for (int i = 0; i < data.size(); i++) {
checks.add(false);
}
notifyDataSetChanged();
}
public void setDataToView(Map<Integer, List<String>> data) {
Log.d("zw", "setDataToView");
this.data.clear();
pos.clear();
this.data.putAll(data);
checks.clear();
for (int i = 0; i < data.size(); i++) {
checks.add(false);
}
notifyDataSetChanged();
}
@Override
public MyViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
View view = LayoutInflater.from(context).inflate(R.layout.dataitmelayout, parent, false);
return new MyViewHolder(view);
}
public Map<Integer, List<String>> getData() {
return data;//输出数据
}
@Override
public void onBindViewHolder(MyViewHolder holder, final int position) {
holder.linearLayout.removeAllViews();
final CheckBox checkBox = new CheckBox(getActivity());
checkBox.setBackgroundResource(R.color.colorheadLine);
LinearLayout.LayoutParams checkParams = new LinearLayout.LayoutParams(ViewGroup.LayoutParams.WRAP_CONTENT, ViewGroup.LayoutParams.MATCH_PARENT);
checkBox.setLayoutParams(checkParams);
checkBox.setChecked(checks.get(position));
checkBox.setOnCheckedChangeListener(new CompoundButton.OnCheckedChangeListener() {
@Override
public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {
checks.set(position, isChecked);
if (isChecked) {
pos.add(data.get(position).get(0));
} else {
pos.remove(data.get(position).get(0));
}
}
});
holder.linearLayout.addView(checkBox);
final List<String> datas = data.get(position);
for (int i = 0; i < datas.size(); i++) {
String value = datas.get(i);
DataItmeView dataItmeView = new DataItmeView(getActivity(), null);
dataItmeView.setContentValue(value);
if (i == 0)
dataItmeView.setContentEdit(false);
dataItmeView.setBackGroudColor(R.color.colorWhite);
LinearLayout.LayoutParams layoutParams = new LinearLayout.LayoutParams(0, ViewGroup.LayoutParams.WRAP_CONTENT, 1.0f);
dataItmeView.setLayoutParams(layoutParams);
holder.linearLayout.addView(dataItmeView);
EditText content = dataItmeView.getContentView();
final int finalI = i;
//用来监听表中数据的变化达到修改之后通过更新按钮更新的效果
content.addTextChangedListener(new TextWatcher() {
@Override
public void beforeTextChanged(CharSequence s, int start, int count, int after) {
}
@Override
public void onTextChanged(CharSequence s, int start, int before, int count) {
data.get(position).set(finalI, s.toString());
}
@Override
public void afterTextChanged(Editable s) {
}
});
}
}
/**
* @return
*/
@Override
public int getItemCount() {
return data.size();
}
class MyViewHolder extends RecyclerView.ViewHolder {
LinearLayout linearLayout;
public MyViewHolder(View itemView) {
super(itemView);
linearLayout = (LinearLayout) itemView.findViewById(R.id.linearLayout);
}
}
}
还有不明白的可以 v楼主 微信号 a21544182123
网友评论