Get mysql table id in application

Question

I am developing an application in Java where I need to redeem the id of a Course table (idCurso, nameCurso, typeCurso). Here’s what I’m trying to do:

public class Teste4 extends javax.swing.JFrame {

        Connection con = new ConnectionFactory().getConnection();
        int id = 0;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        String query = "SELECT idCurso FROM Curso WHERE nomeCurso = "
                + "' xiu ' AND tipoCurso = "
                + "' Técnico '";
        try{
            PreparedStatement stmt = con.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()){
                id = rs.getInt("idCurso");
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        this.id = id;
    }

    public Teste4() {


        initComponents();

    }

    /**
     * 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">                          
    private void initComponents() {

        jButton1 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jButton1.setText("jButton1");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap(170, Short.MAX_VALUE)
                .addComponent(jButton1)
                .addGap(157, 157, 157))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(139, 139, 139)
                .addComponent(jButton1)
                .addContainerGap(138, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>                        

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        JFNovoCurso janela = new JFNovoCurso();
        System.out.println(getId());
        janela.setId(getId());
        janela.setVisible(true);
    }                                        

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(Teste4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(Teste4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(Teste4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Teste4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new Teste4().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify                     
    private javax.swing.JButton jButton1;
    // End of variables declaration                   
}

The result of this query is 0, and when I do the same query in mysql it returns some value for the id, then I guess it’s not syntax.

Connectionfactory class:

public class ConnectionFactory {

    Connection con;

    public Connection getConnection(){

        String url = "jdbc:mysql://localhost:3306/tcc";
        try {
            return (Connection) DriverManager.getConnection(url,"root", "GUstavo08!");
        }catch(SQLException ex){
            System.out.println("Problemas na conexão " + ex.getMessage());
            return null;
            }
    }

}

Answer 1

See that you have spaces in your parameters and how you are using the = won’t work.

Modify the line to look exactly like below:

String query = "SELECT idCurso FROM Curso WHERE nomeCurso = 'xiu' AND tipoCurso = 'Técnico'";

Answer 2

I believe the problem lies in the space between the quotation marks ' Xiu ' and ' Técnico '. When he executes Preparedstatement, by reconciling these spaces, he ends up not finding any tuple that corresponds returning an empty Resultset. In addition I propose the following changes to your code:

1. Use the methods of setString() of the object Preparedstatement
2. As in this case only one tuple should be returned then instead of one while (rs.next()) utilize if(rs.next()
3. The method id parameter setId(int id) ends up not being used but forces you to every invocation of this method define a variable that will serve no purpose, it would be better if the method did not receive parameters and you set a local variable.

The code would look like this:

public void setId() {
    int aux = 0;
    String query = "SELECT idCurso FROM Curso WHERE nomeCurso = ? AND tipoCurso = ?;";
    try{
        PreparedStatement stmt = con.prepareStatement(query);

        stmt.setString(1, "Xiu");
        stmt.setString(2, "Técnico");

        ResultSet rs = stmt.executeQuery();
        if(rs.next()) aux = rs.getInt("idCurso");

    }catch (SQLException ex){
        ex.printStackTrace();
    }
    this.id = aux;
}