Posts
Before we can do any database related operation like queries or stored procedure calls, we first establish a connection to the database. Fundamentally there are 2 ways you can connect to database. Here I will talk about how we connect through JDBC type-IV drivers, because type-IV are what we use most of the time.
1) DriverManager
2) DataSource
Before we start our discussion on how to make a connection, first we make some setups right.
1) Make sure that you have appropriate JDBC driver available, which is generally a jar file. e.g. classes14.jar for oracle, jconnect.jar for sybase etc.
2) Make sure this driver jar file is in your classpath. e.g. in Eclipse you can add the jar file in your classpath like following
I am listing below the JDBC driver download locations. Please choose the appropriate one for your requirements if you don't have the one.
To add the JAR to the project classpath. Right-click on the project and select "Properties". Go to the "Libraries" tab in "Java Build Path" and click "Add External Jars" button
Select the relevant driver jar Click "OK"
Thats it!!!
Defining the Connection URL is the key to connect to the database successfully. The connection URL basically consist of 3 parts
a) The database hostname e.g. localhost
b) The databse port number e.g. 1531
c) additional info e.g. database name for mysql or serviceId for oracle.
The connection URLs are different for different drivers and databases. you are adviced to always consult the driver documentation to know the connection URL formats.
Generally the formats for different database drivers are follwoing.
Oracle :
JDBC Driver : oracle.jdbc.OracleDriver
JDBC Connection URL : jdbc:oracle:thin:@<host_name>:<port_number>:<database_name>
Click here for more help
My-SQL :
JDBC Driver : org.gjt.mm.mysql.Driver
JDBC Connection URL : jdbc:mysql://<host_name>:<port_number>/<database_name>
Click here for more Help
Sybase :
JDBC Driver : com.sybase.jdbc3.jdbc.SybDriver
JDBC Connection URL : jdbc:sybase:Tds:<host_name>:<port_number>/<database_name>
Click here for more Help
MS-SQL :
JDBC Driver : com.microsoft.jdbc.sqlserver.SQLServerDriver
JDBC URL : jdbc:microsoft:sqlserver://<host_name>:<port_number>;DatabaseName=<database_name>
Click here for more Help
The next is the username/password information to establish the JDBC connection.
Once we have all these things in place we can start with writing programs for database operations.
A) JDBC Connection using DriverManager:
1) Register the Driver: Registering the driver instructs JDBC Driver Manager which driver to load. We should know the driver class beforehand and I have listed a few already.
We either use Class.forName() or DriverManager.registerDriver().
2) Call DriverManager.getConnection(String connectionURL, String username, String password) to get the JDBC connection.
The following sample code demonstrates how to register the driver and get the connection for different databases:
MS-SQL :
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=databaseName", "userName", "password");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=databaseName", "userName", "password");
Oracle :
e.g. if serviceId is orcl (default) and port number is 1521
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@hostname:1521:orcl", "scott", "tiger");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@hostname:1521:orcl", "scott", "tiger");
My-SQL:
Class.forName("org.gjt.mm.mysql.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://<host_name>:<port_number>/<database_name>", "sa", "sa");
One of the biggest mistakes we make in our JDBC development is to hardcode configuration information. While everyone realize the fact but it does still happen. Another big mistake is to distribute similar setup information throughout your application, making it really hard to find all the places when you need to change or refactor.
This seems to happen quite frequently with database drivers. There might be several instances of Class.forName("...") for loading the correct driver. In addition, the connection URL, username and password could be hardcoded or read from obscure config files. The desirable behaviour is to centralize such managements as far as possible.
It is sometimes quite useful to know how long a database statement takes to complete and how frequently it is called. A great tool for this is JAMon. In the latest edition, they have support for monitoring JDBC calls. All that you need to do is use their JDBC driver, point it to your driver, and you're done. This is extremely easy when all the config is in one place, but if you are connecting to several databases in various locations in your codebase, it will require code changes. Ideally we should not need any code changes.
B) JDBC Connection using DataSource:
In-Progress