Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

 (INNER) JOIN : Returns records that have matching values in both tables
 LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right
table
 RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left
table
 FULL (OUTER) JOIN : Returns all records when there is a match in either left or right table

Join 3 or more tables

1. Using Joins

SELECT e_name, d_name, man, sal

FROM employees e
INNER JOIN details d ON e.e_id = d.e_id
INNER JOIN department dp ON dp.d_id = d.d_id;

2. Using Parent-Child Relationship

SELECT e_name, d_name, man, sal

FROM employees e, details d, department dp
WHERE e.e_id = d.e_id AND d.d_id = dp.d_id;
Database error handling and null value check :
try
{
try {
[Link]();

String start =[Link]([Link](KEY_START));

if([Link](KEY_END) != -1)
{
String end = [Link]([Link](KEY_END));
[Link](end);
}

[Link](currDate);
[Link](start);
}
catch (NullPointerException ne){ [Link](); }
catch (ArrayIndexOutOfBoundsExceptionae){ [Link](); }
catch (CursorIndexOutOfBoundsException ci) { [Link](); }
catch (Exception e){ [Link](); }
}
catch([Link])
{}

Database Transactions

You have to set begin transaction if you have multiple records to insert into database or if you have to rollback data from other
table if there is a problem in inserting data in one of the database table.

For example

You have two tables

1. A
2. B

Now you want to insert data in these two tables but you will have to rollback transaction if you will get any error at the time of
inserting data in the tables.

Now you have successfully insert data in table A and now you are trying to insert data in the table [Link] if you get error at the
time of inserting data in the table B then you have to delete relevant data from table A that means you have to rollback the
transaction.

How you can use database transaction in Android

1. If you want to start the transaction there is a method beginTransaction()

2. If you want to commit the transaction there is a method setTransactionSuccessful() which will commit the values in
the database
3. If you had start the transaction you need to close the transaction so there is a method endTransaction() which will end
your database transaction

Now there are two main points

1. If you want to set transaction successful you need to write setTransactionSuccessful() and then endTransaction()
after beginTransaction()
2. If you want to rollback your transaction then you need to endTransaction() without committing the transaction by
setTransactionSuccessful().
You can get detailed information about the SQLite database transaction from here

In your case

You can call your saveCustomer() function in try and catch blocks

[Link]();

try {
saveCustomer();
[Link]();
} catch {
//Error in between database transaction
} finally {
[Link]();
}

If there is an exception in the ... in your above code, the code you already have will automatically roll back the transaction. The
finally {} block is executed after the catch() {} block.
DROP TABLE IF EXISTS Products;

DELETE FROM products WHERE id IN (1, 2, 3)

DELETE FROM products WHERE id NOT IN (1, 2, 3)

SELECT * FROM EMP WHERE SALARY LIKE '200%'

SELECT NAME FROM EMP WHERE NAME LIKE '%AM' OR NAME LIKE 'RA%'

SELECT NAME FROM EMP WHERE SALARY LIKE '2_3'

SELECT * FROM EMP WHERE ADDRESS IS NULL

SELECT * FROM EMP WHERE ADDRESS IS NOT NULL

--------------------------------------------------------------------------------------

SELECT * FROM EMP LIMIT 6;

OUTPUT :
ID NAME AGE ADDRESS SALARY
INT TEXT INT CHAR(50) REAL
----------------------------------------------------
1 ram 24 janakpuri 30000.0
2 kamal 30 madanpuri 3000.0
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ravi 14 cant 100000.0
6 mohan 33 laxi nagar 35000.0

SELECT * FROM EMP LIMIT 3 OFFSET 2;

OUTPUT :
========

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
------------------------------------------------------
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ravi 14 cant 100000.0

SELECT * FROM EMP ORDER BY SALARY ASC;

NAME SALARY
TEXT REAL
-------------------------------------------------------------
kamal 3000.0
annu 8000.0
kavi 10000.0
dev 23000.0
ruchi 27000.0
ram 30000.0
mohan 35000.0
madan 45000.0
kabeer 50000.0
ravi 100000.0
-------------------------------------------------------------
SELECT * FROM EMP ORDER BY ADDRESS, SALARY ASC;

OUTPUT :
========

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
--------------------------------------------------------
5 ravi 14 cant 100000.0
2 kamal 30 janakpuri 3000.0
7 kavi 44 janakpuri 10000.0
1 ram 24 janakpuri 30000.0
3 madan 27 kalyanpuri 45000.0
6 mohan 33 laxi nagar 35000.0
4 kabeer 20 palam 50000.0
10 annu 37 tilak nagar 8000.0
8 dev 54 uttam nagar 23000.0
9 ruchi 64 vikas puri 27000.0
--------------------------------------------------------

SELECT NAME,SALARY,ADDRESS FROM EMP;

SELECT NAME, SUM(SALARY) FROM EMP GROUP BY NAME;

OUTPUT :
========

NAME SALARY ADDRESS

TEXT REAL CHAR(50)
--------------------------------------------------------------------------
kamal 30000.0 janakpuri
kamal 3000.0 janakpuri
madan 45000.0 kalyanpuri
kabeer 50000.0 palam
ravi 100000.0 cant
mohan 35000.0 laxi nagar
kamal 10000.0 janakpuri
dev 23000.0 uttam nagar
ruchi 27000.0 vikas puri
annu 8000.0 tilak nagar
---------------------------------------------------------------------------

NAME SUM(SALARY)
TEXT float
-----------------------------------------------------------------
annu 8000.0
dev 23000.0
kabeer 50000.0
kamal 43000.0
madan 45000.0
mohan 35000.0
ravi 100000.0
ruchi 27000.0
-----------------------------------------------------------------

SELECT NAME, SUM(SALARY) FROM EMP GROUP BY NAME ORDER BY NAME;

OUTPUT :
========

NAME SUM(SALARY)
TEXT float
-----------------------------------------------------------------
annu 8000.0
dev 23000.0
kabeer 50000.0
kamal 43000.0
madan 45000.0
mohan 35000.0
ravi 100000.0
ruchi 27000.0
-----------------------------------------------------------------
HAVING Clause

The HAVING clause enables you to specify conditions that filter which group results appear in the final results.

SELECT * FROM emp;

SELECT * FROM emp GROUP BY name HAVING count(name) > 1;

OUTPUT :
========

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
------------------------------------------------------------
1 kamal 24 janakpuri 30000.0
2 kamal 30 janakpuri 3000.0
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ravi 14 cant 100000.0
6 madan 33 laxi nagar 35000.0
7 kamal 44 janakpuri 10000.0
8 dev 54 uttam nagar 23000.0
9 ruchi 64 vikas puri 27000.0
10 annu 37 tilak nagar 8000.0
------------------------------------------------------------

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
------------------------------------------------------------
7 kamal 44 janakpuri 10000.0
6 madan 33 laxi nagar 35000.0
------------------------------------------------------------

DISTINCT Keyword

The SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

SELECT * FROM emp;

SELECT DISTINCT name FROM emp;

output :
========

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
----------------------------------------------------------------
1 kamal 24 janakpuri 30000.0
2 kamal 30 janakpuri 3000.0
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ravi 14 cant 100000.0
6 madan 33 laxi nagar 35000.0
7 kamal 44 janakpuri 10000.0
8 dev 54 uttam nagar 23000.0
9 ruchi 64 vikas puri 27000.0
10 annu 37 tilak nagar 8000.0
----------------------------------------------------------------

NAME
TEXT
----------------------------------------------------
kamal
madan
kabeer
ravi
dev
ruchi
annu
----------------------------------------------------
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
RATE REAL CHECK(RATE > 0)
);

CROSS JOIN

INNER JOIN

select * from emp;

select * from dep;

SELECT [Link],[Link],[Link],dep.emp_id
FROM emp INNER JOIN dep
ON [Link] = dep.emp_id;

OUTPUT :
========

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
----------------------------------------------------
1 ram 24 janakpuri 30000.0
2 kamal 30 madanpuri 3000.0
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ravi 14 cant 100000.0
6 mohan 33 laxi nagar 35000.0
7 kavi 44 rajeev chowk 10000.0
8 dev 54 uttam nagar 23000.0
9 ruchi 64 vikas puri 27000.0
10 annu 37 tilak nagar 8000.0
-----------------------------------------------------

ID DEPT EMP_ID
INT TEXT INTEGER
-----------------------------------
1 BILLING 3
2 SALES 1
3 BILLING 2
4 BILLING 5
5 MARKETING 6
6 PRODUCTION 4
7 BILLING 7
8 PRODUCTION 9
9 PRODUCTION 8
10 PRODUCTION 10
-----------------------------------

NAME SALARY DEPT EMP_ID

TEXT REAL TEXT INTEGER
-----------------------------------------------------
madan 45000.0 BILLING 3
ram 30000.0 SALES 1
kamal 3000.0 BILLING 2
ravi 100000.0 BILLING 5
mohan 35000.0 MARKETING 6
kabeer 50000.0 PRODUCTION 4
kavi 10000.0 BILLING 7
ruchi 27000.0 PRODUCTION 9
dev 23000.0 PRODUCTION 8
annu 8000.0 PRODUCTION 10
-----------------------------------------------------
OUTER JOIN (doesnot work as expected in SQLite)

DELETE FROM EMP;

DELETE FROM EMP WHERE ID = 7;

UPDATE EMP SET ADDRESS = 'New Delhi' WHERE ID = 6;

Find the second and third highest salaries in EMP table:

SELECT SALARY FROM EMP ORDER BY SALARY;

SALARY
REAL
---------
3000.0
8000.0
10000.0
23000.0
27000.0
30000.0
35000.0
45000.0
50000.0
100000.0
---------

SELECT MAX(Salary) FROM EMP WHERE Salary NOT IN (SELECT MAX(Salary) FROM EMP );

MAX(Salary)
float
-------------
50000.0 (Second maximum salary)
-------------

SELECT MAX( SALARY ) FROM EMP WHERE SALARY < ( SELECT MAX( SALARY ) FROM EMP );

MAX( SALARY )
float
---------------
50000.0 (Second maximum salary)
---------------

SELECT MAX( SALARY ) FROM EMP WHERE SALARY < ( SELECT MAX( SALARY ) FROM EMP WHERE SALARY < ( SELECT
MAX( SALARY ) FROM EMP ));

MAX( SALARY )
float
---------------
45000.0 (Third maximum salary)
---------------

You can get n-th salary by arranging the table in desc order, then pick the n-th record.
How does one eliminate duplicates rows from a table using DELETE query?

select * from emp;

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
---------------------------------------------------------------
1 ram 24 janakpuri 30000.0
2 kamal 30 madanpuri 3000.0
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ram 14 janakpuri 30000.0
6 mohan 33 laxi nagar 35000.0
7 kavi 44 rajeev chowk 10000.0
8 dev 54 uttam nagar 23000.0
9 ruchi 64 vikas puri 27000.0
10 annu 37 tilak nagar 8000.0
---------------------------------------------------------------

SELECT MAX(id) FROM emp GROUP BY name;

MAX(id)
integer
--------------
10
8
4
2
7
3
6
5
9
--------------

DELETE FROM emp WHERE id NOT IN (SELECT MAX(id) FROM emp GROUP BY name);

select * from emp;

ID NAME AGE ADDRESS SALARY

INT TEXT INT CHAR(50) REAL
-------------------------------------------------------------
2 kamal 30 madanpuri 3000.0
3 madan 27 kalyanpuri 45000.0
4 kabeer 20 palam 50000.0
5 ram 14 janakpuri 30000.0
6 mohan 33 laxi nagar 35000.0
7 kavi 44 rajeev chowk 10000.0
8 dev 54 uttam nagar 23000.0
9 ruchi 64 vikas puri 27000.0
10 annu 37 tilak nagar 8000.0
------------------------------------------------------------

How do I rename a column in a SQLite database table?

Say you have a table and need to rename "colb" to "col_b":

First you rename the old table:

ALTER TABLE orig_table_name RENAME TO tmp_table_name;

Then create the new table, based on the old table but with the updated column name:

CREATE TABLE orig_table_name (

col_a INT
, col_b INT
);
Then copy the contents across from the original table.

INSERT INTO orig_table_name(col_a, col_b)

SELECT col_a, colb
FROM tmp_table_name;
Lastly, drop the old table.

DROP TABLE tmp_table_name;

Wrapping all this in a BEGIN TRANSACTION; and COMMIT; is also probably a good idea.
ALTER TABLE emp RENAME to employee

CREATE TABLE "emp" (

`id` INTEGER,
`name` TEXT,
`salary` INTEGER,
'commission' INTEGER
)

INSERT INTO emp('id', 'name', 'salary') SELECT id, name, salary FROM employee

DROP TABLE employee

SELECT [Link], [Link], [Link] "department name", [Link]

from emp, dept
where [Link] = dept.emp_id
Indexing

Following is an example where we will create an index in COMPANY table for salary column −

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

list down all the indices available in COMPANY table using .indicescommand as follows −

sqlite> .indices COMPANY

This will produce the following result, where sqlite_autoindex_COMPANY_1 is an implicit index which got created when the table
itself was created.

salary_index
sqlite_autoindex_COMPANY_1

List down all the indexes database wide as follows −

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

Now selecting the data from table COMPANY you can use INDEXED BY clause as follows −

sqlite> SELECT * FROM COMPANY WHERE salary > 5000 INDEXED BY salary_index;

This will produce the following result.

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

The DROP INDEX Command

An index can be dropped using SQLite DROP command. Care should be taken when dropping an index because performance may
be slowed or improved.
Following is the basic syntax is as follows −

DROP INDEX index_name;

You can use the following statement to delete previously created index.

sqlite> DROP INDEX salary_index;

SQLite Database

SQLite is an Open Source database comes as a build in database in Android OS. The APIs you'll need to use a database on Android are available in the
[Link] package.

First, create a model class corresponding to row to be stored in the table.

Creating a Model Class

public class Contact

{
// private variables
int _id;
String _name;
String _phone_number;

// Empty constructor
public Contact(){}

// constructor
public Contact(int id, String name, String _phone_number)
{
this._id = id;
this._name = name;
this._phone_number = _phone_number;
}

// constructor
public Contact(String name, String _phone_number)
{
this._name = name;
this._phone_number = _phone_number;
}

// getting ID
public int getID()
{
return this._id;
}

// setting id
public void setID(int id)
{
this._id = id;
}

...
}

Defining the Data Helper

The data handler will be implemented by subclassing the SQLiteOpenHelper class and overriding the onCreate() and onUpgrade() methods. Since the databse handler
will be required to add, query and delete data on behalf of the activity component, corresponding methods will also need to be added to the class.

package [Link];

import [Link];
import [Link];

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

public class DatabaseHelper extends SQLiteOpenHelper

{
 // All Static variables
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "contactsDB";
private static final String TABLE_NAME = "contacts";

// Contacts Table Columns names

private static final String _ID = "id";
private static final String NAME = "name";
private static final String PH_NO = "phone_number";

SQLiteDatabase db;

public DatabaseHelper (Context context)

{
super(context, DATABASE_NAME, null, DATABASE_VERSION);

db = [Link]();
}

@Override
public void onCreate(SQLiteDatabase db)
{
[Link]("CREATE TABLE " + TABLE_NAME + "( id INTEGER PRIMARY KEY, name TEXT, phone_number TEXT )");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
Log.d("MSNEGI","onUpgrade called");
[Link]("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}

void addContact(Contact contact)

{
ContentValues values = new ContentValues();
[Link](NAME, [Link]()); // Contact Name
[Link](PH_NO, [Link]()); // Contact Phone

// Inserting Row
[Link](TABLE_NAME, null, values);

Log.d("MSNEGI","Record added...");
}

// insert data using transaction and prepared statement

public void insertFast(String name, String ph_no)
{
// you can use INSERT only
String sql = "INSERT INTO " + TABLE_CONTACTS + " ( " + NAME + ", " + PH_NO + " ) VALUES ( ?, ? )";

/*
* According to the docs
* [Link]
* /sqlite/[Link] Writers should use
* beginTransactionNonExclusive() or
* beginTransactionWithListenerNonExclusive(SQLiteTransactionListener)
* to start a transaction. Non-exclusive mode allows database file to be
* in readable by other threads executing queries.
*/

[Link]();
// [Link]();

SQLiteStatement stmt = [Link](sql);

[Link](1, name);
[Link](2, ph_no);

[Link]();
[Link]();
 [Link]();
[Link]();
}

Contact getContact(int id)

{
Cursor cursor = [Link](TABLE_NAME, //table name
new String[]{ _ID, NAME, PH_NO }, //column names
_ID + "= ?" , //selection by “id = ?”
new String[]{ [Link](id) }, //selection args
null, null, null, null); //group by, having, order by, limit

if (cursor != null) [Link]();

Contact contact = new Contact([Link]([Link](0)), [Link](1), [Link](2));

[Link]();

return contact;
}

public List<Contact> getAllContacts()

{
List<Contact> contactList = new ArrayList<Contact>();

Cursor cursor = [Link]("SELECT * FROM " + TABLE_NAME , null);

// looping through all rows and adding to list

if ([Link]())
{
do
{
Contact contact = new Contact();
[Link]([Link]([Link](0)));
[Link]([Link](1));
[Link]([Link](2));

// Adding contact to list

[Link](contact);

Log.d("MSNEGI",[Link](0) + " " + [Link](1) + " " + [Link](2));

}
while ([Link]());
}

[Link]()

// return contact list

return contactList;
}

// Updating single contact

public int updateContact(Contact contact,String ph_no)
{
Log.d("MSNEGI","updateContact : " + [Link]() + " " + [Link]());

ContentValues values = new ContentValues();

[Link](NAME, [Link]());
[Link](PH_NO, ph_no);

// updating row

[Link]();

return [Link](TABLE_NAME, values, _ID + " = ?", new String[]{ [Link]([Link]()) });
}

// Deleting single contact

public void deleteContact(Contact contact)
{
Log.d("MSNEGI","Deleting :" + [Link]());
 [Link](TABLE_NAME, _ID + " = ?", new String[]{ [Link]([Link]()) });
}
}

The onCreate() method needs to be implemented so that the database tables are created when the database is first created. This involves constructing a SQL CREATE
statement containing instructions to create a new table(s) with the appropriate columns and then passing that to the execSQL() method of the SQLiteDatabase object as
an argument to onCreate().

The onUpgrade() method is called when the handler is invoked with a greater database version number from the one previously used. The exact steps to be performed
in this instance will be application specific, so for the purposes of this example we will simply remove the old database and create a new one.

Then, other methods are required to add, read and delete records from the table(s).

Using DatabaseHelper

To access the database, create an instance of DatabaseHelper . Creating an instance of DatabaseHelper will create an instance of specified database and tables inside
the database if not already exist. Then, call the addContact() method to add records to the table.

DatabaseHelper db = new DatabaseHelper (this);

// Inserting Contacts
Log.d("Insert: ", "Inserting ..");
[Link](new Contact("Ram", "9876543211"));
[Link](new Contact("Raj", "9734674433"));
[Link](new Contact("Amisha", "4356789032"));
[Link](new Contact("kapil", "3456789023"));

You can list the records -

// Reading all contacts

Log.d("Reading: ", "Reading all contacts..");
List<Contact> contacts = [Link]();

for (Contact cn : contacts)

{
String log = "Id: " + [Link]() + " ,Name: " + [Link]() + " ,Phone: " + [Link]();
Log.d("MSNEGI: ", log);
}

Another way -

for (int i = 0; i < [Link](); i++)

{
String log = "Id: " + [Link](i).getID() + " ,Name: " + [Link](i).getName()
+ " ,Phone: " + [Link](i).getPhoneNumber();
Log.d("MSNEGI: ", log);
}

You can display a single record –

Log.d("MSNEGI","==" + [Link](18).getID() + " " + [Link](18).getName() + " " +

[Link](18).getPhoneNumber());

You can update a record -

for (Contact cn : contacts)

{
if([Link]() == 18)
{
[Link](cn,"9988776655");
}
}
Every time you add,update and delete a record you will need to re-read the records to reflect the changes in displayed list.

contacts = [Link]();

for (int i = 0; i < [Link](); i++)

{
String log = "Id: " + [Link](i).getID() + " ,Name: " + [Link](i).getName() +
" ,Phone: " + [Link](i).getPhoneNumber();
Log.d("MSNEGI: ", log);
}

You may delete a record –

//deleteContact
for (Contact cn : contacts)
{
if([Link]() == 18)
{
[Link](cn);
}
}

You may use another way to insert records which is several times faster then normal insert method.

[Link]("johan","1234567890");

contacts = [Link]();

for (int i = 0; i < [Link](); i++)

{
String log = "Id: " + [Link](i).getID() + " ,Name: " + [Link](i).getName() +
" ,Phone: " + [Link](i).getPhoneNumber();
Log.d("MSNEGI: ", log);
}
[Link]

dependencies {
compile '[Link]:appcompat-v7:26.+'
compile '[Link]:constraint-layout:1.0.2'
compile files('libs/[Link]')
}

activity_main.xml

<RelativeLayout xmlns:android="[Link]
android:id="@+id/activity_main_linear_layout"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal" >

<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
android:padding="5dp"
android:layout_marginTop="20dp"
android:text="Choose city of departure:"
android:textSize="16sp" />

<Spinner
android:id="@+id/spinner1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBottom="@+id/textView1"
android:layout_alignParentTop="true"
android:layout_toRightOf="@id/textView1"

/>

<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@id/textView1"
android:padding="5dp"
android:text="The rally runs until December 13, 2014"
android:textSize="16sp" />
 <RelativeLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/relativelistlayout"
android:layout_below="@id/textView2" >

<ListView
android:id="@+id/trainListView"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:paddingTop="10dp" >
</ListView>
<TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="10dp"
android:layout_below="@id/trainListView"
android:text="More information at [Link] />
</RelativeLayout>

</RelativeLayout>

Train_item_layout.xml

<?xml version="1.0" encoding="utf-8"?>

<RelativeLayout xmlns:android="[Link]
android:layout_width="match_parent" android:layout_height="match_parent"
android:orientation="horizontal">
<TextView
android:id="@+id/city_text_view"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:padding="5dp"
/>

<TextView
android:id="@+id/time_text_view"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="5dp"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true"
/>

</RelativeLayout>

[Link]

<?xml version="1.0" encoding="utf-8"?>

<resources>

<string name="app_name">Red vožnje</string>

<string name="action_settings">Settings</string>
<string name="hello_world">Hello world!</string>
<string-array name="gradovi">
<item>Niksic</item>
<item>Podgorica</item>
<item>Bijelo Polje</item>
<item>Bar</item>
</string-array>

</resources>

[Link]
package [Link];

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

import [Link];

public class MainActivity extends Activity implements OnItemSelectedListener {

private ListView trainListView;

private MyDatabase db;
private AdapterForTrainDepartures adapter;
private ArrayList<TrainItem> values;

@Override
protected void onCreate(Bundle savedInstanceState) {

[Link](savedInstanceState);
setContentView([Link].activity_main);

trainListView = (ListView) findViewById([Link]);

db = new MyDatabase(getApplicationContext());
values = new ArrayList<TrainItem>();
adapter = new AdapterForTrainDepartures(this, values);

Spinner spinner = (Spinner) findViewById([Link].spinner1);

ArrayAdapter<CharSequence> spinnerAdapter = [Link](this, [Link],
[Link].simple_spinner_item);

[Link]([Link].simple_dropdown_item_1line);

[Link](adapter);
[Link](spinnerAdapter);

[Link](this);

@Override
protected void onDestroy() {
[Link]();
[Link]();
}

@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id)
{
switch (position) {
case 0:
[Link]();
[Link]([Link]());
[Link]();
break;
case 1:
[Link]();
[Link]([Link]());
[Link]();
break;
case 2:
[Link]();
[Link]([Link]());
[Link]();
break;
case 3:
[Link]();
[Link]([Link]());
[Link]();
break;

}
}

@Override
public void onNothingSelected(AdapterView<?> arg0) {

}
}

[Link]

package [Link];

public class TrainItem {

int id;
String destination;
String timeOfDeparture;

public TrainItem(){

public TrainItem(int id, String destination, String timeOfDeparture){

[Link] = id;
[Link] = destination;
[Link] = timeOfDeparture;
}

[Link]

package [Link];

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

import [Link];

public class AdapterForTrainDepartures extends ArrayAdapter<TrainItem> {

private Context context;

private ArrayList<TrainItem> values;

public AdapterForTrainDepartures(Context context, ArrayList<TrainItem> values) {

super(context, [Link].train_item_layout, values);
[Link] = values;
[Link] = context;
}

@Override
public int getCount() {
return [Link]();
}

@Override
public View getView(int position, View convertView, ViewGroup parent) {
LayoutInflater inflater = (LayoutInflater) [Link](Context.LAYOUT_INFLATER_SERVICE);

View view = [Link]([Link].train_item_layout, parent, false);

TrainItem item = [Link](position);

//TODO OPTIMIZE WITH STATIC VIEW HOLDER AND COMPARE PERFORMANCE

TextView cityTextView = (TextView) [Link]([Link].city_text_view);
TextView timeOfDepartureTextView = (TextView) [Link]([Link].time_text_view);

[Link]([Link]);
[Link]([Link]);

return view;
}
}

[Link]
package [Link];

import [Link];
import [Link];
import [Link];
import [Link];

import [Link];

import [Link];

public class MyDatabase extends SQLiteAssetHelper {

private static final String DATABASE_NAME = "vozovi";

private static final int DATABASE_VERSION = 1;

public MyDatabase(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

public ArrayList<TrainItem> getBar() {

ArrayList<TrainItem> values = new ArrayList<TrainItem>();

SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

String[] sqlSelect = {"_id", "polazakiz", "vrijemepolaska"};

String sqlTables = "bar";

[Link](sqlTables);
Cursor cursor = [Link](db, sqlSelect, null, null, null, null, null);

if ([Link]()) {
while ([Link]() == false) {
TrainItem item = new TrainItem([Link]([Link](0)),
[Link](1), [Link](2));
[Link](item);
[Link]();
}
}
[Link]();
return values;
}

public ArrayList<TrainItem> getPodgorica() {

ArrayList<TrainItem> values = new ArrayList<TrainItem>();

SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

String[] sqlSelect = {"_id", "polazakiz", "vrijemepolaska"};

String sqlTables = "podgorica";

[Link](sqlTables);
Cursor cursor = [Link](db, sqlSelect, null, null, null, null, null);

if ([Link]()) {
while ([Link]() == false) {
TrainItem item = new TrainItem([Link]([Link](0)),
[Link](1), [Link](2));
[Link](item);
[Link]();
}
}
[Link]();
return values;
}

public ArrayList<TrainItem> getNiksic() {

ArrayList<TrainItem> values = new ArrayList<TrainItem>();

SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

String[] sqlSelect = {"_id", "polazakiz", "vrijemepolaska"};

String sqlTables = "niksic";

[Link](sqlTables);
Cursor cursor = [Link](db, sqlSelect, null, null, null, null, null);
 if ([Link]()) {
while ([Link]() == false) {
TrainItem item = new TrainItem([Link]([Link](0)),
[Link](1), [Link](2));
[Link](item);
[Link]();
}
}
[Link]();
return values;
}

public ArrayList<TrainItem> getBijeloPolje() {

ArrayList<TrainItem> values = new ArrayList<TrainItem>();

SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

String[] sqlSelect = {"_id", "polazakiz", "vrijemepolaska"};

String sqlTables = "bijelopolje";

[Link](sqlTables);
Cursor cursor = [Link](db, sqlSelect, null, null, null, null, null);

if ([Link]()) {
while ([Link]() == false) {
TrainItem item = new TrainItem([Link]([Link](0)),
[Link](1), [Link](2));
[Link](item);
[Link]();
}
}
[Link]();
return values;
}
}