Database design
In our example we have created a very simple database with just two tables:
- Users: This table contains all the information about the users we are going to keep track in our application. It contains the username, password, their latitude, longitude and whether they are online or not.
- Friends: This table has just two columns which are both a foreign key from the username in the Users table. The first column means a person and the other column means who this user has added as a friend.
Database diagram
Users – 〈〉 – Friends
SQL Code
CREATE TABLE Users
(
username varchar(25) NOT NULL PRIMARY KEY,
password varchar(25) NOT NULL,
status varchar(25),
lat DECIMAL(18,9),
lon DECIMAL(18,9)
);
CREATE TABLE Friends
(
username varchar(25) NOT NULL,
friend varchar(25) NOT NULL,
PRIMARY KEY(username, friend),
FOREIGN KEY (username, friend) REFERENCES Users(username, username)
);
Hosting
In order to make the connection with the database easier, we recommend finding an online hosting that only offers database services. That won’t make us to have to configure ports and configurations inside the database. In our case we have used freemysqlhosting.net, it offers a mysql database working on the port 3306 as usual. It only offers a few Megabytes, but for our example it is more than enough.
DataBase Interaction
To connect to the database we are going to use a library that can be found in the repository. These is a small piece of code that has to be used to create an object that is going to interact with the database. This code can be seen in our repository in the DataBaseInteraction.java file. The library we are using has two important methods that we are going to highlight:
- ExecuteQuery: This function is used to make queries in SQL to our server. The SQL query has to be created in a String format and has to be passed as an argument for the function.
- ExecuteUpdate: This function is used to make all the database interaction that has something to do with changing the database itself, i.e: Inserts or Updates. It works the same way, we have to create a SQL Query inside a String and pass it as an argument to the function.
In order to create this object that is going to interact with the database we are going to need 3 different things to create it:
- Username of the database we are going to use
- Password of the database we are going to use
- URL of the database we are going to use. This URL follows this string structure: jdbc:mysql://[host][:port]/[database]
Threads in Android
When making a connection from Android we have to understand how Android works. Android runs on a main thread, so we cannot create a connection in the same thread because we can make the whole application to freeze or even stop depending on how this connection goes. That’s why we have to keep in mind that every time we want to make a connection in Android we have to create a new Thread following the Java documentation and then from there we will be able to execute the connection to the database.
An example of the code of how to run thread can be found here:
Thread t = new Thread(){
public void run(){
try {
//Here insert the code to be run in a different thread
} catch (IOException e) {
e.printStackTrace();
}
}
};
t.start();