Exporting SQLite database into pdf with PDFJET

In this tutorial I will explain how to convert an SQLite database into pdf using a third party library, PDFJET. You can download the library, as well as the documentation, from this site. Make sure to download the open source library, which is free but relatively complete (there is also a more complete version, which you can use only for evaluation purposes, but you have to pay for it for developing your apps).

Why using PDFJET library

There are 3 main reasons for using PDFJET library in my opinion:

1. there is an open source version of the library (FREE);
2. it’s relatively complete (it has all the main features you need to create a pdf file);
3. it’s light (about 350 kb).

Of course if you need more advanced features you should better use the pay version of the library, or other librarries that you prefer.

Setting up your project (Eclipse) to use the library

To use PDFJET library in your project (Eclipse) follow these steps:

1. download the library from this page (open source version);
2. extract the files to a different folder;
3. in Eclipse create the package com.pdfjet in the src folder;
4. copy alla java files that you find in the folder open source\PDFjet-Open-Source\com\pdfjet in the package com.pdfjet that you have created;
5. import the package in your class with import com.pdfjet.

Now you can use the PDFJET library in your class.

Using the library

If you visit the PDFJET official site you’ll find dozens of examples about how to use the library and a complete documentation of all the classes available (the documentation can also be found in the library you download from the website: look at the folder open source\PDFjet-Open-Source\docs\java\com\pdfjet).

In this tutorial I will only give you a few examples of the instructions you can use to quickly convert an SQLite database into a pdf file.

Creating the pdf file

Let’s suppose we want to create a pdf file, Budget.pdf, in the external storage directory of our device. Here is the code:

String state = Environment.getExternalStorageState();

//check if the external directory is availabe for writing

if (!Environment.MEDIA_MOUNTED.equals(state)) {

return;

}

else {

File exportDir = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS);

}

//if the external storage directory does not exists, we create it

if (!exportDir.exists()) {

exportDir.mkdirs();

}

File file;

file = new File(exportDir, "Budget.pdf");

//PDF is a class of the PDFJET library

PDF pdf = new PDF(new FileOutputStream(file));

//instructions to create the pdf file content

pdf.flush();

Title

Now suppose that we want to create a title and center it on the page (horizontally):

//first we create a page with portrait orientation

Page page = new Page(pdf, Letter.PORTRAIT);

//font of the title

Font f1 = new Font(pdf, CoreFont.HELVETICA_BOLD);

f1.setSize(7.0f);

//title: font f1 and color blue

TextLine title = new TextLine(f1, “TABLE’S TITLE”);

title.setFont(f1);

title.setColor(Color.blue);

//center the title horizontally on the page

title.setPosition(page.getWidth()/2 – title.getWidth()/2, 40f);

//draw the title on the page

title.drawOn(page);

Links

Here is the code to create a link to our website:

//font of the link

Font f2 = new Font(pdf, CoreFont.HELVETICA);

f2.setSize(7.0f);

//text of the link

TextLine website = new TextLine(f2, "Visit Website");

website.setColor(Color.blue);

website.setUnderline(true);

website.setURIAction("https://sites.google.com/site/flingsoftware/");

//position of the link

website.setPosition(40f, 40f);

//draw the link on the page

website.drawOn(page);

Creating a table

To convert an SQLite database into pdf we need of course a table. Here is the code to create it:

Table table = new Table();

List<List<Cell>> tableData = new ArrayList<List<Cell>>();

As you can see to create a table we use an ArrayList where each element is represented by another ArrayList containing Cell objects. Cell is a class of the PDFJET library used to create a single cell in the table.

Adding columns’ titles

List<Cell> columnTitles = new ArrayList<Cell>();

columnTitles.add(new Cell(f1, “COLUMN 1”);

columnTitles.add(new Cell(f1, “COLUMN 2”);

columnTitles.add(new Cell(f1, “COLUMN 3”);

columnTitles.add(new Cell(f1, “COLUMN 4”);

columnTitles.add(new Cell(f1, “COLUMN 5”);

columnTitles.add(new Cell(f1, “COLUMN 6”);

//light gray background and center alignment

for(int i=0; i<6; i++) {

   ((Cell) columnTitles.get(i)).setBgColor(Color.lightyellow);

   ((Cell) columnTitles.get(i)).setTextAlignment(Align.CENTER);

}

tableData.add(columnTitles);

In this example we created an ArrayList of Cells, each of which representing the title of a column, we set the color background and alignment of each Cell, and finally added the ArrayList to the “ArrayList of ArrayLists” tableData, our complete table.

Adding database records to the table

DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.getDefault());

NumberFormat nf = NumberFormat.getCurrencyInstance(Locale.getDefault());

int i = 1;

while(myCursor.moveToNext()) {

   //retrieve data from cursor  

   Long date = myCursor.getLong(myCursor.getColumnIndex("date"));

   String item = myCursor.getString(myCursor.getColumnIndex("item"));

   Double amount = myCursor.getDouble(myCursor.getColumnIndex("amount"));

   String currency = myCursor.getString(myCursor.getColumnIndex("currency"));

   Double convAmount = myCursor.getDouble(myCursor.getColumnIndex("converted_amount"));

   String description = myCursor.getString(myCursor.getColumnIndex("description"));

   //next record in the table

   List<Cell> record = new ArrayList<Cell>();

   //create Cells and add them to the record

   Cell dateCell = new Cell(f2, df.format(new Date(date)));

   dateCell.setTextAlignment(Align.CENTER);

   record.add(dataCell);

   record.add(new Cell(f2, item));

   Cell amountCell = new Cell(f2, nf.format(amount));

   amountCell.setTextAlignment(Align.RIGHT);

   record.add(amountCell);

   Cell currencyCell = new Cell(f2, currency);

   currencyCell.setTextAlignment(Align.CENTER);

   record.add(currencyCell);

   Cell convAmountCell = new Cell(f2, nf.format(convAmount));

   convAmountCell.setTextAlignment(Align.RIGHT);

   record.add(convAmountCell);

   record.add(new Cell(f2, description));

   

   //add the record to the table

   tableData.add(record);

   //one line gray and one line white...

   if(i%2 == 0) {

dateCell.setBgColor(Color.lightgray);

amountCell.setBgColor(Color.lightgray);

        ...

   }

   i++;

}

In the previous example we iterate over the Cursor retrieved from the database and write each record in the table, field (each represented by a Cell object of the PDFJET library) after field.

Printing the table on the page

//populate the table with our tableData ArrayList

table.setData(tableData, Table.DATA_HAS_1_HEADER_ROWS);

//autoadjust column widths to fit the content

table.autoAdjustColumnWidths();

//let’s suppose we want to manually set the width of column 0

table.setColumnWidth(0, 40.0f);

//each cell can contain more rows

table.wrapAroundCellText();

table.setPosition(page.getWidth()/2 – table.getWidth()/2, 40f);

table.drawOn(page);

Printing the table on more than one page

If your table is particularly long and you need more than one page to print it, you can use the following loop:

int numOfPages = table.getNumberOfPages(page);

int currentPage = 0;

while (true) {

point = table.drawOn(page);

   //print page number

   TextLine numPag = new TextLine(f2, ++currentPage + “ of “ + numOfPages);

   numPag.setPosition(largPag - 30.0f - numPag.getWidth(), page.getHeight() - 20.0f);

   numPag.drawOn(page);

   //scrivo il nome tabella a fondo pagina

   TextLine tabella = new TextLine(f2,    mioContext.getString(R.string.menu_esporta_tabellaPdf_fondoPaginaSpese));

   tabella.setPosition(30.0f, page.getHeight() - 20.0f);

   tabella.drawOn(page);

   if (!table.hasMoreData()) {

      // Allow the table to be drawn again later:

      table.resetRenderedPagesCount();

      break;

   }

   page = new Page(pdf, Letter.PORTRAIT);

}

Autostart an app at bootup

Generally speaking it's not possible in Android to make your app to start automatically at bootup. But you can use a trick to achieve the same result.

Basically you have to register a BroadcastReceiver in your AndroidManifest.xml file to listen to the Intent.ACTION_BOOT_COMPLETED event. In your BroadcastReceiver class you can now start your app or Service.
This method is often used to start a Service to perform certain operations in the background (for example, to register an alarm clock).

Let's look at the code of the AndroidManifest.xml file:

<uses-permission android:name="android.permission.RECEIVE_BOOT_COMPLETED" />
...
<receiver android:enabled="true" android:name=".BootUpReceiver"
        android:permission="android.permission.RECEIVE_BOOT_COMPLETED">

        <intent-filter>
                <action android:name="android.intent.action.BOOT_COMPLETED" />
                <category android:name="android.intent.category.DEFAULT" />
        </intent-filter>
</receiver>

Now, let's suppose that you want to start a Service at bootup. The code of our BroadcastReceiver is the following:

public class BootUpReceiver extends BroadcastReceiver{

        @Override
        public void onReceive(Context context, Intent intent) {
                Intent i = new Intent(INTENT_ACTION);  
                i.putExtra...
                [...]
                context.startService(i);  
        }

}

Of course you have to use this code only when really needed: an app starting app every time the device is booting could possibly waste system's resources.

Sending broadcasts within your app with a local BroadcastReceiver

In this tutorial I will explain how to send broadcasts within your own app using the LocalBroadcastManager.

To begin with, let’s start with a FAQ.

What is a BroadcastReceiver?

An Android app is made of components, including Application, Activity, Service, ContentProvider and BroacastReceiver, each of which used for a particular purpose.

So a BroadcastReceiver is a component (optional) of an Android app.

What are BroadcastReceivers generally used for?

BroadcastReceiver is a component running in the background and listening to “broacasts” sent by the Android system or by an Android app.

For example, when the user turns on or off the screen, when the level of the battery changes, when wifi is connected/disconnected, etc., the Android system sends a “broacast” with the relevant information about the event that has occured. You can set up a BroadcastReceiver that intercepts this event and performs a certain action in response (for example, starting a Service).

In your own app you can also send broacasts containing information and set up a BroacastReceiver that listens to these broadcasts and performs certain actions in response. We already saw an example in a previous tutorial about the IntentService class, with which you can use a Service to perform slow or long running operations in a background thread.

When the IntentService finishes its task you may want to communicate the result back to the calling Activity: to do this you have to send a broadcast within your IntentService containing the relevant information, and set up a BroadcastReceiver within you Activity to listen to the result.

So, what is a local BroadcastReceiver?

BroadcastReceiver is generally used to send/receive broacasts between different processes (for example, different apps) because it implements an inter process communication system.

But if the “broadcaster” and the BroadcastReceiver belong to the same process (for example, sending broacasts within your own app), a local BroadcastReceiver, which is managed by the LocalBroadcastManager class, is much more efficient.

In addition to that a local BroadcastReceiver is more secure because all relevant information stays within your own app and is not visible outside.

So a local BroadcastReceiver is a more efficient and secure BroacastReceiver that works only within your own app.

Now let’s see how to implement a local BroadcastReceiver within your Activity (here you can download the code in a more readable format):

public class YourClass extends Activity {

//the main code of the Activity is omitted for brevity

// used for filtering broadcasts (the receiver listens only to Intents with “myBroacast” set as action in the constructor)

public static final String MY_ACTION = “myBroacast”;

@Override

protected void onResume() {

super.onResume(); //call the superclass first

LocalBroadcastManager myLocalBroadcastManager = LocalBroadcastManager.getInstance(this);

IntentFilter myIntentFilter = new IntentFilter(MY_ACTION);

//set up the local LocalBroadcastReceiver

myLocalBroadcastReceiver = new BroadcastReceiver() {

@Override

public void onReceive(Context context, Intent intent) {

//handle the broadcast here

}

};

//register the local BroadcastReceiver with the specified intent filter

myLocalBroadcastManager.registerReceiver(myLocalBroadcastReceiver, myIntentFilter);

}

@Override

protected void onPause() {

super.onPause();

LocalBroadcastManager myLocalBroadcastManager = LocalBroadcastManager.getInstance(this);

//always unregister the receiver when it’s no longer needed to save resources

myLocalBroadcastManager.unregisterReceiver(myLocalBroadcastReceiver);

}

//instance variables

private BroadcastReceiver myLocalBroadcastReceiver;

}

When you want to send a local broadcast, you just have to use the following code:

//create an Intent with the action “myBroadcast” (the filter used in our local receiver)

Intent localBroadcastIntent = new Intent(“myBroacast”);

//you can add additional info to the Intent

localBroadcastIntent.putExtra(“risult”, risult);

LocalBroadcastManager myLocalBroadcastManager = LocalBroadcastManager.getInstance(this);

myLocalBroadcastManager.sendBroacast(localBroadcastIntent);

Using directed broadcasts

Another way of sending broacasts within you own app is by using directed broadcasts, explicitly specifying the BroadcastReceiver that has to intercept them.

You just have to set the ComponentName in the Intent:

Intent myIntent = new Intent(action);

myIntent.setComponent(new ComponentName(packageName, className));

sendBroadcast(myIntent);

action = String representing the filter used by our BroadcastReceiver

packageName = String representing the name of the package of our BroadcastReceiver (like “com.androidthetechnicalblog.example”)

className = String representing the name of the class of our BroadcastReceiver

This way our broadcast is received only by the specified BroadcastReceiver.

Directed broadcasts can also be used with different processes/applications.

If you like this blog please share it on Facebook, Twitter or Google+ using the icons or links below. 

You can also subscribe using the form on the right to be notified via mail or feed rss when new tutorials are posted.

Exporting SQLite Database into .csv format

While developing my next app I came across an interesting problem: how do you export an SQLite database into .csv format? There are of course third party libraries that will do the job for you, but this is Android - The Technical Blog, so we want to understand "how stuff works" under the hood.

So let's start with Wikipedia...
According to Wikipedia csv stands for "comma separated values". CSV files are basically text files where values are separated by commas. There is not a formal standard defining .csv files, but the easiest way to convert an SQLite database into a .csv file is the following:

1. first you create a text file with the .csv extension. This way you can easily read the file with Excel or OpenOffice Calc, but you can also use .txt suffix for reading the file with Notepad;
2. you read the database one record after the other;
3. different records in the database are represented by different lines in the .csv file, while the fields of each record are separeted by commas.

In the following example we use the java.io.FileWriter class, because we want to create a text file writing one character at a time (2 bytes), and the java.io.PrintWriter class as a Decorator (remember the Decorator pattern in Java?), because we want to write one line at a time in the file.

Here you can download the code in a more readable format.

public boolean exportDatabase() {
DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.getDefault());

/**First of all we check if the external storage of the device is available for writing.
* Remember that the external storage is not necessarily the sd card. Very often it is
* the device storage.
*/
String state = Environment.getExternalStorageState();
if (!Environment.MEDIA_MOUNTED.equals(state)) { 
return false;
}
else {
//We use the Download directory for saving our .csv file.
   File exportDir = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS);      
   if (!exportDir.exists()) 
   {
       exportDir.mkdirs();
   }
   
   File file;
   PrintWriter printWriter = null;
   try 
   {
    file = new File(exportDir, "MyCSVFile.csv");
       file.createNewFile();                
       printWriter = new PrintWriter(new FileWriter(file));
                  
       /**This is our database connector class that reads the data from the database.
        * The code of this class is omitted for brevity.
        */
    DBCOurDatabaseConnector dbcOurDatabaseConnector = new DBCOurDatabaseConnector(myContext);
    dbcOurDatabaseConnector.openForReading(); //open the database for reading
   
    /**Let's read the first table of the database.
    * getFirstTable() is a method in our DBCOurDatabaseConnector class which retrieves a Cursor
    * containing all records of the table (all fields).
    * The code of this class is omitted for brevity.
    */
    Cursor curCSV = dbcOurDatabaseConnector.getFirstTable();
    //Write the name of the table and the name of the columns (comma separated values) in the .csv file.
    printWriter.println("FIRST TABLE OF THE DATABASE");
    printWriter.println("DATE,ITEM,AMOUNT,CURRENCY");
       while(curCSV.moveToNext())
       {
        Long date = curCSV.getLong(curCSV.getColumnIndex("date"));
        String item = curCSV.getString(curCSV.getColumnIndex("item"));
        Double amount = curCSV.getDouble(curCSV.getColumnIndex("amount"));
        String currency = curCSV.getString(curCSV.getColumnIndex("currency"));
       
        /**Create the line to write in the .csv file. 
        * We need a String where values are comma separated.
        * The field date (Long) is formatted in a readable text. The amount field
        * is converted into String.
        */
        String record = df.format(new Date(date)) + "," + item + "," + importo.toString() + "," + currency;
   printWriter.println(record); //write the record in the .csv file
}
 
curCSV.close();
dbcOurDatabaseConnector.close();
   }
  
  catch(Exception exc) {
  //if there are any exceptions, return false
  return false;
  }
  finally {
  if(printWriter != null) printWriter.close();
  }
  
  //If there are no errors, return true.
  return true;
}
}

Alternative main Activity

Let's take a little break from Services and cover an easier topic...

In this tutorial I will explain how you can create an alternative main Activity using the attribute android:enabled in the AndroidManifest.xml file.

An alternative main Activity could be useful for different purposes: a brief description or tutorial covering the main features of your app, or for a setup process for example. Of course, you want the alternative main Activity to be started only once (the first time the user launches the app), while afterwards the regular main Activity should be used.

To begin with, we declare the two Activities in the AndroidManifest.xml file, like in the following example:

<activity

   android:name=”.ActivityTutorial”

   android:label=”@string/activity_tutorial_label”

   android:icon=”@drawable/activity_tutorial_icon”

   android:enabled=”true”>

   <intent-filter>

      <action android:name=”android.intent.action.MAIN”/>

      <category android:name=”android.intent.category.LAUNCHER”/>

   </intent-filter>

</activity>

<activity

   android:name=”.ActivityMain”

   android:label=”@string/activity_main_label”

   android:icon=”@drawable/activity_main_icon”

   android:enabled=”false”>

   <intent-filter>

      <action android:name=”android.intent.action.MAIN”/>

      <category android:name=”android.intent.category.LAUNCHER”/>

   </intent-filter>

</activity>

There are two things to notice in the previous example: the enabled attribute is set to true for the tutorial Activity (this is the Activity that starts when the user launches the app for the first time) and false for the usual main Activity.

Notice also the intent filter of the two Activities: it is the same because both Activities are used as the main Activity of your app: the ActivityTutorial only the first time, the ActivityMain from the second time onwards.

In order to toggle the enabled attribute of the two Activities (from true to false for ActivityTutorial, from false to true for ActivityMain) we use the following code within the ActivityTutorial:

PackageManager myPackageManager = getPackageManager();

//let’s disable the ActivityTutorial

myPackageManager.setComponentEnabledSetting(new ComponentName(this, ActivityTutorial.class), PackageManager.COMPONENT_ENABLED_STATE_DISABLED, PackageManager.DONT_KILL_APP);

//let’s enable the ActivityMain

myPackageManager.setComponentEnabledSetting(new ComponentName(this, ActivityMain.class), PackageManager.COMPONENT_ENABLED_STATE_ENABLED, PackageManager.DONT_KILL_APP);

We used the DONT_KILL_APP flag because we don’t want to kill the app containing the component.

The class PackageManager belongs to the following package: android.content.pm.

IntentService in Android

In this tutorial I will explain briefly what an IntentService is and how it should be used in an Android app.
To begin with, let's start with a FAQ...

What is a Service?
An Android app is made of components. The most important components are the following: Activity, Service, BroadcastReceiver and ContentProvider.
An IntentService is a special kind of Service and therefore a component of an Android app.

What are Services used for?
Services in Android are generally used for operations that are not directly involved with the user interface (UI). For example: network operations, writing and reading data to/from the local storage (a database, file, etc.), playing music, and other tasks like these.
It is generally better to use one Service for each different task: one Service for network operations, one different Service for managing the database and so on.
Some ot these tasks can also be managed within a normal Activity, using a dedicated thread (AsyncTask or Handler, as I explained in a different tutorial), especially if the data involved comes from the user interface (for example: writing to a local database the data entered by the user).
But there are other tasks (like network operations or playing music) that are bettere managed by Services.
Generally speaking Services allow you to efficiently separate the logic of background tasks from the code that handles the user interface.

Are Services running in a different thread?
No, the code in a Service runs in the main thread (the thread of the GUI). So it is very important to create a different thread within the Service (using an AsyncTask or Handler) to manage long running or slow operations, otherwise they could slow down or block the user interface.

So, what is an IntentService in the end?
An IntentService is a special utility class which represents a Service whose operations run in a different thread than the thread of the GUI.
This is managed automatically by the IntentService class, so you don't have to implement an AsynckTask or a Handler.

Let's see an example.
The following IntentService is used to upload a text or a photo to a remote server in a different thread.

public class MyIntentService extends IntentService {

    //constants
    private static final String CLASSNAME = "MyIntentService";

    //the following 2 actions have to be added in the intent filter for the
    //IntentService in the manifest file
    public static final String ACTION_UPLOAD_TEXT = "com.tutorial.UPLOADTEXT";
    public static final String ACTION_UPLOAD_PHOTO = "com.tutorial.UPLOADPHOTO";
    
    //extras to put in the Intent that starts this IntentService
    public static final String TEXT = "text";
    public static final String PHOTO= "photo";

    public MyIntentService() {
        super(CLASSNAME);

        //if the system shuts down the service, the Intent is not redelivered
        //and therefore the Service won't start again
        setIntentRedelivery(false);
    }

    //this method runs in a different thread (not the main thread)
    @Override
    protected void onHandleIntent(Intent intent) {
        String action = intent.getAction();

        if(action.equals(ACTION_UPLOAD_TEXT)) {
            String text = intent.getStringExtra(TEXT);
            uploadText(text);
        }
        else if(action.equals(ACTION_UPLOAD_PHOTO)) {
            Bitmap photo = itent.getParcelableExtra(PHOTO);
            uploadPhoto(photo);
        }
    }

    private void uploadText(String text) {
        //in this method you upload the text to the server: omitted for brevity
    }

    private void uploadPhoto(Bitmap photo) {
        //in this method you upload the photo to the server: omitted for brevity
    }
}

If you want to use this IntentService from your Activity to upload a text or a photo to the remote server in a dedicated thread, you just have to use the command Context.startService() with the Itent carrying the specific action and extras.

There is only one disadvantage in this approach: if you have multiple calls to the IntentService, these will be put in a queue and processed one at a time (there is no parallel execution). If you need parallel execution, to make sure that each background operation is executed as soon as possibile, you have to use a different approach, but this will be the object of a different tutorial.

Formatted strings and plurals

If you have been programming in Android for a while, you probably know that string resources should be put in a centralized file, strings.xml in the res/values directory.
This way you can easily change string resources by changing the corresponding value in the strings.xml file (even people who can't program in Android would be able to do this). You can also create different strings.xml files for different languages in order to internationalize your app.

But probably not everyone knows that you can do more than this. Let's see what...

1) FORMATTED STRINGS

You can of course format strings in the strings.xml file. The syntax is the same as that of the java.util.Formatter class (see for more details).
Here is an example:

<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="login_message">Login %s!</string>
...
</resources>

and here is how you can use this string with a paramter:

getString(R.string.login_message, name)

2) PLURALS

With plurals you can obtain different strings depending on the amount of input. Here is a complete example of what you can do:

<?xml version="1.0" encoding="utf-8"?>
<resources>
<plurals name="score_count">
    <item quantity="zero">Your score is zero! Very bad!</item>
    <item quantity="one">Your score is one! This is not very good!</item>
    <item quantity="two">Your score is two! Come on!</item>
    <item quantity="few">Your score is %d!</item>
    <item quantity="many">Your score %1$d! %2$s, well done!</item>
    <item quantity="other">Your score is %1$d!! %2$s, try again!</item>
</plurals>
...
</resources>

and here is how you can use this plurals resource with paramters: the system will select the correct string (using and formatting the parameters if necessary) depending on the amount of input:

getQuantityString(R.plurals.score_count, score, score, yourname);

As you can see, the secondo paramter (score) is used to determine which string has to be chosen (it is the amount), while the other two parameters (score and yourname) are used for formatting the chosen string.