数据库 #
环境准备 #
依次执行以下命令
brew install mysql
gem install dbi
gem install mysql
gem install dbd-mysql
如果报 insecure
异常,需要使用 chmod go-w <path>
修改对应的目录。
基本使用 #
@table = 'test'
@user = 'root'
@psw = 'tiger'
@host = '192.168.1.67'
@dbh = DBI.connect("DBI:Mysql:#{@table}:#{@host}", @user, @psw)
begin
row = @dbh.select_one('SELECT VERSION()')
puts 'Server version: ' + row[0]
rescue DBI::DatabaseError => e
puts 'An error occurred'
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
@dbh.disconnect if @dbh
end
执行 SQL 语句 #
类似 JDBC 中的 Statement
insert
返回 1 表示插入成功,dbh.func(:insert_id)
返回上次插入的 id。
n=@dbh.do("INSERT INTO EMPLOYEE(
FIRST_NAME,
LAST_NAME,
AGE,
SEX,
INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)")
puts @dbh.func(:insert_id)
assert_equal(1, n)
执行 Prepare 语句 #
类似 JDBC 中的 PrepareStatement,可以进行批量操作,execute()
插入参数。
sth = @dbh.prepare('INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME,
AGE,
SEX,
INCOME)
VALUES (?, ?, ?, ?, ?)')
sth.execute('John', 'Poul', 25, 'M', 2300)
sth.execute('Zara', 'Ali', 17, 'F', 1000)
sth.finish
执行查询 #
列号从 1
开始,也可以使用列名获取
sth = @dbh.prepare('SELECT * FROM EMPLOYEE
WHERE INCOME > ?')
sth.execute(1000)
sth.fetch do |row|
printf 'First Name: %s, Last Name : %s, ', row['FIRST_NAME'], row['LAST_NAME']
printf 'Age: %d, Sex : %s, ', row[3], row[4]
printf "Salary :%d \n", row[5]
end
sth.finish
事务 #
有两种方式,一种自行控制事务,一种利用自动提交和回滚的代码块
自行控制
@dbh['AutoCommit'] = false
begin
@dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
WHERE FIRST_NAME = 'John'")
@dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
WHERE FIRST_NAME = 'Zara'")
@dbh.commit
rescue
puts 'transaction failed'
@dbh.rollback
end
@dbh['AutoCommit'] = true
使用代码块
@dbh['AutoCommit'] = false
@dbh.transaction do |dbh|
dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
WHERE FIRST_NAME = 'John'")
dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
WHERE FIRST_NAME = 'Zara'")
end
@dbh['AutoCommit'] = true
自动提交和回滚的代码块 #
有三种
第一种
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
"testuser", "test123") do |dbh|
end
第二种
dbh.prepare("SHOW DATABASES") do |sth|
sth.execute
puts "Databases: " + sth.fetch_all.join(", ")
end
第三种
dbh.execute("SHOW DATABASES") do |sth|
puts "Databases: " + sth.fetch_all.join(", ")
end