Month: September 2017

Duplicates in two columns in a table

thor-in-the-poppies-lisa-twede

That’s a painting of my cat Thor in the backyard.  Apparently he’s looking at the hose, despite the fact that to his right the yard is full of California Poppies.

Today’s blog has some code that is not my own.  Michael Powaga posted it over on Stack Overflow.  I just wanted to keep track of this little gem because I’ve needed it twice now and both times I ended up going with his solution.

I needed to know how many rows (and which ones) were duplicates but not just on one column, on two columns.  I had a situation where (I feel) there should have been a constraint to prevent duplicates in this table.  I wanted to neatly identify the offending records so I could deal with them.

This is his code.  It’s at the link above, but also copied here just in case.

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city
Advertisements

XML Parser

barn-owl-landing-lisa-twede

References:

Needed to parse an XML import file in a Java application.  I liked what I read about JAXB here, so this is the XML parser I used.  What I liked about it is that I didn’t need to write code to deal with the XML file line-by-line.  With JAXB, you do a little prep work to create a schema for the XML file, and at run-time the JAXB methods just need to know the location of the XML file and the location of the class definitions and it creates your Java objects.  Then you just deal with these objects in your Java code however you please. Not a lot of fuss involved.

Creating the schema

First step is to create a schema from your XML file.  I used a free online schema generator.  I just uploaded my XML file and it created an XSD schema for me.

Creating classes (called binding the schema)

The Java SDK comes with a tool for this.  You don’t need to download or install anything that you don’t already have.  The tool is xjc, and it’s in Java’s bin folder.  You can type xjc -help to learn about all the options.  But the main syntax you need is this:

xjc -d <directory to create Java class files in> -p <package name to use in the class files> <schema created in previous step with a .xsd file extension>

Java code

You need these imports:

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import java.io.File;

The code:

 JAXBContext jaxbContext = JAXBContext.newInstance(location of classes you created using the xjc tool);
 Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();
 Object o = unmarshaller.unmarshal(new File(location and name of XML file to parse));

You can cast the output to the object type immediately if you’re only using this code for one type of XML file and you know what type of object it will always generate.  Or leave it as an Object if you want this to handle any XML file, and then later check the type of object with instanceof .