import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/*
Simple example to ilustrate steps of using JDBC

 */
public class JdbcExample {
    public static void main(String[] args) throws SQLException {

        try {
            // test that the JDBC Derby Embedded Driver is accessible - not necessary
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        Connection conn = null;
        Properties connectionProps = new Properties(); // no aditioal connection properties
        String dbName = "schooldb";

        try {
            // Construct the connection URL
            // Uses a Derby Embedded type database
            // the name of the database is contained in dbName
            // if the database does not exist it will be created
            String connectionURL= "jdbc:derby:" + dbName + ";create=true";

            // get a connection to the database
            conn = DriverManager.getConnection(connectionURL, connectionProps);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        // Create a Statement object over the connection
        // This Statement object will be used to transmit SQL statements to the database
        Statement stmt = conn.createStatement();

        // Test if the database contains a TABLE with name STUDENTS
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("DatabaseMetadata "+meta.getClass().getName());
        ResultSet resultSet = meta.getTables(null, null, "STUDENTS", new String[]{"TABLE"});
        System.out.println("ResultSet "+resultSet.getClass().getName());
        if (resultSet.next()) {
            // table STUDENTS already exists
        } else {
            // the first time this program is executed
            // table STUDENTS does not exist and it will be created now
            String createString = "create table " + dbName + ".STUDENTS " +
                    "(Stud_ID int NOT NULL, " +
                    "NAME varchar(32) NOT NULL, " +
                    "SURNAME varchar(32) NOT NULL, " +
                    "GRADE float NOT NULL, " +
                    "PRIMARY KEY (Stud_ID) )";

            // Use the Statement object to execute the create table
            stmt.executeUpdate(createString);
        }

        // Create string containing SQL command to query
        String queryString = "select Stud_ID, NAME,SURNAME, GRADE from " + dbName + ".STUDENTS";
        // Execute the statement with executeQuery
        ResultSet rs = stmt.executeQuery(queryString);

        // iterate over the result set to print data from all received rows
        while (rs.next()) {
            int id = rs.getInt("Stud_ID");
            String name = rs.getString("NAME");
            String surname = rs.getString("SURNAME");
            float grade = rs.getFloat("GRADE");
            System.out.println("" + id+ "\t" + surname + "\t"+ name + "\t" + grade + "\t");
        }

        // Read data for a new data row
        Scanner scanner = new Scanner(System.in);

        // Read the line containing  ID name surname grade
        System.out.print("Enter ID name surname grade: ");
        int intValue = scanner.nextInt();
        String string1 = scanner.next();
        String string2 = scanner.next();
        float floatValue = scanner.nextFloat();

        // Create string containing SQL command to insert a new row into database with placeholders for values
        String updateString = "insert into " + dbName + ".STUDENTS " +
                                   "(Stud_ID, NAME, SURNAME, GRADE) values(?, ?, ?, ?)";

        // Create a PreparedStatement object
        PreparedStatement preparedStatement = conn.prepareStatement(updateString);

        // Set the values of the placeholders
        preparedStatement.setInt(1, intValue);
        preparedStatement.setString(2, string1);
        preparedStatement.setString(3, string2);
        preparedStatement.setFloat(4, floatValue);

        // Execute the prepared statement
        int rowsAffected = preparedStatement.executeUpdate();

        System.out.println("Executed update on "+rowsAffected+" rows");

    }
}