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
- Download the API from https://github.com/rohit7209/AccessRemoteMySQLDB and unzip the zipped files at your preferred directory
- 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) - Add AccessRemoteMySQLDB.jar in your project
- 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 armdb.SQLQuery;
import armdb.SQLUpdate;
import armdb.QueryResult;
import armdb.SQLQueryException;
import armdb.SQLUpdateException;
public class Example {
public static void main(String[] args) {
String fileURL="http://example.com/some_directory/handleSQL.php";
String host="mysql.some_hosting.com";
String user="some_user";
String pass="some_password";
String DBName="some_dbname";
ConnectHost con=new ConnectHost(fileURL, host, user, pass, DBName);
SQLQuery query=new SQLUpdate(con);
try{
String sql="INSERT INTO table_name VALUES ('value_1','value_2','value_3','value_4')";
int rows=update.statement(sql);
System.out.println(rows+" no. of rows affected");
}catch(SQLUpdateException e){
System.out.println(e.getMessage());
}
}
}
Sample code to update rows:
try{
String sql="UPDATE table_name SET column_1='value_1' WHERE some_column='some_value')";
int rows=update.statement(sql);
System.out.println(rows+" no. of rows affected");
}catch(SQLUpdateException e){
System.out.println(e.getMessage());
}
Sample code to delete rows:
try{
String sql="DELETE FROM table_name WHERE some_column='some_value')";
int rows=update.statement(sql);
System.out.println(rows+" no. of rows affected");
}catch(SQLUpdateException e){
System.out.println(e.getMessage());
}
Sample code to select rows:
SQLQuery query=new SQLQuery(con);
QueryResult qr;
try{
qr=query.statement("select * from table_name");
while(qr.nextFlag()){
System.out.println(qr.getValue("column_1")+", ");
System.out.print(qr.getValue("column_2"));
}
}catch(SQLQueryException e){
System.out.println(e.getMessage());
}
You can fetch the result in other ways also:
while(qr.nextFlag()){
System.out.println(qr.getValue(0)+", ");
System.out.print(qr.getValue(1));
}
System.out.println(qr.getValueAt(0, 1));
System.out.println(qr.numFields());
System.out.println(qr.numRows());
qr.resetFlag();
System.out.println(qr.getValue("column_1"));
qr.nextFlag();
System.out.println(qr.getValue("column_1"));
qr.nextFlag();
qr.nextFlag();
System.out.println(qr.getValue("column_1"));
System.out.println(qr.getValue("column_1"));
qr.resetFlag();
System.out.println(qr.getValue("column_1"));
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";
String host="mysql.some_hosting.com";
String user="some_user";
String pass="some_password";
String DBName="some_dbname";
ConnectHost con=new ConnectHost(fileURL, host, user, pass, DBName);
It's very simple to hide these details using EntityManager
-API. For more, visit this link.