I noticed Japan's most beautiful onsen (hot springs) are attached to hotel. What is the common way of onsen experience in Japan? Are they available to book for those who don't stay on those hotels normally? Or does it totally depends on the hotel?
Yes, they often (but not always) are, especially in larger hotels. A dip in the baths alone (入浴 nyuyoku) usually goes for less than ¥1000 and is typically available only in the daytime hours only when overnight guests are away. Many hot spring resorts offer "spa tours" (湯巡り yumeguri) that let you sample multiple baths for a discounted price.
Some travel agents also offer daytrip packages (日帰り温泉 higaeri onsen) consisting transport, entry to the baths plus lunch.
It depends on the hotel, but I would say that normally they are available to non-staying guests, especially in famous onsen towns, but only during the daytime. To find out for a particular hotel, consult their website or contact them directly.
Some onsen towns have share privileges, if you are staying in a hotel, ryokan, minshuku, etc in the town you can use any of the hot spring facilities in any establishment.