Do I use singleton, session bound, connect on demand, etc?
Why use a singleton? What is it about your application that needs to ensure there is only one instance of a connection (or one instance of whatever object manages connections)?
session bound? I'm assuming by this you mean one connection per session? This kind of makes sense, except your database might provide far fewer connections than active sessions. Also is every session actively using the database? What about sessions that are currently unused and just waiting to time out?
connect on demand. By this I assume you mean every time a bit of your code needs a connection it just creates one? This is the most sensible approach - however, I'd suggest you look at using a connection pool. This way, from your coding point of view you just grab a connection when you need it, but your implementation handles the connections in a more prudent way.
The "best" way to connect to a database is determined by the requirements and size of the application. Once you analyze these factors and a few others, you should be able to determine the "best" way for your application.
Take note, that this "best" way only applies to your application.
That said, I suggest that you review the Hibernate Framework. Using the Hibernate Framework to connect to a database is a very good strategy. It may work well for your application.