package kadai1;

import java.sql.*;

public class Database {

    private static Connection getDatabaseConnection() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        String url = "jdbc:hsqldb:file:c:\\mydb\\kadai_a1\\hsqldb;create=true";
        System.out.println("Connecting to " + url);
        Connection con = DriverManager.getConnection(url, "sa", "");
        System.out.println("Connected");
        return con;
    }

    private static void createtable() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Create table");
            Statement st = con.createStatement();
            st.executeUpdate(
            	"create table userdata (" +
            		"lastname varchar(32) NOT NULL," +
            		"firstname varchar(32) NOT NULL," +
            		"lastnamekana varchar(64) NOT NULL," +
            		"firstnamekana varchar(64) NOT NULL," +
            		"sex int NOT NULL," +
            		"birthyear int NOT NULL," +
            		"birthmonth int NOT NULL," +
            		"birthdate int NOT NULL," +
            		"mailaddress varchar(255) NOT NULL," +
            		"area int NOT NULL," +
            		"prefecture int NOT NULL," +
            		"schoolname varchar(128)," +
            		"grade int NOT NULL," +
            		"password varchar(12) NOT NULL" +
            	")");
            System.out.println("Created");

            st.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertdata(ModelUserData userdata) throws Exception {
        createtable();
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Insert data");
            PreparedStatement ps = con.prepareStatement("insert into userdata values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            ps.setString(1, userdata.lastname);
            ps.setString(2, userdata.firstname);
            ps.setString(3, userdata.lastnamekana);
            ps.setString(4, userdata.firstnamekana);
            ps.setInt(5, userdata.sex);
    		ps.setInt(6, userdata.birthyear);
    		ps.setInt(7, userdata.birthmonth);
    		ps.setInt(8, userdata.birthdate);
    		ps.setString(9, userdata.mailaddress);
    		ps.setInt(10, userdata.area);
    		ps.setInt(11, userdata.prefecture);
    		ps.setString(12, userdata.schoolname);
    		ps.setInt(13, userdata.grade);
    		ps.setString(14, userdata.password);
            System.out.println(ps.toString());
    		int result = ps.executeUpdate();
            System.out.println("Inserted " + result);

            ps.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}