Java Tutorial – Connect to Database using JDBC Tutorial

Java Tutorial – Database Programming

This tutorial assumes knowledge of basic database concepts and MySQL.

In this Java tutorial you will learn how to connect to a MySQL database.

Connecting to a database will likely be a common task as your Java projects progress. In this JAVA tutorial, we will use the Java Database Connectivity API (Application Programming Interface) – JDBC to deliver our data.

The JDBC libraries provide the means to connect your programs to a database and perform any operations upon the data that you require. To start using JDBC you need:
the JDK (which you may already have installed)
a JBDC driver, which depends on the Database Management System you are using for your data.

Once you have installed a driver compatible with the DBMS for your data source, create a new Java project in your IDE. Enter the following import statement at the top of your Main Class:

//import required for database operations
import java.sql.*;

Now enter your main method (this code is for MySQL databases, with notes where alterations are required for other systems):

public static void main(String[] args)
{
//try block for sql exceptions
try
{
//create driver – ALTER TO SUIT YOUR DRIVER/ DBMS
Class.forName(“com.mysql.jdbc.Driver”).newInstance();

//database connection code – ENTER YOUR DETAILS
String username = “yourname”;
String password = “yourpwd”;

//URL – ALTER TO CONNECT TO YOUR DATABASE
String dbURL = “jdbc:mysql://somedomain.com/database?user=”
+ username + “&password=” + password;

//create the connection – ALTER FOR YOUR DBMS
java.sql.Connection myConnection =
DriverManager.getConnection(dbURL);

//create statement handle for executing queries
Statement stat = myConnection.createStatement();

}
catch( Exception E )
{ System.out.println( E.getMessage() ); }
}

If exception handling is as yet unfamiliar to you, the try and catch blocks provide your program with the ability to continue when unexpected input is received, for example when communicating with data sources. There is always a possibility of unforeseen error when your code relies on external input, in which case the code may ‘throw an exception’. You simply put the code that will potentially cause an exception to be thrown inside the try block, and the response to any exceptions inside the catch. If an input error (or in this case an sql error) occurs, the code will immediately jump to the catch block – all we do in this case is write the details of the error out to the console.

Your code is now ready to execute some queries on the data – to do so, enter code such as the following example at the end of (inside) the try block:

//query to select all of the data from a table
String selectQuery = “Select * from MyTable”;
//get the results
ResultSet results = stat.executeQuery(selectQuery);
//output the results
while (results.next())
{
//example – column is called ‘firstname’
System.out.println(“first name: ” +
results.getString(“firstname”));
}

You can use the statement handle (the ‘stat’ variable in the code above) to execute other SQL statements on your data, such as updates and inserts. For updates, use the syntax:

//example update statement
String updateStatement =
“Update MyTable set SomeColumn=192 where OtherColumn=12″;
//int return indicates success or failure
int updateSuccess = stat.executeUpdate(updateStatement);
System.out.println(“success? “+updateSuccess);

For inserts the syntax is the same:

//example insert statement
String insertStatement =
“Insert into MyTable (col1, col2, col3)
values (12, ‘bla’, 127)”;
//int return indicates success or failure
int insertSuccess = stat.executeUpdate(insertStatement);
System.out.println(“success? “+insertSuccess);

You can also use the Result Set to get information about metadata:

//get the metadata from a ResultSet
ResultSetMetaData mData = results.getMetaData();

The ResultSetMetaData object provides methods to ascertain details of the data such as table names within the database, column names within tables etc.

Once you’ve carried out whichever operations you need on your data, you should then close the connection:

results.close();
stat.close();
myConnection.close();

The JDBC API is widely used within Java applications due to the fact that it is standard and can be used for many relational database systems. While some of the details within the above code may need to be slightly altered for your chosen DBMS, the overall structure of your database connectivity code will remain the same.