Package edu.jhu.tmaj.database

A stand-alone package called JAnyDatabase.

See:
Description

Class Summary

DatabaseQuery	A class for executing SELECT statement.
DatabaseUtil	Contains various functions used to modify the database (UPDATE, INSERT, and DELETE statements) and to query the database (very simple SELECT statements).

Package edu.jhu.tmaj.database Description

A stand-alone package called JAnyDatabase. JAnyDatabase provides an API that allows users to query different database types. The advantage of using JAnyDatabase is that one could easily switch the database type (say from MySql to Oracle) by only changing a flag. In a sense, JAnyDatabase is an API that "sits on top of" JDBC making sure that what SQL calls are made will work for all databases.

JAnyDatabase is a software package coded 100% in java. The software package that uses JAnyDatabase (like TMAJ does) may have Oracle as a database, or it may have something else like Sybase, MySQL or Microsoft SQL Server.

For example, JAnyDatabase provides a standard way to insert a record and access the auto-incremented column. There are non-standard ways that will not work across databases. For example, in Sybase, you could get the value of an auto-incremented by using the statement "SELECT @@IDENTITY". This, however, will not work in many other databases. So if you wanted to suddenly use another database besides Sybase, your program would not work.

JAnyDatabase has a solution that will work across multiple databases. It is done using the DatabaseUtil class. In this example, lets say we wanted insert a record in table employees and get the EmployeeID of the record that was inserted. To create the following SQL statement:
INSERT INTO Employees (FirstName, LastName, IsMarried, OfficeNumber) VALUES ('John', 'Smith', 0, 25011)

use this code:

InsertSQL insertSQL = InsertSQL.getInstance("Employees", "EmployeeID");
insertSQL.add("FirstName", "John");
insertSQL.add("LastName", "Smith");
insertSQL.add("IsMarried", true);
insertSQL.add("OfficeNumber", 25011);

Next, insert the record into the database using this this statement:
DatabaseUtil databaseUtil = new DatabaseUtil(connectionManager, sqlMaker, logger);
int employeeID = databaseUtil.doInsert(insertSQL);


Unlike UPDATE and INSERT statements, SELECT statements have a much easier time being compatible among different databases. Example:
This code is used to populate a List with all the names of all the Projects.
(Assume the name of the Project is stored in a field called "ProjectName" in the "Projects" table.)

final List projectNamesList=new ArrayList();
AbstractDatabaseQuery d = new AbstractDatabaseQuery("SELECT ProjectName FROM Projects", sqlMaker, cm, logger){
protected void handleResultSet(ResultSet rs) throws SQLException{
String projectName = rs.getString("ProjectName");
projectNamesList.add(projectName);
}
};
d.execute();
System.out.println("The names of the projects: " + d.projectNamesList);


JAnydatabase also provides other features. For example, it will log all your database queries in a standard-fashion. Also, JAnyDatabase will manage your database connections. Establishing a connection to the database can take a long time. JAnyDatabase allows multiple threads to reuse your connections.

Currently JAnyDatabase is joined with TMAJ but it may easily be made into it's own jar file by running the command:
ant jAnyDatabase
This will create the file jAnyDatabase.jar.

Here is how you can get standard classes in jAnyDatabase: DatabaseType databaseType = DatabaseType.MYSQL;
NiceLogger logger = new NiceLogger("C:/temp/logs");
DatabaseParameters databaseParameters = new DatabaseParameters("127.0.0.1", 3306, "TestDatabase", "dba", "secret222");
SQLMaker sqlMaker = SQLMakerGetter.getSQLMaker(databaseType);
String testSQL = "SELECT ID FROM Users WHERE 1 = 0";
ConnectionManager connectionManager = ConnectionManagerGetter.getConnectionManager(databaseType, databaseParameters, logger, testSQL);


The 2 main classes you will use are DatabaseUtil and AbstractDatabaseQuery, as demonstrated in the above 2 examples. You should have a class that creates and initializes a DatabaseUtil class, and then every time a query is needed in your program it will call this instance. In addition, you should subclass AbstractDatabaseQuery and supply all arguments except the SQL. That way, every query in your program will only need to supply the SQL statement.