Setting up a linux MySql backend for your Android Application

So you want to setup a database up for your android application, doesn’t sound too hard right? Well I had some trouble so I thought I’d share a straightforward way to do it.

Note(1): If you ever get a permission error when trying to edit a file, enter the command “sudo -i” (no quotes)

Note(2): Be careful copying any of my code with any quotes (‘ or “) as it may cause errors in the code in Eclipse or Terminal.

Step 1.Installing MySql

Start off by opening terminal on your linux machine and entering the command:

———————————————
sudo apt-get install mysql-server
———————————————

This will install a package and ask you to create a password for root access into your MySql server.

If you would like to set up your database now follow step 2, otherwise go to step 3.

Step 2.Creating a Database

In terminal enter:

———————————————
mysql -u root -p
———————————————

-u = username, -p = password. You will now be prompted for the password you setup in Step 1.

You should have a prompt like ‘mysql>’ you can now set up your database here is a quick example:

———————————————

CREATE DATABASE android;

CREATE TABLE android.user(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100));

INSERT INTO android.user VALUES(1, ‘myData’);

SELECT * FROM android.user;

———————————————

The final statement should show you your data. If you want to do more stuff with SQL look it up on google.

Step 3. Setting up MySql

We are going to need to edit the file /etc/sql/my.conf to (a) allow the server to be accessed from anywhere, and (b) allow jdbc(More on this later) to execute queries on your database. The etc directory is in your File System, if you don’t know how to access it simply enter

cd ../../

The etc folder should be there. Enter

cd etc

Then there should be a folder called sql or mysql. You can use the command ‘ls’ to find out.

cd mysql (or cd sql)

then open my.conf with any text editor. I personally like vim, but someone unfamiliar with vim should probably use something like gedit. e.g.

gedit my.cnf

Now move to move to what is line 53 on my computer which says:

bind-address        = 127.0.0.1

And replace it with

#bind-address        = 127.0.0.1

To allow connections to your server from anywhere.

Now for (b) allowing JDBC to execute queries on your database

We need to add two lines in the [mysqld] section of my.cnf (same file), right below ‘#bind-address’ should be fine. Enter the lines

collation_server=utf8_general_ci
character_set_server=utf8

The need for these lines has to do with Unicode Transformation Format, you can look it up if you’d like. Now save and close the file.

You are now ready to startup the server. So enter:

sudo /etc/init.d/msql start

It should reply saying ‘Starting MySQL database server msqld [ OK ]‘

4. Querying the Database from Java

So you’ve got your server running, and your database setup, now all you have to do is access it from Java within your Android project.

First we need to get the jar file required to talk to a server. So enter in terminal:

sudo apt-get install libmysql-java

This will download into /usr/share/java/mysql-connector-java.jar

Next we simply add this jar to the classpath. In Eclipse Right Click in the Project>BuildPath>Configure Build Path. Then a window should popup, click the libraries tab at the top, click add external jar and navigate to File System/usr/share/java/mysql-connector-java.jar

Alright we are all set up now all we need is the java code, here it is:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DB {

private static final String url = “jdbc:mysql://localhost/android”;
private static final String user = “root”;
private static final String password = “MySql Password”;

public static void main(String args[]) {

try {
Class.forName(“com.mysql.jdbc.Driver”);
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(“Success”);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(“select * from User”);
while(rs.next()) {
System.out.println(“Id ” + rs.getInt(1));
System.out.println(“Data ” + rs.getString(2));
}

} catch (Exception e) {
e.printStackTrace();
}
}
}

JDBC, simpy put, allows you to connect to your server from java.

Press Ctrl+i(or right click the project source>format) to fix the tabbing in Eclipse. Replace ‘andriod’ in “jdbc:mysql://localhost/android” with whatever you called your database, “MySql Password” with our mysql password, and ‘user’ in “select * from user” with your table name. Then run it and you should see your data

5. Allowing external connections

Now we have to change our url in our java file, to match our hostname. On a unix based system simply enter:

hostname

To get your host name, and replace ‘localhost’ with that value. We also want to add the port for mysql(3306). so we will have something like

url = “jdbc:mysql://johnsmith-desktop:3306/android”;

Now we need to open the port on our computer. We can view open ports with the command

netstat -an | grep “LISTENING”

Now we use 2 commands to open the port.

ufw allow mysql

iptables -A INPUT -p tcp -d 0/0 -s 0/0 –dport 3306 -j ACCEPT

If your computer is connected through a router you will have to open the port there as well. You should be able to connect to your mysql server from anywhere now.

Hope that worked, feel free to ask questions in the comments.

About these ads

19 Responses to “Setting up a linux MySql backend for your Android Application”

  1. Sebastián Says:

    Excelent article!
    I’ve tried to run this code from Android 1.6 (HTC Dream) with no success, it keeps throwing exceptions when tries to create the connection, it seems like a bytecode problem. Did you try to run this code on a real device or an specific android version?

    The log output is:
    E/dalvikvm(29573): Could not find method javax.naming.Reference.get, referenced from method com.mysql.jdbc.ConnectionPropertiesImpl$ConnectionProperty.initializeFrom
    W/dalvikvm(29573): VFY: unable to resolve virtual method 6727: Ljavax/naming/Reference;.get (Ljava/lang/String;)Ljavax/naming/RefAddr;
    W/dalvikvm(29573): VFY: rejecting opcode 0x6e at 0×0004
    W/dalvikvm(29573): VFY: rejected Lcom/mysql/jdbc/ConnectionPropertiesImpl$ConnectionProperty;.initializeFrom (Ljavax/naming/Reference;)V
    W/dalvikvm(29573): Verifier rejected class Lcom/mysql/jdbc/ConnectionPropertiesImpl$ConnectionProperty;
    D/AndroidRuntime(29573): Shutting down VM
    W/dalvikvm(29573): threadid=3: thread exiting with uncaught exception (group=0x4001da28)
    E/AndroidRuntime(29573): Uncaught handler: thread main exiting due to uncaught exception
    E/AndroidRuntime(29573): java.lang.VerifyError: com.mysql.jdbc.ConnectionPropertiesImpl$ConnectionProperty

    • shariqmobin Says:

      I was running it on a real Android device fine, not sure what your issue is, I will look into it this weekend if its still problem.

      • Sebastián Says:

        No big deal, seems a driver version problem, I copied the mysql-connector-java.jar from the latest ubuntu build (10.04), perhaps the JDBC version of my android build differs from newer mysql drives. What distro were you using?

  2. Udi Says:

    Hi, thanks for the great article! do you mind adding link to the connector you are using? Or email me the link?

    Bye,
    Udi

  3. xiaolong_long Says:

    i want to ask you some questions.
    when i run my android project,it keeps throwing exceptions when tries to create connetion.That is my sourcecode:
    package org.ilandroid.guestbook;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;

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

    public class Test extends Activity {
    private static final String url = “jdbc:mysql://127.0.0.1/test”;
    private static final String user = “root”;
    private static final String password = “000000″;

    private static final String TAG =”Test”;

    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    Button btn = (Button) findViewById(R.id.btn);
    btn.setOnClickListener(new OnClickListener() {

    public void onClick(View v) {
    sqlCon();
    }
    });

    }

    private void mSetText(String str) {
    TextView txt = (TextView) findViewById(R.id.txt);
    txt.setText(str);
    }

    private void sqlCon() {
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    Log.i(TAG, “LOAD DRIVER SUCCESS.”);

    } catch (Exception e) {
    e.printStackTrace();
    }
    try {
    Connection conn = (Connection) DriverManager.getConnection(url,user,password); // 链接数据库
    Statement stmt = conn.createStatement();
    String sql = “select * from user”;// 查询user表语句
    ResultSet rs = stmt.executeQuery(sql);// 执行查询
    StringBuilder str = new StringBuilder();
    while (rs.next()) {
    str.append(rs.getString(1) + “\n”);
    }
    mSetText(str.toString());

    rs.close();
    stmt.close();
    conn.close();

    } catch (Exception e) {
    e.printStackTrace();
    }

    }

    }
    can you tell me the reason why i can not get the connection object.

  4. xiaolong_long Says:

    if i run the java project,i can do that.

  5. xiaolong_long Says:

    can you help me,thank you.
    and that are the exceptions:
    com.mysql.jdbc.CommunicationsException: Communications link failure

  6. Squale Says:

    Hi,

    Might you make available to download the mysql-connector-java.jar you used ?

    I tried with different versions, some just don’t work at all, I found one that was making the connection to the MySQL server, but was unable to handle queries.

    There’s many people struggling to make direct connection to MySQL from Android and you have the closest working solution from what I’ve seen so far.

    Thanks !

  7. greatmay12 Says:

    It’s work on ubuntu thank you very much!

  8. SQL user Says:

    @shariqmobin: thanks for this article!

    Took 2 days to figure out the solution for this problem:

    http://code.google.com/p/android/issues/detail?id=14699#makechanges

    Use oldest mySQL connector “mysql-connector-java-3.0.17-ga-bin.jar”!

    I compiled my Android project without any errors with up-to-date tools and as so far I need SQL it works well.

    That version is from 2005, size is 1/3 as later versions, but works as intended.

    • shariqmobin Says:

      thanks for providing that for everyone

      • Neil Warner Says:

        What android target and minimum sdk? It says 2.3 in the post. This info is 1 year old but I can’t see how that could matter…. In eclipse when I right-click on the project and select build properties, configure build path, libraries tab I see mysql-connector-java-3.0.17-ga-bin.jar as the first item, followed by Android 2.2 and Android dependencies. Then wheN i look at “Order And Export” it is there on the bottom (eg after Android 2.2 and dependencies and /src and /gen) and all looks well. Error appears to be with the “Class.forName(“com.mysql.jdbc.Driver”);”… (eg cant find class).. What am I missing? Sorry if I’m just being really dumb.

  9. marwa Says:

    This method is not useful because you should connect mysql into php server then android called php server but this method does not work forever ,I try that

    http://www.anddev.org/networking-database-problems-f29/connecting-to-mysql-database-t50063.html

    http://www.helloandroid.com/tutorials/connecting-mysql-database

  10. Tarun Says:

    hey… excellent article… but i want to ask that this code will run on android device or wat is needed for running this code on android device……. reply me asap plzzz

  11. Admiration Security Provides You With Global Security News. Admiration Security is designed to facilitate discussion on computer security related topics, create computer security awareness, and to provide the Internet's largest and most comprehensive data Says:

    Admiration Security Provides You With Global Security News. Admiration Security is designed to facilitate discussion on computer security related topics, create computer security awareness, and to provide the Internet’s largest and most comprehensiv…

    [...]Setting up a linux MySql backend for your Android Application « Hackers Anonymous[...]…

  12. TC Kimlik Sorgulama Says:

    TC Kimlik Sorgulama…

    [...]Setting up a linux MySql backend for your Android Application « Hackers Anonymous[...]…

  13. Create a basic Android barcode scanner | Adam Zwakk Says:

    [...] found this, it’s a basic tutorial for getting mysql data to echo out on an Android device. If you know [...]

  14. Neil Warner Says:

    in step3- a is ok in their my.cnf but b has nothing for it. Is this part still necessary at this point? (ie could it relate to old version of mysql or something). I looked at mysql’s documentation… I hate to modify their my.cnf and restart their server if it is not necessary.

    Do I need to do the utf8 part on an existing mysql installation, it’s not a new install, it’s not “mine”

    • shariqmobin Says:

      I’m not sure, I believe I had problems communicating with the server without the utf8 change, but it was quite awhile ago. Just give it a try, if it doesn’t work, I suspect you should look up the default collation for mysql and configure jdbc to use that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: