Perl类操作mysql

20309

　　
　　最近公司需要用Perl做自动化，才学了两三周的Perl，所以代码贴出来写的不好不要笑。正好碰到一个处理Mysql的需求，按照以前的习惯，将几个操作简单整合一下，以防日后之需。
　　下面使用DBI访问Mysql的一个类。
　　1.new  实例化一个类
　　2.query  执行一个查询操作
　　3.do  执行一条sql语句（update,delete, insert）
　　4.execMultiSql  执行带占位符的语句(insert into user(name,age) values(?,?); )
　　5.各种，set,get方法



#!/user/bin/perl -w
package Perl4Mysql;
use strict;
use DBI;
use Data::Dumper;

sub new { #host, dbname, user, password, port
my $class = shift;
my ($host, $dbName, $user, $password, $port) = @_;
$host = "localhost" if !defined($host) or $host eq "";
$dbName = "mysql" if !defined($dbName) or $dbName eq "";
$user = "root" if !defined($user) or $user eq "";
$password = "" if !defined($password) or $password eq "";
$port = 3306 if !defined($port);
my $self = {
"host"=>$host,
"database"=>$dbName,
"user"=>$user,
"password"=>$password,
"port"=>$port
};
bless $self, $class;
return $self;
}
sub query { #sql string for select
my ($self, $sql) = @_;
my @result;
my ($database,$host, $user,$password,$port) = (
$self->{database},
$self->{host},
$self->{user},
$self->{password},
$self->{port}
);
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port", $user, $password)
or die "Can't connect to database: $DBI::errstr\n";    #连接数据库
my $sth = $dbh->prepare($sql);                        #准备
$sth->execute();                                #执行
while(my @res = $sth->fetchrow_array())    {
push @result, \@res;
}                                                #打印抽取结果
$sth->finish;                                    #结束句柄
$dbh->disconnect;                                #断开
return \@result;
}
#执行一条语句
sub do {
my ($self, $sql) = @_;
my ($database,$host, $user,$password,$port) = (
$self->{database},
$self->{host},
$self->{user},
$self->{password},
$self->{port}
);
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port",
$user,
$password)
or die "Can't connect to database: $DBI::errstr\n";    #连接数据库
my $rows = $dbh->do($sql) or die "Can't execute $sql: $dbh->errstr\n";
#    $dbh->commit or die "commit error :$dbh->errstr\n";
$dbh->disconnect;                                #断开
return $rows;
}
#执行多条带占位符（?）的sql
sub execMultiSql {
my ($self, $sql, $params) = @_;
my ($database,$host, $user,$password,$port) = (
$self->{database},
$self->{host},
$self->{user},
$self->{password},
$self->{port}
);
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port",
$user,
$password)
or die "Can't connect to database: $DBI::errstr\n";    #连接数据库
my $sth = $dbh->prepare($sql);
my $rows = 0;
eval {
foreach my $ref_param (@{$params})
{
$rows += $sth->execute(@{$ref_param});
}
$sth->finish;
$dbh->disconnect;                                #断开
    };
if($@) {
print "an error: $@,continue... \n";
$dbh->rollback;
$sth->finish;
$dbh->disconnect;
return 0;
}
return $rows;
}
sub setHost {
my ($self, $host) = @_;
$self->{host} = $host;
}
sub getHost {
my $self = shift;
return $self->{host};
}
sub setDB {
my ($self, $db) = @_;
$self->{database} = $db;
}
sub getDB {
my $self = shift;
return $self->{database};
}
sub getUser {
my $self = shift;
return $self->{user};
}
sub setUser {
my ($self, $user) = @_;
$self->{user} = $user;
}
sub setPassword {
my ($self, $password) = @_;
$self->{password} = $password;
}
sub getPort {
my $self = shift;
return $self->{port};
}
sub setPort {
my ($self, $port) = @_;
$self->{port} = $port;
}

1;
　　测试程序如下：



my @param;
my $sqltemp = "insert blog_comment(content, date, userName) values(?, sysdate(), ?);";
my @p1 = ("this is ime.", "ok");
my @p2 = ("okokla, yoin.","jack");
push @param, \@p1;  #将两个参数以引用放入进去
push @param, \@p2;
my $rows = $mysql->execMultiSql($sqltemp, \@param);
print $rows;

my $ref_result = $mysql->query("select count(*) from blog_comment");
print Dumper($ref_result);