//Inner join public String[][] innerJoin(String fieldName, String joinTable, String condition1, String condition2, String column, String value) { DBConnector db = new DBConnector(); String sqlStr = "SELECT " + fieldName + " FROM " + tableName + " INNER JOIN " + joinTable + " ON " + condition1 + "=" + condition2 + " WHERE " + column + "='" + value + "'"; return db.sqlSelect(sqlStr); }
//查詢前幾筆資料 public String[][] searchTop(int value) { DBConnector db = new DBConnector(); String sqlStr = "SELECT TOP " + value + " * FROM " + tableName; return db.sqlSelect(sqlStr); }
//依照欄位查詢列 public String[][] searchRowByColumn(String fieldName, String column, String value) { DBConnector db = new DBConnector(); String sqlStr = "SELECT " + fieldName + " FROM " + tableName + " WHERE " + column + " = '" + value + "'"; return db.sqlSelect(sqlStr); }
//查詢Schema public String[][] searchSchema(String fieldName) { DBConnector db = new DBConnector(); String sqlStr = "SELECT id FROM sysobjects WHERE name = '" + tableName + "'"; String[] id = db.sqlSelect(sqlStr)[0]; String sqlResult = "SELECT name FROM syscolumns WHERE id='" + id[0] + "'"; return db.sqlSelect(sqlResult); }
//依條件做Group by查詢 public String[][] searchGroupBy(String fieldName, String column, String value) { DBConnector db = new DBConnector(); String sqlStr = "SELECT [" + fieldName + "] FROM " + tableName + " WHERE " + column + " = '" + value + "' GROUP BY [" + fieldName + "]"; return db.sqlSelect(sqlStr); }
//查詢欄位包含字串 public String[][] searchLikeByRow(String column, String value) { DBConnector db = new DBConnector(); String sqlStr = "SELECT * FROM " + tableName + " WHERE " + column + " LIKE '%" + value + "%'"; return db.sqlSelect(sqlStr); }
//Group by查詢 public String[][] searchGroupBy(String fieldName) { DBConnector db = new DBConnector(); String sqlStr = "SELECT [" + fieldName + "] FROM " + tableName + " GROUP BY [" + fieldName + "]"; return db.sqlSelect(sqlStr); }
//查詢列order by desc public String[][] searchByRowOrder(String column, String value) { DBConnector db = new DBConnector(); String sqlStr = "SELECT * FROM " + tableName + " WHERE " + column + " = '" + value + "' ORDER BY ID DESC "; return db.sqlSelect(sqlStr); }
//查詢範圍內的資料列 public String[][] searchByRow(String column, int minValue, int maxValue) { DBConnector db = new DBConnector(); String sqlStr = "SELECT * FROM " + tableName + " WHERE " + column + " >= " + minValue + " AND " + column + " <= " + maxValue; return db.sqlSelect(sqlStr); }
//依照欄位查詢 order by desc public String[][] searchByColumnOrder(String fieldName) { DBConnector db = new DBConnector(); String sqlStr = "SELECT " + fieldName + " FROM " + tableName + " ORDER BY ID DESC "; return db.sqlSelect(sqlStr); }
//查詢全部 public String[][] searchAll() { DBConnector db = new DBConnector(); String sqlStr = "SELECT * FROM " + tableName; return db.sqlSelect(sqlStr); }