package com.mybank.db;

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

public class MyConnection {
	public static void main(String[] args) {
		// database configurations
		String url = "jdbc:mysql://localhost:3306/ftmkbank";
		String user = "root";
		String password = "yourpassword";

		try {
			// load and register mysql jdbc driver
			Class.forName("com.mysql.cj.jdbc.Driver");

			// establish a connection
			Connection conn = DriverManager.getConnection(url, user, password);

			// if connection success, display message
			if (conn != null) {
				System.out.println("Connected to database successfully!");
				
				// create statement from connection
				Statement statement = conn.createStatement();
				
				// prepare query
				String query = "SELECT id, firstname, lastname, email FROM customer";
				
				// execute query and get result
				ResultSet result = statement.executeQuery(query);
				
				// extract result
				while (result.next()) {
					int id = result.getInt("id");
					String firstname = result.getString("firstname");
					String lastname = result.getString("lastname");
					String email = result.getString("email");
					System.out.println("Id: " + id + ", First Name: " + firstname 
							+ ", Last Name: " + lastname + ", Email: " + email);
				}
				
				// create prepared statement
				query = "SELECT id, firstname, lastname, email FROM customer WHERE id = ?";
				
				PreparedStatement preparedStatement = conn.prepareStatement(query);
				
				// pass parameter to prepared statement
				preparedStatement.setInt(1, 1);
				
				// execute query and get result
				result = preparedStatement.executeQuery();
				
				// extract result
				while (result.next()) {
					int id = result.getInt("id");
					String firstname = result.getString("firstname");
					String lastname = result.getString("lastname");
					String email = result.getString("email");
					System.out.println("Id: " + id + ", First Name: " + firstname 
							+ ", Last Name: " + lastname + ", Email: " + email);
				}
				
				// close resources
				result.close();
				statement.close();
				conn.close();
			}
			
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}
}