package pckg1;

import java.sql.*;

public class DB{
	  private Connection conn = null;
	  private Statement stmt = null;
	  private ResultSet rs = null;

  public void connect(){
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      conn =
        DriverManager.getConnection("jdbc:mysql://mysql.agh.edu.pl/tomaada","tomaada","R2jqv8np");
    } catch (SQLException ex) {
      // handle any errors
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
    }catch(Exception e){e.printStackTrace();}
  }
  public void listNames(){
	    try {
	      connect();
	      stmt = conn.createStatement();
	      rs = stmt.executeQuery("SELECT * FROM books");

	      while(rs.next()){
	    	  for (int i=1;i<5;i++){
	    		  String name = rs.getString(i)+' ';
	    		  System.out.print(name );
	    	  }
	    	  System.out.println();
	      }
	    }catch (SQLException ex){
	      // handle any errors

	    }finally {
	      // zwalniamy zasoby, które nie będą potrzebne
	      if (rs != null) {
	        try {
	          rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	      }

	      if (stmt != null) {
	        try {
	          stmt.close();
	        } catch (SQLException sqlEx) { } // ignore

	        stmt = null;
	      }
	    }
	  }

  public void findNames(String pattern){
	  try {
	      connect();
	      stmt = conn.createStatement();
	      rs = stmt.executeQuery("SELECT * FROM books");
	      boolean found=false;
	      while(rs.next()){
	    		  if (pattern.equals(rs.getString(1)) || pattern.equals(rs.getString(3)) ){ //1-isbn, 3-autor
	    			  for(int j=1;j<5;j++){
	    				  System.out.print(rs.getString(j)+' '); //wypisanie pozostalych informacji
	    			  }
	    			  System.out.println();
	    			  found = true;
	    		  }
	      }
	      if (!found){
	    	  System.out.println("Nie znaleziono rekordu.");
	      }
	  }catch (SQLException ex){
	      // handle any errors

	   }finally {
	      // zwalniamy zasoby, które nie będą potrzebne
	      if (rs != null) {
	        try {
	          rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	      }

	      if (stmt != null) {
	        try {
	          stmt.close();
	        } catch (SQLException sqlEx) { } // ignore

	        stmt = null;
	      }
	    }
  }

  public void deleteNames(String pattern){
	  try {
	      connect();
	      stmt = conn.createStatement();
	      stmt.executeUpdate(
	              "DELETE FROM books WHERE isbn='"+pattern+"' OR author='"+pattern+"'");
	      System.out.println("Dane usnięte z bazy");
	  }catch (SQLException ex){
	      // handle any errors
	   }finally {
	      // zwalniamy zasoby, które nie będą potrzebne
	      if (rs != null) {
	        try {
	          rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	      }

	      if (stmt != null) {
	        try {
	          stmt.close();
	        } catch (SQLException sqlEx) { } // ignore

	        stmt = null;
	      }
	    }
  }
}