//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 insertAndSearchID(String[] attributes, String[] value)
{
DBConnector db = new DBConnector();
String s = "INSERT INTO [" + tableName + "](";
for (int i = 1; i < attributes.Length - 1; i++)
{
s += "[" + attributes[i] + "],";
}
s += "[" + attributes[attributes.Length - 1] + "])VALUES(";
for (int i = 1; i < value.Length - 1; i++)
{
s += "'" + value[i] + "',";
}
s += "'" + value[value.Length - 1] + "')SELECT TOP 1 [ID] AS id FROM " + tableName + " ORDER BY id DESC";
return db.sqlGetID(s);
}
//新增列
public String insert(String[] attributes, String[] value)
{
DBConnector db = new DBConnector();
String s = "INSERT INTO [" + tableName + "](";
for (int i = 1; i < attributes.Length - 1; i++)
{
s += "[" + attributes[i] + "],";
}
s += "[" + attributes[attributes.Length - 1] + "])VALUES(";
for (int i = 1; i < value.Length - 1; i++)
{
s += "'" + value[i] + "',";
}
s += "'" + value[value.Length - 1] + "')";
return db.sql(s);
}
//修改字串欄位
public String modify(String modifyColumn, String modifyValue, String conditionColumn, String conditionValue)
{
DBConnector db = new DBConnector();
String sqlStr = "UPDATE " + tableName + " SET " + modifyColumn + " = '" + modifyValue + "' WHERE " + conditionColumn + " = '" + conditionValue + "'";
return db.sql(sqlStr);
}
//新增列並回傳ID
public String insertAndSearchID(String type, String content)
{
DBConnector db = new DBConnector();
String s = "INSERT INTO discount ([type],[content]) values ('" + type + "','" + content + "') SELECT TOP 1 [discountID] AS id FROM discount ORDER BY id DESC";
return db.sqlGetID(s);
}
//查詢前幾筆資料
public String[][] searchTop(int value)
{
DBConnector db = new DBConnector();
String sqlStr = "SELECT TOP " + value + " * FROM " + tableName;
return db.sqlSelect(sqlStr);
}
//刪除列
public String delete(String fieldName, String value)
{
DBConnector db = new DBConnector();
String sqlStr = "DELETE FROM "+tableName+" WHERE ["+fieldName +"] = '" + value + "'";
return db.sql(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);
}