JDBC example with mysql

JDBC example with mysql

ThisĀ is a basic example which describe how to connect mysql using JDBC. In practice very few peopleĀ uses JDBC as there are more advance frameworks like Hiberante, Spring templates, iBatis are available. However, it is advisable to know the basic of JDBC which help to understand more advance frameworks.
This tutorial describes how to use Java JDBC to connect to MySQL and perform select query on emp table. Technologies used in this examples are :

  1. Mysql
  2. Maven
  3. JDK 1.7

In MySQL, Emp table is created which contains three columns id, firstName and lastName. There are 2 emp records are available with Emp table as below.

emp JDBC

Step 1: Add Mysql connector to project classpath.

In this example maven is used as build tool, so add below dependency to the pom.xml file. In case you are not using maven manually add mysql-connector jar to your projects classpath.

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.30</version>
</dependency>

Step 2: code snippet to connection with mysql.

Class.forName("com.mysql.jdbc.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/code2succeed","root", "manager");
connection .close();

Step 3 : Complete example with select Statement.

package com.code2succeed;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class App {
 public static void main(String[] args) {
  System.out.println("-------- MySQL JDBC Connection Testing ------------");
  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   System.out.println("MySQL JDBC Driver not found");
   e.printStackTrace();
   return;
  }

  System.out.println("MySQL JDBC Driver Registered!");
  Connection connection = null;

  try {
   connection = DriverManager
    .getConnection("jdbc:mysql://localhost:3306/code2succeed", "root", "manager");

  } catch (SQLException e) {
   System.out.println("Not connected to the database schema, please check username/password");
   e.printStackTrace();
   return;
  }

  if (connection != null) {
   try {
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("select * from emp");
    while (resultSet.next()) {
     int id = resultSet.getInt("id");
     String firstName = resultSet.getString("firstName");
     String lastName = resultSet.getString("lastName");

     System.out.println(id + " " + firstName + " " + lastName);
    }
   } catch (SQLException e) {
    System.out.println("Exception while creating statement");
    e.printStackTrace();
   }
  }
 }
}

Step 4: Load and run the program

-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
1 Rahul Anand
2 Daniel Anderson

 

Leave a Reply

Your email address will not be published. Required fields are marked *