Good Post on Clean Code

Using SQL Statements as a String in Class file while working on Android or Java application can be such a pain.

I was checking in the net to find out whether there are any ways in which we can store the SQL Statments separately so that the code can be more readable.

I found some interesting stuff. Please find below the links for the same.

1. Clean Code

2. Java Practices : Keep SQL out of code.

3. Android : Store SQL Queries in an XML File (Stackoverflow answer)

I am working on a way to store all my SQL Statements in an XML File while working with Android.

Found this post on placing XML File with Data on Android in StackOverflow.

res

• No subdirectorys are allowed under the specific resource-folders.
• The R-class indexes all resources and provides simple access.
• There are some simple methods which help reading files stored in the res-directory

assets

• Subdirectorys are allowed (as much as you like).
• No indexing by the R-class
• Reading resources stored in assets is done using the AssetManager.

Also found a cool post on Working with resources in Android : Link.

 

SQL Statements in the case of Android can be stored in the strings.xml under the resources–> values  folder.

In case you using a Database class which is a pure java class the this link will be able to help you on how to do the same.

In my case, I created a separate DAO Class called the DBClass which deals with the connecting and disconnecting with the database. I set the context of the DAO class so that the strings can be used here.

Java Swing- Create dialog in front its JFrame parent

If you want to create a child frame which you would want the parent frame to freeze(not accept input) untill it receives from the child frame.

Refer this question in stackoverflow. Should help.

Working with date in SQLite for Android

Working with dates in Oracle for reporting is very easy ( Since Oracle recognizes the date as datatype). But SQLite does not recognize Date as a datatype, this causes an issue when when you have to query for records that fall in between a range in SQLite.

 

Oracle it is very easy, just use he to_date command -

Oracle query :

SELECT amount,purchase_date FROM table_z

WHERE purchase_date >= TO_DATE('21-MAY-2011’,’DD-MON-YYYY’)

  AND puchase_date  <  TO_DATE('21-JAN-2012','DD-MON-YYYY');
In SQLite date needs to be converted into appropriate format before storing it in the database.
While storing in the database the date can be converted into YYYYMMDD format and stored in the database as an integer. Storing in this format in the database will ensure that older dates will have lesser value than the later ones.

Eg: 21-MAY-2011 will have 20110521

      
      21-JAN-2012  will have 20120121
The integer 20110521 < 20120121

So the SQlite equivalent of the query will be
SQLite Query :
SELECT amount,purchase_date FROM table_z

WHERE purchase_date >= 20110521

  AND puchase_date  <  20120121



  
Source: StackOverflow

					

				

Android Database Table export to CSV – Part 3

I tried out the method mentioned here. Soon I found out that this was not the thing that I needed .

I needed the table level data which had the transactional information and not database level data.

So CSV was a good option.

In Android phones you can view the csv file in an excel format using ThinkFree Office tools. Similarly in PC using MSOffice Excel.

The code structure is similar to the export to DB as used in the previous post, the difference is that we use a thrid-party Java CSV library called OPENCSV.

/*******************************************************************************

********************************************************************************

Export Database into CSV Class

********************************************************************************

*******************************************************************************/

    class ExportDatabaseCSVTask extends AsyncTask<String, Void, Boolean> 

    {

        private final ProgressDialog dialog = new ProgressDialog(ctx);


        // can use UI thread here

        @Override

        protected void onPreExecute() 

        {

            this.dialog.setMessage("Exporting database...");

            this.dialog.show();

        }   


        // automatically done on worker thread (separate from UI thread)

        protected Boolean doInBackground(final String... args) 

        {

            File dbFile=getDatabasePath("excerDB.db");

            DbClass DBob = new DbClass(MainActivity.this);

            File exportDir = new File(Environment.getExternalStorageDirectory(), "");        

            if (!exportDir.exists()) 

            {

                exportDir.mkdirs();

            }

            File file = new File(exportDir, "excerDB.csv");

            try 

            {

                file.createNewFile();                

                CSVWriter csvWrite = new CSVWriter(new FileWriter(file));

                SQLiteDatabase db = DBob.getReadableDatabase();

                Cursor curCSV = db.rawQuery("SELECT * FROM ExcerTable",null);

                csvWrite.writeNext(curCSV.getColumnNames());

                while(curCSV.moveToNext())

                {

                    String arrStr[] ={curCSV.getString(0),curCSV.getString(1),

                        curCSV.getString(2),curCSV.getString(3),curCSV.getString(4)};

                    csvWrite.writeNext(arrStr);

                }

                csvWrite.close();

                curCSV.close();

                return true;

            }

            catch(SQLException sqlEx)

            {

                Log.e("MainActivity", sqlEx.getMessage(), sqlEx);

                return false;                

            }

            catch (IOException e) 

            {

                Log.e("MainActivity", e.getMessage(), e);

                return false;

            }

        }

        // can use UI thread here

        @Override

        protected void onPostExecute(final Boolean success) 

        {

            if (this.dialog.isShowing()) 

            {

                this.dialog.dismiss();

            }

            if (success) 

            {

                Toast.makeText(ctx, "Export successful!", Toast.LENGTH_SHORT).show();

            } 

            else 

            {

                Toast.makeText(ctx, "Export failed", Toast.LENGTH_SHORT).show();

            }

        }

    }
 
This is used with a button Listener as follows -
Context ctx = MainActivity.this;

//Create a Button listener for the CSV Export

Button impCSVBtn = (Button) findViewById(R.id.imp_csv_id);

impCSVBtn.setOnClickListener(new OnClickListener() {


    public void onClick(View view)

    {


  try

  {

      new ExportDatabaseCSVTask().execute("");

  }

  catch(Exception ex)

  {

      Log.e("Error in MainActivity",ex.toString());

  }

    }

});


This solved the export data problem for my app. 
 
References :
http://stackoverflow.com ( No specific question but after reading the responses for similar questions I came up with this ).
 
Related Reading :
Android Database Table export to CSV – Part 1
Android Database Table export to CSV – Part 2

					

				

Android Database Table export to CSV – Part 2

Copy the Android database into DB File -

• This is the easier of the two methods. I put a search in Google and found many queries and resolution for this.
• Basically what we do here is the SQLite Database creates .db file. this db file is copied as it is as a backup. We can use external SQLite readers in our PC to access the data.
• Code :

We create an class that extends AsyncTask so that export is handled in a thread.

/*******************************************************************************

********************************************************************************

Export Database into file Class

********************************************************************************

*******************************************************************************/    

    class ExportDatabaseFileTask extends AsyncTask<String, Void, Boolean> 

    {

        private final ProgressDialog dialog = new ProgressDialog(ctx);


        // can use UI thread here

        @Override

        protected void onPreExecute() 

        {

            this.dialog.setMessage("Exporting database...");

            this.dialog.show();

        }   


        // automatically done on worker thread (separate from UI thread)

        protected Boolean doInBackground(final String... args) 

        {

            File dbFile = new File(Environment.getDataDirectory() + 

                    "/data/org.test.xyz/databases/excerDB.db");

            File exportDir = new File(Environment.getExternalStorageDirectory(), "");        

            if (!exportDir.exists()) 

            {

                exportDir.mkdirs();

            }

            File file = new File(exportDir, dbFile.getName());

            try 

            {

                file.createNewFile();

                this.copyFile(dbFile, file);

                return true;

            } 

            catch (IOException e) 

            {

                Log.e("mypck", e.getMessage(), e);

                return false;

            }

        }

        // can use UI thread here

        @Override

        protected void onPostExecute(final Boolean success) 

        {

            if (this.dialog.isShowing()) 

            {

                this.dialog.dismiss();

            }

            if (success) 

            {

                Toast.makeText(ctx, "Export successful!", Toast.LENGTH_SHORT).show();

            } 

            else 

            {

                Toast.makeText(ctx, "Export failed", Toast.LENGTH_SHORT).show();

            }

        }

        void copyFile(File src, File dst) throws IOException 

        {

            FileChannel inChannel = new FileInputStream(src).getChannel();

            FileChannel outChannel = new FileOutputStream(dst).getChannel();

            try 

            {

                inChannel.transferTo(0, inChannel.size(), outChannel);

            } 

            finally 

            {

                if (inChannel != null)

                    inChannel.close();

                if (outChannel != null)

                    outChannel.close();

            }

        }

    }
This function is called in the OnClick Button event
Context ctx = MainActivity.this; 

//Create a Button Listener for Export database

Button impDBBtn = (Button) findViewById(R.id.imp_db_id);

impDBBtn.setOnClickListener(new OnClickListener() {

    public void onClick(View view)

    {


  try

  {

      new ExportDatabaseFileTask().execute("");

  }

  catch(Exception ex)

  {

      Log.e("Error in MainActivity",ex.toString());

  }

    }            

});

//Export Database into file Class
This is a very easy way of creating the backup of your App Data. But the diadvantage is that we need external database viewer to read the data.
If the data needs to be viewed then we need to use CSV Format which can be easily opened through Microsoft Excel.
References 
StackOverFlow

					

				

Android Database Table export to CSV – Part 1

The best way to export data out of Android Sqlite database is create a copy the DB file into the SDCard.

Copy the Android database into DB File -

Advantages -

• The import and export of the database would be a lot easy.
• All tables could be backed up using a single file. ( This can have the backup of the user defined configuration of the program also ).

Disadvantages -

• We need special tools to read this DB file which makes it difficult to be portable.

Copy a table into CSV file -

Advantages -

• Only data required by the user can be given.
• It is the more readable and portable format.

In my application I have decided to have both this methods. Will be updating soon.