As it's written, you actually don't need the new() at all. Any code that wants to use this can just do:

use MyDB;

my $dbh = MyDB->dbh();
[download]

If there's already been a connection made, it will get the existing $DBH.

Your $DBH variable is a lexical in the scope of the file that your package is in. It starts out as undef, but when you set it, it will stay set. It never goes out of scope and so never loses its value. There's not much more to it than that. Is there some more specific question we can answer than "how does this work"?

I've written code similar to this that seems to work fine under mod_perl with Apache::DBI, but I don't know if I'd call it a best practice.

Your $DBH variable is a lexical in the scope of the file that your package is in. It starts out as undef, but when you set it, it will stay set. It never goes out of scope and so never loses its value.

I know that $DBH is file lexical and visiable to the MyDB package. but i think the second call to MyDB->dbh is independent to the first one and like a new instance of something.

but i think the second call to MyDB->dbh is independent to the first one and like a new instance of something.

New instance of what? $DBH? no.

{
   package Cow;
   my $i;
   sub moo { print(("moo " x ++$i), "\n"); }
}

Cow->moo();  # moo 
Cow->moo();  # moo moo 
Cow->moo();  # moo moo moo
[download]

{
   package MyDB;
   my $DBH;
   sub dbh {
      if ($DBH) {
         print "found existing $DBH\n";
      } else {
         $DBH = 'dbh';
         print "Init $DBH\n";
      }
   }
}

MyDB->dbh();  # Init dbh
MyDB->dbh();  # Found existing dbh
MyDB->dbh();  # Found existing dbh
[download]

dbh caching works but i sort of reaching the solution by trying/guessing but don't know exactly why.

can someone please explain to me?

by the way, is it a good practice for caching dbh this way?

Will it still work under mod_perl without using Apache::DBI?

I would do it. The only real problems you might get with this approach is if you need multiple handles or switch schemas.

The singleton approach won't deal with stale handles. Once your $dbh has lost its connection, you'll have to live with that bad value for the lifetime of your script. connect_cached solves the issues you describe, _and_ will handle reconnecting for you. IMHO, it makes Apache::DBI redundant (although it doesn't hurt).

Others have already answered your question better about the specific domain of DBI objects and caching but I was also struck that you'd just reinvented the interface for Class::Singleton.

True. But as far as I know Apache::DBI would do more or less the same, but with the advantage that it would allow multiple connections with differing parameters. Weather that would be usefull for the CGI script in question I don't know.
"What should it profit a man, if he should win a flame war, yet lose his cool?"

Right. It adds nothing in this particular case but for other similar problems when Apache *isn't* involved, it's nice to know the perl module that implements that interface.

⠤⠤ ⠙⠊⠕⠞⠁⠇⠑⠧⠊

Just a little deeper than the CPAN search there is an exact Class:Singleton DBI implementation (scroll a page or two down):

Class:Singleton DBI Example :)

    package MyApp::Database;
    use vars qw( $ERROR );
    use base qw( Class::Singleton );
    use DBI;

    $ERROR = '';

    # this only gets called the first time instance() is called
    sub _new_instance {
        my $class = shift;
        my $self  = bless { }, $class;
        my $db    = shift || "myappdb";    
        my $host  = shift || "localhost";

        unless (defined ($self->{ DB } 
                         = DBI->connect("DBI:mSQL:$db:$host"))) {
            $ERROR = "Cannot connect to database: $DBI::errstr\n";
            # return failure;
            return undef;
        }

        # any other initialisation...
        
        # return sucess
        $self;
    }
[download]

The above example might be used as follows:

    use MyApp::Database;

    # first use - database gets initialised
    my $database = MyApp::Database->instance();
    die $MyApp::Database::ERROR unless defined $database;
[download]