To be clear to anyone else who may read this, the only reason you expend more energy separating hydrogen from oxygen when you split water than you get back when burning it is because of losses in the machine that splits the water. Chemically the energy required to split water and the energy you get back when it burns is exactly the same.
Also, I'd like to point out that capacitors and batteries act quite differently on discharge. A battery is typically putting out a very steady amount of voltage that drops only slightly as the battery discharges. A capacitor's voltage follows a time constant curve on charging and discharging, and if you discharge the capacitor to 50% the output voltage has dropped by 50% as well.
Current super-capacitors have about 10% the energy density of conventional batteries, but have 10-100 times the power density due to their ability to discharge extremely quickly, whereas batteries are limited by their chemical reactions and internal resistance and charge and discharge much slower. So super caps have only 10% the storage capacity of a battery, but can apply that energy as power at a much greater rate.
I think asking if capacitors will rival batteries is like comparing apples to oranges. Batteries and capacitors are simply two energy storage devices with different uses and strengths and weaknesses. Even if we do get capacitors to comparable energy densities the differences between batteries and capacitors means that batteries will probably never die out.
However, here's a link to a prototype capacitor with an energy density of about 300 Wh/kg. For comparison, typical batteries you use around the house have an energy density of about 100-300 Wh/Kg. I don't know if these are being manufactured yet or not.
http://nanopatentsandinnovations.blogspot.se/2010/01/polyaniline-nanoporous-carbon-electrode.html