EasySQLite

Additional

Language
Java
Version
N/A
Created
Feb 8, 2015
Updated
Feb 18, 2016 (Retired)
Owner
Antonio López Marín (tonilopezmr)
Contributor
Antonio López Marín (tonilopezmr)
1
Activity
Badge
Generate
Download
Source code

Advertisement

#EasySQLite

Use easily the database SQLite on android using the DAO and transformer design patterns, I modified the library of the professor @jvprofe, I learned it from him.

Basic methods are already implemented in the class SQLiteDelegate<T>, if you need further queries you should extend from it.

#How to use

####Import EasySQLite dependency:

Grab via maven:

<dependency>
    <groupId>com.github.tonilopezmr</groupId>
    <artifactId>easysqlite</artifactId>
    <version>2.1.0</version>
</dependency>

or gradle:

compile 'com.github.tonilopezmr:easysqlite:2.1.0'

####1. Create the object Transformer which implements SQLiteTransformer

 public class SubjectTransformer implements SQLiteTransformer<SubjectEntity>{

     public static final String ID = "id";
     public static final String NAME = "name";

     public static final String TABLE_NAME = "subject";
     public static final String[] FIELDS = {ID, NAME};

     @Override
     public SubjectEntity transform(Cursor cursor) throws Exception {
         int id = cursor.getInt(0);
         String name = cursor.getString(1);
         return new SubjectEntity(id, name);
     }

     @Override
     public ContentValues transform(SubjectEntity dto) throws Exception {
         ContentValues values = new ContentValues();
   //values.put(ID, dto.getId());   it is not necessary, autoincrement!
         values.put(NAME, dto.getName());

         return values;
     }

     @Override
     public String getWhereClause(SubjectEntity dto) throws Exception {
         return  ID+"="+dto.getId();
     }

     @Override
     public SubjectEntity setId(SubjectEntity dto, Object id) throws Exception {
         dto.setId((Integer.valueOf(id.toString())));
         return dto;
     }

     @Override
     public String[] getFields() throws Exception {
         return FIELDS;
     }

     @Override
     public String getTableName() throws Exception {
         return TABLE_NAME;
     }
 }

####2. Create the object DAO which extends SQLiteDelegate

The SQLiteDelegate<T> has implemented the following methods:

//Default methods implement with SQLiteDelegate
T create(T dto)
int update(T dto)
T read(T id)
Collection<T> readAll()
boolean delete(T dto)
int deleteAll()
 public class SubjectDAO extends SQLiteDelegate<SubjectEntity> {

     public SubjectDAO(SQLiteDatabase db) {
         super(db, new SubjectTransformer());
     }
  
     //In the case you need more querys, write this for example:
     public List<T> getSubjectsApproved() {
      ...     
     }
     
 }

####If you need one relation between objects (foreign key)

 public class SubjectTransformer implements SQLiteTransformer<SubjectEntity>{
  
  ...

  private ExamDAO examDAO;

     public SubjectTransformer(SQLiteDatabase db){
      examDAO = new ExamDAO(db);
     }

  ... 
 }

####and

 public class SubjectDAO extends SQLiteDelegate<SubjectEntity> {
 
      public SubjectDAO(SQLiteDatabase db) {
          super(db, new SubjectTransformer(db));
      }
      
 }

####3. Instance the object DAO and use the CRUD methods

    
    ...
    
    SubjectDAO subjectDAO = new SubjectDAO(database);
    Subject subject = new Subject("maths");
    
    //Create
    subject = subjectDAO.create(subject);
    
    //Delete
    boolean isDelete = subjectDAO.delete(subject);
    ...
    

Use the SQLiteHelper and get rid of SQLiteOpenHelper

####If you need one simple database:

//Create table
final private String SUBJECT_TABLE =
        "CREATE TABLE SUBJECT(" +
         "ID INTEGER PRIMARY KEY AUTOINCREMENT," +
         "NAME TEXT NOT NULL" +
        ")";

//Table name        
final private String SUBJECT = "SUBJECT";

final private String[] TABLES = {SUBJECT_TABLE};
final private String[] TABLE_NAMES = {SUBJECT};
 

Important:

  • The array TABLES in database must be sorted in order of creation, to avoid problems with the foreign keys!.

  • The array tables in database must be sorted in opposite order by the array of creation tables, for example:

final private String[] TABLES = {SUBJECT_TABLE, EXAM_TABLE, PROFESSOR_TABLE};
//Very important, sorted in opposite order.
final private String[] TABLE_NAMES = {PROFESSOR, EXAM, SUBJECT_TABLE};  

Create SQLiteHelper:

SQLiteHelper helper = SQLiteHelper.builder()
 .tables(TABLES)
  .tableNames(TABLE_NAMES)
  .build(context);
  
SQLiteDatabase dataBase = helper.getWritableDatabase(); 

In this case, the name and version of the database by default are Name: com.easysqlite and Version: 1. For put the name and version you can do:

SQLiteHelper helper = SQLiteHelper.builder()
 .tables(TABLES)
  .tableNames(TABLE_NAMES)
  .build(context, DATABASE_NAME, SQLITE_VERSION);

OR

SQLiteHelper helper = SQLiteHelper.builder()
 .tables(TABLES)
  .tableNames(TABLE_NAMES)
  .name(DATABASE_NAME)
  .version(DATABASE_VERSION)
  .build(context);

####If you need one custom implementation:

private static SQLiteHelper.SQLiteHelperCallback helperCallback = new SQLiteHelper.SQLiteHelperCallback() {
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(SUBJECT_TABLE);
            db.execSQL("INSERT INTO "+SUBJECT+"(name) VALUES ('Maths')");
            
            ...
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            ... 
        }
};
SQLiteHelper helper = SQLiteHelper.builder()
 .beginConfig()
  .helperCallback(helperCallback)
  .foreignKey(true)  //PRAGMA foreign_keys = ON
 .endConfig()
 .tables(TABLES)
 .tableNames(TABLE_NAMES)
 .build(context, DATABASE_NAME, cursorFactory, SQLITE_VERSION);

If you need change only the method onCreate or onUpgrade, use onCreateCallback(OnCreateCallback callback) or onUpgradeCallback(OnUpgradeCallback callback).

Sample Clean architecture

The code which uses this library is in the package 'com.tonilopezmr.sample.data.SQLite'.

I use the Clean architecture with the pattern MVP, I have been motivated for the speaker pedrovgs in DroidCon 2014.

After see the MVP implementations of:

Libraries used on the sample project

If anything is wrong contact me

######Sorry for my English :(

License

Copyright 2015 Antonio López Marín <tonilopezmr.com>

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.