Inserting data into the database

The SQL INSERT statement is used whenever you want to insert data into an SQL database. The INSERT statement maps to the "create" operation of the CRUD methodology.

In the MJAndroid application, the user can add jobs to the list by clicking on the Add Job menu item when looking at the Jobs list. The user can then fill out a form to input the employer, job title, and description. After the user clicks on the Add Job button on the form, the following line of code is executed:

db.addJob(employer.id, txtTitle.getText().toString(), txtDescription.getText().toString());

This code calls the addJob function, passing in the employer ID, the job title, and the job description. The addJob function does the actual work of writing the job out to the database.

Example 8-1 shows you how to use the insert method.

Example 8-1. Using the insert method /**

* Add a new job to the database. The job will have a status of open.

* @param employer_id The employer offering the job

* @param title The job title

* @param description The job description */

public void addJob(long employer_id, String title, String description){ ContentValues map = new ContentValues();0 map.put("employer_id", employer_id); map.put("title", title); map.put("description", description); try{

getWritableDatabase().insert("jobs", null, map); } catch (SQLException e) {

Log.e("Error writing new job", e.toString());

Here are some of the highlights of the code in Example 8-1:

O The ContentValues object is a map of column names to column values. Internally, it's implemented as a HashMap<String,Object>. However, unlike a simple HashMap, ContentValues is strongly typed. You can specify the data type of each value stored in a ContentValues container. When trying to pull values back out, ContentValues will automatically convert values to the requested type if possible.

© The second parameter to the insert method is nullColumnHack. It's used only when the third parameter, the map, is null and therefore the row would otherwise be completely empty.

Example 8-2 shows you how to use the execSQL method.

Example 8-2. Using the execSQL method /**

* Add a new job to the database. The job will have a status of open.

* @param employer_id The employer offering the job

* @param title The job title

* @param description The job description */

public void addJob(long employer_id, String title, String description){ String sql = O

"INSERT INTO jobs (_id, employer_id, title, description, start_time, end_time, status) " +

Object[] bindArgs = new Object[]{employer_id, title, description}; try{

getWritableDatabase().execSQL(sql, bindArgs); } catch (SQLException e) {

Log.e("Error writing new job", e.toString());

Here are some of the highlights of the code in Example 8-2:

O First, we build a SQL string template named sql that contains bindable parameters that will be filled in with user data. The bindable parameters are marked by a question mark in the string. Next, we build an object array named bindArgs that contains one object per element in our SQL template. There are three question marks in the template, and therefore there must be three elements in the object array.

© Executes the SQL command by passing the SQL template string and the bind arguments to execSQL. Using a SQL template and bind arguments is much preferred over building up the SQL statement, complete with parameters, into a String or StringBuilder. By using a template with parameters, you protect your application from SQL injection attacks. These attacks occur when a malicious user enters information into a form that is deliberately meant to modify the database in a way that was not intended by the developer. This is normally done by ending the current SQL command prematurely, using SQL syntax characters, and then adding new SQL commands directly in the form field. The template-plus-parameters approach also protects you from more run-of-the-mill errors, such as invalid characters in the parameters.

0 0

Post a comment