DBCommAPINB
Tutorial | DBExplorer Without The Explorer
Doc Contributor; Bret Fisher
[[{TableOfContentsTitle=TableOfContents} | {TableOfContents title='Table of Contents'}]]
Introduction
NOTE:
The Arrays6 source code was removed because I am using Mac OS which is not up to JDK 6 yet.
This is a Netbeans Standalone application that backs up the structure of the database you supply in the connection. You can either use the software or the Explorer to create the connection to the database. The author intends to provide you the code, which is needed to explain how to use the library. (See the Notes). You may also download the attachment DBApp_DBCommAPINB.zip listed at the end of this page.
You can see a image of the files included in the library, below.
Getting Started
Once you get the application compiled and running, go to the Tools menu and select the Connection Manager menu option. This will allow you to add a database connnection. NOTE: This program only supports a MySql (org.gjt.mm.mysql.Driver) database connection. Although with very little effort you could fit to any other database. Once a connection is established with this manager OR the Netbeans Explorer. You can use the other menu option provided by the application, i.e. Tools > Backup Database
What does this Dialog do?
When the dialog first opens it will try and get the name of the database and put it into the banner at the top of the dialog. Also it will put the version info of the database server in the first line of the ouput console area. Once this is done the start button will enable and the done button will be disabled. Such as above. Clicking on the start button will display the structure of the database tables listed in the database. That is all of this examples functionality. Now lets go over the code so you can discover how to use this code for other purposes.
Source Code
This is the source code for the constuctor of the Backup Database form in the SQL Backup module.
initComponents();
makeActions();
// Normally you wold extract this from the connection but since
// this only supports MySql we hard code it here.
String connectionType = "MySql";
String text = NbBundle.getMessage(BackupForm.class, "BackupForm.title.text");
text = MessageFormat.format(text, (Object[[ | ]]) new String[] {connectionType});
title.setText(text);
// this code is called to get the name of the database.
// it displays this in the banner area of the dialog.
AbstractClient abc = new AbstractClient() {
String statictext = null;
@Override
public void analizeResults(ResultSet rs) throws SQLException {
try {
if (rs.next())
statictext = rs.getString(1);
}
catch (Exception e) { statictext = null; }
}
@Override
public void finished() {
// sets the database name.
if (statictext != null)
databaseLabel.setText(statictext);
// displays the version info of the server
info = new JDBCInfo(getConnectionUsed());
println(info.toString() + "\n");
startAction.setEnabled(true);
}
@Override
public Object buildQuery(Statement statement) throws SQLException {
return "SELECT DATABASE()";
}
@Override
public Object runQuery(Statement statement, Object q) throws SQLException {
return statement.executeQuery(q.toString());
}
@Override
public void reportError() {
// this reports the error to the Ouput window
super.reportError();
// make sure we can exit but cannot start because of
// the error. Note you still have to test for a error
// here because this method is also called for warnings
if (hasError()) {
doneAction.setEnabled(true);
startAction.setEnabled(false);
}
}
};
final NBConnection nb = NBConnectionManager.getDefault().establishConnection(abc, new Factory());
Code Explanation
- The
AbstractClient
class is the class you override to define the actual functionality that you want to do with the database transaction. The first function you need is {buildQyery}.
- This function returns an
Object
, usually a {String}. Since you also define the function that uses the Object this function returns namely the runQuery function you can supply whatever Object you wish. This example returns a String with which the runQuery function simply executes as a query. It returns the ResultSet from the query.
- NOTE: If this query was an update or batch query you would return an Integer or Array of int objects.
- Since this function returns a
ResultSet
it calls the function {analizeResults} to analyze the result set. It stores the results in a local instance variable which is used in the finished routine to update the UI.
- NOTE: The finished routine will never be called if an error occurs. If you want to catch the error branch you need to override the reportError function.
- The superclass will ouptut the error info to the Output window. In our function we change the enablement of the done button so the user can exit.
- The entry point is the last call
NBConnectionManager.getDefault().establishConnection(abc, new Factory());
. You pass it your handler and an instance of a thread factory to create threads. This allows you to handle, for instance, disallowing the user to quit the application or do other things while the Threads created by this factory are present.
Coding Continues...
This class is also defined in the form -
class BackupDBRunnable implements Runnable {
final AbstractClient TableNamesClient = new AbstractClient() {
@Override
public void analizeResults(ResultSet rs) throws SQLException {
StringBuffer buf = new StringBuffer();
while (rs.next()) {
String tablename = rs.getString(1);
tabels.put(tablename, new Vector<Hashtable<String, String>>());
}
}
@Override
public void finished() {
if (tabels.size() == 0) {
println("NO Tables in database!");
doneAction.setEnabled(true);
return;
}
final NBConnection[[ | ]] nb = new NBConnection[Tabels.size()];
Enumeration<String> enum0 = tabels.keys();
int steps = 100 / tabels.size();
// NOTE: These calls are asynchronous so they may not be executed
// in the order they are called here. Also note that when one of
// the calls is executing the finished routine the others will either
// still be running or waiting on the event thread to execute their
// finished call.
int conncount = 0;
while (enum0.hasMoreElements()) {
try {
String rs = enum0.nextElement();
ColumnVerifier client = new ColumnVerifier(rs, steps);
nb[Conncount++] = NBConnectionManager.getDefault().establishConnection(client, new Factory());
} catch (Exception ex) {
ex.printStackTrace(IOProvider.getDefault().getStdOut());
}
}
/*
this code will generate an error because the connection has probably
not even been established by the time this code executes.
String s = generateTableCreateCmds(((AbstractClient) nb.getClient()).getConnectionUsed());
println(s);
*
this code will generate a deadlock
since the connection is established in the
event thread you have just blocked it and the
connection will never be established.
* //
nb.sleepUntilDone();
* */
// this is the correct way. You cannot rely on waiting on the
// last connection in the loop above since it may not be the
// last query to finish. So we have to wait on each connection
// to finish. Once that is done we can be sure we have the data
// we asked for assuming no errors occurred. Remember finished
// never even gets called if a error occurs. Warnings yes errors
// no
new Thread(new Runnable() {
public void run() {
NBConnection connector = null;
int failures = 0;
try {
for (NBConnection conn : nb) {
conn.sleepUntilDone();
if (conn.getClient().hasError()) failures++;
}
connector = nb[0]; // this should be ok we only want the connection used
} catch (InterruptedException ex) {
Exceptions.printStackTrace(ex);
} catch (IllegalAccessException ex) {
Exceptions.printStackTrace(ex);
}
// if any failures occurred the default code
// has already reported the errors so we just
// need to dump out here.
if (failures != 0) {
doneAction.setEnabled(true);
return;
}
// if it is supported use the server to generate the info
if (info.getMajorVersion() < 3.23f || (info.getMinorVersion() < 20 && info.getMajorVersion() == 3.23f)) {
// This generates the commands for earlier versions.
String s = generateTableCreateCmds(((AbstractClient) connector.getClient()).getConnectionUsed());
println(s);
} else {
// use the server to generate the queries that created the
// databases. NOTE: The query will probably not execute correctly
// if used on a older version of the database.
String s = queryTableCreateCmds(((AbstractClient) connector.getClient()).getConnectionUsed());
println(s);
}
// update the progress bar we are done..
progressBar.setValue(100);
doneAction.setEnabled(true);
}
}).start();
}
@Override
public Object buildQuery(Statement statement) throws SQLException {
return "Show tables";
}
@Override
public Object runQuery(Statement statement, Object q) throws SQLException {
return statement.executeQuery(q.toString());
}
@Override
public void reportError() {
super.reportError();
if (hasError()) doneAction.setEnabled(true);
}
};
public void run() {
NBConnectionManager.getDefault().establishConnection(TableNamesClient, new Factory());
}
}
More Explanation
The following line of code is used to run thisclass EventQueue.invokeLater(new BackupDBRunnable());
- This causes it to run in the event thread which is not really necessary but I did it to prove it can be done. The one thing you have to watch out for is not to freeze or block the event thread. The reason for this is that Netbeans and my library use the event thread to obtain the connection. If the connection requires a password it will bring up a modal dialog and ask for the password.
- For this reason you should not block the Event Thread or it will be hopelessly deadlocked. Pay special attention to the finished function, it shows in the comments some of the things that come up beacuse of the multithreaded nature of the library. In the finished routine it will kick off several other connections that get information about the tables in the database.
- This occurs in a loop that basically starts several threads. Each time the line
nb[[conncount++] = NBConnectionManager.getDefault().establishConnection(client, new Factory());
executes, it creates at least 2 threads and returns immediately. This means you have to be able to wait until all these threads are done to work with the results. - Since we are in the event thread, the finished routine always executes in the event thread, we cannot block. Also note that each of these calls may start and end in a different order than they were called.
- The next section of code will wait on each connection to finish. Notice this is done in a seperate thread in order not block the event dispatch thread. The code then proceeds to use the connection to ouput the SQL statements needed to recreate the database.
