Translate

Saturday, June 29, 2013

Android App Development 201 3nd tutorial; Android and SQLite, CREATE, INSERT and SELECT

In this tutorial we will look at how SQLite can be used in Android application to persist and retrieve data. We will make a simple Login and Register form. Register form will have several fields. Once the user is registered, the registration information is stored in SQLite database and the user can now log in using the credentials used during registration. When logged in we will simply show a dialog with user informations retrieved from the database.



Here is the form layout:

activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical"
    android:gravity="center_vertical"
    android:layout_marginLeft="20dp"
    android:layout_marginRight="20dp" >

    <EditText
        android:id="@+id/firstName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="First Name"
        android:ems="10"
        android:inputType="textPersonName" >

        <requestFocus />
    </EditText>

    <EditText
        android:id="@+id/lastName"
        android:layout_width="match_parent"
         android:hint="Last Name"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/email"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Email Address"
        android:ems="10"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/phone"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Phone Number"
        android:ems="10"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/userName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
         android:hint="Username"
        android:ems="10"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/password"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Password"
        android:ems="10"
        android:inputType="textPassword" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <Button
            android:id="@+id/login"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="0.5"
            android:text="Login" />

        <Button
            android:id="@+id/register"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="0.5"
            android:text="Register" />

    </LinearLayout>

    <Button
        android:id="@+id/submit"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:text="Submit" />

</LinearLayout>

We have three different classes, An activity where the layout in inflated, A model Class with getters and setters for form elements and DatabaseConnector Class which takes care of creating database, table, inserting data into database and retrieving data from the database.

Here is the model class:

Form.java
 package com.example.sqlliteandloginform;

public class Form {
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String username;
private String password;
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}

}

Here is the DatabaseConnector class:

DatabaseConnector.java

package com.example.sqlliteandloginform;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseConnector extends SQLiteOpenHelper {

private static final int Version =1;
private static final String firstName="FIRST_NAME";
private static final String lastName="LAST_NAME";
private static final String email="EMAIL";
private static final String phoneNumber="PHONE_NUMBER";
private static final String username="USERNAME";
private static final String password="PASSWORD";
private static final String databaseName="FORM";
private static final String tableName="USER_RECORDS";
private static final String id = "ID";

public DatabaseConnector(Context context) {
super(context, databaseName, null, Version);

}

@Override
public void onCreate(SQLiteDatabase database) {
String createTableSQL = "CREATE TABLE " + tableName + " (" + id +" INTEGER NOT NULL PRIMARY KEY, " + firstName +" TEXT, " + lastName
+ " TEXT, " +email + " TEXT, " + phoneNumber + " TEXT, " + username + " TEXT, " + password + " TEXT)";
Log.d("onCreate()", createTableSQL);
database.execSQL(createTableSQL);

}

@Override
public void onUpgrade(SQLiteDatabase database, int arg1, int arg2) {

}

public void addRecord(String firstname, String lastname, String emailAddress, String phone, String uname, String pword ) {
String insertSQL = "INSERT INTO " + tableName + " (" + firstName + ", " + lastName + " ," + email + " ," + phoneNumber + " ," + username + " ," + password + ") "
+ "VALUES" + " ('" + firstname + "', '" + lastname + "', '" + emailAddress + "', '" + phone + "', '" + uname + "', '" + pword + "')" ;
Log.d("addRecord()", insertSQL);
SQLiteDatabase dataBase = this.getWritableDatabase();
dataBase.execSQL(insertSQL);
dataBase.close();
}

public List<String> getRecord(String uname, String pword) {
List<String> recordList = new ArrayList<String>();
SQLiteDatabase dataBase = this.getReadableDatabase();
String getSQL = "SELECT * FROM " + tableName + " WHERE " + username + " = '" + uname + "' AND " + password + " = '" + pword + "'";
Cursor cursor = dataBase.rawQuery(getSQL , null);
Log.d("getRecord()", getSQL + "##Count = " + cursor.getCount());
cursor.moveToFirst();
String fName = cursor.getString(1);
String lName = cursor.getString(2);
String eMail = cursor.getString(3);
String ph = cursor.getString(4);
Log.d("getRecord()", "FirstName: " + fName + "LastName: " + lName + "Email: " + eMail + "Phone" + ph);
recordList.add(fName);
recordList.add(lName);
recordList.add(eMail);
recordList.add(ph);
dataBase.close();
return recordList;

}


}

This class has four different methods, onCreate() is where we create our table with a simple CREATE SQL statement. We are not implementing onUpgrade(). addRecord() is responsible to enter new registration data into the table using SQL INSERT statement and getRecord() is responsible to retrieve the data from the table and return a List with the user information.

Here is our Activity Class:

MainActivity.java


package com.example.sqlliteandloginform;


import java.util.List;


import android.os.Bundle;
import android.app.Activity;
import android.app.Dialog;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;


public class MainActivity extends Activity {
DatabaseConnector dConnector = new DatabaseConnector(this);
Form form = new Form();
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);


final EditText firstName = (EditText) findViewById(R.id.firstName);
final EditText lastName = (EditText) findViewById(R.id.lastName);
final EditText email = (EditText) findViewById(R.id.email);
final EditText phoneNumber = (EditText) findViewById(R.id.phone);
final EditText username = (EditText) findViewById(R.id.userName);
final EditText password = (EditText) findViewById(R.id.password);

final Button register = (Button)findViewById(R.id.register);
final Button login = (Button) findViewById(R.id.login);
final Button submit = (Button) findViewById(R.id.submit);

firstName.setVisibility(View.GONE);
lastName.setVisibility(View.GONE);
email.setVisibility(View.GONE);
phoneNumber.setVisibility(View.GONE);
submit.setVisibility(View.GONE);
register.setOnClickListener(new OnClickListener(){


@Override
public void onClick(View view) {
firstName.setVisibility(View.VISIBLE);
lastName.setVisibility(View.VISIBLE);
email.setVisibility(View.VISIBLE);
phoneNumber.setVisibility(View.VISIBLE);
login.setVisibility(View.GONE);
register.setVisibility(View.GONE);
submit.setVisibility(View.VISIBLE);
}

});
login.setOnClickListener(new OnClickListener(){


@Override
public void onClick(View view) {
try {
List<String> userRecord = dConnector.getRecord(username.getEditableText().toString(), password.getEditableText().toString());
Log.d("Login onClick()", String.valueOf(userRecord.size()));
Dialog dialog = new Dialog(view.getContext());
TextView tv = new TextView(getApplicationContext());
tv.setText("First Name : " + userRecord.get(0) +" \n" + "Last Name : " + userRecord.get(1) + "\n" + "Email : " + userRecord.get(2)
+ "\n" + "Phone : " + userRecord.get(3));
dialog.setContentView(tv);
dialog.setTitle("User Details");
dialog.show();
dialog.setCanceledOnTouchOutside(true);
}

catch (Exception e) {
Toast.makeText(getApplicationContext(), "Login Failed, Record Not Found", Toast.LENGTH_SHORT).show();
e.printStackTrace();
}

}

});
submit.setOnClickListener(new OnClickListener(){


@Override
public void onClick(View view) {
form.setFirstName(firstName.getEditableText().toString());
form.setLastName(lastName.getEditableText().toString());
form.setEmail(email.getEditableText().toString());
form.setPhoneNumber(phoneNumber.getEditableText().toString());
form.setUsername(username.getEditableText().toString());
form.setPassword(password.getEditableText().toString());
dConnector.addRecord(form.getFirstName(), form.getLastName(), form.getEmail(), form.getPhoneNumber(), form.getUsername(), form.getPassword());
Toast.makeText(getApplicationContext(), "Record Added!", Toast.LENGTH_SHORT).show();
firstName.setVisibility(View.GONE);
lastName.setVisibility(View.GONE);
email.setVisibility(View.GONE);
phoneNumber.setVisibility(View.GONE);
submit.setVisibility(View.GONE);
login.setVisibility(View.VISIBLE);
register.setVisibility(View.VISIBLE);
}

});

}


}


This class is responsible for passing data from different EditText fields to the Form and the DatabaseController to perform SQL operations. Complete source code for this project is available here.



1 comment:

  1. Android – Creating Registration Page using SQLITE Database

    http://apptronix.net/registration-page-sqlite/

    ReplyDelete