Let me throw out a couple of quick responses, and others can add details.
1) The "main" way of getting a Connection is:
The "getConnection" method is overloaded so you can ignore the user and password if they are not required (they may be passed as part of the URL), or you can pass in optional database-specific parameters. Check the
java.sql.DriverManager API for details.
The main alternative to this is the use of some sort of connection pooling. You can look at
Jakarta DBCP for an example of this, though there are several similar APIs available. The idea is that you need to have a pool of connections that can be shared among many users. So the pool is initialized and then when you call the pool for a connection. It may hand you back an existing connection, or create a new one, or return null, depending on how it's been configured. This is increasingly common with web applications.
2) I won't necessarily give you a detailed explanation for the differences between Statement and PreparedStatement, mainly because there is a ton of stuff on the WWW about this. Start with
Sun tutorial and go from there.
But the basic idea is that a "Statement" can be used once and thrown away, or used to execute many different queries. A "PreparedStatement" can be used to execute the same query many times. A "PreparedStatement" is pre-compiled so there is more set-up time when it's instantiated, but it takes less time to execute after that.
If you search the WWW you can find a lot of articles written by people who have profiled the use of "PreparedStatement" vs. "Statement" and the performance penalty or gain differs from database to database and from usage to usage.
However one other big advantage to the use of "PreparedStatement" is that you don't have to worry about how the underlying database handles
String delimiters or time/date formats. If you work with large text strings that can contain nested single- or double-quotes or other non-standard characters, or if you do a lot with date/time stamps, I would recommend using "PreparedStatement" unless there is too much of a performance penalty.
3) You'll get an exception. You MUST do a "next()" on the ResultSet before accessing the results.
[ March 08, 2004: Message edited by: Wayne L Johnson ]