package com.examples.recipes;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author mchapagai The following example shows Java Database Connectivity
 */
public class SQLDataAccess {
    private static final String MYSQL_CONNECTION = "jdbc:mysql://localhost:3306/data_sampledb";//connection url
    private static final String SELECT_EMPLOYEES = "select * from employees";
    private static final String EMPLOYEE_ID = "id";
    private static final String EMPLOYEE_COUNTRY = "country";
    static Logger log = LoggerFactory.getLogger(SQLDataAccess.class);

    public static void main(String[] args) {
        SQLDataAccess dataAccess = new SQLDataAccess();
        dataAccess.query();
    }

    private void query() {
        /**
         * Create the database statement which creates the statement after
         * the connection has been made
         * To access the Data in the Database using Java Database Connectivity
         * We have to process the ResultSet and Execute the query
         * Following code Processes resultset and Executes the query
         */
        ResultSet rs = null;
        Statement stmt = null;
        Connection conn = createConnection();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SELECT_EMPLOYEES);
            while (rs.next()) {
                String ID = rs.getString(EMPLOYEE_ID);
                String Country = rs.getString(EMPLOYEE_COUNTRY);

                log.info("Employee ID: " + ID);
                log.info("Employee Country: " + Country);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {

            }
        }
    }

    /**
     * First step to work in the JDBC is to create a connection The
     * following line of code creates JDBC connection.
     */
    private Connection createConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(MYSQL_CONNECTION, "username", "password");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}