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


public class DBmanager {

	private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
	private static final String url = "jdbc:derby:opdracht4;create=true" ;
	private Connection connection;


	public DBmanager() throws SQLException{
		try {
			Class.forName(driver).newInstance();
			connection = DriverManager.getConnection(url);
			createTable();
			testTable();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			if (connection != null){
				DriverManager.getConnection("jdbc:derby:;shutdown=true");
				connection.close();
			}
		}
	}




	public void createTable(){

		String sql = "CREATE TABLE APP.URL ("
				+ "createdOn DATE,"
				+ "timesVisited INTEGER,"
				+ "fullname VARCHAR(50) NOT NULL,"
				+ "lastUsedOn DATE);";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.execute();

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

	public void testTable(){
		String sql = "show tables;";

		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()){
				System.out.println(rs);
			}

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


	}
}