Java Database Connectivity (JDBC)

I've been wondering for a while what techniques people use to access databases. I'm not talking about the JDBC API but how are applications designed. One obvious objective is to consolidate all database access into one package and not have SQL strewn throughout the app. This obviously helps for abstraction purposes, you could easily change the backend, but also makes for easy updates if you make a schema change. I've read about the Database Access Object pattern and like the idea of seperating database access details from the rest of the application. My question is one of implementation. Say you use something like the DAO pattern. Now you have one database access class for each business object and typically (not always) there is a 1:1 mapping of business objects to database tables. How do you actually implement the DAO class? Do you write a single method for each type of query you may perform? For example, say I have a simple schema where table A references table B and table B references tables C. I could query for Object C (from table C) in a number of ways such as:

select * from tableC, tableB where tableC.column2=tableB.column1 and tableB.column3=1234;

or

select * from tableC, tableB, tableA where tableC.column2=tableB.column1 and tableB.column2=tableA.column1;

and so on . . .

I'm wondering if people use some kind of abstraction to dynamically genereate the sql query on the fly or do you simple have seperate method calls for each query and just drop in the values:

ObjectC getCfromB(int tableBcolumn3Value){
String sql="select * from tableC where tableC.column2=tableB.column1 and tableB.column3="+tableBcolumn3Value;
//do your JDBC stuff and return ObjectC
}

Is there a way of having the database access classes understand the schema of your DB so if you supply a column from tableB and ask for objectC it knows what to do?
It seems labor intesive to have to write a method for each type of query,insert, update,delete you might do. How do people handle this? Are there any other standard/accepted methods of separating database details from the rest of the app, other than the DAO pattern?

thanks