Generate registration number

Question

Generate registration number

Navigation

#1 by (1 votes)

1

Friends, I am trying to generate an autoincrementable Registration Number with a certain structure ... The first two numbers correspond to the current year that is added 000000 (5 zeros) and that must be autoincrementando for each record that is added ... and in addition to that to meet a new year must return to zero and the first two number must also change ... REMAINING LIKE: 1800001

I tried to do it in an archaic way and it has not worked out for me, since it is adding the year again, remaining in 18181800005

    DateFormat df = new SimpleDateFormat("yy"); 
String formattedDate = df.format(Calendar.getInstance().getTime());


        String sql = "SELECT LAST_INSERT_ID(nregistro)+1 as nregistro 
FROM muestras ORDER BY nregistro DESC LIMIT 1";

        try {
            java.sql.Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                texto_registro = rs.getString("nregistro");
                System.out.println(" texto registro");
                if (texto_registro == null) {
                 //   lb_registro.repaint();
                    lb_registro.setText("000001");
                    lb_registro.repaint();
                }
            }if (year == ahora) {
                        lb_registro.setText(formattedDate + texto_registro);
                        System.out.println("registro");
                    } else {
                        lb_registro.setText(formattedDate + "000001");
                        lb_registro.repaint();
                    }
        } catch (SQLException e) {
            Logger.getLogger(Ladiox_Insertar.class.getName()).log(Level.SEVERE, null, e);
        }
    }

mysql java

asked by Juan Pablo 20.11.2018 в 20:52

source

1 answer

Change image play to pause of an audio player with angular 6 When making changes to HTML or CSS, they are not visible on the page

score 1 · Answer 1

Since the inconvenience is presented in the reading of the last record, because when you get it you are returning "1800005" when what you want to obtain would be 5, what you can do is support yourself in the casteos to make a substring of your sequence, something like this:

SELECT CAST(SUBSTRING(nregistro, 2), INTEGER) + 1 as nregistro 
FROM muestras ORDER BY nregistro DESC LIMIT 1";

To the column "nregistro" type character, I do a substring to the digits that represent the sequential (obtaining '00004'), then I convert the obtained in whole (obtaining 4), and in the end I add the 1 (obtaining 5) ).

With this instead of getting 1800005, I get the 5. already with this, it depends on you to pass it back to the format you want when you are going to generate the insert. Greetings.