壯士日志

說(shuō)明:根據(jù)提供的sql/標(biāo)題/參數(shù)列表進(jìn)行查詢,返回的二維String數(shù)組可以直接用來(lái)顯示,將會(huì)陸續(xù)增加排序/格式化等功能,核心的execQuery方法如下:

public String[][] execSQL(String sql,String[] titles,Object[] params) throws Exception {

//todo:驗(yàn)證數(shù)據(jù)庫(kù)連接是否可用
??if(con==null||con.isClosed())throw new Exception("數(shù)據(jù)庫(kù)連接不可用");

//todo:簡(jiǎn)單的驗(yàn)證輸入?yún)?shù)
??if(sql==null)
???throw new Exception("SQL語(yǔ)句不能為空");
??sql=sql.trim();
??if(sql.equals(""))
???throw new Exception("SQL語(yǔ)句不能為空");
??
//對(duì)輸入?yún)?shù)做簡(jiǎn)單的日志記錄??
logger.info(sql);??
??logger.info(Dao.msgOfArray(titles));
??logger.info(Dao.msgOfArray(params));
???
//準(zhǔn)備sql??
PreparedStatement pst = con.prepareStatement(sql);

//如果參數(shù)不空,則準(zhǔn)備參數(shù)??
if(params!=null)
??{
???for(int i=0;i<params.length;i++)
???{
????if(params[i]==null)
????{
?????break;
????}
????pst.setObject(i+1,params[i]);? //此處依賴JDBC Driver實(shí)現(xiàn),不同的驅(qū)動(dòng)可能實(shí)現(xiàn)的程度不一樣
???}
??}

//查詢
??ResultSet rs = pst.executeQuery();
??
//metaData
ResultSetMetaData md = rs.getMetaData();
??int colCount = md.getColumnCount();
??ArrayList al = new ArrayList(maxCount); //maxCount為Dao的一個(gè)屬性,定義每次查詢返回的最大記錄數(shù)

//如果參數(shù)沒(méi)有輸入表格頭信息,則從metadata取得列名字作為默認(rèn)值??
if (titles == null) {
???titles = new String[colCount];
???for (int i = 0; i < colCount; i++) {
????String columnName = md.getColumnName(i + 1);
????if (columnName == null) {
?????columnName = "無(wú)列名";
????};
????titles[i] = columnName;
???}
??}

??int count = 0;
??al.add(count++, titles); // 標(biāo)題

//遍歷結(jié)果集合?
?while (rs.next()) {
???String[] buffer = new String[colCount];
???for (int i = 0; i < colCount; i++) {
????int type = md.getColumnType(i + 1);
????int scale = md.getScale(i + 1);
????String value=null;

//todo:根據(jù)對(duì)于頁(yè)面顯示的要求,對(duì)不同的數(shù)據(jù)類型用不同的方式格式化
????switch (type)
????{
????case Types.LONGVARCHAR: // -1 dataType="Long";
?????value=rs.getString(i+1);
?????break;
????case Types.CHAR: // 1 dataType="Character";
?????value=rs.getString(i+1);
?????break;
????case Types.NUMERIC: // 2
?????switch (scale) {
?????case 0: // dataType="Number";
??????value=rs.getString(i+1);
??????break;
?????case -127: // dataType="Float";
??????value=rs.getString(i+1);
??????break;
?????default:
??????value=rs.getString(i+1);
???????? break;
?????}
?????break;

????case Types.VARCHAR: // 12
?????// dataType="String";
?????value=rs.getString(i+1);
?????break;
????case Types.DATE: // 91
?????// dataType="Date";
?????value=rs.getString(i+1);
?????break;
????case Types.TIMESTAMP: // 93
?????// dataType="Date";
?????value=rs.getString(i+1);
?????break;
????case Types.BLOB:
?????// dataType="Blob";
?????value="不支持的數(shù)據(jù)類型";
?????break;
????default:
?????value=rs.getString(i+1);
????}
????
????buffer[i]=value;
???}
???al.add(count++, buffer);
???if (count >= maxCount) {
????break;
???}
??}

//convert to String[][] and return??
String[][] returnValue = new String[count][];
??for (int i = 0; i < count; i++) {
???returnValue[i] = (String[]) al.get(i);
??}
??return returnValue;
?}
//end here