Considering full blown web frameworks such as Struts, Stripes etc. which pretty much encapsulates all of the underlying boilterplate away for you I think that knowing the basics is still a fundamental part. Without knowing what a technology is build on you will never fully understand the technology itself.
JDBCSupport - An easy to use, light-weight JDBC framework -
I always get edgy when someone asks me about "relevance"! I think that's because the technology world changes so fast that every time I look, there's something new emerging, and when I'm busy with my head down working, I'm concentrated on that and don't always have time to keep up with emerging technologies. Then again, there are a lot of languages and frameworks which never become popular and die out, so I do only tend to pay attention to the ones with large uptake. Sound dodgy? Yes, it probably is
Anyway, I guess the kinds of Web technologies you are referring to are totally separate things like Ruby on Rails, .NET and PHP, and also Java-based frameworks like JSF and Struts? If you're meaning the latter, an SCWCD is definitely worthwhile: knowing how the fundamentals works is important for when the frameworks don't appear to be doing what you want, or even if there's something you need done which cannot be done inside your chosen framework (admittedly rare, but can happen).
In terms of competing with other languages, Java EE still seems to be holding its ground. I haven't noticed a sharp decline in the UK market for Java developers. Although compared to say PHP, which is generally seen to be less complex, there have always tended to be fewer but more highly paid Java EE jobs around (interestingly, these are often advertised more than PHP jobs, presumably because good people are harder to find). Ruby is gaining ground, but I admit I've had very little time recently to learn anything about it: a colleague of mine says it's very much "able to do everything in just a couple of lines, but has a command set so large that finding the right commands can take hours", which contrasts with Java which is "able to do everything in almost any way, but takes more lines to write". My memory isn't the greatest though, so I think I'd struggle to remember 1000s of Ruby functions and would spend forever looking them up... so I guess I'll just stick with Java for the time being
If you're thinking that Java EE development is for you, then SCWCD basically says to employers that you understand the fundamentals, and can work with a Web container to build applications. More importantly, it means you really understand the fundamentals and can build new components from scratch should you need to. Companies can each use different frameworks on top of this, or even none at all, so then you'd have to demonstrate you know about that framework too. SCWCD is still relevant I think, though it will be interesting to see what the exam authors do with the next version (which I predict won't be available for at least another year, since Servlet 3.0 is still in Proposed Final Draft and will form a key component of Java EE 6).