/* realizado SETA43
   xsetaseta@gmail.com
   seta43.duckdns.org
   http://seta43.blogspot.com/
   23/04/2020
*/



import java.sql.*;
import org.sqlite.JDBC;

public class ventana extends javax.swing.JFrame {
    String fichero ="libros.sqlite";
    String controlador = "org.sqlite.JDBC";
    String cadenaconex = "jdbc:sqlite:"+ fichero;
    
    Connection cn;
    Statement st;
    String sql1;
    ResultSet resultado;
    
    String miclave;
    int  Nrecord,Nactual;
    
    
    public ventana() 
    {
        
        try {          
            Class.forName(controlador);          
            cn = DriverManager.getConnection(cadenaconex);
            st =cn.createStatement();
            sql1 ="SELECT * FROM Autores";
            resultado= st.executeQuery(sql1);
            System.out.println("ABIERTA BD=" + fichero); 
            System.out.println("SUB_BASE  =Autores" ); 
            }
        catch(Exception e) {  System.out.println("ERROR ABRIENDO " + fichero); }
        
        initComponents();
        Tabla.getColumnModel().getColumn(0).setPreferredWidth(8);
        Tabla.getColumnModel().getColumn(1).setPreferredWidth(8);
        Tabla.getColumnModel().getColumn(2).setPreferredWidth(8);
        Tabla.getColumnModel().getColumn(3).setPreferredWidth(200);
        Tabla.getColumnModel().getColumn(4).setPreferredWidth(50);
        
        listarTodos();
        ListarRegistro();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        Bexit = new javax.swing.JButton();
        Bsiguiente = new javax.swing.JButton();
        jScrollPane2 = new javax.swing.JScrollPane();
        Tabla = new javax.swing.JTable();
        Bautor = new javax.swing.JTextField();
        Bbuscar = new javax.swing.JButton();
        Btodos = new javax.swing.JButton();
        Consola = new javax.swing.JTextField();
        BbuscarId = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("AUTORES");
        setMaximumSize(new java.awt.Dimension(1000, 1000));
        setPreferredSize(new java.awt.Dimension(556, 520));
        setResizable(false);

        Bexit.setText("Exit");
        Bexit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BexitActionPerformed(evt);
            }
        });

        Bsiguiente.setText("Siguiente");
        Bsiguiente.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BsiguienteActionPerformed(evt);
            }
        });

        Tabla.setBackground(new java.awt.Color(255, 255, 204));
        Tabla.setFont(new java.awt.Font("Dialog", 1, 12)); // NOI18N
        Tabla.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null},
                {null, null, null, null, null}
            },
            new String [] {
                "Nº", "Id", "TipoAutor", "Autor", "Extras"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.String.class, java.lang.String.class
            };
            boolean[] canEdit = new boolean [] {
                false, false, false, false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        Tabla.setAlignmentX(1.0F);
        Tabla.setAlignmentY(1.0F);
        Tabla.setMaximumSize(new java.awt.Dimension(800, 1000));
        Tabla.setName("miTABLA"); // NOI18N
        Tabla.setRowMargin(2);
        Tabla.setShowGrid(true);
        jScrollPane2.setViewportView(Tabla);

        Bautor.setFont(new java.awt.Font("Dialog", 0, 18)); // NOI18N
        Bautor.setForeground(new java.awt.Color(204, 0, 51));
        Bautor.setText("reverte");
        Bautor.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BautorActionPerformed(evt);
            }
        });

        Bbuscar.setText("Buscar Autor");
        Bbuscar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BbuscarActionPerformed(evt);
            }
        });

        Btodos.setText("Todos");
        Btodos.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BtodosActionPerformed(evt);
            }
        });

        Consola.setFont(new java.awt.Font("Dialog", 1, 14)); // NOI18N
        Consola.setText("AUTORES");

        BbuscarId.setText("Buscar Id");
        BbuscarId.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BbuscarIdActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane2)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(Bbuscar)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(BbuscarId, javax.swing.GroupLayout.PREFERRED_SIZE, 118, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(214, 214, 214)
                        .addComponent(Bexit))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(Bautor, javax.swing.GroupLayout.PREFERRED_SIZE, 139, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(Bsiguiente)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(Btodos)
                        .addGap(76, 76, 76))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(Consola, javax.swing.GroupLayout.PREFERRED_SIZE, 254, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(24, 24, 24)
                .addComponent(Consola)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 343, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(Bautor, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(Bsiguiente)
                    .addComponent(Btodos))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(Bbuscar)
                    .addComponent(BbuscarId)
                    .addComponent(Bexit))
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void BexitActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BexitActionPerformed
        System.exit(0);
    }//GEN-LAST:event_BexitActionPerformed

    private void BsiguienteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BsiguienteActionPerformed
            ListarRegistro();
    }//GEN-LAST:event_BsiguienteActionPerformed

    private void BbuscarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BbuscarActionPerformed
     buscarAutor();   // TODO add your handling code here:
    }//GEN-LAST:event_BbuscarActionPerformed

    private void BtodosActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BtodosActionPerformed
      listarTodos();
      ListarRegistro();     
    }//GEN-LAST:event_BtodosActionPerformed

    private void BautorActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BautorActionPerformed
        buscarAutor();        // TODO add your handling code here:
    }//GEN-LAST:event_BautorActionPerformed

    private void BbuscarIdActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BbuscarIdActionPerformed
       buscarId();
    }//GEN-LAST:event_BbuscarIdActionPerformed

    void listarTodos()
    {
       Nrecord=0;
       Nactual=0;
       
       try {          
            sql1 ="SELECT * FROM Autores";
            resultado= st.executeQuery(sql1);
            }
        catch(Exception e) {  System.out.println("ERROR_LISTAR TODOS1"); }
       
       try {  
             for( int z=0;z<1000000;z++)  
                    {  resultado.next();   Nrecord++;  resultado.getString("Autor"); }
            }
        catch(Exception e) 
            { //System.out.println("E_Todos"); 
            }
       
       try {          
            sql1 ="SELECT * FROM Autores";
            resultado= st.executeQuery(sql1);
            }
        catch(Exception e) {  System.out.println("ERROR_LISTAR TODOS2");         }
       
       Consola.setText("Registros encontrados= " + Nrecord);  
    }
    
    void buscarAutor()
    {
        int z;
        miclave=Bautor.getText().toUpperCase();
        
        try { 
            sql1 ="SELECT * FROM Autores  where   autor  LIKE '%"+ miclave + "%'";
            System.out.println("Buscar Autor="+sql1);         
            resultado= st.executeQuery(sql1);  
            }
        catch(Exception e) 
             {  System.out.println("CALCULO"); }
        
        System.out.println("Buscar autor="+ miclave);

        Nrecord=0;
        try {  
             for( z=0;z<10000;z++)  
             {  resultado.next();   resultado.getString("Autor"); Nrecord++;     }
            }
        catch(Exception e) 
            { //System.out.println("cuentas");  
            }
   
        System.out.println("TAMAÑO="+ Nrecord);

        try { 
            sql1 ="SELECT * FROM Autores  where   autor  LIKE '%"+ miclave + "%'";
            System.out.println("Buscar Autor="+sql1);         
            resultado= st.executeQuery(sql1);           
            }
        catch(Exception e) { System.out.println("ERROR buscarAutor2"); }
        Nactual=0;
        ListarRegistro();

        Consola.setText("Registros encontrados= " + Nrecord);      
    }
    
      void buscarId()
    {
        int z;
        miclave=Bautor.getText().toUpperCase();
        
        try { 
            sql1 ="SELECT * FROM Autores  where   IdAutor ="+ miclave ;
            System.out.println("Buscar Autor="+sql1);         
            resultado= st.executeQuery(sql1);  
            }
        catch(Exception e) 
             {  System.out.println("BuscarID_E1"); }
        
        System.out.println("Buscar autor="+ miclave);

        Nrecord=0;
        try {  
             for( z=0;z<10000;z++)  
             {  resultado.next();   resultado.getString("Autor"); Nrecord++;     }
            }
        catch(Exception e) 
            { //System.out.println("cuentas");  
            }
   
        System.out.println("TAMAÑO="+ Nrecord);
        try { 
            sql1 ="SELECT * FROM Autores  where   IdAutor ="+ miclave ;
            System.out.println("Buscar Autor="+sql1);         
            resultado= st.executeQuery(sql1);           
            }
        catch(Exception e) { //System.out.println("ERROR_ID_"); 
        }
        Nactual=0;
        ListarRegistro();
        Consola.setText("Registros encontrados= " + Nrecord);      
    }
    
    
    void ListarRegistro()
    {
        String newline = "\n";
        String texto="";
        int z=0;
                try {  
                    for( z=0;z<20;z++)
                    {
                         resultado.next(); 
                         Nactual++;
                         Tabla.setValueAt(Nactual, z, 0);
                         Tabla.setValueAt(resultado.getString("IdAutor"), z, 1);
                         Tabla.setValueAt(resultado.getString("TipoAutor"), z, 2);
                         Tabla.setValueAt(" "+ resultado.getString("Autor"), z, 3);
                         Tabla.setValueAt(" "+ resultado.getString("extra"), z, 4);
                    }  
                 }
                
                  catch(Exception e) 
                  { 
                      //System.out.println("No hay mas registros"); 
                      for(;z<20;z++)
                    {  
                       Tabla.setValueAt("", z, 0); Tabla.setValueAt("", z, 1);
                       Tabla.setValueAt("", z, 2); Tabla.setValueAt("", z, 3);Tabla.setValueAt("", z, 4);
                    }
  
                  }
    }
      
    
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new ventana().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JTextField Bautor;
    private javax.swing.JButton Bbuscar;
    private javax.swing.JButton BbuscarId;
    private javax.swing.JButton Bexit;
    private javax.swing.JButton Bsiguiente;
    private javax.swing.JButton Btodos;
    private javax.swing.JTextField Consola;
    private javax.swing.JTable Tabla;
    private javax.swing.JScrollPane jScrollPane2;
    // End of variables declaration//GEN-END:variables
}
