We stayed at Goose Island State Park for two weeks last December. There are sites that are right on the water, however that time of year it can be pretty windy with blowing sand. Also the sites on the water are very close together so you sacrifice privacy. We stayed in the wooded area of the park. It's very private with lots of space between sites. We were in site 134 or 135 and couldn't see any of our neighbors through the woods. On the nice evenings we took our camp chairs and a cocktail to be beach and watched the sunset. Then we went back to our nice private campsite.
We ended up staying a couple of nights at https://www.saltwaterflatsrvresort.com/
It's near the water but like most of the RV parks in Rockport that are near the water there was a lot of highway noise.
I think it's pretty hard to beat Goose Island State Park. Or any of the other state parks in Texas for that matter. We stayed in quite a few last winter and there wasn't one that I wouldn't go back to.
If you really want to be right on the water there was a couple of RV parks in Port Aransas that might be worth checking out. They all looked like the sites were very close together but you'll find that anywhere near the water.