DBIx::Custom的使用参考

DBIx::Custom的使用参考

转自: http://www.cnblogs.com/tjxwg/archive/2013/02/08/2909075.html

DBIx :: Custom是DBI的拓展。提供很多方便的功能。相对其他关系映射的模块,该模块更容易使用因为很多用法接近sql的语法。 
最简单的方法如下。

use DBIx::Custom; my $dbi = DBIx::Custom->connect(dsn => $dsn); $dbi->insert({id => 1, title => 'Perl'}, table => 'book'); $dbi->update({title => 'Perl'}, table => 'book', where => {id => 1}); $dbi->delete(where => {id => 1}, table => 'book'); my $rows = $dbi->select(table => 'book')->all;

特点:

  • 执行增删改查操作很方便
  • 灵活的创建where查询语句
  • Named place holder 的支持
  • Model support
  • 链接管理的支持
  • 支持 MySQL, SQLite, PostgreSQL, Oracle, Microsoft SQL Server, Microsoft Access, DB2 or anything,
  • 通过列名检索
  • 灵活创建order by子句
  • 大数据量的快速插入
  • Bulk insert support (MySQL, PostgreSQL)

安装:

cpan DBIx::Custom

数据库链接

# Connect to Oracle (SID) my $dbi = DBIx::Custom->connect(
  dsn => "dbi:Oracle:host=localhost;port=1521;sid=lddb", user => 'xxxx' password =>' xxxx' );

查询的执行

  • Execute SQL

           通过execute方式

 # Execute SQL my $result = $dbi->execute("select * from book");

返回值是DBIx::Custom::Result object,通过all method调用所有行。

 # Fetch all rows my $rows = $result->all;

DBIx::Custom 支持名字占位替换

 # Named placeholder my $result = $dbi->execute( "select * from book where title = :title", {title => 'Perl'}
  );

同名占位符的使用

例如开始时间与结束时间都用 :date占位符

"select * from book where date > :date and date < :date"

通过数组引用来实现例:date => [‘2012-01-01′, ‘2012-02-03′].

 # Use named placeholder more than once my $result = $dbi->execute( "select * from book where date > :date and date < :date", {date => ['2012-01-01', '2012-02-03']}
  );

  • Insert row

         插入一行的方法

$dbi->insert({id => 1, title => 'Perl'}, table => 'book');

第一个参数是一个hash的引用,表明是可选的option中设置

insert into book (id, title) values (?, ?);

插入的可选的参数

设置插入时间到指定的列

ctime => 'created_time'

缺省的格式:YYYY-mm-dd HH:MM:SS,这个值被now的值所替代

Example:

$dbi->insert({title => 'Perl}, table => 'book', ctime => 'created_time');

和下边的代码执行效果相同

 use Time::Piece; my $now_tp = localtime; my $now = $t->strftime('%Y-%m-%d %H:%M:%S'); $dbi->insert({title => 'Perl', created_time => $now}, table => 'book');

  • Update row

          更新一行通过update方法

$dbi->update(
    {title => 'Perl', author => 'Ken'}, table => 'book', where => {id => 1}, );

           更新所有行的方法

$dbi->update_all({title => 'Perl', author => 'Ken'}, table => 'book');

  • Delete row

        删除一行的方法.

$dbi->delete(
    table => 'book', where => {id => 1}, );

     删除所有行通过 delete_all 方法

$dbi->delete_all(table => 'book');

  • Select row

      通过select 方法查询

my $result = $dbi->select(table => 'book');

Return value is DBIx::Custom::Result, which can fetch rows.

返回DBIx::Custom::Result 类型的值,通过fetch返回行

my $rows = $result->all;

select 的东西感觉相对于写sql语句更麻烦,直接excute写sql语句就可以了。

具体用到再仔细阅读测试就是了。

  • Count rows

          返回行数通过count方法

my $count = $dbi->count(table => 'book', where => {title => 'Perl'});

同select单列单值的方法同样的执行效果

my $count = $dbi->select('count(*)', table => 'book', where => {title => 'Perl'})->value;

  • Fetch row

    • fetch – each row as array

              读取一行放入数组引用

    my $row = $result->fetch;

    通常fetch都是通过while循环获取值,没有值返回undef

    while (my $row = $result->fetch) { my $title = $row->[0]; my $author = $row->[1];
      }

    • fetch_one -返回一行作为数组

               返回一行到数组引用,之后自动调用dbi finish。

    my $row = $result->fetch_one;

    • fetch_all – 返回所有行

    my $rows = $result->fetch_all;

    fetch_hash -fetch每一行hash方式

    获取一样放入hash索引

    my $row = $result->fetch_hash;

    通常通过wile循环获取每一行的返回至,没有返回undef

    while (my $row = $result->fetch_hash) { my $title = $row->{title}; my $author = $row->{author};
      }

    • fetch_hash_one or one – 返回一行hash

    my $row = $result->fetch_hash_one;

    可以通过别名简写one获取

    my $row = $result->one;

    • fetch_hash_all or all – fetch all rows as hash

    my $rows = $result->fetch_hash_all;

    简写

    my $rows = $result->all;

  • Useful fetching ways

    • value – only one value

           获取第一行第一列的值,没有返回undef

    my $value = $result->value;

    Example:

    my $count = $dbi->select('count(*)')->value;

    • values – all rows of first column

           获取第一行所有列的值放入数组索引

    my $values = $result->values;

    • flat – Convert rows to flatten list.

              转变rows到一个list容器

    my @list = $dbi->select(['id', 'title'])->flat;

    如果想放入hash中,你能通过键值对很容易实现

     # (1 => 'Perl', 2 => 'Ruby') my %titles = $dbi->select(['id', 'title'])->flat;

    • kv – Create key-value pairs

          创建键值对

    my $key_value = $result->kv; my $key_values = $result->kv(multi => 1);

    Example:

    key是id,只是hansh引用

    my $books = $dbi->select(['id', 'title', 'author'])->kv;

    返回下列数据

    { 1 => {title => 'Perl', author => 'Ken'}, 2 => {title => 'Ruby', author => 'Taro'}
    }

    如果一个key有多个值,使用multi选项

    my $books = $dbi->select(['author', 'title', 'price'])->kv(multi => 1);

    返回下列数据

    复制代码
    {
        Ken => [
          {title => 'Perl', price => 1000}, {title => 'Good', price => 2000}
        ], Taro => [
          {title => 'Ruby', price => 3000}, {title => 'Sky', price => 4000}
        ]
    }
    复制代码