package Main;

import java.sql.*;
import javax.imageio.ImageIO;
import javax.swing.*;
import java.io.*;
import java.awt.*;


/**
 * Basic connection to MySQL (MariaDB) database.
 * Conexión básica a la base de datos MySQL (MariaDB).
 */
public class JavaMySQLBasic {

    /**
     * We establish the connection with the database <b>customerdb</b>.
     * Establecemos la conexión con la base de datos <b>customerdb</b>.
     */
    public void connectDatabase() {
    	Connection connection = null;
        try {
            // We register the MySQL (MariaDB) driver
            // Registramos el driver de MySQL (MariaDB)
            try {
                Class.forName("org.mariadb.jdbc.Driver");
            } catch (ClassNotFoundException ex) {
                System.out.println("Error al registrar el driver de MySQL: " + ex);
            }

            // Conectamos con la base de datos
            connection = DriverManager.getConnection(
                    "jdbc:mariadb://esp.uem.es:3306/test","test", "test");
            boolean valid = connection.isValid(50000);
            System.out.println(valid ? "TEST OK" : "TEST FAIL");
        } catch (java.sql.SQLException sqle) {
            System.out.println("Error: " + sqle);
        }
        try {
        	// our SQL SELECT query.
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "SELECT * FROM personas";

            // create the java statement
            Statement st = connection.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            // iterate through the java resultset
            while (rs.next())
            {
              String nombre = rs.getString("Nombre");
              int edad = rs.getInt("edad");


              // print the results
              System.out.format("%s, %d\n", nombre, edad);
            }

        }catch(java.sql.SQLException sqle) {
            System.out.println("Error: " + sqle);
        }
        try {
        	File image = new File("c:/foto.jpg");
        	FileInputStream fis = new FileInputStream ( image );

            String sql="update personas set Img=? where Nombre='diego'";
            PreparedStatement pst;
            pst = connection.prepareStatement(sql);

            //pst.setInt(1, 10);
            pst.setBinaryStream(1, fis);

            pst.executeUpdate();

        }catch(Exception e) {
            System.out.println("Error: " + e);
        }
    }

    public void showImag(){
    	Image image = null;
    	Connection connection = null;

    	try {
            Class.forName("org.mariadb.jdbc.Driver");

            // Conectamos con la base de datos
            connection = DriverManager.getConnection(
                    "jdbc:mariadb://esp.uem.es:3306/test","test", "test");
            boolean valid = connection.isValid(50000);
            System.out.println(valid ? "TEST OK" : "TEST FAIL");

        	// our SQL SELECT query.
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "SELECT Img FROM personas where Nombre='diego'";

            // create the java statement
            Statement st = connection.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            // iterate through the java resultset
            rs.next();

            InputStream is = rs.getBinaryStream("Img");
    		image = ImageIO.read(is);
	  } catch (Exception e) {
          System.out.println("Exception: " + e);
	  }

	  // Use a label to display the image
	  JFrame frame = new JFrame();
	  JLabel label = new JLabel(new ImageIcon(image));
	  frame.getContentPane().add(label, BorderLayout.CENTER);
	  frame.pack();
	  frame.setVisible(true);
    }

    /**
     * Testing Java MySQL connection with host and port
     * Probando la conexión en Java a MySQL especificando el host y el puerto.
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        JavaMySQLBasic javaMySQLBasic = new JavaMySQLBasic();
        javaMySQLBasic.connectDatabase();
        javaMySQLBasic.showImag();
    }
}