Java DB connections in the real world

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

The Writing is on the Wall (Training Pt 1)

On with my preparations for the suspected tapering off of my role at my current main client, first part…TRAINING

Training is one of the things that all techs like to do differently, personally i like a combination of videos i can have on repeat in the background and building practical apps to solve problems. but their are some core bits that i recon that everyone on a training frenzy should have

1) vmware workstation (or virtual box or parallels or what ever rocks your boat), if your not using virtual machines, then start!!, with them you can mess about and try things, then bin them when they go wrong, top tip: build a ‘core machine’ and clone it each time you want to play with something new, that way you don’t waste time building up the base OS each time you want to try something.

2) http://www.safaribooksonline.com , the cheapest and best way to get at all the technical books you could want for one price, they also have a really good line in videos included in the price, yes i know all about the practical side being the important, but as i have discovered with flex and spring, that just because you are making it work does not mean you are doing it right, so OFFICIALY RATIFIED base theory is needed as well (especially for interviews), understandably safari had “stuff all” new Domino training material, but all is not lost (more on that later),

now on the to individual things i have to learn

Flex 4 and Adobe Air

Thankfully this is as easy as ever with adobe really leading the way when it comes to documentation, all varieties of learning stuff can be found at http://flex.org/resources with my personal fav being the “flex in a week” video and work sheets (already updated for flex 4) found at http://www.adobe.com/devnet/flex/videotraining/

Xpages

Next up is xpages, now i hear there is a book coming form the powers at IBM, but alas i cant wait, and actually i don’t have to (ha ha!), because there is good resources out there from the community, the 2 important ones being:

http://xpages101.net/ (a paid for video training site produced by Matt White who co wrote IBMs internal xpages training course,and has presented on xpages at every ‘lug’ and notes conference you can mention, the content is excellent though as i know Matt as a mate I’m always the expecting him to say at the end of each lesson “so that’s done, shall we go for a pint”),

http://xpagesblog.com/ (a community technical blog that is stuffed full of juicy xpages bits and an excellent read)

There is also a spectacular 40+ series of xpages articles that i have lost the link to (will dig up and update here)

Granite Data services

This is just a “just build the darn thing and make it work” job but to my relief the guys and gals at http://www.graniteds.org have written very good documentation thank you, found at http://www.graniteds.org/confluence/display/DOC/Documentation+-+2.1

Jquery

God damn where to start, hundreds of good sources, lots of them free, but of them all i ended up with the purchased book “JQUERY: NOVICE TO NINJA” found at http://www.sitepoint.com/books/jquery1/ then worked my way thought the some usefull looking jquery pluggins taking them apart ( http://www.gmarwaha.com/jquery/jcarousellite/ and http://bassistance.de/jquery-plugins/jquery-plugin-validation/ )

Spring

This is such a hideous subject, that you just have go to http://www.safaribooksonline.com and look thought all the myriad spring books they have until you find one you like the style off, then sit down with a pack of head ache tablets and grind your way through it, you will swear a lot, then suffer an moment of Epiphany after which its just a case of filling in the mad java developer “lets make this elegant” gaps.

Adobe illustrator

Strangely Adobe aren’t as good on this as they are on flex (maybe because they have everyone by the short an curlies on it as a market leader) so its back to http://www.safaribooksonline.com where there is a excellent video series and the normal good books, practice practice party people

Android dev

This is a bit like jquery in that there is simply tones of content, I’m going to use a mixture of http://developer.android.com and http://www.safaribooksonline.com content combined with practice apps ( although as i don’t have a android phone I’m using the a vmware image of the live android image http://code.google.com/p/live-android/, thanks to the wretch for pointing this one out)

Right, back to work!!

Fitness Week 4

2 weeks without a technical article just updates on the feeble efforts of the ‘Green’ and I trying to not be spherical is poor, very poor, but the stalemate holds, however the viciousness increased, with Mr Green giving me a savage kicking on Tuesday at squash and me returning the favour in spades today, Ha!!,

First person to win 250 squash games
Kieran: 7 Games
Mark: 19 Games
(1 point head start on a game for every 10 games the other person is ahead)

First person to loose 2 stone
Kieran ( Initially 15 Stone, 8 pounds) : 15′ 2″
Mark ( Initially 16 Stone, 8 pounds) : 16′ 7″ (still no change boo hoo!)

I think Mr Green has installed a tape worm for his weight loss!!

Fitness Week 3

Still a draw, i do however think Mr Green is being unfair by trying to eat properly and moving up to maybe 6 sessions a week

First person to win 250 squash games
Kieran: 3 Games
Mark: 15 Games
(1 point head start on a game for every 10 games the other person is ahead)

First person to loose 2 stone
Kieran ( Initially 15 Stone, 8 pounds) : 15 3″
Mark ( Initially 16 Stone, 8 pounds) : 16′ 7″ (sobs!!)

The Writing is on the wall (The initial conclusion)

As a contractor you know in the back of your mind that nothing can last, but humans tend to prefer a warm fluffy feeling and try to forget this fact, however my largest client has taken on a large outsourced company (its pretty much a run of the mill outsourcing, nothing that reflects badly on the company, I’m presuming its being done so that someone somewhere gets a tidier company org chart as in the long term it sure as hell is not going to save them money, i heard the phrase “strategic partnership” and thats never a good sign), unsurprisingly said company has moved to ‘acquisitive’ mode as soon as they had their foot in the door (to be fair they would be daft if they did not), this means that i suspect that time is limited (hence the countdown timer on the side of this page).

I however have more time (hopefully) to cater to this movement than lots of other people do in similar situations, and my CV is nearly as i would want it, but it could do with a bit more shinning, will therefore be trying to get the following down pat in the next few months, even if I’m wrong such a exercise wont do any harm at all.

Need to get a grip on
Granite Dataservices
Flex 4
get better at Xpages (compared to my other domino dev, i suck)
get better at Jquery (i use it but don’t UNDERSTAND it)
get better at Spring (i have practical knowledge but would suck at a formal interview)
Adobe illustrator (im ok in a “its C.A.D in colour” way, but want it for the flex 4 skinning and chibi images which means i need to be better)

Time Permiting
Android dev
Adobe Air

Its a lot of work but i have 160 days, now i just have to stop working for clients all the time so i have time to learn something (mixed blessing)