Java & MySQL – Connections
After you”ve installed the appropriate driver, it is time to establish a database connection using JDBC.
The programming involved to establish a JDBC connection is fairly simple. Here are these simple three steps −
-
Import JDBC Packages − Add import statements to your Java program to import required classes in your Java code.
-
Database URL Formulation − This is to create a properly formatted address that points to the database to which you wish to connect.
-
Create Connection Object − Finally, code a call to the DriverManager object”s getConnection( ) method to establish actual database connection.
Import JDBC Packages
The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code.
To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code −
import java.sql.* ; // for standard JDBC programs import java.math.* ; // for BigDecimal and BigInteger support
Register JDBC Driver
You must have the required JDBC driver in the classpath. In current case, you set CLASSPATH variable to C:Program FilesMySQLmysql-connector-java-5.1.8mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation.
Database URL Formulation
After you”ve loaded the driver, you can establish a connection using the DriverManager.getConnection() method. For easy reference, let me list the three overloaded DriverManager.getConnection() methods −
-
getConnection(String url)
-
getConnection(String url, Properties prop)
-
getConnection(String url, String user, String password)
Here each form requires a database URL. A database URL is an address that points to your database.
Formulating a database URL is where most of the problems associated with establishing a connection occurs.
Following table lists down the MySQL JDBC driver name and database URL.
RDBMS | JDBC driver name | URL format |
---|---|---|
MySQL | com.mysql.jdbc.Driver | jdbc:mysql://hostname/ databaseName |
All the highlighted part in URL format is static and you need to change only the remaining part as per your database setup.
Create Connection Object
We have listed down three forms of DriverManager.getConnection() method to create a connection object.
Using a Database URL with a username and password
The most commonly used form of getConnection() requires you to pass a database URL, a username, and a password −
As you are using MySQL driver, you”ll specify a host:port:databaseName value for the database portion of the URL.
If you have a host at TCP/IP address 192.0.0.1 with a host name of localhost, and your MySQL listener is configured to listen on port 3306 as default, and your database name is TUTORIALSPOINT, then complete database URL would be −
jdbc:mysql://localhost/TUTORIALSPOINT
Now you have to call getConnection() method with appropriate username and password to get a Connection object as follows −
String URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; String USER = "guest"; String PASS = "password" Connection conn = DriverManager.getConnection(URL, USER, PASS);
Using a Database URL and a Properties Object
A third form of the DriverManager.getConnection( ) method requires a database URL and a Properties object −
DriverManager.getConnection(String url, Properties info);
A Properties object holds a set of keyword-value pairs. It is used to pass driver properties to the driver during a call to the getConnection() method.
To make the same connection made by the previous examples, use the following code −
import java.util.*; String URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; Properties info = new Properties( ); info.put( "user", "guest" ); info.put( "password", "guest123" ); Connection conn = DriverManager.getConnection(URL, info);
For a better understanding, we suggest you to study our .
Now let us compile the above example as follows −
C:>javac FirstExample.java C:>
When you run FirstExample, it produces the following result −
C:>java FirstExample ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal C:>