Class::DBI isn't really for abstracting the SQL-generation process. Of course, it must generate SQL, but everything's under the hood -- you don't use Class::DBI to get back SQL that you execute yourself. Use it (or similar modules*) when you want to have an abstract, object-oriented view of your data and not have to think about SQL at all. The catch for this abstraction is that it's harder to do complex queries all at once. But don't think you can't get the same results, for instance:

for my $cd (@cds) {
    print  $cd->name, $/;
    printf " - %d: %s\n", $_->num, $_->song->name
        for sort { $a->num <=> $b->num } $cd->tracks;
}
[download]

This code simulates a join among the "cds", "tracks", and "songs" tables using an object-persistence interface. You get the same results. Of course, it uses many queries on the database instead of just one join query. You need to weigh the gains of this abstraction against the performance of your complex queries when you simulate them like this. But also keep in mind that Class::DBI and friends execute most select queries using the primary key as a constraint, making all those small individual queries very fast.

OK, now assuming you really do want to abstract the SQL-generation process yourself, to do the whole generate-SQL/prepare/execute/fetch process, then here are some good starting points for common queries. Personally, I prefer using sprintf for SQL generation:

my $select = sprintf "select %s from $table where %s",
    join(","     => @cols),
    join(" and " => map { "$_=?" } keys %constraints);

my $sth = $dbh->prepare($select);
   $sth->execute( values %constraints );

#########

my $insert = sprintf "insert into $table (%s) values (%s)",
    join("," => keys %data),
    join("," => ('?') x scalar keys %data);

my $sth = $dbh->prepare($insert);
   $sth->execute( values %data );

##########

my $update = sprintf "update $table set %s where %s",
    join(","     => map { "$_=?" } keys %data),
    join(" and " => map { "$_=?" } keys %constraints);

my $sth = $dbh->prepare($update);
   $sth->execute( values %data, values %constraints );
[download]

But if you do need to do complex stuff like joins, you'd probably be better off with a CPAN solution. You're own your own there, I've never tried any of them myself. Try searching for SQL or in the DBIx namespace.

*: similar modules = Class::Tables. I'm quite biased, but I can't pass up any opportunity for a plug ;)