The story of David and Bathsheba from 2 Samuel 11 is fairly well known. Roughly speaking, David, military hero, awesome king of Israel and God's chosen and anointed leader takes a year off while his army is out fighting. He sees a woman - Bathsheba, who appropriately (or not) enough is taking a bath. David likes the look of her, has sex with her, she gets pregnant. Her husband, who isn't even an Israelite, is off fighting for David. David gets him back and tries to get him to sleep with his wife; he refuses as he's still mid-campaign, so David arranges for the tactics to go a little wonky to get Uriah killed.
The application of that story I hear second most often is that it's a bad idea to find yourself with lots of time on your hands and nothing to do, especially if you're a bloke with a fairly strong sex drive and opportunity to use it. That is a true and valid application. It's why 2 Samuel 11 comes right after 2 Samuel 10, which is about the war that David should have been fighting in instead of watching naked women. But I don't think it's the main point.
The application that I hear most often is that even the best human leaders mess up, but that doesn't stop God using them. That's kind of true, and it is definitely important to know and understand that there is such a thing as real forgiveness, but it is a) so not the point of the passage and b) like a surgeon, very dangerous when not qualified properly.
What is the main point? Well, in 2 Samuel 12, Nathan the prophet confronts David with his behaviour in quite a clever way, ending with this conclusion from God...
Why did you despise the word of the LORD by doing what is evil in his eyes? You struck down Uriah the Hittite with the sword and took his wife to be your own. You killed him with the sword of the Ammonites. Now, therefore, the sword will never depart from your house, because you despised me and took the wife of Uriah the Hittite to be your own.'
"This is what the LORD says: 'Out of your own household I am going to bring calamity upon you. Before your very eyes I will take your wives and give them to one who is close to you, and he will lie with your wives in broad daylight. You did it in secret, but I will do this thing in broad daylight before all Israel.' "
Then David said to Nathan, "I have sinned against the LORD."
Nathan replied, "The LORD has taken away your sin. You are not going to die. But because by doing this you have made the enemies of the LORD show utter contempt, the son born to you will die."
2 Samuel 12:9-14, NIV
Yes, David repents, and that's what Psalm 51 is about, and there's a lot we can learn about repentance from that. Repentance saves David's life here, but it doesn't make it all alright. The next EIGHT CHAPTERS are all about the virtual collapse of the kingdom because of David's sons Absalom and Amnon and their lack of self control when it comes to having sex with the wrong women and killing people, which was exactly David's problem. Later, David's son and successor Solomon would have the same problem with women, which would lead to the kingdom splitting up. The start of the disastrous downward spiral in Israel's history from the high point of God's promise to David in 2 Samuel 7 and his victories in 2 Samuel 8-10 is often traced to Solomon's palace building, or to his very large number of foreign wives who introduced adultery, but actually I think it starts with David and Bathsheba. Yes, there's a kind of repeat of that with Solomon, but Solomon is just following in the footsteps of his father, only worse, as Solomon's son does after him.
The key message of 2 Samuel 11, David and Bathsheba, is that sin among the leaders of God's people is incredibly destructive. Yes, it can be forgiven if and when we repent, but that only means we don't go to hell immediately (and sinning more because we think we can be forgiven needs to be repented of itself). But it doesn't take away the awful temporal consequences of that sin for God's people.
Not many of you should presume to be teachers, my brothers, because you know that we who teach will be judged more strictly. We all stumble in many ways. If anyone is never at fault in what he says, he is a perfect man, able to keep his whole body in check.
James 3:1-2, NIV
David was one of the best kings there was, but he was not good enough.
The lesson of this is that leaders need to be holy. And while that should be strived for, at the end of the day, it is unattainable. The only adequate leader of God's people, the only king who is good enough is Great David's Greater Son - Jesus.