java, mysql compare and edit values

Question

I`m building a decision support system in java. Below is a part of code, that scans the search word that is typed in by user, then compares it with database searchlog.searchcolumn values and if the word is not there creates a new entry. BUT in the if statement i want it to check for the entry, and if it IS already in searchlog.searchcolumn column, then I want it NOT to create a new duplicate entry, but to add +1 value to searchlog.counter column for the specific word. for example if search word is "UMBRELLA" and there is already one entry for umbrella in database, i want it to add +1 to counter column in UMBRELLA row.

the purpose of this, is to store all searchwords and keep a track of the most popular ones. Thank you for your time String CheckSearch = "SELECTsearchcolumn FROMsearchlog";

String InsertColumn = "INSERT INTO `mydb`.`searchlog` (`searchcolumn`) VALUES ('"+ InputScanner + "'); 
         //
if (InputScanner.equals(CheckSearch)) 
   System.out.println("value ealready exist, counter well be updated"); 
else
   stmt.executeUpdate(InsertColumn);

EDIT Thank you for advice of using PreparedStatement, but this is my first more or less serious challenge and for this time, let`s just ignore vulnerability of my code. Thanks

At a glance, if you're developing a system (decision support system as you say) then you must use [PreparedStatement](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) to mitigate SQL injection attacks or use an ORM model like Hibernate or JPA along with a controller to achieve a complete MVC architecture, if possible. — Lion, Dec 06 '12 at 22:29
Thank you for advice of using PreparedStatement, but this is my first more or less serious challenge and for this time, let`s just ignore vulnerability of my code. Thanks — G.M, Dec 06 '12 at 22:45

PermGenError · Answer 1 · 2012-12-06T22:34:01.207

your query should be :

String InsertColumn = "INSERT INTO `mydb`.`searchlog` (`searchcolumn`) VALUES ('"+ InputScanner + "'");

Values clause should also be wrapped around brackets.

and always use equals() to check if two strings are meaningfully equal. In case of objects == checks if two reference variables refer to the same object.

      if (InputScanner == CheckSearch) {

should be:

      if (InputScanner.equals(CheckSearch)) {

Then, your if statement would return true if InputScanner is same as checkSearch.

ADVICE:

I strongly recommend you to use PreparedStatement rather than simple Statement to prevent SQL Injection.

PreparedStatement st = conn.preparedStatement(InsertColumn);
st.setString(1, val1);

score 1 · Accepted Answer · answered Dec 06 '12 at 22:58

1

What database are you using? If you are using MySQL, then you should look into INSERT ... ON DUPLICATE KEY UPDATE statement. (Other SQL databases have MERGE, which I'm less familiar with.) Here is the MySQL Documentation.

You will need make your searchcolumn a UNIQUE or PRIMARY column, then something along the lines of:

INSERT INTO searchlog (searchcolumn, count) VALUES ("my search query", 0) ON DUPLICATE KEY UPDATE count = count + 1;

will accomplish what you want.

answered Dec 06 '12 at 22:58

jet

698
6
12

it looks like it works, but i have autoincrement ID column in same table which, using this query, increases by1 each time statement is executed. Ex. if umbrella is searched 10times, id becomes 11 instead of 2 – G.M Dec 06 '12 at 23:14
If your usage case already dictates that the searchcolumn must be unique, I don't see why you need a second autoincrement column. However, if you must have the autoincrement as well, you just need to make the combination of the two columns unique. See http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – jet Dec 07 '12 at 00:40

score 0 · Answer 3 · answered Dec 06 '12 at 22:28

1) Values also need to be in braces

Example:

VALUES ('"+ InputScanner + "');

2) Assuming InputScanner and CheckSearch are Strings/comparable objects, you need to do .equals() instead of ==

Example:

if (InputScanner.equals(CheckSearch)) {

Note: Your SQL statement is prone to SQL Injection attack. Better to use PreparedStatement.

score 0 · Answer 4 · answered Dec 06 '12 at 23:06

There is an MySQL statement for your task:

Requierement: searchcolumn is a unique key.

String sql = "INSERT INTO mydb.searchlog(searchcolumn, counter) VALUES(?, 1) "
    + " ON DUPLICATE KEY UPDATE counter = counter + 1";
PreparedStatement stmt = connection.createPreparedStatement(sql);
stmt.setParameter(1, checkSearch.toUpperCase());
int updateCount = stmt.executeUpdate();

I do not know whether updateCount distinghuishes whether an INSERT or UPDATE happened though. You could use getGeneratedKeys for that purpose, if there is a unmentioned AUTOINCR primary key.

java, mysql compare and edit values

4 Answers4

ADVICE: