package YourOrg::DB;

use strict;
use warnings;

use DBI;

# stub method. should be implemented from subclass.
#
# Return:  a database handler.
sub dbh {}

# ( sql => $sql, pholder => [..,..] )
sub row_arrayref {
    my $self = shift;
    my %parm = @_;

    my $sth = $self->dbh->prepare_cached( $parm{sql} );
    $sth->execute( @{ $parm{pholder} } );
    return $sth->fetchrow_arrayref;
}

sub row_hashref {
    my $self = shift;
    my %parm = @_;

    my $sth = $self->dbh->prepare_cached( $parm{sql} );
    $sth->execute( @{ $parm{pholder} } );
    return $sth->fetchrow_hashref( $parm{name} );
}

sub all_hashref {
    my $self = shift;
    my %parm = @_;

    my $sth = $self->dbh->prepare_cached( $parm{sql} );
    $sth->execute( @{ $parm{pholder} } );
    return $sth->fetchall_hashref( $parm{name} );
}

sub all_arrayref {
    my $self = shift;
    my %parm = @_;

    my $sth = $self->dbh->prepare_cached( $parm{sql} );
    $sth->execute( @{ $parm{pholder} } );
    return $sth->fetchall_arrayref;
}

# ( sql => $sql, pholder => [ ..,..] )
sub all_AoHref {
    my $self = shift;
    my %parm = @_;

    my $sth = $self->dbh->prepare_cached( $parm{sql} );
    $sth->execute( @{ $parm{pholder} } );

    my ( %row , @rows );
    $sth->bind_columns( \( @row{ @{$sth->{NAME_lc}} } ) );
    while ( $sth->fetch ) {
        push @rows, { %row };
    }
    return \@rows;
}

1;

### subclass YourOrg::Db
package MyApp::DB;
use base 'YourOrg::Db';
use strict;
use warnings;

sub dbh {
    return DBI->connect_cached( "DBName",
                                "DBUser", 
                                "DBPass",
                                { RaiseError => 1, PrintError => 0 }
                              );
}

1;

### in your code..

use MyApp::DB;

my $row = MyApp::DB->row_arrayref( sql => 'select * from mytable where username = ?', pholder => [ 'Qiang' ] )

print $row->[0];