Select Queries

By Fanxtea | 1 Januari 2015

Tutorial ini akan menunjukkan bagaimana user menggunakan select queries untuk memaparkan senarai data yang telah dimasukkan ke dalam pangkalan data. Tutorial ini adalah sambungan daripada tutorial sebelom ini iaitu tutorial insert queries.

Android Layout

Edit Text


 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
    android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:orientation="vertical"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">
 
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Name"
        android:id="@+id/textViewName" />
 
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/editTextName" />
 
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Address"
        android:id="@+id/textViewAddress" />
 
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/editTextAddress" />
 
    <Button
        style="?android:attr/buttonStyleSmall"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Add"
        android:id="@+id/btnAdd" />
  
 <ListView
        android:id="@+id/list"
        android:layout_height="wrap_content"
        android:layout_width="match_parent">
    
    
</LinearLayout>

Contact class


 package com.fanxtea.helloworld;

public class Contact {

    //private variables
    int _id;
    String _name;
    String _address;
     
    // Empty constructor
    public Contact(){
         
    }
    // constructor
    public Contact(int id, String name, String _address){
        this._id = id;
        this._name = name;
        this._address = _address;
    }
     
    // constructor
    public Contact(String name, String _address){
        this._name = name;
        this._address = _address;
    }
    // getting ID
    public int getID(){
        return this._id;
    }
     
    // setting id
    public void setID(int id){
        this._id = id;
    }
     
    // getting name
    public String getName(){
        return this._name;
    }
     
    // setting name
    public void setName(String name){
        this._name = name;
    }
     
    // getting phone number
    public String getAddress(){
        return this._address;
    }
     
    // setting phone number
    public void setAddress(String _address){
        this._address = _address;
    }
}


 package com.fanxtea.helloworld;

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

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;


public class MainActivity extends Activity implements View.OnClickListener {

    private EditText editTextName;
    private EditText editTextAdd;
    private Button btnAdd;    

 private String idName;
    private  ArrayAdapter adapter;
    
    private  ListView listView ;
 
    private SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        createDatabase();  

        editTextName = (EditText) findViewById(R.id.editTextName);
        editTextAdd = (EditText) findViewById(R.id.editTextAddress);

        btnAdd = (Button) findViewById(R.id.btnAdd);
  
  listView = (ListView) findViewById(R.id.list);
        
        btnAdd.setOnClickListener(this);  
  
  List contact = this.getAllContacts();   
        
        List your_array_list = new ArrayList();
        
        for (Contact cn : contact) {
            String log = "Id: "+cn.getID()+" ,Name: " + cn.getName() + " ,Address: " + cn.getAddress();
                // Writing Contacts to log
            Log.d("Name: ", log);
            your_array_list.add(cn.getID()+") "+cn.getName()+"\n---\n"+cn.getAddress());
            
        }
        
        adapter = new ArrayAdapter(this,
                android.R.layout.simple_list_item_1, your_array_list);
        listView.setAdapter(adapter);
        adapter.notifyDataSetChanged();
        
    }


    protected void createDatabase(){
        db=openOrCreateDatabase("PersonDB.db", Context.MODE_PRIVATE, null);
        db.execSQL("CREATE TABLE IF NOT EXISTS persons(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name VARCHAR,address VARCHAR);");
    }

    protected void insertIntoDB(){
        String name = editTextName.getText().toString().trim();
        String add = editTextAdd.getText().toString().trim();
        if(name.equals("") || add.equals("")){
            Toast.makeText(getApplicationContext(),"Please fill all fields", Toast.LENGTH_LONG).show();
            return;
        }

        String query = "INSERT INTO persons (name,address) VALUES('"+name+"', '"+add+"');";
        db.execSQL(query);
        Toast.makeText(getApplicationContext(),"Saved Successfully", Toast.LENGTH_LONG).show();
    } 
 
 protected List getAllContacts() {
        List contactList = new ArrayList();
        // Select All Query
        String selectQuery = "SELECT  * FROM persons";
 
        Cursor cursor = db.rawQuery(selectQuery, null);        
 
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Contact contact = new Contact();
                contact.setID(Integer.parseInt(cursor.getString(0)));
                contact.setName(cursor.getString(1));
                contact.setAddress(cursor.getString(2));
                // Adding contact to list
                contactList.add(contact);
            } while (cursor.moveToNext());
        }
 
        // return contact list
        return contactList;
    }

    @Override
    public void onClick(View v) {
        if(v == btnAdd){
            insertIntoDB();finish();
            startActivity(getIntent());
        }
    }
}

Demo

Run

Fan-X-Tea

FanXTea Pages

Thursday, 10 September 2015

Select Queries

Select Queries