Apr 17 2013
SO:Removing duplicate rows from data pulled from db in java
I’ve become an active member of stack overflow, both answering and making questions.
It’s amazing, there’s a whole community there that I hadn’t noticed before.
As I have not had time to post new items, I will be borrowing my answers to stackoverflow questions and re posting them here.
User Question:
So here’s my question. I have a giant text file of data and I need to input all of this data into a mySQL database fast through obviously using a java program. My only problem is that, the data is identified by a certain ID. Some of these ID’s have duplicates and contain all the same info as eachother. I would like to remove all of these for sorting purposes and clarity sake.
What would be the best way to go about this? If anyone could help I’d appreciate it
Answer:
While reading the data have a hashmap or hashset. check if the id exists in the hasmap/hashset and if so continue. otherwise enter in set/map and insert.
An aside: The difference between hashmap and hashset is hashset only takes values while hashmap takes key values. However, Hashset itself uses a hashmap within memory and just inserts a dummy object for values. See: Differences between HashMap and Hashtable?
Oct 2 2014
Calling Mysql Iteratively and Passing Parameters from Bash Loop
sql script
So make a file with the following (or whatever else you wish):
SET @i = 1; use databaseName; SET @pID=@i+100; SET @partnerID=concat('partner',@pID); select @i, @pID , @partnerID;Now, this will print out the values of i, pID, and partnerID.
Below that you could include insert statements or whatever else you wish.
Now how do we call this from bash and set the @i param?
bash script
user=root password=mysqlPass database=db for i in {300..400..5} do sed -i "1 s/.*/set @i=$i;/" sample.sql mysql --user="$user" --password="$password" < sample.sql doneSo how does this work?
We use sed to replace the first line completely in sample.sql with a new “set @i=” command containing the i param we want.
We then call mysql and feed the rewritten file.
Another way to do this would be to concatenate the sample.sql contents to the set command, and feed them to mysql in memory.
By Menelaos Bakopoulos • Mysql 0