Esempio n. 1
0
        //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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        //新增列
        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);
        }
Esempio n. 4
0
 //修改字串欄位
 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);
 }
Esempio n. 5
0
 //新增列並回傳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);
 }
Esempio n. 6
0
        //查詢前幾筆資料
        public String[][] searchTop(int value)
        {
            DBConnector db = new DBConnector();
            String sqlStr = "SELECT TOP " + value + " * FROM " + tableName;

            return db.sqlSelect(sqlStr);
        }
Esempio n. 7
0
 //刪除列
 public String delete(String fieldName, String value)
 {
     DBConnector db = new DBConnector();
     String sqlStr = "DELETE FROM "+tableName+" WHERE ["+fieldName +"] = '" + value + "'";
     return db.sql(sqlStr);
 }
Esempio n. 8
0
        //依照欄位查詢列
        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);
        }
Esempio n. 9
0
        //查詢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);
        }
Esempio n. 10
0
        //依條件做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);
        }
Esempio n. 11
0
        //查詢欄位包含字串
        public String[][] searchLikeByRow(String column, String value)
        {
            DBConnector db = new DBConnector();
            String sqlStr = "SELECT * FROM " + tableName + " WHERE " + column + " LIKE '%" + value + "%'";

            return db.sqlSelect(sqlStr);
        }
Esempio n. 12
0
        //Group by查詢
        public String[][] searchGroupBy(String fieldName)
        {
            DBConnector db = new DBConnector();
            String sqlStr = "SELECT [" + fieldName + "] FROM " + tableName + " GROUP BY [" + fieldName + "]";

            return db.sqlSelect(sqlStr);
        }
Esempio n. 13
0
        //查詢列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);
        }
Esempio n. 14
0
        //查詢範圍內的資料列
        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);
        }
Esempio n. 15
0
        //依照欄位查詢 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);
        }
Esempio n. 16
0
        //查詢全部
        public String[][] searchAll()
        {
            DBConnector db = new DBConnector();
            String sqlStr = "SELECT * FROM " + tableName;

            return db.sqlSelect(sqlStr);
        }