package com.company;

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

public class Main {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    String driverString = "org.apache.derby.jdbc.EmbeddedDriver";
    Class.forName(driverString);

    String connString = "jdbc:derby:dbName;create=true";
    Connection conn = DriverManager.getConnection(connString);
    Statement stmt = conn.createStatement();

    String createTableString = "" + "CREATE TABLE students ("+
    " id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+
    " name VARCHAR(24) NOT NULL,"+
    " studyYear INTEGER DEFAULT 0,"+
    "CONSTRAINT primary_key PRIMARY KEY (id)"+
    ")";
    try {
        stmt.execute((createTableString));
    }catch(SQLException e){
        if(!"X0Y32".equals(e.getSQLState())){
            throw e;
        }
    }

    String insertString = "INSERT INTO students(name, studyYear) VALUES (?,?)";
    PreparedStatement preparedStmt = conn.prepareStatement(insertString);

    Scanner sc = new Scanner(System.in);
    while(true) {
        String name = sc.nextLine();
        if (name.isEmpty()) break;
        int studyYear = sc.nextInt();
        sc.nextLine();

        preparedStmt.setString(1,name);
        preparedStmt.setInt(2,studyYear);
        int updateCount = preparedStmt.executeUpdate();
        System.out.println("updateCunt = " + updateCount);
    }

    Statement updatableStmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    String selectString = "SELECT id, name, studyYEAR FROM students";
    ResultSet rs = updatableStmt.executeQuery(selectString);

    while(rs.next()){


        System.out.printf("%4d %-24s %d\n", rs.getInt("id"), rs.getString("name"), rs.getInt("studyYear"));

        rs.updateInt("studyYear", rs.getInt("studyYear")+1);
        rs.updateRow();

    }

    try {
        DriverManager.getConnection("jdbc:derby:;shutdown=true");
    }catch (SQLException e){
        if(!"XJ015".equals(e.getSQLState())){
            throw e;
        }
    }

    }

}