ioannis cherouvim

I’m not ashamed of those sins any more, so here you go :)

1. ORM

Stupidity

Building my own Object Relational Mapping framework.

Consequence

Project is a mess after 2 years of maintenance with hardcore hacks to bypass my own ORM and call custom SQL queries.

What should I have done

Use hibernate, iBATIS, Cayenne or something similar.

2. EAV

Stupidity

Using an Entity-Attribute-Value model database schema design.

Consequence

Non scalable solution and total impossibility to run any useful queries on the database level.

What should I have done

Use an ordinary normalized database schema design.

3. Database Access

Stupidity

Synchronize (serialize) database access using one shared connection.

Consequence

Zero scalability. Very slow response times when more than 10 users where using the application.

What should I have done

Don’t do that and use a connection pool such as c3p0 and use a “new” (reused) connection returned from the pool for every request/response cycle.

4. IDE

Stupidity

Avoided learning and using an Integrated development environment.

Consequence

Inability to build test and deploy the application quickly and generally do anything useful.

What should I have done

Get familiar with an IDE. NetBeans, eclipse etc.

5. Transactions

Stupidity

Not using them.

Consequence

Corrupt data in an application involving e-shop like functionality.

What should I have done

Use database transactions. When in MySQL use InnoDB.

6. Prepared Statements

Stupidity

Using Statements, string concatenation and naive character escaping to assemble my own “safe” queries.

Consequence

SQL Injections possible in my application. I managed to login using ‘or 1=1;delete from users;– and alter the database state in a very nasty way.

What should I have done

Use Prepared Statements which correctly assemble and escape the query properly depending on the JDBC driver used.

7. Business Logic

Stupidity

Doing it in the template (JSP).

Consequence

Messy non maintainable application.

What should I have done

Do it in an MVC style with servlets or with a Front Controller. Even better by using an existing open source MVC framework such as Struts, Spring MVC etc.

Of course, all the bad choices above have probably made me a better programmer.

You are building a webapp. You want database connectivity. You want pooling (because its an expensive resource). You start building your own database connection pool. STOP!!!

Who told you that you can do it well? Why did you hack your own connection pool implementation, which is seriously broken, spawns thousands of threads and turns the server into a miserable piece of shit that needs restart every 24h?

Wrong choice my friend. Next time do us all a favour and use one of the following:

• DBCP
• c3p0
• proxool

And don’t forget: The standard idiom for releasing a connection is to close (return) the connection in a finally block.

Connection con = getPooledConnectionFromSomewhere();
try {
  // do stuff with connection
} catch (SQLException e) {
  // handle problems
} finally {
  con.close();
}

Of course, closing the connection can throw an SQLException, but it’s up to you on how you will handle it.

Good luck

You are building a webapp and you want database connection pooling. Your container can help you manage this javax.sql.DataSource by configuring it and exposing it through the JNDI tree.

Containers usually come with Jakarta Commons DBCP out of the box. In order to use it edit the context.xml file of your webapp and set your datasource there.

<?xml version="1.0" encoding="UTF-8"?>
 <Context path="/foo-app">

 <!-- TOMCAT 5.5.xx DESCRIPTOR -->
 <Resource name="foo"
  auth="Container"
  type="javax.sql.DataSource"
  maxActive="1"
  maxIdle="1"
  maxWait="3000"
  username="user"
  password="pass"
  driverClassName="net.sourceforge.jtds.jdbc.Driver"
  url="jdbc:jtds:sqlserver://localhost:1433;DatabaseName=foobar;charset=utf8"
 />

 <!-- TOMCAT 5.0.28 DESCRIPTOR -->
 <!--
  <Resource name="foo" type="javax.sql.DataSource"/>
  <ResourceParams name="foo">
   <parameter><name>maxActive</name><value>1</value></parameter>
   <parameter><name>maxIdle</name><value>1</value></parameter>
   <parameter><name>maxWait</name><value>3000</value></parameter>
   <parameter><name>username</name><value>user</value></parameter>
   <parameter><name>password</name><value>pass</value></parameter>
   <parameter><name>driverClassName</name><value>net.sourceforge.jtds.jdbc.Driver</value></parameter>
   <parameter><name>url</name><value>jdbc:jtds:sqlserver://localhost:1433;DatabaseName=foobar;charset=utf8</value></parameter>
  </ResourceParams>
  -->

</Context>

Tomcat 5.0.xx and 5.5.xx uses different xml syntax for most of it’s configuration. Here I’m presenting both with 5.0.xx’s block commented out.

So, you’ve got your app called “foo-app”. Next time you’ll deploy it, tomcat will copy context.xml to ${catalina.home}/conf/localhost/foo-app.xml where from it will be reading the configuration on each context or container initialization. Your Datasource is called “foo”.
In your java code now, what you need to get a reference to “foo” is:

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/foo");

And then something along the lines:

Connection con = ds.getConnection();

Happy coding.