Adding data into mysql database without repeating data

Question

I'm supposed to add data from my csv file into my database. I have no problem adding data into the database, however, I have a situation where my java program has to add new values or update current values into the database, say under my column "date". When a new data is not identified in my current database, it would be added. However, under the same row, there's a column called "demand". If the date is the same but the demand is different. It would have to be updated. At the moment, my script can only retrieve the data line by line from mysql and compare with the data collected from the csv file. When the date is different, it can add the data into a new row. The problem is that the java program that I created can only read line by line from the beginning. I would have a repetition of data because when it start comparing from the earliest data to the latest. For example, my earliest date is 01 jan and it is compared to my csv data say 31 jan. However, my later entries has a 31 jan already stored. But my java program continue to add this data in because it compared from the beginning. I will explain in greater detail if you need more information.

public void readDataBase(int val, String d1, String d2, String d3, String d4 ) throws Exception {
try {
     Class.forName("com.mysql.jdbc.Driver");
     connect = DriverManager.getConnection("jdbc:mysql://localhost/testdb?"
                        + "user=root&password=");
     statement = connect.createStatement();
     resultSet = statement.executeQuery("SELECT COUNT(*) FROM testdb.amg");
      while (resultSet.next()) {
      total = resultSet.getInt(1);
         }

         resultSet = statement.executeQuery("select * from testdb.amg");
         try{
                    int id;
                    String Date,Demand;
                    while (resultSet.next()) {
                    id = resultSet.getInt("id");

                    Date = resultSet.getString("Date");
                    Demand = resultSet.getString("Demand");
                    System.out.println("total: " + total);
                    System.out.println("d4: " + d4);
                    if (!Date.equals(d1) && !Demand.equals(d3))
                    {
                        int val1 = total +1;
                        preparedStatement = connect
                        .prepareStatement("insert into  testdb.amg values (?, ? ?)");
                        preparedStatement.setInt(1, val1);
                        preparedStatement.setString(2, d1);
                        preparedStatement.setString(3, d3);
                        preparedStatement.executeUpdate();

                        System.out.println("UpdatedII");

                    }

Rocky · Accepted Answer · 2012-02-01 07:52:46Z

3

Identify the distinct columns in your database (date + demand + whatever_is_distinct ) and define a UNIQUE constraint on that combination.

By doing that ,during insertion, if there is any Constraint Violation exception that gets thrown , update that record or else the record gets inserted.

answered Feb 1, 2012 at 7:52

Rocky

9517 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eugene Over a year ago

could you illustrate this to me? or link to the particular doc? Thanks!

Rocky Over a year ago

Check this link to find out as to how to setup UNIQUE constraint - stackoverflow.com/questions/635937/… & just put a try catch around the code which is trying to insert into database . In the catch block , check the exception message and if it has something to do with constraint violation then in the catch block update the record instead of inserting the record

prajeesh kumar · Accepted Answer · 2012-02-01 13:18:12Z

1

As Rocky said, define the unique key on the table. Since the database is MySQL, you can achieve what you want by simple query with INSERT... ON DUPLICATE KEY UPDATE ... INSERT ... ON DUPLICATE KEY UPDATE Syntax

In your case it can be

insert into  testdb.amg values (?, ? ?) on duplicate key update <update fields here>

So the final query shall be

"insert into testdb.amg values (?, ?, ?) on duplicate key update Demand=?"

and add

preparedStatement.setString(4, d3);

edited Feb 1, 2012 at 13:18

answered Feb 1, 2012 at 8:01

prajeesh kumar

1,9761 gold badge17 silver badges17 bronze badges

7 Comments

Eugene Over a year ago

.prepareStatement("insert into testdb.amg values (?, ?, ?) on duplicate key update d3"); I added this line, however it came out with an error message You have an error in your SQL syntax; check the manual that corresponds to your MySQL

prajeesh kumar Over a year ago

you have to give the update <column name>=<value to update to>, in your case it may be "insert into testdb.amg values (?, ?, ?) on duplicate key update Demand=?" and add preparedStatement.setString(4, d3); (If your third column is 'Demand')

Eugene Over a year ago

Hi kumar, thanks for the help. However, I have this problem: Date Time Demand 01-Jan 01:00-02:00 500 01-Jan 02:00-03:00 600 However, I'm adding a new data, a new demand value 01-Jan 01:00-02:00 700 I want the system to update with the new demand value. However, it must be able to differentiate which row to update. In this case, there's a need for 2 unique key?

prajeesh kumar Over a year ago

Give only one unique key with multiple columns if necessary as you can identify. I mean if there can be only one value for a particular id on a particular date then keep the unique id with both 'id' and 'date' columns.

Eugene Over a year ago

#1062 - Duplicate entry '01 Feb 2012-12:00-12:30' for key 'PRIMARY'

|

Collectives™ on Stack Overflow

Adding data into mysql database without repeating data

2 Answers 2

2 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related