Java + JDBC + MySQL

Connecting to MySQL from Java application using plain java API.

Objective

  • How to connect to MySQL database from Java application?

Environment

  • Eclipse (Indigo)

Libraries

( 1 ) Database

( 2 ) Java Project

  • Create new Java Project
  • Add new folder “lib”
  • Copy mysql-connector-java (jar) to “lib” folder
  • Add jar file to java build path

( 3 ) Java Classes (Person.java + PersonDAO + JDBCPersonDAO + Main.java)

Person.java

package com.hmkcode.vo;

public class Person {

    int id;
    String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    public String toString(){
        return "ID: "+id+" Name: "+name;
    }
}

PersonDAO.java

package com.hmkcode.dao;

import java.util.List;

import com.hmkcode.vo.Person;

public interface PersonDAO {
	
	public void insert(Person person);
	public List<Person> select();

}

JDBCPersonDAO.java

package com.hmkcode.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.hmkcode.vo.Person;

public class JDBCPersonDAO implements PersonDAO {

	Connection connection = null;

	public Connection getConnection(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			if(connection == null)
				connection = DriverManager.getConnection("jdbc:mysql://localhost/hmkcode?user=root&password=");

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			
		}
		return connection;
	}
	@Override
	public void insert(Person person) {
		try {
			PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO hmkcode.persons (id ,name) VALUES (NULL , ?)");
			preparedStatement.setString(1,  person.getName());
		    preparedStatement.executeUpdate();
		    preparedStatement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public List<Person> select() {
		List<Person> persons = new LinkedList<Person>();
		 try {
			 	Statement statement = connection.createStatement();
			 	ResultSet resultSet = statement.executeQuery("SELECT * FROM hmkcode.persons");
			 	
			 	Person person = null;
			 	while(resultSet.next()){
			 		person = new Person();
			 		person.setId(Integer.parseInt(resultSet.getString("id")));
				    person.setName(resultSet.getString("name"));
				    
				    persons.add(person);
				}
			 	resultSet.close();
			 	statement.close();
			 	
			} catch (SQLException e) {
				e.printStackTrace();
			}
		 	System.out.println(persons);
		 	return persons;
	}
	
	
	public void closeConnection(){
		try {
		      if (connection != null) {
		    	  connection.close();
		      }
		    } catch (Exception e) { 
		    	//do nothing
		    }
	}

}

Main.java

package com.hmkcode;

import com.hmkcode.dao.JDBCPersonDAO;
import com.hmkcode.vo.Person;

public class Main {

    public static void main(String args[]){
        
        Person person = new Person();
        person.setName("HMK");
        
        JDBCPersonDAO jdbcPersonDAO = new JDBCPersonDAO();
        jdbcPersonDAO.getConnection();
        jdbcPersonDAO.insert(person);
        
        person.setName("Another Name");

        jdbcPersonDAO.insert(person);
        
        jdbcPersonDAO.select();
        jdbcPersonDAO.closeConnection();


        
    }
}

( 4 ) Test Application

Download Code: Java-jdbc-mysql.zip