Ok, so for our first app, we’re going to ditch the tried and tested ‘HelloWorld’ and go for something new – a simple app that creates a database and then plays with it.
To create a database, we need an SDCard to store it in. So first off, create a new folder titled BBCard (or whatever the heck you wanna call it! 😉 ). This folder can be anywhere in your system…the local disk or desktop or whichever place pleases you. For the sake of consistency, I put mine within the workspace folder.
Once you have an SDCard ready, you can create a simple database. Our database is going to contain only one table. Let’s call it EmployeesTable. This table has three fields – one each for employee name, employee id and employee salary.
Open up MyApp.java and fill the following code into the constructor :
// Create a string holding path to DB directory
// The path to your SDCard file is always /SDCard/
// But I have added subfolders for the sake of clarity
// As you will see later, if the subfolders dont exist
// they will automatically be created
String dbLocation = "/SDCard/Databases/Revenues/";
// Create URI;this is th complete path to the DB file called Employees (this our DB's name)
uri = URI.create(dbLocation + "Employees"); // Create a new DatabaseOptions object forcing foreign
// key
// constraints
databaseOptions = new DatabaseOptions();
databaseOptions.set("foreign_key_constraints", "on");
// Open or create a plain text database. This will
// create the
// directory and file defined by the URI (if they do not
// already
// exist).
db = DatabaseFactory.openOrCreate(uri,
new DatabaseSecurityOptions(false)); // Close the database in case it is blank and we need to
// write
// to the file
db.close();
Now we have created a file to store our database. If you run only this code on the simulator, a file called Employees will be created within the SDCard folder. After running the app, you can go to the folder and see for yourself. The neaxt thing we need is a way to enter and read data from our DB. The best way to do this is to have a ‘database manager’. Basically this is a class that will do all the talking to the database for us.
So within the package explorer in Eclipse, go to YOURAPP -> src -> mypackage and right click on mypackage. Now go to new -> class. We are going to make a DBManager class. Type in DBManager as the class name and click Finish. Now create a constructor and two functions- one to read and one to write – within this class as follows:
/**
* A class to handle SQLite database logic
*/
public class DBManager
{
private Database _db;
/**
* Constructs a new SQLManager object
* @param db Database to manage
*/
public DBManager(Database db)
{
_db = db;
}
//**************************************************
//...................INSERTION......................
//**************************************************
/**
* Adds an Employee row to the EmployeesTable table in the database
* @return The id of the new directory Revenue
*/
int addEmployee(int id, String name, float salary)
{
long id2 =-1;
// Insert a new record in the table
// 1. Create a statement to execute
// 2. Prepare statement for execution
// 3. Bind argument values to ? marks in statement. Binding is done to improve performance.
// 4. Execute
// 5. Close the statement
Statement statement = _db.createStatement("INSERT INTO EmployeesTable VALUES( ?, ?, ?)");
statement.prepare();
statement.bind(1, id);
statement.bind(2, name);
statement.bind(3, salary);
statement.execute();
statement.close();
// Retrieve the auto-generated ID of the Revenue just added
id2 = _db.lastInsertedRowID();
return (int)id2;
}
/**
* Retrieves all records in the Revenues database table
*/
Cursor getRevenues()
{
Cursor cursor = null;
Statement statement = _db.createStatement("SELECT * FROM Revenues");
statement.prepare();
cursor = statement.getCursor();
return cursor;
}
}
Okay now that we have a database and a manager class,it’s time to create our table. Go back to MyApp and create a function within that class called initializeDB(). This will be our function where we create a table and add data using DBManager. Fill the function as shown :
/**
* function to create table and enter data
*
* @param uri
* Path to DB file
* @param databaseOptions
* DB options
*/
public void initializeDB(URI uri, DatabaseOptions databaseOptions) {
Database db;
//Open the database.
db = DatabaseFactory.open(uri, databaseOptions);
//Create table statement.
Statement statement = db
.createStatement("Create table if not exists Revenues ( _id INTEGER NOT NULL, _geolocation TEXT NOT NULL, _serviceLine TEXT NOT NULL, _amount float NOT NULL, PRIMARY KEY (_geolocation,_serviceLine) )");
statement.prepare();
statement.execute();
statement.close();
// Create a new manager to talk to our database db.
DBManager manager = new DBManager(db);
//Add four new rows.
manager.addEmployee(1, "Samantha Adams", 12000.00);
manager.addEmployee(2, "Sameer Sinha", 10000.00);
manager.addEmployee(3, "L. V. Jones", "BPO", 12000.00);
manager.addEmplyee(4, "Alice MacMohan", 9000.00);
Dialog.alert("4 rows added.");
db.close();
}
Don’t worry too much about missing try-catch blocks. Eclipse will automatically prompt you about these and add them where required. We’re nearly there! All we have to do is call this function from the MyApp constructor and then read the table.
initializeDB();
DBManager manager = new DBManager(db);
Cursor cursor = null;
while (cursor.next()) {
Row row = cursor.getRow();
String s = “Employee”+
String.valueof(row.getFloat
(0))+row.getString(1)+”earns $”
+String.valueof(row.getFloat (2));
Dialog.alert(s);
}
Yes,yes, you can now run the app! 😀
NOTE: Be sure to call the initializeDB() function ONLY ONCE when you run the app. After running the app once and seeing the dialog that confirms the addition of new rows, delete the line that call this method in the MyApp constructor and rerun the app. Otherwise, your app will crash because of constraints on the table.
Databases in BB are very versatile and a lot more can be done with them but I have included only very basic insert and read operations to keep things simple. This has been a very long post but I hope you find it useful.
Happy Coding, my lovelies! 😀
codejunkster 
Leave a comment