How do I read MySQL data from Android using JDBC?

This example show you how to connect and read data from MySQL database directly from Android. The following steps and code snippet will show you how to do it.

Add the MySQL JDBC driver into your project dependencies. Open the app/build.gradle file and add the dependency.

...
...

dependencies {
    ...
    ...
    implementation 'mysql:mysql-connector-java:5.1.49'
}

If you want to connect to MariaDB you can change the JDBC driver dependency using 'org.mariadb.jdbc:mariadb-java-client:1.8.0', also update the JDBC url in the code snippet by replacing mysql with mariadb.

Next, add internet permission to our application in AndroidManifest.xml file.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
          package="org.kodejava.android">

    <uses-permission android:name="android.permission.INTERNET" />

    ...
    ...

</manifest>

Let’s connect, read data from the database and display the information on the screen. In the code snippet we create an AsyncTask to read the information from the database. In the doInBackground() method we open a connection to the database, create a PreparedStatement, execute a query, get a ResultSet and read the information from it. We pack the data into a Map and return it.

After the doInBackground() method finish its execution the onPostExecute() method will be called. In this method we take the result, the Map returned by the doInBackground() method, and set the values into the TextView compoments for display.

package org.kodejava.android;

import android.annotation.SuppressLint;
import android.os.AsyncTask;
import android.util.Log;
import android.widget.TextView;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

public class MainActivity extends AppCompatActivity {
    private static final String URL = "jdbc:mysql://192.168.0.107:3306/kodejava";
    private static final String USER = "kodejava";
    private static final String PASSWORD = "kodejava";

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

        new InfoAsyncTask().execute();
    }

    @SuppressLint("StaticFieldLeak")
    public class InfoAsyncTask extends AsyncTask<Void, Void, Map<String, String>> {
        @Override
        protected Map<String, String> doInBackground(Void... voids) {
            Map<String, String> info = new HashMap<>();

            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
                String sql = "SELECT name, address, phone_number FROM school_info LIMIT 1";
                PreparedStatement statement = connection.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery();
                if (resultSet.next()) {
                    info.put("name", resultSet.getString("name"));
                    info.put("address", resultSet.getString("address"));
                    info.put("phone_number", resultSet.getString("phone_number"));
                }                
            } catch (Exception e) {
                Log.e("InfoAsyncTask", "Error reading school information", e);
            }

            return info;
        }

        @Override
        protected void onPostExecute(Map<String, String> result) {
            if (!result.isEmpty()) {
                TextView textViewName = findViewById(R.id.textViewName);
                TextView textViewAddress = findViewById(R.id.textViewAddress);
                TextView textViewPhoneNumber = findViewById(R.id.textViewPhone);

                textViewName.setText(result.get("name"));
                textViewAddress.setText(result.get("address"));
                textViewPhoneNumber.setText(result.get("phone_number"));
            }
        }
    }
}
  • Finally here is the screenshot of our Android application.
Android - MySQL JDBC

Android – MySQL JDBC

The complete source code can be accesses in our GitHub repository here: android-mysql-example.

Creating MySQL database programmatically in Java

There are times that you might need to create database or tables right after you run your program instead of manually creating it. In this example I will show you how you can do this using JDBC and MySQL database. The first thing we need to do as usual when creating a JDBC program is to define a JDBC URL. One thing that you’ll notice here is that we don’t define the database name in the URL. So the URL will be like jdbc:mysql://localhost.

After defining the URL we need to create a connection to the database. We issued the DriverManager.getConnection() method and pass the URL, username and password as the arguments. The next step is to create a PreparedStatement. When we call the preparedStatement() method we pass an SQL command to create the database, which is CREATE DATABASE IF NOT EXISTS DEMODB. This will create the database when there is database with DEMODB exists in the database. Finally call the PreparedStatement‘s execute() method.

Now you can try for your self, start typing the following code snippet in your text editor or IDE and execute it to create the database.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class CreateMySQLDatabaseExample {
    public static void main(String[] args) {
        // Defines the JDBC URL. As you can see, we are not specifying
        // the database name in the URL.
        String url = "jdbc:mysql://localhost";

        // Defines username and password to connect to database server.
        String username = "root";
        String password = "";

        // SQL command to create a database in MySQL.
        String sql = "CREATE DATABASE IF NOT EXISTS DEMODB";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

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

After you are executing the code snippet above you will find a new database named DEMODB created in your MySQL database server.

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How to establish connection to database with Properties?

In the following code snippet you’ll see how to pass some connection arguments when connecting to a database. To do this we can use the java.util.Properties class. We can put some key value pairs as a connection arguments to the Properties object before we pass this information into the DriverManager class.

Let’s see the example below:

package org.kodejava.example.jdbc;

import java.sql.*;
import java.util.Properties;

public class GetConnectionWithProperties {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try (Connection connection = demo.getConnection()) {
            // do something with the connection.
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM products");
            while (rs.next()) {
                System.out.println("Code = " + rs.getString("code"));
                System.out.println("Name = " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", USERNAME);
        connectionProps.put("password", PASSWORD);

        Connection connection = DriverManager.getConnection(URL, connectionProps);
        System.out.println("Connected to database.");
        return connection;
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How to automatically close resources in JDBC?

One thing that we need to do manually when programming using JDBC is to make sure to close all the resources that we use. All resources including the ResultSet, Statement and Connection must be closed. This will usually produce a lot of boilerplate code in our program.

Starting from JDBC 4.1 which is a part of Java 7 we can use the try-with-resources statement to automatically manage the resources that we use. This try statement closes the resources used when the block finishes its execution either normally or abruptly.

Here is an example that show us how to use the try-with-resources statement.

package org.kodejava.jdbc;

import java.sql.*;

public class TryWithResourceJdbc {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM products")) {

            while (rs.next()) {
                String code = rs.getString("code");
                String name = rs.getString("name");

                System.out.println("Code: " + code + "; Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.27/mysql-connector-java-8.0.27.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>

Maven Central

How do I check whether a driver is JDBC compliant?

package org.kodejava.jdbc;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCCompliant {
    private static final String URL = "jdbc:mysql://localhost/kodejava";

    public static void main(String[] args) {
        try {
            Driver driver = DriverManager.getDriver(URL);

            // Check if the driver is a genuine JDBC compliant driver.
            if (driver.jdbcCompliant()) {
                System.out.println("A genuine JDBC compliant driver");
            } else {
                System.out.println("Not a genuine JDBC compliant driver");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

Maven Central

How do I get JDBC driver property information?

The example below show you how to get the information about the possible properties for the corresponding JDBC driver.

package org.kodejava.jdbc;

import java.sql.*;

public class DriverPropertyInfoDemo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";

    public static void main(String[] args) {
        try {
            // Gets information about the possible properties for this
            // driver.
            Driver driver = DriverManager.getDriver(URL);
            DriverPropertyInfo[] props = driver.getPropertyInfo(URL, null);

            for (DriverPropertyInfo info : props) {
                System.out.println("Name       : " + info.name);
                System.out.println("Description: " + info.description);
                System.out.println("Value      : " + info.value);
                System.out.println("-----------------------------------");

                String[] choices = info.choices;
                if (choices != null) {
                    StringBuilder sb = new StringBuilder("Choices    : ");
                    for (String choice : choices) {
                        sb.append(choice).append(",");
                    }

                    System.out.println(sb);
                }
            }

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

The example result of the code snippet above:

Name       : host
Description: Hostname of MySQL Server
Value      : localhost
-----------------------------------
Name       : port
Description: Port number of MySQL Server
Value      : 3306
-----------------------------------
Name       : dbname
Description: Database name;
Value      : kodejava
-----------------------------------
Name       : user
Description: Username to authenticate as
Value      : null
-----------------------------------
Name       : password
Description: Password to use for authentication
Value      : null
-----------------------------------
Name       : clientCertificateKeyStorePassword
Description: Password for the client certificates key store.
Value      : null
-----------------------------------
Name       : serverRSAPublicKeyFile
Description: File path to the server RSA public key file for sha256_password authentication. If not specified, the public key will be retrieved from the server.
Value      : null
-----------------------------------
Name       : cacheDefaultTimeZone
Description: Caches clients default time zone. This results in better performance when dealing with time zone conversions in Date and Time data types, however it wont be aware of time zone changes if they happen at runtime.
Value      : true
-----------------------------------
Choices    : TRUE,FALSE,YES,NO,
...
...
...

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

Maven Central

How do I check if the OUT parameter value is null?

This example show you how to use the CallableStatement.wasNull() method call to see if the last OUT parameter has a value of SQL NULL.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Types;
import java.sql.SQLException;

public class WasNullExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Prepares a call to the sored procedure
            String query = "call Get_Product_By_Price2(?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setBigDecimal(1, new BigDecimal("50"));

            // Registers the OUT parameter
            cb.registerOutParameter(2, Types.VARCHAR);

            // Executes the query
            cb.executeQuery();

            // Gets the OUT parameter value
            cb.getString(2);

            // Checks if the last OUT parameter has value of SQL NULL.
            // This method should be called only after calling a
            // getter method; otherwise, there is no value to use in
            // determining whether it is null or not.
            if (cb.wasNull()) {
                System.out.println("Product has an SQL NULL value");
            } else {
                System.out.println("Product: " + cb.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we called in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price2;;
CREATE PROCEDURE Get_Product_By_Price2(
    IN product_price DECIMAL(10, 2),
    OUT product_name VARCHAR(50))
BEGIN
    SELECT NAME
    INTO product_name
    FROM products
    WHERE price = product_price;
END;;
DELIMITER ;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

Maven Central

How do I call a stored procedure that return a result set?

This example show you how to call a stored procedure that return a result set of the query execution result.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallableStatementExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Prepares a call to the sored procedure. This SP takes
            // one IN parameter
            String query = "call Get_Product_By_Price(?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setBigDecimal(1, new BigDecimal("50.99"));

            // Execute the query
            ResultSet rs = cb.executeQuery();

            while (rs.next()) {
                System.out.println("Product: " + rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we use in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price;;
CREATE PROCEDURE Get_Product_By_Price(IN product_price DECIMAL(10, 2))
BEGIN
    SELECT name FROM products WHERE price = product_price;
END;;
DELIMITER ;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

Maven Central