返回顶部

收藏

ruby连接数据库总结

更多

ruby连接SQLite:

require 'sqlite'  
db = SQLite::Database.new("library.db")  
db.execute("select title,author from books") do |row|  
  p row  
end  
db.close  

ruby连接mysql:

require 'mysql'  

m = Mysql.new("localhost","name","password","maillist")  
r = m.query("SELECT * FROM people ORDER BY name")  
r.each_hash do |f|  
  print "#{f['name']} - #{f['email']}"  
end  

ruby连接到PostgreSQL:

require 'postgres'  
conn = PGconn.connect("",5432, "", "", "testdb")  

conn.exec("create table rtest ( number integer default 0 );")  
conn.exec("insert into rtest values ( 99 )")  
res = conn.query("select * from rtest")  
# res id [["99"]]  

ruby连接到LDAP:

ruby中至少有3种LDAP库这里只介绍两种:

conn = LDAP::Conn.new("rsads02.foo.com")  

conn.bind("CN=username,CN=Users,DC=foo,DC=com","password") do |bound|  
  bound.search("DC=foo,DC=com", LDAP::LDAP_SCOPE_SUBTREE,  
               "(&(name=*) (objectCategory=person))", ['name','ipPhone']) do |user|  
    puts "#{user['name']} #{user['ipPhone']}"  
  end  
end  
require 'net/ldap'  

ldap = Net::LDAP.new :host => server_ip_address,    
      :port => 389,    
      :auth => {    
                  :method => :simple,    
                  :username => "cn=manager,dc=example,dc=com",    
                 :password => "opensesame"    
      }  

filter = Net::LDAP::Filter.eq( "cn", "George*" )    
treebase = "dc=example,dc=com"    
ldap.search( :base => treebase, :filter => filter ) do |entry|    
  puts "DN: #{entry.dn}"    
  entry.each do |attribute, values|    
    puts "   #{attribute}:"    
    values.each do |value|    
      puts "      --->#{value}"    
    end    
  end    
end  

p ldap.get_operation_result  

ruby连接到oracle

我们可以使用OCI8库,它可以支持oracle8之后的版本:

require 'oci8'  
session = OCI8.new('user', 'password')  
query = "SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL"  
cursor = session.exec(query)  
result = cursor.fetch         # Only one iteration in this case  
cursor.close  
session.logoff  

下面是查询的例子:

session = OCI8.new("user","password")  
query = "select * from people where name = :name"  

# One way...  
session.exec(query,'John Smith')  

# Another...  
cursor = session.parse(query)  
cursor.exec('John Smith')  

# And another...  
cursor = session.parse(query)  
cursor.bind_param(':name','John Smith')  # bind by name  
cursor.exec  

# And another.  
cursor = session.parse(query)  
cursor.bind_param(1,'John Smith')        # bind by position  
cursor.exec  

使用DBI包装器

理论上,DBI允许你以数据库无关的方式存取你的数据库.不管你的数据库是Oracle, MySQL, PostgreSQL 还是其他,访问的代码都是一样的。他有时是不能处理比较复杂的或则某个数据库独有的特性。

这里假设我们使用的是oracle数据库:

require "dbi"  

db = DBI.connect("dbi:OCI8:mydb", "user", "password")  
query = "select * from people"  

stmt = db.prepare(query)  
stmt.execute  

while row = stmt.fetch do  
  puts row.join(",")  
end  

stmt.finish  
db.disconnect  

使用ORMs

ruby中有两种流行的orm框架ActiveRecord和Og。 ActiveRecord是以Martin Fowler所定义的ActiveRecord设计模式所命名的.每一个数据库表,都是一个继承ActiveRecord::Base的一个类.

require 'active_record'  

ActiveRecord::Base.establish_connection(:adapter => "oci8",  
                                        :username => "username",  
                                        :password => "password",  
                                        :database => "mydb",  
                                        :host => "myhost")  

class SomeTable < ActiveRecord::Base  
  set_table_name "test_table"  
  set_primary_key "some_id"  
end  

SomeTable.find(:all).each do |rec|  
  # process rec as needed...  
end  

item = SomeTable.new  
item.id = 1001  
item.some_column = "test"  
item.save  

og和ActiveRecord 的不同是,后者主要是针对数据库,而前者主要是以面向对象为核心.

当我们需要定义一个存储的类时,我们能够使用property 方法:

class SomeClass  
  property :alpha, String  
  property :beta, String  
  property :gamma, String  
end  

连接数据库:

db = Og::Database.new(:destroy  => false,  
                      :name => 'mydb',  
                      :store  => :mysql,  
                      :user     => 'hal9000',  
                      :password => 'chandra')  

每一个对象都有一个save方法来插入数据库数据:

obj = SomeClass.new  
obj.alpha  = "Poole"  
obj.beta   = "Whitehead"  
obj.gamma  = "Kaminski"  
obj.save  

还有一些描述传统数据库中的对象关系的方法:

class Dog  
  has_one :house  
  belongs_to :owner  
  has_many :fleas  
end  

标签:ruby,sqlite,数据库,oracle,mysql,PostgreSQL,LDAP

收藏

1人收藏

支持

1

反对

0

发表评论