壯士日志

說明:根據提供的sql/標題/參數列表進行查詢,返回的二維String數組可以直接用來顯示,將會陸續增加排序/格式化等功能,核心的execQuery方法如下:

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

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

//todo:簡單的驗證輸入參數
??if(sql==null)
???throw new Exception("SQL語句不能為空");
??sql=sql.trim();
??if(sql.equals(""))
???throw new Exception("SQL語句不能為空");
??
//對輸入參數做簡單的日志記錄??
logger.info(sql);??
??logger.info(Dao.msgOfArray(titles));
??logger.info(Dao.msgOfArray(params));
???
//準備sql??
PreparedStatement pst = con.prepareStatement(sql);

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

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

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

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

//遍歷結果集合?
?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:根據對于頁面顯示的要求,對不同的數據類型用不同的方式格式化
????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="不支持的數據類型";
?????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