Database Connectivity with Mysql

Learn how to connect to mysql database


JDBC(Java database connectivity) API allows programmer to connect to a variety of databases. JDBC in turn makes use of driver in order to interact with databases.

There are four types of drivers available.

  • Type-1 Driver(Jdbc Odbc bridge) :

    This driver makes use of ODBC (Open database connectivity) API for connecting to database. This driver convert jdbc calls into format of ODBC which in turn provide connection with database.

    1. Since the Bridge driver is not written fully in Java, Type 1 drivers are not portable.

    2. A performance issue is seen as a JDBC call goes through the bridge to the ODBC driver, then to the database, and this applies even in the reverse process. They are the slowest of all driver types.

    3. The client system requires the ODBC Installation to use the driver.

  • Type-2 Driver (Native Driver) :

    Type-2 Driver convert jdbc calls into a database specific format using a native client library.

    1. Like Type 1 drivers, it’s not written in Java Language which forms a portability issue.

    2. Native API must be installed in the Client System and hence type 2 drivers cannot be used for the Internet.

  • Type-3 Driver (Network Driver) :

    Type-3 Driver forward jdbc calls to middleware server which send those calls to database server. Middleware server has to connect to database and it can use any driver.

    1. It requires another server application to install and maintain. Traversing the recordset may take longer, since the data comes through the back-end server.

  • Type-4 Driver(Pure java Driver) :

    Type-4 Driver completely written in java that's why also known as pure Java driver or thin driver. This driver connect to database directly using network protocol.


Steps for Connecting to Mysql Database

Install mysql on your machine. During installation it will ask for user name and password.
In case if your databaser server is running on another machine in that case skip this step.


Download mysql driver for jdbc from here. Extract downloaded zipped file. In extracted files there will be jar file which we will use to load driver.


import java.sql.*;  
 public class dataConnect
  {
    public static void main(String arg[]) throws Exception
	 {
	  // Load the Driver
	  Class.forName("com.mysql.jdbc.Driver");
	  
	  // Create connection 
	  Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/world","root","root");	  
	  System.out.println("Connected to database");
	 }
  }

<

Class.forName method is used to load driver at run time. This method needs one argument which specify name of class. In downloaded jar file there is class file named Driver in com.mysql.jdbc package.

Class.forName("com.mysql.jdbc.Driver");

After loading of driver, getConnection() static method of DriverManager class is used to create a connection with database.
getConnection() method needs three arguments.

Connection con=DriverManager.getConnection("db_url","user","password");

First argument specify URL of database. Following is format for databse url.

jdbc:<sub-protocol>://<server-ip>:<port>/<database-name>

Second and third arguments specify user name and password for mysql databse respectively.



Execution

For execution of above program classpath of driver class has to be set or specified at run time.
Let's assume we have downloaded jar file and program both on desktop then use following command for execution.

C:\Users\Jgyan\Desktop>java -cp mysql-connector-java-5.0.8-bin.jar;. dataConnect

Video Tutorial for Database Connection