Writers doubt themselves all the time. I'd guess even really good authors. I imagine you can have twenty books published and a really harsh review could still make you wonder. I'm sure there are some people who are self-confident, and there is certainly evidence of some writers thinking they're God, but I think most people wonder and at least occasionally doubt.
An instant of doubt isn't going to make a bestselling author not a bestselling author. It might make him/her decide to write a different book instead, or might mean they spend a day or a week or a month doubting themselves and then get over it and nothing changes. It might mean that they work extra hard on the manuscript and turn out something even better than anything else they've done so far.
Did you know that nerves/confidence works on a bell curve with performance? People who are exceedingly nervous/lack any confidence whatsoever don't perform well because they're so caught up in nerves/doubt they don't get anything done. People on the opposite extreme also don't perform as well because they're so confident they don't actually try as hard as they should. In terms of writing, this would be the person (I've known a few) who is convinced that their first draft is brilliance, that every agent should want to pick it up, and that they'll be an instant bestseller/Nobel prize winner (this is not an exaggeration, I've known people who say things like this). The ones who perform best are those in the middle, those with a bit of doubt/nerves, but not so much that it's disabling. This is basic psychology.
Yes, writers have to be persistent in the face of doubt, but a writer also has to be able to look at those hundreds of rejections and recognize that maybe there's a reason for it. Maybe they were rejected hundreds of times because their writing just wasn't good enough yet. Overconfidence can be just as debilitating in this case as lack of confidence is for a person who gets those hundred rejections and gives up. The overconfident person will not be willing to recognize that maybe they are, in fact, part of the problem.
Also, I think it depends on who you are. Rejections don't really bug me. I just got a full rejection and even that didn't bug me. I sent out another query. Does that mean it won't sting when I have to trunk this book with my favorite character ever? Nope, but I imagine I'll get over it pretty quick. What was a lot harder was writing the book itself (took a very, very long time), and then the months of doing nothing but editing, cutting, and getting critiques from beta readers trying to improve to make it good enough to send out. Going through lists of hundreds of agents and trying to find the best fits, and making my chart, and all the time I spent on that was even harder for me than getting rejected.
I'm hopeful. That's the difference here. Do I think my current work on submission will get very far? Nope, not really. What I do think is that my next one probably will, and if not that one the next, and then I can revisit my favorite. I'm hopeful that I will be good enough, and it's
hope that carries me on. But it's also realistic hope. Would I love to one day be a bestseller that might be able to do this for a living? Goodness yes. Am I going to work toward that? Yup. But my hope is that I'm going to one day be published, and that my hard work and talent will be enough to get me through sticky times (and you know, the inevitable collapse of society

).
Overconfidence is dangerous. Hope is not.