Java: while the entry exists = Keep adding number to the end of the entry
Basically I add a record to the database: title and description. I want to do this if the title value already exists - add (1) or (2) to the end to make it distinguishable. I wrote this function:
public boolean existCheck(int userId, String title){
String Validate = "SELECT COUNT(*) FROM memories WHERE userid='"+userId+"' AND title='"+title+"'";
int cnt = 0;
boolean result = false;
try{
rs = st.executeQuery(Validate);
if(rs.next()){
cnt = rs.getInt(1);
System.out.println(cnt);
if(cnt != 0){
result = true;
}
}
}
catch(Exception e){e.printStackTrace();}
return result;
}
It counts how many records exist with this user id and type. If not 0, then it exists and returns true that it exists.
This code adds a record to the database.
if(existCheck(userId, title) == true){
st.executeUpdate("INSERT INTO memories (userid, title, description) VALUES ('"+userId+"', '"+title+"(1)', '"+desc+"')");
}
else{
st.executeUpdate("INSERT INTO memories (userid, title, description) VALUES ('"+userId+"', '"+title+"', '"+desc+"')");
}
But the flaw in this code is that if header (1) already exists, it will add another header (1) again. Of course I could do a lot of if statements, but I'm sure I can do it with smaller lines using a while loop.
I don't know how to use it in this case, but I don't want the server to end up in an infinite loop.
How do I apply a while () loop to this?
source to share
Yours existCheck
should return a score instead of a logical one.
Then you can do something like:
int index = existCheck(userId, title);
if(index > 0){
st.executeUpdate("INSERT INTO memories (userid, title, description) VALUES ('"+userId+"', '"+title+index+"', '"+desc+"')");
} else{
st.executeUpdate("INSERT INTO memories (userid, title, description) VALUES ('"+userId+"', '"+title+"', '"+desc+"')");
}
The first entry entered would title
have a second title+"1"
, third title+"2"
, etc ...
Of course, you will also have to change the WHERE clause in existCheck
to account for different headers:
SELECT COUNT(*) FROM memories WHERE userid='"+userId+"' AND title LIKE '"+title+"'%"
And while you are not completely connected to what you are asking, I highly recommend that you use prepared statements instead of static statements (in which the parameter values are part of the SQL string). It would be much safer.
source to share
You should change existCheck
to look for the title unclear, not:
"SELECT COUNT(*) FROM memories WHERE userid='"+userId+"' AND title='"+title+"'"
which is looking for an exact match, use
"SELECT COUNT(*) FROM memories WHERE userid='"+userId+"' AND title LIKE '"+title+"%'"
What will a heading starting with title
.
Or, so that headers that start like the previous ones don't match, you can also use:
"SELECT COUNT(*) FROM memories WHERE userid='"+userId+"' AND ( title='"+title+"' OR title LIKE '"+title+"(%)' )"
A note %
will match anything in parentheses, so title(soemthing)
it will match as well.
If you are using MySQL you can use regular expressions , I'm not sure about other database servers.
To avoid duplicate headings (1), you need to know the number of headings that match your criteria, so just return COUNT
:
public int existCount(int userId, String title){
String Validate = "SELECT COUNT(*) FROM memories WHERE userid='"+userId+"' AND ( title='"+title+"' OR title LIKE '"+title+"(%)' )";
int cnt = 0;
try{
rs = st.executeQuery(Validate);
if(rs.next()){
cnt = rs.getInt(1);
System.out.println(cnt);
}
}
catch(Exception e){
e.printStackTrace();
}
return cnt;
}
// ...
final int count = existCount(userId, title);
if(count > 0){
final String sql = String.format("INSERT INTO memories (userid, title, description) VALUES ('%d', '%s(%d)', '%s')", userId, title, count, desc);
st.executeUpdate(sql);
}
else{
st.executeUpdate("INSERT INTO memories (userid, title, description) VALUES ('"+userId+"', '"+title+"', '"+desc+"')");
}
source to share
In the exisTCheck() function, I will advice you to return the number instead of boolean. The return number will be number appended with the title. Say if you find only title in DB, then return 0. If you find title(1) then with string split get the number and return it.
While you are calling existCheck() function to check whether it is present or not, rather then true or false you will get counter. You just need to increment that counter with new value according to your requirement and modify your query.
The abstract idea is like this....
Function int existCheck()enter code here
{
// Get the data from the db
// Split the string with title
//if after title no string then return 0
//else return your number
}
// call to existCheck()
int count = existCheck();
//Modify your query with
st.executeUpdate("INSERT INTO memories (userid, title, description) VALUES ('"+userId+"', '"+title+"count', '"+desc+"')");
source to share