Yes I’m muttering about relational database connections AGAIN (prepared to be bored to within an inch of your life), and why you ask, because as a domino person who now makes most of his money from none domino projects, as is my current team leader, we are used to database connections being something you just ask for and then forget, they are always there, they don’t time out and they handle them selves quite beautifully, not like the java lot, for all you “domino is old and naff” lot, i’ll take you on with database connection any day of the bloody week, anyway enough ranting.
Technologies used/supported:
Jboss 4.0.5,
MYSQL 5,
hibernate 3.2
C3p0 1.7.2
Java 1.6
These kind of issues (including the Quartz based one I’m doing an article on later), all come about because we are trying to do things properly, and by properly i mean the same .War file works the same all the way from dev to Live, because it gets its connection info from con-fig documents on the server/db rather than hard coded into the app
Soooo
Now your all used to seeing a persistence.xml or hibernate.cfg.xml file that has stuff in it like this
<property name=”dialect” value=”org.hibernate.dialect.MySQLDialect” />
<property name=”hibernate.connection.driver_class” value=”com.mysql.jdbc.Driver” />
<property name=”hibernate.connection.url” value=”jdbc:mysql://127.0.0.1:3306/SETTINGS” />
<property name=”hibernate.connection.username” value=”root” />
<property name=”hibernate.connection.password” value=”rootpassword” />
(if you not, then tell me in the comments and Ill do an “from basics” blog)
All well and good and fine for dev and such, but not what you want in your actual distribution, what you want is something that just says “can i have the ‘setting db’ please”, and for that you need a ‘data source’, so swap out all the database specific lines for the following
<jta-data-source>java:/MYDATASOURCE-DS</jta-data-source>
Then create an xml file (mines called “data-source.xml” in your deployments directory, in my servers case “d:jboss4.0.5serverdefaultdeploy”, and put the following in, filling in the correct gaps
<?xml version=”1.0″ encoding=”UTF-8″?>
<datasources>
<local-tx-datasource>
<jndi-name>MYDATASOURCE-DS</jndi-name>
<connection-url>jdbc:mysql://127.0.0.1:3306/SETTINGS</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>rootpassword</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
<metadata>
<type-mapping>mySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
You will need to put the appropriate driver *.jar file in the lib directory of your server (rather than in your app deployment .war file), in my case “d:jboss4.0.5serverdefaultlib mysql-connector-java-5.1.7-bin.jar”, restart your server and ta-da!, you suddenly have a server level data source.
Now in a sane world this should be all you need, but “i don’t think we are in Domino any more toto”, we are in the land of java, you will get lots of weird things happening from time to time but if you are using MYSQL the first one you will encounter is MYSQLs default timeout period of 8 hours, i.e. if you don’t do something every 8 hours the server will chuck you off and refuse to let you back on, unless you redo your whole connection, which in the case of a data source means a server reboot, ARSE!, one solution is to extent the timeout to the max you could want, but that stinks as a solution, a better solutions is to introduce connection pools, which means we introduce yet another layer of abstraction, this one designed to maintain control of the load a application puts on its server and to ensure those connections are available to the application, we will be using c3p0, not only is it the most popular one but its also has the daftest name (we are a star trek house).
First thing is to download c3p0 (it does come with hibernate but for purposes of instructions we will do it from scratch) from http://sourceforge.net/projects/c3p0/ and copy c3p0-x.x.x.x.jar to the same lib directory as you put your database driver, next you need to REPLACE the existing data source file with a new xml file (my new one is called “data-service.xml”) containing the below:
<?xml version=”1.0″ encoding=”UTF-8″?>
<server>
<mbean code=”com.mchange.v2.c3p0.jboss.C3P0PooledDataSource”
name=”jboss.jca:service=DataSourceBinding,name=MYSQL-DS”>
<attribute name=”JndiName”>java:MYDATASOURCE-DS</attribute>
<attribute name=”JdbcUrl”>
jdbc:mysql://127.0.0.1:3306/SETTINGS
</attribute>
<attribute name=”DriverClass”>com.mysql.jdbc.Driver</attribute>
<attribute name=”User”>root</attribute>
<attribute name=”Password”>rootpassword</attribute>
<!– <attribute name=”AcquireIncrement”>3</attribute> –>
<!– <attribute name=”AcquireRetryAttempts”>30</attribute> –>
<!– <attribute name=”AcquireRetryDelay”>1000</attribute> –>
<!– <attribute name=”AutoCommitOnClose”>false</attribute> –>
<!– <attribute name=”AutomaticTestTable”></attribute> –>
<!– <attribute name=”BreakAfterAcquireFailure”>false</attribute> –>
<!– <attribute name=”CheckoutTimeout”>0</attribute> –>
<!– <attribute name=”ConnectionTesterClassName”>0</attribute> –>
<!– <attribute name=”Description”>A pooled c3p0 DataSource</attribute> –>
<!– <attribute name=”FactoryClassLocation”></attribute> –>
<!– <attribute name=”ForceIgnoreUnresolvedTransactions”>true</attribute> –>
<attribute name=”IdleConnectionTestPeriod”>300</attribute>
<!– <attribute name=”InitialPoolSize”>3</attribute> –>
<attribute name=”MaxIdleTime”>600</attribute>
<!– <attribute name=”MaxPoolSize”>15</attribute> –>
<!– <attribute name=”MaxStatements”>0</attribute> –>
<!– <attribute name=”MaxStatementsPerConnection”>0</attribute> –>
<!– <attribute name=”MinPoolSize”>0</attribute> –>
<!– <attribute name=”NumHelperThreads”>3</attribute> –>
<!– <attribute name=”PreferredTestQuery”></attribute> –>
<!– <attribute name=”TestConnectionOnCheckin”>false</attribute> –>
<!– <attribute name=”TestConnectionOnCheckout”>false</attribute> –>
<!– <attribute name=”UsesTraditionalReflectiveProxies”>false</attribute> –>
<depends>jboss:service=Naming</depends>
</mbean>
</server>
The reason you replace the xml file, is that you will most likely be using the same data source name, and they have to be unique to the server, as you can see i have put all of the possible attribute names in the example as they are not documented very well and it was a bugger to find them, but commented them out, you should only really need “MaxIdleTime” and “IdleConnectionTestPeriod”, make sure its set to less than 8 hours (or what ever your db server timeout is), now things should just work after a restart, but you will get a bit more ‘static’ on your console, as c3p0 chatters a bit to the console,
there you go another riveting read, yell if bits are unclear