Wise Monks,

I made a module that is supposed to be an interface between a perl program and the DBI module. The basic idea is that there is just 1 method, named do() that you use to get or set data.

The do() method takes 2 or 3 arguments. The first argument is the table name, the second argument is a reference to a hash with key-value pairs to search for. (key is column name)
The third (optional) argument is a reference to a hash of key-value pairs to set/insert/update.

A main program using this module could look like this:
Edited to comply to Juerd and iburrell ;) 
use DBIx::Easy;

my $dbez = DBIx::Easy->new("DBI:mysql:database=mydb;host=localhost", '
+dbusername', 'dbpassword');
foreach ($dbez->do(tableName => 'mytable', get => {'column_name' => 'v
+alue', 'another_column_name' => 'another value'}, set => {'column_nam
+e' => 16}))
{
        foreach my $key (keys %$_)
        {
                print "$key: ", $$_{$key} || '', "\t";
        }
        print "\n";
}

[download]
The module code is below: 
package DBIx::Easy;
use strict;
use warnings;
use DBI;

sub new
{
        my $class = shift;
        my @connectionParameters = @_;
        my $self = {};
        bless($self, $class);
        $self->{'dbh'} = DBI->connect(@connectionParameters);
        return $self;
}

sub do
{
        my $self = shift;
        my $tableName = shift;
        my $getRef = shift;
        my $setRef = shift;

        if ($setRef && ref($setRef) eq 'HASH' && %$setRef)
        {
                if ($getRef && ref($getRef) eq 'HASH' && %$getRef)
                {
                        ### update
                        my @results = $self->select($tableName, $getRe
+f);
                        $self->update($tableName, $getRef, $setRef);
                        for (my $i = 0; $i < @results; $i++)
                        {
                                foreach (keys %$setRef)
                                {
                                        $results[$i]->{$_} = $$setRef{
+$_};
                                }
                        }
                        return @results;
                }
                else
                {
                        ### insert
                        $self->insert($tableName, $setRef);
                        return ($setRef);
                }
        }
        ### select
        return $self->select($tableName, $getRef);
}
sub select
{
        my $self = shift;
        my $tableName = shift;
        my $dataRef = shift;
        my $dbquery = "select * from $tableName";
        my $counter = 0;
        my @placeholder = ();
        foreach (keys %$dataRef)
        {
                $dbquery .= $counter ? ' and ' : ' where ';
                $dbquery .= "$_=?";
                push (@placeholder, $$dataRef{$_});
                $counter++;
        }
        my $sth = $self->{'dbh'}->prepare($dbquery);
        $sth->execute(@placeholder);
        my @result;
        while (my $ref = $sth->fetchrow_hashref())
        {
                push (@result, $ref);
        }
        $sth->finish();
        return @result;
}
sub insert
{
        my $self = shift;
        my $tableName = shift;
        my $dataRef = shift;
        my $dbquery = "insert into $tableName(";
        $dbquery .= join(", ", (keys %$dataRef));
        $dbquery .= ") values(";
        $dbquery .= join (", ", map('?', (keys %$dataRef)));
        $dbquery .= ")";
        my $sth = $self->{'dbh'}->prepare($dbquery);
        $sth->execute(values %$dataRef);
}

sub update
{
        my $self = shift;
        my $tableName = shift;
        my $getRef = shift;
        my $setRef = shift;
        #$rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 
+1");
        #UPDATE search SET Category = 'Java', Test = '1' WHERE Categor
+y = 'Jive' AND Test = '2';
        my $dbquery = "update $tableName set ";
        $dbquery .= join(", ", map ("$_=?", keys %$setRef));
        $dbquery .= " where ";
        $dbquery .= join(" and ", map ("$_=?", keys %$getRef));
        my $sth = $self->{'dbh'}->prepare($dbquery);
        $sth->execute(values %$setRef, values %$getRef);
}
sub DESTROY
{
        my $self = shift;
        $self->{'dbh'}->disconnect();
}

[download]
Any comments would be highly appreciated.

In reply to RFC: Simple DBI abstraction by Jaap

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.