How to access the database from within a component or plugin?

hi,

I’'m developing a plugin that need to store information to the database WF is using, e.g.: creat new tables and input and extract and delete information in those tables.

I’‘d like to take advantage of WF’‘s database connection pool (if it exists). could anybody provide any info on how to write the code? I’'ve looked at the javadoc and got new usable clue.

or, should I abandon this idea all together and use a separate JNDI/DBCP (e.g.: jakarta commons DBCP) to connect to the database?

thank you for any ideas

Hi,

Have a look at the Plugin Developers Guide. It explains how to include SQL scripts to your plugin. You can use those to create and update the database-schema your plugin needs.

To make use of your database entries from your code, you can make use of the DBConnectionManager class. You can use the .getConnection() method to get a new SQL connection, which you can use to prepare statements on, or perform other database actions.

hi guus,

thanks for your input.

relating to database access, all I can find in the plugin development guide is a brief intro to ‘‘databaseKey’’ entry in the plugin.XML configuration file. I’'m a novice here, so a few more questions:

a). Seems the ‘‘databasekey’’ s only purpose is to let WF know and track the version of database the specific plugin is using. are there any other purposes?

b). the script file (.sql) only need to contain one line?: INSERT INTO jiveVersion (name, version) VALUES (’‘foo’’, 0);

c). how to get a reference to the Dbconnectionmanager in my code?

d). if I’'m concerned with connection pooling, shall I use connectionpool.getconnection() ? Or, is Dbconnectionmanager is already taking advantage of the connectionpool?

thank you in advance

Hi,

I’'ll start with (a) and (b).

The script file that goes into your plugin ( in the , 0);’’ query in that file. The jiveVersion table is used by Wildfire to make sure that the last version of the database schema is installed. My tip is to include it as the last statement in that file, so that it only gets executed when all other statements have succeeded.

Now, when you want to make changes to your database structure (say, include a column or add an index), you create a new directory in the /src/database/ directory. You’‘ll have to name the first directory 1 (’‘one’’), the second 2, and so on. In such a directory, you include similar scripts as you did in the base directory. Instead of recreating all tables, you include scripts that change your existing table.

Finally, increase the databaseVersion value in plugin.xml. If that databaseVersion value is higher than 0, Wildfire will look which version it has installed (by querying the jiveVersion table), and executes the scripts in the subsequent directory names. (Executing the script in directory database/1/ should therefor increate the number in jiveVersion to ‘‘1’’).

What you did up to this point is make sure that Wildfire creates or updates the database structure you want to use.

Now for question ©.

You should import org.jivesoftware.database.DbConnectionManager in your plugin class. After that, you can do something like this:

java.sql.Connection con = null;

java.sql.PreparedStatement ps = null;

try

{

con = DbConnectionManager.getConnection();

ps = con.prepareStatement

(“SELECT attribute FROM mytable WHERE otherattribute = ?”);

java.sql.ResultSet rs = null;

for (int value=0; value<5; value++ )

{

ps.setInt(1, value);

rs = ps.executeQuery();

while (rs.next())

{

… do something with your query result

}

rs.close();

}

ps.close();

}

catch (SQLException ex)

{

Log.warn

(“MyPluginName: Unexpected database error!”, ex);

}

finally

{

try

{

if (con != null)

{

con.close();

}

}

catch (SQLException ex)

{

Log.warn

(“MyPluginName: Couldn’'t close the connection!”, ex);

}

}

(I didn’'t compile this code, forgive me any errors).

As for your last question (d), the DbConnectionManager makes use of the connection pool you configured when you performed the initial setup of Wildfire. You can reconfigure those settings in wildfire.xml, but you probably have to restart the server for those changes to take effect.

hi guus,

thank you very much for this very clear explaination.

however, I’'m curious that, if I set up the database using scripts in

3). write java code like you showed me, in the plugin, test and run

please tell me if this plan will work.

some questions remain besides above mentioned.

my database schema requires username and password to access, do I configure this in wildfire.xml to tell Dbconnectionmanager?

Hi,

Each time wildfire loads your plugin (which will happen after you start Wildfire, restart the plugin or update the plugin), Wildfire will look at your plugin.xml to see which version (0, 1, 2, 3) of the database schema the plugin needs. After that, it will look in the jiveVersion table to see what version it has installed. So, if you include a statement in those scripts that update the jiveVersion table, those scripts will be executed only once, no matter how often you restart the plugin.

I used a manual approach myself first. That worked fine, until I had to re-deploy the server. Figuring out what database relation went where was very time-consuming. Creating scripts as described in the postings above are a small investment in time which in my case saved me a lot of time later on.

Using the technique described, Wildfire will need to be able to access your database schema’‘s with the username and password you configured during the setup of Wildfire. They’'re stored in the wildfire.xml configuration file (in the conf directory).

that’'s great! thanks guus

though I will try that later (wildly busy lately), I feel this perfectly answered my questions.

maybe they should put this thread in FAQ along with the plugin development guide