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