How to insert database from java a random unique integer?

Question

i want to create a priority number where an account will e randomly given a unique number. I'm still currently learning java and mysql pardon any mistakes and i hope you'll help me solve this. thanks!

    public void add() {
    try {

        Class.forName("com.mysql.jdbc.Driver");  // MySQL database connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/system?" + "user=root&password=");
        PreparedStatement pstmt = null;

        pstmt = conn.prepareStatement("insert into customer values (?,?,?,?,?,?,?)");

        //customer_id
        pstmt.setInt(1, 0);
        //customer_name
        pstmt.setString(2, tf_name.getText());
        //customer_address
        pstmt.setString(3, tf_address.getText());
        //customer_contactqw
        pstmt.setString(4, tf_contact.getText());
        //customer_email
        pstmt.setString(5, tf_email.getText());
        //order_priority
        pstmt.setInt(6, 1000 + RAND() * 89999); // <-- pls help me here
        //customer_date            
        pstmt.setDate(7, convertUtilDateToSqlDate(dateChooser.getDate()));

        //execute the query
        pstmt.executeUpdate();

        //      JOptionPane.showMessageDialog(null, "Successfully added a new record!");
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);

    }

}

You forgot to describe the problem. In what way does this code not work as expected? How is it failing? — David, Mar 09 '16 at 18:20
Is there any particular reason that you don't want to tell us what the error is? Try to understand that we can't see your screen from here. — David, Mar 09 '16 at 18:26
@David `pstmt.setInt(6, 1000 + RAND() * 89999);` i got an error here. — jane, Mar 09 '16 at 18:29
Then clearly you should correct that error. Whatever that error may happen to be. Which, again, for some reason you're refusing to show us. — David, Mar 09 '16 at 18:32
Why not just do it in the database? `FLOOR(RAND() * 2147483648)`. — tadman, Mar 09 '16 at 18:32

score 2 · Accepted Answer · edited May 23 '17 at 11:59

How do I create a unique ID in Java?

You can create a UUID:

 pstmt.setInt(6, UUID.randomUUID().toString());

Edit: There is no simple way to create an integer unique ID with no collision as there are only 2^32 options, which might end up with a collision. You can create an array which will hold for you all the random generated numbers so far.

If you want to take the risk of collision, you can try:

SecureRandom random = new SecureRandom();
pstmt.setInt(6, random.nextInt(Integer.MAX_VALUE));

If you want to track all the id's you entered so far to prevent a collision you can store them in an array. Let's say you have ArrayList<int> arr;

SecureRandom random = new SecureRandom();
boolean inserted;
do{
    inserted = true;
    int id = random.nextInt(MAX_VALUE);
    for (int i=0; i<arr.size();i++){
        if (arr.get(i) == id)
            inserted = false;
    }
    if (!inserted){
        arr.add(id);
        pstmt.setInt(6,id);
    }
}while(!inserted);

i received an error saying that UUID can't be converted to int. — jane, Mar 09 '16 at 18:28

score 0 · Answer 2 · answered Mar 09 '16 at 18:33

0

Assuming you are using Math.random() (or similar method) inside you RAND() method, it always returns a double. setInt method of PreparedStatement expects an int argument whereas the multiplication results in a double that's why the error.

You can change setInt(.. to setDouble(.. and it would work fine.

answered Mar 09 '16 at 18:33

Darshan Mehta

30,102
11
68
102

changed it to double but got an error that it can't find the RAND() method – jane Mar 09 '16 at 18:39
try `1000 + Math.random() * 89999`. It won't 100% guarantee you the uniqueness though. – Darshan Mehta Mar 09 '16 at 18:45

How to insert database from java a random unique integer?

2 Answers2