- Execute Oracle or MySQL SQL update scripts given a valid and authorized DB connection.
- Execute Oracle or MySQL SQL query and collect the results as a collection of lines (where a line is a Collection of Strings) or as a simple HTML page with a table summarizing the results.
The classes are grouped in an Eclipse Java Project illustrated by the following tree:
1. Project dependencies:
The project needs jdbc libraries for both Oracle (download here) and MySQL (download here) databases.2. Project Packages:
The project defines two packages:sql.util.connection: which contains classes defining JDBC Oracle and MySQL database connections.
ConnectionDefinition.java: is an abstract class describing general JDBC database connection as follows:
package sql.util.connection;
public abstract class ConnectionDefinition
{
private String url;
private String user;
private String password;
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public ConnectionDefinition(String url, String user, String password)
{
this.url = url;
this.user = user;
this.password = password;
}
public static String display(ConnectionDefinition cd)
{
if (cd == null)
{
return("ConnectionDefinition :\n\tnull");
}
else
{
return( "ConnectionDefinition :\n"
+ "\turl = " + ((cd.getUrl() == null)?"null":cd.getUrl()) + "\n"
+ "\tuser = " + ((cd.getUser() == null)?"null":cd.getUser()) + "\n"
+ "\tpassword = " + ((cd.getPassword() == null)?"null":cd.getPassword()) + "\n");
}
}
}
The class defines three properties (the DB connection url, the user name and the corresponding password) and a display function returning a friendly String description of the connection. This class is extended by :
OracleConnectionDefinition.java:
package sql.util.connection;
public class OracleConnectionDefinition extends ConnectionDefinition
{
private String host;
public String getHost() {
return host;
}
public void setHost(String host) {
this.host = host;
}
private int port;
public int getPort() {
return port;
}
public void setPort(int port) {
this.port = port;
}
private String sid;
public String getSid() {
return sid;
}
public void setSid(String sid) {
this.sid = sid;
}
public OracleConnectionDefinition(String host, int port, String sid, String user, String password)
{
super("jdbc:oracle:thin:@" + host + ":" + port + ":" + sid, user, password);
}
public OracleConnectionDefinition(String sid, String user, String password)
{
super("jdbc:oracle:thin:@localhost:1521:" + sid, user, password);
}
public OracleConnectionDefinition(String user, String password)
{
super("jdbc:oracle:thin:@localhost:1521:xe", user, password);
}
}
For Oracle databases the JDBC connection url is formed by three parameters the host (the server address), the listening port and the sid (site identifier). The class provides several default constructors (1521 is the default listening port and xe is the default sid for Oracle Express Editions).
MySQLConnectionDefinition.java:
package sql.util.connection;
public class MySQLConnectionDefinition extends ConnectionDefinition
{
private String host;
public String getHost() {
return host;
}
public void setHost(String host) {
this.host = host;
}
private int port;
public int getPort() {
return port;
}
public void setPort(int port) {
this.port = port;
}
private String schema;
public String getSchema() {
return schema;
}
public void setSchema(String schema) {
this.schema = schema;
}
public MySQLConnectionDefinition(String host, int port, String schema, String user, String password)
{
super("jdbc:mysql://" + host + ":" + port + "/" + schema, user, password);
}
public MySQLConnectionDefinition(String schema, String user, String password)
{
super("jdbc:mysql://localhost:3306/" + schema, user, password);
}
public MySQLConnectionDefinition(String user, String password)
{
super("jdbc:mysql://localhost:3306/mysql", user, password);
}
}
For MySQL databases the JDBC connection url is formed by three parameters the host (the server address), the listening port and the schema. The class provides several default constructors (3306 is the default listening port and mysql is the default schema).
sql.util: which contains the class that will execute scripts.
SQLExecutor.java:
package sql.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import sql.util.connection.ConnectionDefinition;
import sql.util.connection.MySQLConnectionDefinition;
import sql.util.connection.OracleConnectionDefinition;
public class SQLExecutor
{
public static String LINE_SEPARATOR = System.getProperty("line.separator");
private static final String ORACLE_DRIVER_NAME = "oracle.jdbc.OracleDriver";
private static final String MYSQL_DRIVER_NAME = "com.mysql.jdbc.Driver";
public static void loadMySQLDriver()
{
try
{
Class.forName(MYSQL_DRIVER_NAME);
}
catch(ClassNotFoundException e)
{
System.out.println(MYSQL_DRIVER_NAME + " is missing.");
e.printStackTrace();
}
}
public static void loadOracleDriver()
{
try
{
Class.forName(ORACLE_DRIVER_NAME);
}
catch(ClassNotFoundException e)
{
System.out.println(ORACLE_DRIVER_NAME + " is missing.");
e.printStackTrace();
}
}
ConnectionDefinition connection;
public ConnectionDefinition getConnection() {
return connection;
}
public void setConnection(ConnectionDefinition connection) {
this.connection = connection;
}
public SQLExecutor(ConnectionDefinition connection)
{
this.connection = connection;
if (connection instanceof OracleConnectionDefinition)
{
loadOracleDriver();
}
if (connection instanceof MySQLConnectionDefinition)
{
loadMySQLDriver();
}
}
public void executeUpdate(String query)
{
Connection con;
Statement stmt;
try
{
con = DriverManager.getConnection(getConnection().getUrl(),getConnection().getUser(),getConnection().getPassword());
try
{
stmt = con.createStatement();
query = query.trim();
if (query.endsWith(";"))
{
query = query.substring(0,query.length()-1);
}
try
{
stmt.executeUpdate(query);
}
catch (SQLException e)
{
System.out.println("Could not execute update query:\n" + query);
e.printStackTrace();
}
stmt.close();
stmt = null;
}
catch (SQLException e)
{
System.out.println("Could not create statement.");
e.printStackTrace();
}
con.close();
con = null;
}
catch (SQLException e)
{
System.out.println("Something is wrong with your connection parameters:\n" + ConnectionDefinition.display(getConnection()));
e.printStackTrace();
}
}
public void executeScript(String script)
{
Connection con;
Statement stmt;
try
{
con = DriverManager.getConnection(getConnection().getUrl(),getConnection().getUser(),getConnection().getPassword());
try
{
stmt = con.createStatement();
script = script.trim();
if (script.endsWith(";"))
{
script = script.substring(0,script.length()-1);
}
String[] queries = script.split(";" + LINE_SEPARATOR);
int counter = 0;
for (String query : queries)
{
counter++;
if ((query != null) && !query.isEmpty())
{
try
{
stmt.executeUpdate(query);
}
catch (SQLException e)
{
System.out.println("Could not execute update query in line " + counter + ":\n" + query);
e.printStackTrace();
}
}
}
stmt.close();
stmt = null;
}
catch (SQLException e)
{
System.out.println("Could not create statement.");
e.printStackTrace();
}
con.close();
con = null;
}
catch (SQLException e)
{
System.out.println("Something is wrong with your connection parameters:\n" + ConnectionDefinition.display(getConnection()));
e.printStackTrace();
}
}
public ArrayList<ArrayList<String>> executeQueryArray(String query)
{
ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
Connection con;
Statement stmt;
try
{
con = DriverManager.getConnection(getConnection().getUrl(),getConnection().getUser(),getConnection().getPassword());
try
{
stmt = con.createStatement();
query = query.trim();
if (query.endsWith(";"))
{
query = query.substring(0,query.length()-1);
}
ResultSet resultSet;
try
{
resultSet = stmt.executeQuery(query);
ResultSetMetaData rsmd;
ArrayList<String> columns = new ArrayList<String>();
try
{
rsmd = resultSet.getMetaData();
int counter = 0;
while (counter < rsmd.getColumnCount())
{
columns.add(rsmd.getColumnLabel(counter+1));
counter++;
}
result.add(columns);
while (resultSet.next())
{
ArrayList<String> line = new ArrayList<String>();
int counter2 = 0;
while (counter2 < columns.size())
{
line.add(resultSet.getString(counter2+1));
counter2++;
}
result.add(line);
}
}
catch (SQLException e)
{
System.out.println("Could not get MetaData from ResultSet for query:\n" + query);
e.printStackTrace();
}
resultSet.close();
}
catch (SQLException e)
{
System.out.println("Could not execute query:\n" + query);
e.printStackTrace();
}
stmt.close();
stmt = null;
}
catch (SQLException e)
{
System.out.println("Could not create statement.");
e.printStackTrace();
}
}
catch (SQLException e)
{
System.out.println("Something is wrong with your connection parameters:\n" + ConnectionDefinition.display(getConnection()));
e.printStackTrace();
}
return(result);
}
public String executeQuery2HTML(String query)
{
String finalResult = "<html>" + LINE_SEPARATOR + "\t</head>" + LINE_SEPARATOR + "\t<body>" + LINE_SEPARATOR + "\t\t<table border = \"1\" align = \"center\">";
boolean done = false;
for (ArrayList<String> array : executeQueryArray(query))
{
finalResult += LINE_SEPARATOR + "\t\t\t<tr>" + LINE_SEPARATOR;
for (String cell : array)
{
finalResult += ((done)?"\t\t\t\t<td align = \"center\">":"\t\t\t\t<th align = \"center\">") + cell + ((done)?"</td>" + LINE_SEPARATOR:"</th>" + LINE_SEPARATOR);
}
finalResult += "\t\t\t</tr>";
done = true;
}
return(finalResult + LINE_SEPARATOR + "\t\t</table>" + LINE_SEPARATOR + "\t</body>" + LINE_SEPARATOR + "</html>");
}
public static void testOracle(String user, String password)
{
String scriptOracle = "CREATE TABLE test (id NUMERIC(2) NOT NULL, title VARCHAR2(10), description VARCHAR2(20), PRIMARY KEY(id));" + LINE_SEPARATOR
+ "INSERT INTO test VALUES(1,'title1','Title One');" + LINE_SEPARATOR
+ "INSERT INTO test VALUES(2,'title2','Title Two');" + LINE_SEPARATOR
+ "INSERT INTO test VALUES(3,'title3','Title Three');" + LINE_SEPARATOR
+ "COMMIT;";
SQLExecutor oracleSQLExecutor = new SQLExecutor(new OracleConnectionDefinition(user,password));
oracleSQLExecutor.executeScript(scriptOracle);
System.out.println(oracleSQLExecutor.executeQuery2HTML("SELECT * FROM test;"));
String scriptCleanUp = "DROP TABLE test;";
oracleSQLExecutor.executeScript(scriptCleanUp);
}
public static void testMySQL(String user, String password)
{
String scriptMySQL = "CREATE TABLE test (id NUMERIC(2) NOT NULL, title VARCHAR(10), description VARCHAR(20), PRIMARY KEY(id));" + LINE_SEPARATOR
+ "INSERT INTO test VALUES(1,'title1','Title One');" + LINE_SEPARATOR
+ "INSERT INTO test VALUES(2,'title2','Title Two');" + LINE_SEPARATOR
+ "INSERT INTO test VALUES(3,'title3','Title Three');" + LINE_SEPARATOR
+ "COMMIT;";
SQLExecutor mySQLExecutor = new SQLExecutor(new MySQLConnectionDefinition(user,password));
mySQLExecutor.executeScript(scriptMySQL);
System.out.println(mySQLExecutor.executeQuery2HTML("SELECT * FROM test;"));
String scriptCleanUp = "DROP TABLE test;";
mySQLExecutor.executeScript(scriptCleanUp);
}
public static void main(String[] args)
{
if (args.length == 3)
{
if ("oracle".equals(args[0].toLowerCase()))
{
testOracle(args[1],args[2]);
}
if ("mysql".equals(args[0].toLowerCase()))
{
testMySQL(args[1],args[2]);
}
}
else
{
System.out.println("Executes test scripts on \"localhost:1521:xe\" or \"localhost:3306/mysql\"\n\nArguments:\n- DB Type : Oracle or MySQL\n- DB User Name\n- DB Password\n\nExample: \"oracle\" \"system\" \"manager\"");
}
}
}
We briefly describe here the important methods:
public void executeScript(String script) in lines 120-178:
The method splits the script into several single instructions (statements) then executes the instructions one by one. Exceptions are caught whenever the connection does not succeed, the statement execution is not possible (SQL syntax error or maybe not enough privileges to execute the statement).
public ArrayList<ArrayList<String>> executeQueryArray(String query) in lines 180-273:
The method returns the result of the execution of a select query as a list of lists of Strings. The first inner list contains the returned field titles and each subsequent inner list (if any) contains values for those fields. Again, exceptions are caught whenever the connection does not succeed, the statement execution is not possible (SQL syntax error or maybe not enough privileges to execute the statement).
public String executeQuery2HTML(String query) in lines 275-294:
Relies on the previous method to format the result of the execution of a select query as a table in a simple html page.
public static void testOracle(String user, String password) in lines 296-311 and public static void testMySQL(String user, String password) in lines 313-328:
These two methods use the previously described methods to execute test scripts and queries for simple default settings for Oracle and MySQL databases. A dummy test table is created and three lines are inserted into it. A select statement is executed against the test table and corresponding results are displayed as html. Finally the test table is dropped.
These two methods can be used by executing the main method of the class with three arguments: the database type, the authorized user name and password.
No comments:
Post a Comment