Status of MySQL Instance
This post deals with the MySQL database which is widely used in open source project.
A product which requires database must check database exists or not and based that appropriate information shown to the end user so that he/she can perform relevant actions to deal with such situation. The details of message will help the end-user to find out the causes why application is not running.
1) Check MySQL instance is running or not?
2) Check login credentials are valid or not?
3) Check database schema exists or not?
Let’s implement a java class which checks various MySQL database statuses and based on that display appropriate information /error messages to the end-user.
Code snippet 1
package com.ashish.mysqlinstance;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* This class used to check MySql status.
* @author Ashish.Chudasama
*/
public class MySQLInstanceStatus
{
/**
* UNABLE TO LOCATE SPECIFIED DATASOURCE.
*/
public static final int DATA_SOURCE_NOT_REACHABLE = 2;
/**
* SPECIFILED USER ID AND PWD ARE NOT VALID .
*/
public static final int AUTHENTICATION_FAILURE = 4;
/**
* MySQl Test successful.
*/
public static final int MYSQL_INSTALLED_AND_RUNNING = 8;
/**
* Specified database name not exist.
*/
public static final int DATABASE_SCHEMA_NOT_EXISTS = 16;
/**
* database name exist.
*/
public static final int DATABASE_SCHEMA_EXISTS = 32;
/**
* database name exist but incomplete.
*/
public static final int DATABASE_SCHEMA_INCOMPLETE = 64;
/**
* Represent database name. like local-host,127.0.0.1,IPAddress where database located
*/
private String dataSource = "";
/**
* UserId for database login.
*/
private String userID = "";
/**
* User Password for the given UserID.
*/
private String password = "";
/**
* Port number on which services is running.
* Default port number 3306.
*/
private String portNumber = "";
/**
* Database name. Example: information_schema.
*/
private String databaseName = "";
/**
* This method internally used following methods
* 1) isMySQLInstanceRunning()
* 2) isDatabaseExists()
* This method used to check MySQL is Installed or not also check for the database schema exists or not.
*/
public int checkMySQLStatus()
{
int value = isMySQLInstanceRunning();
if ( value == MYSQL_INSTALLED_AND_RUNNING )
{
return isDatabaseExists();
}
else
{
return value;
}
}
/**
* @return the databaseName
*/
public String getDatabaseName()
{
return databaseName;
}
/**
* @return the dataSource.
*/
public String getDataSource()
{
return this.dataSource;
}
/**
* @return the password
*/
public String getPassword()
{
return this.password;
}
/**
* @return the portNumber
*/
public String getPortNumber()
{
return portNumber;
}
/**
* @return the userID
*/
public String getUserID()
{
return this.userID;
}
/**
* This method used to check whether specified database Exists or not. this method is different compare with
* isMySQLInstanceRunning() isMySQLInstanceRunning this method basically used to check MySQL is Installed or not.
*/
public int isDatabaseExists()
{
Connection con = null;
int returnValue = DATABASE_SCHEMA_NOT_EXISTS;
try
{
if ( dataSource.trim().length() == 0 )
{
throw new Exception ("Database name is not specified. ");
}
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
String connectionString = "jdbc:mysql://" + dataSource;
if ( portNumber.trim().length() > 0 )
{
connectionString = connectionString + ":"+portNumber;
}
if ( databaseName.trim().length() > 0 )
{
connectionString = connectionString +"/"+databaseName;
}
else
{
throw new Exception ("Database name is not specified. ");
}
if ( userID.trim().length() > 0 )
{
con = DriverManager.getConnection(
connectionString, userID, password );
}
else
{
con = DriverManager.getConnection(
connectionString );
}
Statement statement = con.createStatement();
statement.execute( "Use " + databaseName );
if ( !con.isClosed() )
{
returnValue = DATABASE_SCHEMA_EXISTS;
}
}
catch ( Exception excep )
{
returnValue = DATABASE_SCHEMA_NOT_EXISTS;
}
finally
{
if ( con != null )
{
try
{
con.close();
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
}
return returnValue;
}
/**
* @return True if dataSource,userID,password are valid and its running on given port.
*/
public int isMySQLInstanceRunning()
{
Connection con = null;
try
{
if ( dataSource.trim().length() == 0 )
{
throw new Exception ("Database name is not specified. ");
}
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
String connectionString = "jdbc:mysql://" + dataSource;
if ( portNumber.trim().length() > 0 )
{
connectionString = connectionString + ":" + portNumber;
}
if ( userID.trim().length() > 0 )
{
con = DriverManager.getConnection(
connectionString, userID, password );
}
else
{
con = DriverManager.getConnection(
connectionString );
}
if ( !con.isClosed() )
{
return MYSQL_INSTALLED_AND_RUNNING;
}
}
catch ( com.mysql.jdbc.exceptions.jdbc4.CommunicationsException communicationsException )
{
return DATA_SOURCE_NOT_REACHABLE;
}
catch ( Exception e )
{
String message = e.getMessage();
if ( message.indexOf( "is not allowed to connect to this MySQL server" ) != 0 )
{
return AUTHENTICATION_FAILURE;
}
else if (
message.indexOf( "Access denied for user" ) != 0 )
{
return AUTHENTICATION_FAILURE;
}
}
finally
{
if ( con != null )
{
try
{
con.close();
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
}
return MYSQL_INSTALLED_AND_RUNNING;
}
/**
* @param databaseName the databaseName to set
*/
public void setDatabaseName( String databaseName )
{
this.databaseName = databaseName;
}
/**
* @param dataSource the dataSource to set
*/
public void setDataSource( String dataSource )
{
this.dataSource = dataSource;
}
/**
* @param password the password to set
*/
public void setPassword( String password )
{
this.password = password;
}
/**
* @param portNumber the portNumber to set
*/
public void setPortNumber( String portNumber )
{
this.portNumber = portNumber;
}
/**
* @param userID the userID to set
*/
public void setUserID( String userID )
{
this.userID = userID;
}
}
Code snippet 2
package com.ashish.mysqlinstance;
/**
* @author Ashish.Chudasama
*/
public class StubClass
{
public static void main(String[] args)
{
MySQLInstanceStatus instanceStatus = new MySQLInstanceStatus();
instanceStatus.setDatabaseName("information_schema");
instanceStatus.setDataSource("127.0.0.1");
instanceStatus.setPortNumber("3306");
instanceStatus.setUserID("root");
instanceStatus.setPassword("root");
System.out.println(instanceStatus.checkMySQLStatus());
}
}
Output:
32 – means DATABASE_SCHEMA_EXISTS
Now specify incorrect login credentials
4 – means AUTHENTICATION_FAILURE
Now specify incorrect database name.
16 –means DATABASE_SCHEMA_NOT_EXISTS
Click here to download source code.
No comments:
Post a Comment