workspace/JuggleMaster/src/com/jm/db/Dao.java

   1 package com.jm.db;\r
   2 \r
   3 import android.R;\r
   4 import android.content.Context;\r
   5 import android.database.Cursor;\r
   6 import android.database.SQLException;\r
   7 import android.database.sqlite.SQLiteDatabase;\r
   8 import android.database.sqlite.SQLiteStatement;\r
   9 \r
  10 import com.jm.JmPattern;\r
  11 import com.jm.utility.JmException;\r
  12 \r
  13 public class Dao {\r
  14         private static Dao instance = new Dao();\r
  15         private SQLiteStatement insertStmt = null;\r
  16         private SQLiteStatement updateStmt = null;\r
  17         private SQLiteStatement deleteStmt = null;\r
  18         \r
  19         public static Dao getInstance(){\r
  20                 return instance;\r
  21         }\r
  22         \r
  23         private Dao(){\r
  24 \r
  25         }\r
  26         \r
  27         public void init(SQLiteDatabase db){\r
  28                 db.execSQL(\r
  29                                 "create table pattern ("\r
  30                                 + "id integer primary key, "\r
  31                                 + "type integer not null, "\r
  32                                 + "siteswap text not null, "\r
  33                                 + "height integer, "\r
  34                                 + "dwell integer, "\r
  35                                 + "name integer, "\r
  36                                 + "motion text,"\r
  37                                 + "lang integer,"\r
  38                                 + "idx integer);"\r
  39                         );\r
  40                 db.execSQL("ALTER TABLE pattern ADD COLUMN lang integer;");\r
  41                 db.execSQL("ALTER TABLE pattern ADD COLUMN idx integer;");\r
  42                 start(db);\r
  43         }\r
  44         \r
  45         public void start(SQLiteDatabase db){\r
  46                 insertStmt = db.compileStatement(\r
  47                                 "insert into pattern ("\r
  48                                 + "type, siteswap, height, dwell, name, motion, lang, idx"\r
  49                                 + ") values ("\r
  50                                 + "?, ?, ?, ?, ?, ?, ?, ?"\r
  51                                 + ");");        \r
  52                 updateStmt = db.compileStatement(\r
  53                                 "update pattern set "\r
  54                                 + "siteswap = ?, height = ?, dwell = ?, name = ?, motion = ? "\r
  55                                 + "where id = ? and lang = ?;");\r
  56                 deleteStmt = db.compileStatement(\r
  57                                 "delete from pattern where id = ? and lang = ?;");\r
  58         }\r
  59         \r
  60         public void add(JmPattern jp, int index) throws JmException{\r
  61                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
  62                 add(jp, helper.getLangId(), index);\r
  63         }\r
  64         \r
  65         public void add(JmPattern jp, int lang, int index) throws JmException{\r
  66                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
  67                 SQLiteDatabase db = helper.getWritableDatabase();\r
  68                 db.beginTransaction();\r
  69                 try {\r
  70                         int i = 1;\r
  71                         SQLiteStatement stmt = insertStmt;\r
  72                         stmt.bindLong(i++, jp.getType());\r
  73                         stmt.bindString(i++, jp.getSiteSwap().toString());\r
  74                         stmt.bindLong(i++, jp.getHeight());\r
  75                         stmt.bindLong(i++, jp.getDwell());\r
  76                         stmt.bindString(i++, jp.getName());\r
  77                         stmt.bindString(i++, jp.motionToString());\r
  78                         stmt.bindLong(i++, lang);\r
  79                         stmt.bindLong(i++, index);\r
  80                         stmt.executeInsert();\r
  81                         db.setTransactionSuccessful();\r
  82                 } catch (SQLException e){\r
  83                         throw new JmException(e);\r
  84                 } finally {\r
  85                         db.endTransaction();\r
  86                 }\r
  87         }\r
  88         \r
  89         public void set(JmPattern jp) throws JmException{\r
  90                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
  91                 SQLiteDatabase db = helper.getWritableDatabase();\r
  92                 db.beginTransaction();\r
  93                 try {\r
  94                         int i = 1;\r
  95                         SQLiteStatement stmt = updateStmt;\r
  96                         stmt.bindString(i++, jp.getSiteSwap().toString());\r
  97                         stmt.bindLong(i++, jp.getHeight());\r
  98                         stmt.bindLong(i++, jp.getDwell());\r
  99                         stmt.bindString(i++, jp.getName());\r
 100                         stmt.bindString(i++, jp.motionToString());\r
 101                         stmt.bindLong(i++, jp.getId());\r
 102                         stmt.bindLong(i++, helper.getLangId());\r
 103                         stmt.execute();\r
 104                         db.setTransactionSuccessful();\r
 105                 } catch (SQLException e){\r
 106                                 throw new JmException(e);\r
 107                 } finally {\r
 108                         db.endTransaction();\r
 109                 }\r
 110         }\r
 111 \r
 112         public void delete(int id) throws JmException{\r
 113                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 114                 SQLiteDatabase db = helper.getWritableDatabase();\r
 115                 db.beginTransaction();\r
 116                 try {\r
 117                         int i = 1;\r
 118                         SQLiteStatement stmt = deleteStmt;\r
 119                         stmt.bindLong(i++, id);\r
 120                         stmt.bindLong(i++, helper.getLangId());\r
 121                         stmt.execute();\r
 122                         db.setTransactionSuccessful();\r
 123                 } catch (SQLException e){\r
 124                                 throw new JmException(e);\r
 125                 } finally {\r
 126                         db.endTransaction();\r
 127                 }\r
 128         }\r
 129 \r
 130         public JmPattern[] get(int type) throws JmException{\r
 131                 JmPattern[] list = null;\r
 132                 try {\r
 133                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 134                         SQLiteDatabase db = helper.getReadableDatabase();\r
 135                         list = get(db, type);\r
 136                 } catch (SQLException e){\r
 137                         throw new JmException(e);\r
 138                 }\r
 139                 return list;\r
 140         }\r
 141 \r
 142         public JmPattern[] get(SQLiteDatabase db, int type) throws JmException{\r
 143                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 144                 String selection = "lang = " + helper.getLangId() + " and type = " + type;\r
 145                 String orderBy = "idx";\r
 146                 return get(db, selection, orderBy);\r
 147         }\r
 148         \r
 149         public JmPattern[] get(SQLiteDatabase db, String selection, String orderBy) throws JmException{\r
 150                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 151                 JmPattern[] list = null;\r
 152                 Cursor c = db.query(\r
 153                                 "pattern",\r
 154                                 new String[] {\r
 155                                                 "id",\r
 156                                                 "type",\r
 157                                                 "siteswap",\r
 158                                                 "height",\r
 159                                                 "dwell",\r
 160                                                 "name",\r
 161                                                 "motion"},\r
 162                                                 selection, null, null, null, orderBy);\r
 163                 c.moveToFirst();\r
 164                 list = new JmPattern[c.getCount()];\r
 165                 for (int i = 0; i < list.length; i++) {\r
 166                         JmPattern jp = new JmPattern(\r
 167                                         c.getInt(0), // id\r
 168                                         c.getInt(1), // type\r
 169                                         c.getString(5), // name\r
 170                                         c.getString(2), // siteswap\r
 171                                         c.getInt(3), // height\r
 172                                         c.getInt(4), // dwell\r
 173                                         JmPattern.getMotion(c.getString(6))); // motions\r
 174                     list[i] = jp;\r
 175                     c.moveToNext();\r
 176                 }\r
 177                 c.close();\r
 178                 return list;\r
 179         }\r
 180         \r
 181         public JmPattern[] getFromId(int id) throws JmException{\r
 182                 JmPattern[] list = null;\r
 183                 try {\r
 184                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 185                         SQLiteDatabase db = helper.getReadableDatabase();\r
 186                         String selection = "id = " + id + " and lang = " + helper.getLangId();\r
 187                         Cursor c = db.query(\r
 188                                         "pattern",\r
 189                                         new String[] {\r
 190                                                         "id",\r
 191                                                         "type",\r
 192                                                         "siteswap",\r
 193                                                         "height",\r
 194                                                         "dwell",\r
 195                                                         "name",\r
 196                                                         "motion"},\r
 197                                                         selection, null, null, null, null);\r
 198                         c.moveToFirst();\r
 199                         list = new JmPattern[c.getCount()];\r
 200                         for (int i = 0; i < list.length; i++) {\r
 201                                 JmPattern jp = new JmPattern(\r
 202                                                 c.getInt(0), // id\r
 203                                                 c.getInt(1), // type\r
 204                                                 c.getString(5), // name\r
 205                                                 c.getString(2), // siteswap\r
 206                                                 c.getInt(3), // height\r
 207                                                 c.getInt(4), // dwell\r
 208                                                 JmPattern.getMotion(c.getString(6))); // motions\r
 209                             list[i] = jp;\r
 210                             c.moveToNext();\r
 211                         }\r
 212                         c.close();\r
 213                 } catch (SQLException e){\r
 214                         throw new JmException(e);\r
 215                 }\r
 216                 return list;\r
 217         }\r
 218 \r
 219         public int max(int type) throws JmException{\r
 220                 int m = -1;\r
 221                 try {\r
 222                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 223                         SQLiteDatabase db = helper.getReadableDatabase();\r
 224                         String sql = "select max(idx) from pattern where type = " + type + " and lang = " + helper.getLangId();\r
 225                         Cursor c = db.rawQuery(sql, null);\r
 226                         c.moveToFirst();\r
 227                         m = c.getInt(0);\r
 228                         c.close();\r
 229                 } catch (SQLException e){\r
 230                         throw new JmException(e);\r
 231                 }\r
 232                 return m;\r
 233         }\r
 234         \r
 235         public int count() throws JmException{\r
 236                 int count = -1;\r
 237                 try {\r
 238                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 239                         SQLiteDatabase db = helper.getReadableDatabase();\r
 240                         String sql = "select count(*) from pattern where lang = " + helper.getLangId() + ";";\r
 241                         Cursor c = db.rawQuery(sql, null);\r
 242                         c.moveToFirst();\r
 243                         count = c.getInt(0); // count;\r
 244                         c.close();\r
 245                 } catch (SQLException e){\r
 246                         throw new JmException(e);\r
 247                 }\r
 248                 return count;\r
 249         }\r
 250 \r
 251         public int count(int type) throws JmException{\r
 252                 int count = -1;\r
 253                 try {\r
 254                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 255                         SQLiteDatabase db = helper.getReadableDatabase();\r
 256                         String sql = "select count(*) from pattern where type = " + type + " and lang = " + helper.getLangId() + ";";\r
 257                         Cursor c = db.rawQuery(sql, null);\r
 258                         c.moveToFirst();\r
 259                         count = c.getInt(0); // count;\r
 260                         c.close();\r
 261                 } catch (SQLException e){\r
 262                         throw new JmException(e);\r
 263                 }\r
 264                 return count;\r
 265         }\r
 266 }\r