需求:將任意查詢出來的結果(前臺輸入的sql查詢陳述句)存入到一個通用的物體類中,這個物體來設定了100個欄位,都是String型別.
以前使用的查詢結果回傳到ResultSet中,這樣逐個遍歷每個列就可以達到不知道查詢出來的結果,即便你提前并不知道欄位型別也可以手動裝入.
最近使用了springboot2的框架,里面多資料源使用了mybatis,但mybatis好像必須要知道查詢的sql的具體欄位型別和長度才能裝入制定的物體類,如果是任意的sql查出來的結果怎么裝入物體類中?
物體類:
package com.springboot.entity.datasource;
public class Comm {
public String c01;
public String c02;
public String c03;
public String c04;
public String c05;
public String c06;
public String c07;
public String c08;
public String c09;
public String c10;
setter...
getter...
}
uj5u.com熱心網友回復：
使用mybatis默認是結果集的欄位名稱映射到物體類相同的欄位名稱，可以使用resultmap修改映射。如你所說你可以將一個表的所有欄位添加到一個物體類中，然后所有的查詢結果都可以存入到該物體類，未使用到的欄位值為null，然后使用json工具格式化可以忽略掉null值uj5u.com熱心網友回復：
但我這個需求有個問題,腳本是前臺輸入進來的,查出來什么欄位是不定的,這種使用mybatis可以實作嗎?uj5u.com熱心網友回復：
只要是查詢的同一個表，就可以用同一個物體類接收，表的所有欄位必須寫入物體類。然后就能達成無論查詢什么欄位都可以uj5u.com熱心網友回復：
查詢的不是同一個表,也有可能查詢的腳本是兩個三個表的關聯結果,也可能是視圖.
uj5u.com熱心網友回復：
你這個需求可以選擇不使用Mybatisuj5u.com熱心網友回復：
resultmap or as,但是你這需求很怪uj5u.com熱心網友回復：
你這個用hibernate或者jpa比較方便，獲取動態sql直接查詢回傳結果給前臺uj5u.com熱心網友回復：
不知道結果映射，回傳List，如果沒記錯應該是List<String[]>型別，只能索引沒有欄位名uj5u.com熱心網友回復：
我也感覺這個功能好像mybatis不適合,mybatis還沒有這個功能,果斷用最普通的ResultSet了uj5u.com熱心網友回復：
最后用這種方法了:package com.cmcc.project.any.serviceImpl;
import com.cmcc.db.ConnectionManager;
import com.cmcc.project.any.domain.KpiInfo;
import com.cmcc.project.any.service.IAnyService;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class AnyServiceImpl implements IAnyService {
@Override
public Map<String, List> getPublicDataBySQL(String sql, String type_id, String db) {
Map<String, List> dataMap = new HashMap<String, List>();
ConnectionManager manager = new ConnectionManager();
Connection conn = manager.getConnection("46");
PreparedStatement preparedStatement;
try {
preparedStatement = conn.prepareStatement(sql);
preparedStatement.execute();
ResultSet rs = preparedStatement.getResultSet();
if (rs == null) {
System.out.println("查無資料[" + sql + "]");
return null;
}
// 獲取列數
int columnCount = rs.getMetaData().getColumnCount();
List<KpiInfo> list = null;
while (rs.next()) {
list = dataMap.get(type_id);
if (list == null) {
list = new ArrayList<KpiInfo>();
dataMap.put(type_id, list);
}
KpiInfo info = new KpiInfo();
info.setType_id(type_id);
// 回圈遍歷列
for (int i = 0; i < columnCount; i++) {
ResultSetMetaData mageDate = rs.getMetaData();
// 注意：rs取數的序列從1開始，并非從0開始
switch (i) {
case 0:
info.setValue_0(rs.getString(1));
break;
case 1:
info.setValue_1(rs.getString(2));
break;
case 2:
info.setValue_2(rs.getString(3));
break;
case 3:
info.setValue_3(rs.getString(4));
break;
case 4:
info.setValue_4(rs.getString(5));
break;
case 5:
info.setValue_5(rs.getString(6));
break;
case 6:
info.setValue_6(rs.getString(7));
break;
case 7:
info.setValue_7(rs.getString(8));
break;
case 8:
info.setValue_8(rs.getString(9));
break;
case 9:
info.setValue_9(rs.getString(10));
break;
case 10:
info.setValue_10(rs.getString(11));
break;
case 11:
info.setValue_11(rs.getString(12));
break;
case 12:
info.setValue_12(rs.getString(13));
break;
case 13:
info.setValue_13(rs.getString(14));
break;
case 14:
info.setValue_14(rs.getString(15));
break;
case 15:
info.setValue_15(rs.getString(16));
break;
case 16:
info.setValue_16(rs.getString(17));
break;
case 17:
info.setValue_17(rs.getString(18));
break;
case 18:
info.setValue_18(rs.getString(19));
break;
case 19:
info.setValue_19(rs.getString(20));
break;
case 20:
info.setValue_20(rs.getString(21));
break;
case 21:
info.setValue_21(rs.getString(22));
break;
case 22:
info.setValue_22(rs.getString(23));
break;
case 23:
info.setValue_23(rs.getString(24));
break;
case 24:
info.setValue_24(rs.getString(25));
break;
case 25:
info.setValue_25(rs.getString(26));
break;
case 26:
info.setValue_26(rs.getString(27));
break;
case 27:
info.setValue_27(rs.getString(28));
break;
case 28:
info.setValue_28(rs.getString(29));
break;
case 29:
info.setValue_29(rs.getString(30));
break;
case 30:
info.setValue_30(rs.getString(31));
break;
case 31:
info.setValue_31(rs.getString(32));
break;
case 32:
info.setValue_32(rs.getString(33));
break;
case 33:
info.setValue_33(rs.getString(34));
break;
case 34:
info.setValue_34(rs.getString(35));
break;
case 35:
info.setValue_35(rs.getString(36));
break;
case 36:
info.setValue_36(rs.getString(37));
break;
case 37:
info.setValue_37(rs.getString(38));
break;
case 38:
info.setValue_38(rs.getString(39));
break;
case 39:
info.setValue_39(rs.getString(40));
break;
case 40:
info.setValue_40(rs.getString(41));
break;
case 41:
info.setValue_41(rs.getString(42));
break;
case 42:
info.setValue_42(rs.getString(43));
break;
case 43:
info.setValue_43(rs.getString(44));
break;
case 44:
info.setValue_44(rs.getString(45));
break;
case 45:
info.setValue_45(rs.getString(46));
break;
case 46:
info.setValue_46(rs.getString(47));
break;
case 47:
info.setValue_47(rs.getString(48));
break;
case 48:
info.setValue_48(rs.getString(49));
break;
case 49:
info.setValue_49(rs.getString(50));
break;
case 50:
info.setValue_50(rs.getString(51));
break;
case 51:
info.setValue_51(rs.getString(52));
break;
case 52:
info.setValue_52(rs.getString(53));
break;
case 53:
info.setValue_53(rs.getString(54));
break;
case 54:
info.setValue_54(rs.getString(55));
break;
case 55:
info.setValue_55(rs.getString(56));
break;
}
}
list.add(info);
}
rs.close();
preparedStatement.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return dataMap;
}
}
------------KPIInfo.java
package com.cmcc.project.any.domain;
public class KpiInfo {
private String type_id;
private String value_0;
private String value_1;
private String value_2;
private String value_3;
private String value_4;
private String value_5;
private String value_6;
private String value_7;
private String value_8;
private String value_9;
private String value_10;
private String value_11;
private String value_12;
private String value_13;
private String value_14;
private String value_15;
private String value_16;
private String value_17;
private String value_18;
private String value_19;
private String value_20;
private String value_21;
private String value_22;
private String value_23;
private String value_24;
private String value_25;
private String value_26;
private String value_27;
private String value_28;
private String value_29;
private String value_30;
private String value_31;
private String value_32;
private String value_33;
private String value_34;
private String value_35;
private String value_36;
private String value_37;
private String value_38;
private String value_39;
private String value_40;
private String value_41;
private String value_42;
private String value_43;
private String value_44;
private String value_45;
private String value_46;
private String value_47;
private String value_48;
private String value_49;
private String value_50;
private String value_51;
private String value_52;
private String value_53;
private String value_54;
private String value_55;
public String getValue(int i) {
String value = "";
switch (i) {
case 0:
value = getValue_0();
break;
case 1:
value = getValue_1();
break;
case 2:
value = getValue_2();
break;
case 3:
value = getValue_3();
break;
case 4:
value = getValue_4();
break;
case 5:
value = getValue_5();
break;
case 6:
value = getValue_6();
break;
case 7:
value = getValue_7();
break;
case 8:
value = getValue_8();
break;
case 9:
value = getValue_9();
break;
case 10:
value = getValue_10();
break;
case 11:
value = getValue_11();
break;
case 12:
value = getValue_12();
break;
case 13:
value = getValue_13();
break;
case 14:
value = getValue_14();
break;
case 15:
value = getValue_15();
break;
case 16:
value = getValue_16();
break;
case 17:
value = getValue_17();
break;
case 18:
value = getValue_18();
break;
case 19:
value = getValue_19();
break;
case 20:
value = getValue_20();
break;
case 21:
value = getValue_21();
break;
case 22:
value = getValue_22();
break;
case 23:
value = getValue_23();
break;
case 24:
value = getValue_24();
break;
case 25:
value = getValue_25();
break;
case 26:
value = getValue_26();
break;
case 27:
value = getValue_27();
break;
case 28:
value = getValue_28();
break;
case 29:
value = getValue_29();
break;
case 30:
value = getValue_30();
break;
case 31:
value = getValue_31();
break;
case 32:
value = getValue_32();
break;
case 33:
value = getValue_33();
break;
case 34:
value = getValue_34();
break;
case 35:
value = getValue_35();
break;
case 36:
value = getValue_36();
break;
case 37:
value = getValue_37();
break;
case 38:
value = getValue_38();
break;
case 39:
value = getValue_39();
break;
....
return value;
}
uj5u.com熱心網友回復：
結合jpa來使用挺方便的,物體類與資料庫欄位先進行映射,物體類中存在資料庫中沒有的欄位用@Transient進行標注,多表聯查,也可以的,查出的sql欄位只要與物體類對就沒有問題uj5u.com熱心網友回復：
你這如果查詢欄位大于55個怎么辦？難道修改源代碼嗎？
uj5u.com熱心網友回復：
看起來可以用LinkedHashMapMap<String, String> map = new LinkedHashMap<String, String>();
map.put("aa", "");
map.put("ss", "");
map.put("dd", "");
map.put("ww", "");
map.put("as", "");
Iterator<String> it = map.keySet().iterator();
while(it.hasNext()) {
System.out.println(it.next());
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/101814.html
標籤：Web 開發