Thursday, July 14, 2011

Android MySQL Connectivity (MySQL Connector)



Hope this method works. However its not recommended to use JDBC Connectivity for connections over wifi or 3g. So the best way to connect MySQL from android is to use JSON or XML Parsings

Step 1 Including MySQL Connector
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

Step 2 Coding Part

JDBC, simply allows you to connect to your server from java.

1:  import java.sql.Connection;  
2:  import java.sql.DriverManager;  
3:  import java.sql.ResultSet;  
4:  import java.sql.Statement;  
5:  public class DB {  
6:  private static final String url = “jdbc:mysql://localhost/android”;  
7:  private static final String user = “root”;  
8:  private static final String password = “MySql Password”;  
9:  public static void main(String args[]) {  
10:  try {  
11:  Class.forName(“com.mysql.jdbc.Driver”);  
12:  Connection con = DriverManager.getConnection(url, user, password);  
13:  Statement st = con.createStatement();  
14:  ResultSet rs = st.executeQuery(“select * from User”);  
15:  while(rs.next()) {  
16:  Log.v("DB", rs.getString(2) )  
17:  }  
18:  } catch (Exception e) {  
19:  }  
20:  }  
21:  }  

33 comments:

  1. Hi thanks for nicegoogle android application development information and i like it can we use it in asp.net ?

    ReplyDelete
    Replies
    1. I haven't used mysql connector to connect asp.net and mysql but according to my knowledge its possible to do this

      Delete
    2. yes.. I'ts possible.. I tried few months ago..

      Delete
  2. Thanks will try to use it and I commented on this route.

    ReplyDelete
  3. i tried to connect with the jdbc but i have some error.
    I used Eclipse Helios v2 with Android SDK, emulator 2.3.1 and MySql Connector/J 5.1.18. The project is compiled with no errors but on the emulator appeare the message

    "java.lang.ClassNotFoundException: com.mysql.jdbc.driver" and i tried to change the name with others but is the same.
    I add the jar in project with right click on project -> build path -> configure builde path -> library, add library, connectivity driver definition, specify driver name MySQL JDBC

    Driver (for example), add the jar of connector 5.1.18 located in C:\Users\user\Downloads\mysql-connector-java-5.1.18\mysql-connector-java-5.1.18.jar; in the properties specify

    param connectiot and others (mysql have the user and permission for the user with that password and database test exist):
    Connection Urls: jdbc:mysql://localhost:3306/
    Database Name: test
    Driver Class: com.mysql.jdbc.Driver
    user: root
    pass: root

    java code:
    package test.mysql;

    import android.app.Activity;
    import android.os.Bundle;
    import java.sql.DriverManager;
    import com.mysql.jdbc.Connection;
    import android.app.Activity;
    import android.app.AlertDialog;
    import android.content.DialogInterface;

    public class Main extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    /**Alert*/
    AlertDialog ad = new AlertDialog.Builder(this).create();
    ad.setCancelable(false); // This blocks the 'BACK' button

    ad.setButton("OK", new DialogInterface.OnClickListener() {
    @Override
    public void onClick(DialogInterface dialog, int which) {
    dialog.dismiss();
    }
    });

    Connection conn = null;
    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "test";

    //String driver = "com.mysql.jdbc.Driver";
    String driver = "MySQLJDBCDriver41"; //with this is the same
    //String driver = "com.mysql.jdbc.driver"; //with this is the same

    String userName = "root";
    String password = "daniele";
    try {
    Class.forName(driver).newInstance();
    conn = (Connection) DriverManager.getConnection(url+dbName,userName,password);
    ad.setMessage("Connected to the db");
    ad.show();
    conn.close();
    ad.setMessage("Disconnect to db");
    ad.show();

    } catch (Exception e) {
    ad.setMessage(e.toString());
    ad.show();
    e.printStackTrace();
    }

    }
    }

    ReplyDelete
    Replies
    1. I got an error communication link failure please help me...!!!

      Delete
  4. I have the same problem:

    This error started appearing to me when I updated the Android SDK to the latest version, that before the application worked perfectly.

    ReplyDelete
  5. I got the same problem in mac, but in windows its working well... any comments on this???

    ReplyDelete
    Replies
    1. please guide me or give me sample of it means specific program ...email id:arpithparikh@gmail.com....please...!!!

      Delete
  6. Hey friend, maybe the jre referenced in the project (must be 6). The same thing happened to me, I had two installed JREs (6 and 7), and my project pointed to the 7. Check this please and tell me if decided.

    Greetings.

    ReplyDelete
  7. i have connected android with mysql using php...

    data is moving to database is successfully.........

    i gave the data is "9999999999999"

    nameValuePairs.add(new BasicNameValuePair("f3","9999999999"));

    i was set length is 20 for that column..

    but data is storing always as "2147483647" in my table...

    Give some solutions for that..

    ReplyDelete
    Replies
    1. Check this link for the solution
      http://stackoverflow.com/questions/10718968/confusion-in-int-conversion-of-a-string-in-php

      Delete
    2. how to build path mysql-connector-java on eclipse,android connect to mysql server?

      Delete
  8. I copied your code and I has errors:

    java.lang.ExceptionInInitializerError
    com.mysql.jdbc.NonRegisteringDriver.parseURL(NonRegisteringDriver.java:729)
    06-11 09:25:13.939: E/AndroidRuntime(659): at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:296)
    java.sql.DriverManager.getConnection(DriverManager.java:180)
    java.sql.DriverManager.getConnection(DriverManager.java:214)

    how can I modyfied it to code works?

    ReplyDelete
  9. When you found a not-found error in Android you must assure you have the library added in the Android folder of your project preferences ;)

    ReplyDelete
  10. Sir ,
    Is it possible to connect JDBC connectivity through android emulator?I have been tried a lot but could get success.
    I have been tried following code :
    package com.da;

    import java.sql.*;

    import android.app.Activity;
    import android.os.Bundle;

    import android.widget.TextView;

    public class DatabaseActivity extends Activity {

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    TextView tv = new TextView(this);
    tv.setText(\”orcale Connect Example.\”);
    setContentView(tv);

    try {

    Class.forName(\”com.mysql.jdbc.Driver\”);
    Connection con =(Connection) DriverManager.getConnection(\”jdbc:mysql://127.0.0.1:3306/co\”, \”root\”, \”arpit\”);
    Statement st=(Statement) con.createStatement();
    st.executeUpdate(\”insert into new_table values(3,5);\”);
    tv.setText(\”Connected to the database \”);
    } catch (Exception e) {
    tv.setText(\”NOT CONNECTED\”);
    }
    }

    }

    But it goes into exception every time.Please help me & guide me for this issue…!!!

    ReplyDelete
    Replies
    1. only some versions of the MySQL Connector works with Android, try using the older version. Also can you tell the exact error you are getting

      Delete
    2. You must give the neccesary permissions to the application
      in your manifet.xml archive to allow it to make conections.

      add this line to the above metioned file:

      Delete
  11. give some more explanation man ..... how can beginners understand
    http://aitamelearning.blogspot.in

    ReplyDelete
    Replies
    1. Well this is just a simple SQL connection and query code. If you are having any problem please post the problem, so that I can assist you.

      Delete
  12. how to build path mysql-connector-java on eclipse, android connect to mysql server?

    ReplyDelete
  13. Thanks for your sample it does work pretty well.

    ReplyDelete
  14. I'm having a problem when trying to connect Android with MySQL using JDBC

    I am using mysql-connector-java-5.1.17-bin.jar.

    The error that occurs is this:

    Error: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    PID: 1214

    My code:

    public void conectarMySQL(){
    try{
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    }catch(Exception erro){
    Log.e("MYSQL","Erro: "+erro);
    }
    try{
    conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/adm", "root", "root");
    Log.i("MYSQL","Conectado.");
    }catch(Exception erro){
    desconectarMySQL();
    Log.e("MYSQL","Erro: "+erro);
    }
    }

    Please help me. Thank you!

    ReplyDelete
  15. I am getting error as com.msql.jdbc.Driver not found

    ReplyDelete
  16. Hi, For three days I try to do what you have posted in this article but nevertheless I can not fix, I have tried a number of JDBC libraries:

    mysql-connector-java-5.1.26-bin
    mysql-connector-java-5.1.25-bin
    mysql-connector-java-5.0.8-bin
    mysql-connector-java-3.0.17-ga-bin
    mysql-connector-java-5.1.22-bin

    java exception

    Could not find class 'javax.naming.StringRefAddr', referenced from method com.mysql.jdbc.ConnectionPropertiesImpl $ Property.storeTo Connection

    I'm using the library Android 4.0
    ADT on Windos 8 64-bit

    How can I fix

    Tanks
    Claudio

    ReplyDelete
  17. Hey friends I am getting this error

    "com.mysql.jdbs.exceptions.jdbc4.MySQLNonTransientConnectionException:Could not create connection to database server" .

    here is my code:

    package project.app.edirectory;

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

    import android.os.Bundle;
    import android.app.Activity;
    import android.content.Intent;
    import android.util.Log;
    import android.view.Menu;
    import android.widget.Toast;

    public class MainActivity extends Activity {

    Connection con=null;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    String url=new String("jdbc:mysql://localhost:3306/eDirectory");
    String driver="com.mysql.jdbc.Driver";
    String username=null;
    String password=null;
    try{
    Toast.makeText(getApplicationContext(), "Enter in try block before Class.forName()", Toast.LENGTH_LONG ).show();
    Class.forName(driver).newInstance();
    Toast.makeText(getApplicationContext(), "after Class.forName()", Toast.LENGTH_LONG ).show();
    con=(Connection)DriverManager.getConnection(url,"root",null);
    Toast.makeText(getApplicationContext(), "after Class.forName()2", Toast.LENGTH_LONG ).show();
    Statement s=con.createStatement();
    ResultSet rs=s.executeQuery("Select * from Category_detail");
    int i=0;
    while(rs.next())
    {
    Toast.makeText(getApplicationContext(), rs.getString(i), Toast.LENGTH_LONG).show();
    i=i+1;
    }
    }
    catch(Exception e){
    Toast.makeText(getApplicationContext(), e.toString(), Toast.LENGTH_LONG).show();
    }
    //Intent intent=new Intent(MainActivity.this,Category_list.class);
    //startActivity(intent);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.main, menu);
    return true;
    }

    }


    Please help me to solve it as early as possible . My project submission date is after 5 days please help me..

    ReplyDelete
    Replies
    1. I have SQL server management studio. and I have server type as "database engine" and Authentication as "windows authentication"

      Delete
  18. Its nice block thank you very much…And also improve your blog with more explanation…
    http://top7mobiles.com/top-list/best-app-to-download-free-movies-on-android

    ReplyDelete
  19. public void testDB() {
    TextView tv = (TextView) this.findViewById(R.id.tv_data);
    try {

    Class.forName("com.mysql.jdbc.Driver");

    // perfect

    // localhost

    /*
    * Connection con = DriverManager .getConnection(
    * "jdbc:mysql://192.168.1.5:3306/databasename?user=root&password=123"
    * );
    */

    // online testing

    Connection con = DriverManager
    .getConnection("jdbc:mysql://173.5.128.104:3306/vokyak_heyou?user=viowryk_hiweser&password=123");

    String result = "Database connection success\n";
    Statement st = con.createStatement();

    ResultSet rs = st.executeQuery("select * from tablename ");
    ResultSetMetaData rsmd = rs.getMetaData();

    while (rs.next()) {

    result += rsmd.getColumnName(1) + ": " + rs.getString(1) + "\n";

    }
    tv.setText(result);
    } catch (Exception e) {
    e.printStackTrace();
    tv.setText(e.toString());
    }

    }

    ReplyDelete
  20. your Android Concepts are really awesome and very much interesting and i got more information about your blog.

    Android Training in Chennai

    ReplyDelete
  21. Thank you for every other great post. The place else may just anyone get that type of info in such a perfect way of writing? I ave a presentation next week, and I am at the look for such info.
    Oracle SOA Online Training Classes With Real Time Support From India

    ReplyDelete