At the recent
NetBeans Platform Certified Training organized by
Visitrend we were discussing how to work w/ the built in database functionality. NetBeans ships w/ a pretty decent database/SQL functionality out of the box - you can connect to any JDBC compliant database, add your drivers, sling queries, edit the results. It even has code completion for SQL queries - up until SQL Server 2005 it even provided better support for SQL code authoring than the built in Management tools ( and it is still better than the standard Mysql console).
Now, it turns out that for data driven applications, it is a pretty common occurrence that the users need to connect to a database. Sometimes it makes sense to hide the details of the database; at the same time, when you're dealing with sophisticated that have intimate knowledge of the underlying database schema and need to be able to deal with the underlying data, hiding the fact that they're dealing with a database just doesn't make sense. In our case, we have a team of QA Engineers who need to be able to look into all aspects of the database behind the application, so the best a tool can do is to make the setup and access to the database as easy and transparent as possible.
Thus, to solve this problem, my users need the following :
1. Make sure that the IDE has the proper drivers set up to access our test databases
2. Easy setup of the database connection with the details for a specific project/system under tes
Automatically setting up JDBC driverUnfortunately, Microsoft's SQL Server is not one of the JDBC drivers that ships with the IDE. A new user could just navigate to the "Drivers" node in the "Services" top component and walk through the wizard to register a new driver. This can certainly be an extra step in the list of "setup instructions". But, why should a user have to remember to do that if we can do it in a module. Thus, the first hurdle we need to overcome is have a module that automatically registers the JDBC driver in the IDE :
1. First, we need to provide the MSSQL JDBC driver. For that, I created a new Library Wrapper module. I wanted to mention this because for whatever reason when I tried providing the JDBC driver and the XML registration below in the same module, it failed to find the JDBC driver.
In the general case for a pure JDBC driver, this should be enough. However, in order to support windows authentication, the JDBC driver needs to have a dll included on the path. In order to sutpport jars that need native libraries, the native library needs to be placed in the release/modules/lib as indicated on
Modules API Javadoc2. Create a second module for the actual driver registration. Add an XML descriptor for registering new drivers (named SQLServer2008Driver.xml). For MSSQL it looks like this :
<?xml version='1.0'?>
<!DOCTYPE driver PUBLIC '-//NetBeans//DTD JDBC Driver 1.1//EN' 'http://www.netbeans.org/dtds/jdbc-driver-1_1.dtd'>
<driver>
<name value='SQLServer2008'/>
<display-name value='Microsoft SQL Server 2008'/>
<class value='com.microsoft.sqlserver.jdbc.SQLServerDriver'/>
<urls>
<url value="nbinst:/modules/ext/sqljdbc4.jar"/>
</urls>
</driver>
I am not entirely sure of the meaning of the nbinst: prefix, but this works.
3. Add an entry into the layer.xml file of the second module to add the driver registration XML :
Unfortunately, in the case of the MSSQL JDBC driver just adding the dll into the module doesn't cut it as it appears that the SQL Server driver also depends on other DLLs, so it actually needs to be in c:\windows\system32 . Thus, adding the jdbc driver dll would have been not needed, as the dll needs to be copied to windows\system32 directory. To do that, register a module installer :
public class MssqlDriverInstaller extends ModuleInstall {
@Override
public void restored() {
File mssqlJdbcDll = new File(System.getenv("windir"), "system32\\sqljdbc_auth.dll");
boolean foundDll = mssqlJdbcDll.exists();
if (!foundDll) {
FileOutputStream system32MssqlDll = null;
InputStream bundledDll = null;
try {
system32MssqlDll = new FileOutputStream(mssqlJdbcDll);
bundledDll = MssqlDriverInstaller.class.getResourceAsStream("sqljdbc_auth.dll");
System.out.println("Copying sqljdbc_auth.dll to windows system32");
FileUtil.copy(bundledDll, system32MssqlDll);
} catch (IOException ex) {
Logger.getLogger(MssqlDriverInstaller.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
system32MssqlDll.close();
} catch (IOException ex) {
Logger.getLogger(MssqlDriverInstaller.class.getName()).log(Level.SEVERE, null, ex);
}
try {
bundledDll.close();
} catch (IOException ex) {
Logger.getLogger(MssqlDriverInstaller.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}
Automatically connecting to the database
Now, the last step is, how to automate the creation of the database connection node now that we can now be sure that the IDE has the right driver to connect to the database: (the APIs used below are from the Database Explorer API, org.netbeans.api.db.explorer) public void createProjectConnection(DatabaseConfig dbc) {
DatabaseConfig dbConfig = dbc;
if (dbConfig == null) {
dbConfig = DatabaseConfig.getDefault();
}
JDBCDriver sqlSrvDrv = findSqlServerDriver();
if (sqlSrvDrv != null) {
try {
DatabaseConnection dbConn = createDbConnection(dbConfig, sqlSrvDrv);
final ConnectionManager connMgr = ConnectionManager.getDefault();
DatabaseConnection foundConn = findSameConnection(dbConn);
if (foundConn == null) {
foundConn = dbConn;
connMgr.addConnection(dbConn);
}
final DatabaseConnection dbConn2 = foundConn;
RequestProcessor.getDefault().post(new Runnable() {
@Override
public void run() {
try {
connMgr.connect(dbConn2);
connMgr.selectConnectionInExplorer(dbConn2);
} catch (DatabaseException ex) {
Logger.getLogger(Ats3ProjectDataService.class.getName()).log(Level.SEVERE, "Failed to connect to database", ex);
}
}
});
} catch (DatabaseException ex) {
Logger.getLogger(Ats3ProjectDataService.class.getName()).log(Level.SEVERE, "Failed to connect to database", ex);
}
}
}
private DatabaseConnection createDbConnection(DatabaseConfig dbConfig, JDBCDriver sqlSrvDrv) {
DatabaseConnection dbConn;
String url = null;
String userDb = dbConfig.getName();
if (userDb != null) {
if (userDb.contains("${username}")) {
userDb = userDb.replace("${username}", System.getProperty("user.name"));
}
} else {
userDb = System.getProperty("user.name") + "_sb_rc";
}
if (!dbConfig.getUseSqlAuth()) {
url = String.format("jdbc:sqlserver://%s:1433;databaseName=%s;integratedSecurity=true", dbConfig.getServer(), userDb);
dbConn = DatabaseConnection.create(sqlSrvDrv, url, "", "dbo", "", true);
} else {
url = String.format("jdbc:sqlserver://%s:1433;databaseName=%s", dbConfig.getServer(), userDb);
dbConn = DatabaseConnection.create(sqlSrvDrv, url, dbConfig.getUser(), "dbo", dbConfig.getPassword(), true);
}
return dbConn;
}
private JDBCDriver findSqlServerDriver() {
JDBCDriver sqlSrvDrv = null;
JDBCDriver[] drivers = JDBCDriverManager.getDefault().getDrivers("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// we know that there should be at least one as this module registers it
for (JDBCDriver drv : drivers) {
if ("SQLServer2008".equals(drv.getName())) {
sqlSrvDrv = drv;
}
}
return sqlSrvDrv;
}
private DatabaseConnection findSameConnection(DatabaseConnection dbConn) {
DatabaseConnection foundConn = null;
ConnectionManager connMgr = ConnectionManager.getDefault();
for (DatabaseConnection dbc : connMgr.getConnections()) {
if (dbc.getDatabaseURL().equals(dbConn.getDatabaseURL())) {
foundConn = dbc;
}
}
return foundConn;
}
Posts
