Madyson Madyson - 5 months ago 9
Java Question

Inserting into SQL Database more than once

I am making a code generator that makes a new code and then querys a database to see if it exists. If it does, try again to make a different code. If it doesn't exist, add it into the database. But when I add the one code into the database, the query adds 3 different rows with 3 different values. One of the values, is the one supposed to be added, and the other two I don't know where they come from. Why is it inserting 3 when I only set it to add one. My full class file is:

package com.xium.accesscode;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ThreadLocalRandom;

import com.xium.log.ServerLogger;
import com.xium.sql.DBConnections;
import com.xium.utils.StringUtils;

public class NewAccessCode {

static String AccessCodeDBuser = "root";
static String AccessCodeDBpass = "";
static String AccessCodeDBhost = "localhost";

static String newAccessCode;
static String randS;
static String randFinal;

static int min = 000000000;
static int max = 999999999;
static int randI;

public static void AccessCode() {
if(newAccessCode() == 0) {
ServerLogger.writeLog("[ALERT] Database Error");
} else if(newAccessCode() == 1) {
//Reruns the code generator, to make a unique code
newAccessCode();
} else if(newAccessCode() == 2) {
ServerLogger.writeLog("[NOTE] New Access Code: " + newAccessCode);
}
}

/*
* Return Codes:
* 0 - Database Error
* 1 - Code Already Exists
* 2 - New Access Code Added
*/

private static int newAccessCode() {
genAccessCode();
newAccessCode = randFinal;

//Does it already exist?
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet results = null;
String statement = "SELECT count(*) FROM `xium`.`accesscodes` WHERE `accesscode`='" + newAccessCode + "'";
String statement2 = "INSERT INTO `xium`.`accesscodes` (`accesscode`, `used`, `assignedto`) VALUES ('" + newAccessCode + "', '0', '')";

try {
connection = DBConnections.getAccessCodeDB(AccessCodeDBuser, AccessCodeDBpass, AccessCodeDBhost);
preparedStatement = connection.prepareStatement(statement);
results = preparedStatement.executeQuery();

results.next();

if(results.getInt(1) == 0) {

} else if(results.getInt(1) >= 1) {
return 1;
}

connection = DBConnections.getAccessCodeDB(AccessCodeDBuser, AccessCodeDBpass, AccessCodeDBhost);
preparedStatement = connection.prepareStatement(statement2);
preparedStatement.executeUpdate();
return 2;
} catch (SQLException e) {
return 0;
}
}

private static String genAccessCode() {
randI = ThreadLocalRandom.current().nextInt(min, max + 1);
randS = randI + "";
randFinal = StringUtils.toMD5(randS);
return randFinal;
}


}

Answer

Every time you run your AccessCode() function, the if statements are running the statement as well. So don't do:

if(newAccessCode() == 0)

You should make a new integer value set equal to the value of your newAccessCode() function and then check the value of the int.

So:

int returnValue = newAccessCode();

Then check the value of the returnValue.

if(returnValue == 0)

That should fix your problem.

Comments