How to Access web-server MySQL Database in Java?

Introduction

The purpose of this article is to show in a step by step manner how to use and connect Java with web-server MySql using AccessRemoteMySQLDB API. I will create simple examples about the DML (Insert, Update, Select, Delete) throughout the article to show how to query the database using Java.

Why We Need AccessRemoteMySQLDB API?

For example, you are working on an application (in Java) which would use the web-server MySQL database located in your website hosted on a Linux server with PHP & MySQL support. There are below discussed methods which you can use to access the database.

Method A

Open ports to access mysql:

It would be a best solution if your web provider allows you to open specific ports for a given IP address, but I doubt it’s possible. Whereas you can open ports for all but that can give rise to a security issue, I’ll firmly recommend you to not practice this.

Method B

Use VPN to access mysql:

Another method is to install some kind of VPN between webserver and your computer. Once VPN is setup, you can access port 3306, but this method requires some skill and experience, it’s quite tricky for new programmers and users of developed application. A distribution issue may also arise. So this method is also not much helpful for buddy programmers.

As we saw, there is a problem with both the methods. You can open ports for all, but a security issue arises and using VPN connection is tricky for users. So the best method I will recommend is to use AccessRemoteMySQLDB API in your application.

Get Started

1. Download the API from https://github.com/rohit7209/AccessRemoteMySQLDB and unzip the zipped files at your preferred directory
2. Copy file 'handleSQL.php' to the web-server where your mysql database is located
   (remember to note down the url of the 'handleSQL.php'. url ex: example.com/somedirectory/handleSQL.php)
3. Add AccessRemoteMySQLDB.jar in your project
4. That's all, now start happy coding :)

Using the Code

Here is a brief demonstration of how to use this API.

Sample code to insert rows in mysql table:

import armdb.ConnectHost;             //import to make connection
import armdb.SQLQuery;                //import to make SQL query
import armdb.SQLUpdate;               //import to make SQL update
import armdb.QueryResult;             //import to store the query results
import armdb.SQLQueryException;       //import to handle the exception thrown during query
import armdb.SQLUpdateException;      //import to handle the exception thrown during update 

public class Example {

    public static void main(String[] args) {

        String fileURL="http://example.com/some_directory/handleSQL.php";  //URL of 'handleSQL.php' file
        String host="mysql.some_hosting.com";                              //server host name
        String user="some_user";                                           //username
        String pass="some_password";                                       //password
        String DBName="some_dbname";                                       //database name

        ConnectHost con=new ConnectHost(fileURL, host, user, pass, DBName);  //make connection        
        SQLQuery query=new SQLUpdate(con);               //create object to execute query statement

        
        //insert rows in database table

        try{

            //sql statement
            String sql="INSERT INTO table_name VALUES ('value_1','value_2','value_3','value_4')";

            int rows=update.statement(sql);      //execute statement and returns the no. of affected rows

            System.out.println(rows+" no. of rows affected");            //prints no. of affected rows

        }catch(SQLUpdateException e){                                    //catch exception if occurred

            System.out.println(e.getMessage());                          //print exception message
        }
    }
}

Sample code to update rows:

//update contents of mysql table
try{
    //statement
    String sql="UPDATE table_name SET column_1='value_1' WHERE some_column='some_value')";
    int rows=update.statement(sql);                          //executes statement
    System.out.println(rows+" no. of rows affected");        //printing no. of affected rows
}catch(SQLUpdateException e){                                //catch exception if occurred
            System.out.println(e.getMessage());              //print exception message
}

Sample code to delete rows:

//delete contents of mysql table
try{
    //statement
    String sql="DELETE FROM table_name WHERE some_column='some_value')";
    int rows=update.statement(sql);                            //executes statement
    System.out.println(rows+" no. of rows affected");          //printing no. of affected rows
}catch(SQLUpdateException e){                                  //catch exception if occurred
    System.out.println(e.getMessage());                        //print exception message
}

Sample code to select rows:

SQLQuery query=new SQLQuery(con);         //SQLQuery object to execute select statement
QueryResult qr;                           //QueryResult object to store the queried result
try{
    qr=query.statement("select * from table_name");     //execution of query statement
        
    //qr holds the selected rows, let us print the values of some columns 
    //(say column_1 and column_2) of all rows

    while(qr.nextFlag()){                               //setting flag to next row till next row exists
        //print column_1 & column_2 value of row where flag is set

        System.out.println(qr.getValue("column_1")+", ");
        System.out.print(qr.getValue("column_2"));
    }
}catch(SQLQueryException e){                            //catch exception if occurred
    System.out.println(e.getMessage());                 //print exception message
}

You can fetch the result in other ways also:

//print the contents of selected columns (say column_1 and column_2) and all rows using column index
while(qr.nextFlag()){                          //setting flag to next row till next row exists
     System.out.println(qr.getValue(0)+", ");  //printing column_1 data of the row where flag is set
     System.out.print(qr.getValue(1));         //printing column_2 data of the row where flag is set
}                         

//print the value of specific column and row                         
System.out.println(qr.getValueAt(0, 1));

//some more functions of QueryResult
//get no. of selected columns
System.out.println(qr.numFields());

//get no. of selected rows
System.out.println(qr.numRows());

//using nextFlag() and resetFlag()
qr.resetFlag();                               //reset the flag to row 0
System.out.println(qr.getValue("column_1"));  //prints the value of column_1 row 0
qr.nextFlag();                                //increment the Flag by 1
System.out.println(qr.getValue("column_1"));  //prints the value of column_1 row 1
qr.nextFlag();                                //increment the flag by 1
qr.nextFlag();                                //increment the flag by 1
System.out.println(qr.getValue("column_1"));  //prints the value of column_1 and row 3
System.out.println(qr.getValue("column_1"));  //again prints the same value as flag is not incremented
qr.resetFlag();                               //reset the flag to row 0
System.out.println(qr.getValue("column_1"));  //prints the value of column_1 and row 0 as flag 
					      //is reset to 0                                                  

//clear the contents of qr
qr.clear();

Some More Suggestions

We have hard-coded the password and other details near establishing the connection, this is not a good practice. We should hide these details or else anybody can snoop these details.

String fileURL="http://example.com/some_directory/handleSQL.php";   //URL of 'handleSQL.php' file 
String host="mysql.some_hosting.com";                               //server host name 
String user="some_user";                                            //username 
String pass="some_password";                                        //password 
String DBName="some_dbname";                                        //database name 
ConnectHost con=new ConnectHost(fileURL, host, user, pass, DBName); //make connection

It's very simple to hide these details using EntityManager-API. For more, visit this link.