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

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