Hotel

package Hotel_Menagement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import com.mysql.jdbc.PreparedStatement;

public class Hotel {
    /*
     * maja evo probaj ovaj dio ---pretrazivanje baze po imenu/broju lične
     * karte/usernameu searchDatabase(String s); imas cak primjer na github
     * linku ali jedino kod "na suho" mozes radit
     */
    public static void main(String[] args) throws Exception {

        // create scanner object, message to user and take user input
        Scanner input = new Scanner(System.in);
         System.out.println("Enter your name, username or identity card number: ");
         String name = input.nextLine();
         searchDatabase(name);
//      inset info for testing
//       Admin user = new Admin();
//       user.enterInfo();

        System.out
                .println(" Enter user id you want to check out form the hotel.");
        int id = input.nextInt();
        userCheckOut1(id);

        input.close();
    }

    // ID, FirstName, LastName, Gender, IdentityCard, Age, RoomNumber, RoomType,
    // CheckInTime, UserName,Password

    /**
     * @author Maja Vasilic
     *
     *         providing information from database based on entry
     */
    public static void searchDatabase(String s) throws Exception {
        try {
            // estalish connection to the database invoking method
            // prepare statement
            PreparedStatement statement = (PreparedStatement) getConnection()
                    .prepareStatement(
                            "SELECT * FROM information WHERE FirstName = ? OR IdentityCard= ? OR UserName = ?");

            // actual values are set to paramiters
            statement.setString(1, s);
            statement.setString(2, s);
            statement.setString(3, s);

            // query execution
            ResultSet result = statement.executeQuery();

            ResultSetMetaData rsmd = result.getMetaData();
            int columnCount = rsmd.getColumnCount();

            String format = "%-20s";

            // print column names
            for (int i = 1; i <= columnCount; i++) {
                System.out.printf(format, rsmd.getColumnLabel(i));
            }
            System.out.println();

            // print underline
            for (int i = 1; i <= columnCount * 30; i++) {
                System.out.print("_");
            }
            System.out.println();

            // print information from database
            while (result.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.printf(format, result.getString(i));
                }
                System.out.println();
            }
        } catch (Exception e) {
            System.out.println(e);
        }

    }

    /**
     * author Maja Vasilic
     *
     * Method copying information from "information" table to the "archive"
     * table and removes user from "information"
     *
     * @throws Exception
     */
    public static void userCheckOut(int id) throws Exception {
        // declare object
        Connection conn = null;
        ResultSet result = null;
        PreparedStatement st = null;
        PreparedStatement stDelete = null;

        try {
            // estalish connection to the database hotel invoking method
            conn = getConnection();

            /** FIND row with specified name */

            st = (PreparedStatement) conn
                    .prepareStatement("SELECT * FROM `information` WHERE ID = "
                            + id);
            // Execute PreparedStatement and fill up ResultSet with result from
            // the DB
            result = st.executeQuery(); // user executeQuery for
                                        // sql SELECT
            result.next();
            /**
             * INSERT information from the information table to the archive
             * table
             */

            // create prepared statement
            st = (PreparedStatement) conn
                    .prepareStatement("INSERT INTO archive (ID,FirstName, LastName,"
                            + " Gender, IdentityCard, Age) VALUES (?, ?, ?, ?, ?, ?)");

            System.out.println("Sending data to the archive.");

            // set values
            st.setInt(1, result.getInt(1));
            st.setString(2, result.getString(2));
            st.setString(3, result.getString(3));
            st.setString(4, result.getString(4));
            st.setString(5, result.getString(5));
            st.setInt(6, result.getInt(6));

            // execute query to insert values in the archive to the information
            st.execute();

            /** DELETE row from the information table */

            // now we can remove user from current table for users information

            Statement s = conn.createStatement();

            s.executeUpdate("DELETE  FROM information WHERE ID = " + id + "");

            // print message
            System.out.println("User " + result.getString(2) + " is removed.");
            conn.close();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            // close connection with the database
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /** establish connection with database */
    public static Connection getConnection() throws Exception {
        try {
            // load jdbc driver
            Class.forName("com.mysql.jdbc.Driver");

            // establish connection using url, username and password
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/hotel", "root", "maja");
            return con;

        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }
}