Performing Increment and Decrement with PreparedStatement

Question

I have a column in my database which just contains a counter value. The value should be 1 if the entry is new, otherwise it will either be incremented or decremented if the entry is being updated.

I am trying to figure out how to use the java.sql.PreparedStatement for this purpose.

It is to my understanding that the SQL query which eventually has to be created has to look like:

INSERT INTO `DropletNames` (`Title`,`Count`) VALUES ('Travel to Mars',Count + 1)

My question is: how do I create this query? (fyi: I know the Count + 1 is sort of irrelevant here, since INSERT should always set it to 1, but I am trying to use the same syntax for INSERT as I would for UPDATE)

I tried doing the following:

String query = "INSERT INTO `DropletNames` (`Title`,`Count`) VALUES (?,?)";
PreparedStatement stm = con.prepareStatement(query);
stm.setString(1,"Travel to Mars");
stm.setString(2,"Count + 1"); // I used this because I couldn't find a better way of setting this

However, that obvious produces the wrong result, because it attempts to insert "Count + 1" as a String, instead of evaluating Count + 1.

The only other way I can think of doing it is:

String query = "INSERT INTO `DropletNames` (`Title`,`Count`) VALUES (?,Count + 1)";
PreparedStatement stm = con.prepareStatement(query);
stm.setString(1,"Travel to Mars");

But I am unsure if that violates the "best practices" rules for using the PreparedStatement's setter methods when building the SQL Statement. I cannot see how that would leave me open to SQL injection or other vulnerabilities, but maybe there is something I haven't considered.

Is there a way to do this using the PreparedStatement class, or is my second solution the way I should do this?

@Drew, well he could be using the ON DUPLICATE KEY UPDATE syntax — David Soussan, Nov 02 '15 at 18:51
if you want to see an IODKU, see this link I wrote up. there are better I am sure: http://stackoverflow.com/a/33319173 ... some will waste hours on it thinking it is IOU (insert or update). No, it is IO**DK**U — Drew, Nov 02 '15 at 18:54
The `Count` column is just in this example to simplify the problem. In actual fact, we have a column for each day of the week. When a reference to the `Title` is made, we increment the day of the week column for what day of the week it is. At midnight for each day, we reset the day's counter to 0. Then, throughout the day we sum up all 7 counts to attempt to determine the "most trending this week" for the user. This question can be applied to any of those 7 columns. — Adrian, Nov 02 '15 at 19:07
how about if you give @DavidSoussan a `show create table DropletNames` or whatever the table is called. Then he can make sure it has a unique key so IODKU will work — Drew, Nov 02 '15 at 19:09

score 0 · Answer 1 · answered Nov 03 '15 at 08:01

If you want to increment a column value then the syntax is

UPDATE `DropletNames` SET `Monday` = `Monday` + 1 WHERE `Title = "Travel to Mars"

That will just add 1 to whatever value is in the Monday column. Similarly if you want to decrement the value it will be

UPDATE `DropletNames` SET `Monday` = `Monday` - 1 WHERE `Title = "Travel to Mars"

Now the only thing you need to change to make this into a prepared statement is the placeholder for the WHERE value.

EMUEVIL · Answer 2 · 2015-11-03T00:56:43.670

After reading your comments, you appear to want to increment a value that counts a value per day. Try the following example:

--Create Schema
CREATE TABLE #day(
    [ID] int,
    [DayOfWeek] NVARCHAR(16),
    [Value] INT
)
--Add Days
INSERT INTO #day ([ID], [DayOfWeek], [Value]) VALUES
    (1,'Sunday',0),
    (2,'Monday',0),
    (3,'Tuesday',0),
    (4,'Wednesday',0),
    (5,'Thursday',0),
    (6,'Friday',0),
    (7,'Saturday',0)

--Increment Day Example
UPDATE #day
SET [Value] += 1
WHERE [DayOfWeek] = 'Sunday'

--Increment Day Based on the current day
UPDATE #day
SET [Value] += 1
WHERE [ID] = DAY(GETDATE())

--Display Table
SELECT * FROM #day

OUTPUT:

ID  DayOfWeek   Value
1   Sunday  1
2   Monday  1
3   Tuesday 0
4   Wednesday   0
5   Thursday    0
6   Friday  0
7   Saturday    0

Performing Increment and Decrement with PreparedStatement

2 Answers2