//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);
}