The Blogger In Me

Working with date in SQLite for Android

batta420 — Sat, 03 Mar 2012 07:49:11 +0000

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

Filed under: Android, Computer, Java, Programming, Software Tagged: Android, Java, Sqlite

Android Programming – setContentView()

batta420 — Sat, 10 Dec 2011 13:28:00 +0000

setContentView() is a very important function when it comes to programming with Android.

One has to understand its use completely to work with Android UserInterface.

Basically what this function does is display the Layout created thorugh XML or the Dynamically created layout view in the Screen.

Syntax -

For an XML Layout -

setContentView(R.layout.showexcer);
where showexcer.xml is your xml file.
For Dynamically created Java View/Layout
View v1=new View(this);

setContentView(v1);
Similarly a dynamically created layout can be dispalyed using setContentView();

setContentView is amethod of Activity class. So to call you the class should be a subclass of Activity class.


Filed under: Android, Computer Tagged: Android, Computer, Java, Problem, Programming, setContentView()

Android Database Table export to CSV – Part 3

batta420 — Sun, 04 Dec 2011 20:19:24 +0000

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 

    {

        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

Filed under: Android, Computer, Java, Programming

Android Database Table export to CSV – Part 2

batta420 — Sat, 03 Dec 2011 06:31:38 +0000

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 

    {

        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

Filed under: Android, Computer, Java, Programming

Android Database Table export to CSV – Part 1

batta420 — Mon, 28 Nov 2011 19:17:00 +0000

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.

Filed under: Android, Computer, Java, Programming Tagged: Android, Computer, CSV, Export, Java, Programming

Create a Alert Dialog in Android

batta420 — Mon, 21 Nov 2011 15:43:09 +0000

Dialogs are generally used to display the the intermediate data like Status Messages, Warning Messages or Error Messages.

In this post we will go through how to create a Dialog Box in Android App development -

Create a function will call the Alert Dialog (AddEx is the activity. You can use your activity name over here ).

  1: private void displayDialog(String str1)

  2: {

  3:   AlertDialog.Builder builder = new AlertDialog.Builder(AddEx.this);

  4:   builder.setMessage(str1);

  5:   builder.setCancelable(false);

  6:   builder.setTitle("Status");

  7:   builder.setPositiveButton("OK", new DialogInterface.OnClickListener() {

  8:          @Override

  9:          public void onClick(DialogInterface dialog, int id) {

 10:           AddEx.this.finish();

 11:          }

 12:        });

 13:   AlertDialog alert = builder.create();

 14:   alert.show();

 15: }



Call this function anywhere in your activity class.

  1:  if(status == true)

  2:  {

  3:   displayDialog("Completed Successfully");

  4:  }

  5:  else

  6:  {

  7:    displayDialog("Error inserting into Table");

  8:  }

In the Alert Dialog you can also implement builder.setNegativebutton() method to have “CANCEL” part included in the dialog. Implementation is similar to setPositiveButton.


Filed under: Computer, Programming, Software Tagged: Android, create, Dialog, Java, Programming

How to compile JAVA Project into a single JAR File using NetBeans

batta420 — Thu, 03 Nov 2011 15:32:00 +0000

I found an interesting article about compiling a NetBeans project into a single JAR file.

xml version="1.0" encoding="UTF-8"?>

<project name="build-OneJar1" default="all" basedir="..">

   <target name="package-for-store" depends="jar">


        <!– Change the value of this property to be the name of your JAR,

             minus the .jar extension. It should not have spaces.

             <property name="store.jar.name" value="MyJarName"/>

        –>

        <property name="store.jar.name" value="MyProject"/>


        <!– don’t edit below this line –>


        <property name="store.dir" value="store"/>

        <property name="store.jar" value="${store.dir}/${store.jar.name}.jar"/>


        <echo message="Packaging ${application.title} into a single JAR at ${store.jar}"/>


        <delete dir="${store.dir}"/>

        <mkdir dir="${store.dir}"/>


        <jar destfile="${store.dir}/temp_final.jar" filesetmanifest="skip">

            <zipgroupfileset dir="dist" includes="*.jar"/>

            <zipgroupfileset dir="dist/lib" includes="*.jar"/>


            <manifest>

                <attribute name="Main-Class" value="${main.class}"/>

            manifest>

        jar>


        <zip destfile="${store.jar}">

            <zipfileset src="${store.dir}/temp_final.jar"

            excludes="META-INF/*.SF, META-INF/*.DSA, META-INF/*.RSA"/>

        zip>


        <delete file="${store.dir}/temp_final.jar"/>


    target>

project>

Save the above code as build-OneJar.xml and save it in the NetBeans project folder.( I saved in the nbproject folder and also had the file as a ANT project file.). 

 

In Build.xml put an import statement and import the build-onejar.xml file. 

 

To build the NetBeans project into a single JAR file, it needs to be run using the specified parameters as TARGET. To do this Right Click build.xml in the Files TAB and select Run Target –> Other Targets –> Package-For-Store. 

 

The JAR file will be built and stored in a folder called Store in the 
Also remember to save the build-onejar.xml file as a template for future use. To do this go right click on the file in the Files TAB and select Save As Template….. select the appropriate folder and click OK. 

 
 
  In this way a NetBeans project can be compiled into a single JAR file.

Sources
http://java.sun.com/developer/technicalArticles/java_warehouse/single_jar/

Filed under: Computer, Programming, Software Tagged: Java

How to use SQLite with Java using NetBeans IDE

batta420 — Wed, 02 Nov 2011 17:17:19 +0000

SQLite is an embedded database which will have all the tables stored in a db file. This saves the trouble of having to setup database servers everytime we develop a new programs. Android uses SQLite as a database.
I was browsing through the net on how to use SQLite for a small app I was developing. I came across this page which had very good directions on how to use SQLite with Java.

1. SQLite is serverless. With SQLiteJDBC driver, we can write programs to access SQLite using JAVA.
This tutorial will have step by step description about how to do it.
2. We will use Netbeans, but it will work in the same manner with eclipse too.
3. Download SQLiteJDBC driver jar, ‘sqlitejdbc-v056.jar’ from website: http://www.zentus.com/sqlitejdbc/
4. Create a java project named ‘SQLite’ in Netbeans.(You can give any name
5. Create a class called ‘Test’ in that project.
6. Add ‘sqlitejdbc-v056.jar’ in the class path of ‘SQLite’ project in Netbeans.
7. Paste following code in Test class
—>
import java.sql.*;
public class Test {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn =
DriverManager.getConnection("jdbc:sqlite:Vinit");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists school;");
stat.executeUpdate("create table school (name, state);");
PreparedStatement prep = conn.prepareStatement(
"insert into school values (?, ?);");
prep.setString(1, "UTD");
prep.setString(2, "texas");
prep.addBatch();
prep.setString(1, "USC");
prep.setString(2, "california");
prep.addBatch();
prep.setString(1, "MIT");
prep.setString(2, "massachusetts");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from school;");
while (rs.next()) {
System.out.print("Name of School = " + rs.getString("name") + " ");
System.out.println("state = " + rs.getString("state"));
}
rs.close();
conn.close();
}
}
—>
8. Run the project as java application.
9. It will create database file by name ‘Vinit’ in the Netbeans ‘SQLite’ project folder.
10. Here is in above code, we are creating sqlite database ‘Vinit’.

In NetBeans add libraries by going to Tools –> Libraries and Click ADD LIBRARY. An Add Library dialog will appear

Add the driver JAR file location into CLASSPATH tab

In the Projects window Right Click on Libraries folder and Select Add Library…

Click on Add Library and select Sqlite3.

Sources:

http://en.wikibooks.org/wiki/Java_JDBC_using_SQLite/Connecting

http://tech-britney.blogspot.com/2010/07/using-sqlite-with-sqlitejdbc-driver.html

Filed under: Computer, Programming, Software Tagged: Java, Sqlite

Final Year Projects

batta420 — Mon, 01 Mar 2010 17:00:13 +0000

Finally after 4 years of struggle and hard work( ??? ), finally engineering studies is going to end in 3 months. But wait…. to complete it we all have to complete a project where we are supposed to apply all that we had studied in the past 4 years come out with some good projects. Well one month already passed and I’ve not started anything yet. Hope I get it done soon…..

Filed under: personal

Placed in Infosys

batta420 — Sun, 28 Feb 2010 20:09:32 +0000

I got placed in Infosys last week. It was through the Campus recruitment in our College. We had first Aptitude Round and then HR interview. It was actually a cool thing because I was not expecting to get placed in the company and I guess I really did surprise myself!!!! Now the classes seem very boring since after doing 4 years of Engineering in Electronics And Communication , I am going to leave this field anyway to join the software industry. So started writing this blog again. Hope I’ll be writing for sometime ( Not that anyone cares). Still I guess I like to imagine myself heard by lot of people.

Will be writing again soon….

Filed under: personal Tagged: Campus, Infosys, Placement