DBIx::Custom的使用参考

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}
    ]
}