Archive for the ‘jdbc’ Category

The * stupidest things I’ve done in my programming job

Saturday, February 7th, 2009

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.

Database Connection Pooling

Friday, February 23rd, 2007

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:

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

DataSource exposed through JNDI

Friday, February 23rd, 2007

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.