Click here to Skip to main content
15,891,431 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a map which holds word as a key and count as value. What I want is when I insert same word into db I want to update its count. But it updates count and insert a new row with updated count num and word. How can I fix this?

What I have tried:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;

public class DatabaseOperations {
	DBConnection conn = new DBConnection();
	Connection con = conn.connDb();
	Statement statement = null;
	PreparedStatement preparedStatement = null;

	public void insertWordCount(HashMap<String, Integer> map) throws SQLException {
		Iterator<String> wordIterator = map.keySet().iterator();
		String sql = ("INSERT INTO words" + "(count, word)" + "VALUES (?,?)");
		statement = con.createStatement();
		preparedStatement = con.prepareStatement(sql);
		while (wordIterator.hasNext()) {
			String key = wordIterator.next();
			ResultSet rs = statement.executeQuery("SELECT * FROM words WHERE word='" + key + "'");
			String query = "UPDATE words SET count= ? WHERE word = ?";
			preparedStatement.setInt(1, map.get(key));
			preparedStatement.setString(2, key);
			preparedStatement.executeUpdate();
			while (rs.next()) {
				preparedStatement = con.prepareStatement(query);
				int oldCount = rs.getInt(1);
				int newCount = oldCount + map.get(key);
				preparedStatement.setInt(1, newCount);
				preparedStatement.setString(2, key);
				preparedStatement.executeUpdate();
			}

		}
	}
Posted
Updated 16-Apr-22 6:18am

1 solution

Your code is somewhat confusing. You create an INSERT statement, and then create a preparedstatement of it but never use either version. You then start an iterator and run a SELECT on the first key of the map. Using that information you execute an update statement posting the same values back to the database. You then use the resultset from the SELECT statement, (which is likely only one record). You then extract the fields from the record, add the record count to the map count and update the database with that information. I think you need to reorganise your code into a more logical sequence.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900