Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Use ORMLite to Optimize your sqlite database

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.67k
    Comment on it

    Hey, are you working with large database having many columns and many queries . Is this working slow with sqlite?
    Thanks to ORMLite that manages your database very efficiently as well as no need to manage lots of queries....

    ORMLite supports more than one database using JDBC and also sqlite with native calls to android database APIs..

    ORMLite simply add objects of java using annotations and its have powerful abstract Database Access Object classes, also provides simple and flexible query using QueryBuilder. Auto generates SQL to create and drop database tables and its have basic supports for database transactions

    If you want to work with ORMLite then you should make sure that you have downloaded and are depending on the ormlite-core.jar and ormlite-android.jar files.

    Example:
    1.You will need to create your own database helper class which should extend the OrmLiteSqliteOpenHelper class and have its own life cycles methods to manage database.

        /**
     * Database helper class used to manage the creation and upgrading of your database. This class also usually provides
     * the DAOs used by the other classes.
     */
    public class DatabaseHelper extends OrmLiteSqliteOpenHelper{
    
    private static final String DATABASE_NAME = "savenames.db";
    private static final int DATABASE_VERSION = 1;
    private Context mContext;
    
    // the DAO object we use to access the SimpleData table
    private Dao<DatabaseModel, Integer> databaseModelDao = null;
    private RuntimeExceptionDao<DatabaseModel , Integer> databaseModelRunTimeDao = null;
    
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;
    }
    
    @Override
    public void onCreate(SQLiteDatabase arg0, ConnectionSource arg1) {
    
        try {
            //Directly create database with Database model fields
            TableUtils.createTable(connectionSource, DatabaseModel.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase arg0, ConnectionSource arg1, int arg2,
            int arg3) {
    
        try {
            TableUtils.dropTable(connectionSource, DatabaseModel.class, true);
            onCreate(arg0);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * Returns the Database Access Object (DAO) for our Database model class. 
     * It will create it or just give the cached value.
     */
    public Dao<DatabaseModel, Integer> getDatabaseModelDao()
    {
        if(databaseModelDao == null){
            try {
                databaseModelDao = getDao(DatabaseModel.class);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return databaseModelDao;
    }
    
    public RuntimeExceptionDao<DatabaseModel, Integer> getDatabaseModelRuntimeExceptionDao() {
        if(databaseModelRunTimeDao == null){
            databaseModelRunTimeDao = getRuntimeExceptionDao(DatabaseModel.class);
        }
        return databaseModelRunTimeDao;
    }
    
    //method for insert data
    public int addData(DatabaseModel databaseModels)
    {
        databaseModelRunTimeDao = getDatabaseModelRuntimeExceptionDao();
        int i = databaseModelRunTimeDao.create(databaseModels);
        return i;
    }
    
    //method for list of names
    public List<DatabaseModel> GetData()
    {
        databaseModelRunTimeDao = getDatabaseModelRuntimeExceptionDao();
        List<DatabaseModel> list = databaseModelRunTimeDao.queryForAll();
        return list;
    }
    
    //method for particular person data
    public List<DatabaseModel> GetSingleData(int id)
    {
        databaseModelRunTimeDao = getDatabaseModelRuntimeExceptionDao();
        List<DatabaseModel> list = databaseModelRunTimeDao.queryForEq("id", id);
        Log.e("in dbHelper", "single record for id"+list.toString());
        return list;
    }
    
    //method for delete all rows
    public void deleteAll()
    {
        databaseModelRunTimeDao = getDatabaseModelRuntimeExceptionDao();
        List<DatabaseModel> list = databaseModelRunTimeDao.queryForAll();
        databaseModelRunTimeDao.delete(list);
    }
    
    //method for delete all rows
    public void deleteOneByOne(String i)
    {
        databaseModelRunTimeDao = getDatabaseModelRuntimeExceptionDao();
        DeleteBuilder<DatabaseModel, Integer> deleteBuilder = databaseModelRunTimeDao.deleteBuilder();
        try {
            deleteBuilder.where().eq("subject", i);
            deleteBuilder.delete();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //method for update row data
        public void updateOneByOne(String title,String subject)
        {
            databaseModelRunTimeDao = getDatabaseModelRuntimeExceptionDao();
            UpdateBuilder<DatabaseModel, Integer> updateBuilder = databaseModelRunTimeDao.updateBuilder();
            try {
                updateBuilder.updateColumnValue("subject", subject);
                updateBuilder.where().eq("text", title);
                updateBuilder.update();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    
        }
    
    /**
     * Close the database connections and clear any cached DAOs.
     */
    @Override
    public void close() {
        super.close();
        databaseModelDao = null;
        databaseModelRunTimeDao = null;
    }
    

    2.Create a model class for your database and use annotations to create columns

    public class DatabaseModel implements Serializable{
    
    public DatabaseModel() {
        // needed by ormlite
    }
    
    //Create column name id that is auto generated primary key
    @DatabaseField(generatedId = true)
    public int id;
    //Create another databse field to store name
    @DatabaseField
    public String name;
    
    
    public DatabaseModel(String name){
        super();
        this.name = name;   
    }
    
    
    public int getId() {
        return id;
    }
    
    
    public void setId(int id) {
        this.id = id;
    }
    
    
    public String getName() {
        return name;
    }
    
    
    public void setName(String name) {
        this.name = name;
    }
    
    }
    

    In Mainactivity access database helper class

    private DatabaseHelper dbHelper;
    private EditText etName;
    private Button btnAdd;
    private LinearLayout linearName;
    
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        etName = (EditText)findViewById(R.id.etName);
        btnAdd = (Button)findViewById(R.id.btnAdd);
        linearName = (LinearLayout)findViewById(R.id.linearName);
    
    btnAdd.setOnClickListener(new OnClickListener() {
    
    @Override
        public void onClick(View v) {
            addToDatabase(etName.getText().toString());
            fetchAllData();
        }
    });  }
    
    private void addToDatabase(String name) {
    
    dbHelper = new DatabaseHelper(this);
    dbHelper.addData(new DatabaseModel(name));
    }
    public void fetchAllData(){
        dbHelper = new DatabaseHelper(this);
        TextView tv = new TextView(getApplicationContext());
    List list = dbHelper.GetData();
    for (DatabaseModel model : list) {
        tv.setText(model.name);
    }
    linearName.addView(tv);
    }  }
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: