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");

                    }

score 3 · Answer 1 · answered Feb 01 '12 at 07:52

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 01 '12 at 07:52

Rocky

941
7
11

could you illustrate this to me? or link to the particular doc? Thanks! – Eugene Feb 01 '12 at 12:03
Check this link to find out as to how to setup UNIQUE constraint - http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql & 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 – Rocky Feb 01 '12 at 12:42

prajeesh kumar · Accepted Answer · 2012-02-01T13:18:12.323

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 01 '12 at 13:18

answered Feb 01 '12 at 08:01

prajeesh kumar

1,916
1
17
17

.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 – Eugene Feb 01 '12 at 11:58
you have to give the update =, 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') – prajeesh kumar Feb 01 '12 at 13:08
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? – Eugene Feb 01 '12 at 13:36
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. – prajeesh kumar Feb 01 '12 at 13:45
#1062 - Duplicate entry '01 Feb 2012-12:00-12:30' for key 'PRIMARY' – Eugene Feb 01 '12 at 13:59
can you give the table name and column details and the unique key you are trying to add ? – prajeesh kumar Feb 01 '12 at 14:02
ah..I managed to solve it, I had to flush all data from the table first. Brillant! Prajeesh kumar, so method works very effectively. I will vote you up and tick this solution. Many thanks for the wonderful help. God bless you! – Eugene Feb 01 '12 at 14:32

Adding data into mysql database without repeating data

2 Answers2