在Ruby中,访问数据库可以通过DBI(database API)来访问。
Ruby DBI模块为ruby程序访问数据库提供了一个与数据库无关的接口,就像perl的DBI模块一样。其具体的结构如下图,
1.The database interface (DBI): 数据库的接口层,类似于JDBC
2.The database driver (DBD) layer:实际的数据库的驱动,类似于java中实际的MySQL,Oracle的
JDBC的具体实现包。
下面以postgresql数据库为例子
1. 下载dbd-pg
2. 下载dbi
3. 代码
require
'pg'
require
'SecureRandom'
class
Pg_DB_Test
attr_reader
:conn
def
createDBConnection
if
!
@conn
@conn
=
PGconn
.
connect
(
@dbhost
,
5432
,
''
,
''
,
@dbname
,
@dbuser
,
@dbpassword
)
end
end
def
initialize
(
dbhost
,
dbname
,
dbuser
,
dbpassword
,
tableName
)
@dbhost
=
dbhost
@dbname
=
dbname
@dbuser
=
dbuser
@dbpassword
=
dbpassword
@tableName
=
tableName
@conn
= createDBConnection
end
def
queryTablesEmployeeExist?
res
=
@conn
.exec(
'select tablename, tableowner from pg_tables'
)
res
.each
do
|
row
|
#puts row[
'tablename'
] +
' | '
+ row[
'tableowner'
]
if
row
[
'tablename'
].
match
(
/
employee
/
)
return true
else
next
end
end
return false
end
def
createTable
begin
@conn
.exec(
"DROP TABLE IF EXISTS EMPLOYEE"
)
@conn
.exec(
"CREATE TABLE EMPLOYEE (ID serial primary key,FIRST_NAME CHAR(20) NOT NULL,LAST_NAME VARCHAR,AGE INT,SEX CHAR(1),INCOME FLOAT )"
)
rescue
DBI
::
DatabaseError
=>
e
puts
"An error occurred"
puts
"Error code:
#{
e
.err
}
"
puts
"Error message:
#{
e
.errstr
}
"
end
end
def
insertEmployee
if
!
self
.queryTablesEmployeeExist?
puts
"----------Created new table:employee-------"
createTable
else
puts
"-----The employee table already existed!!!-----"
end
sqlStatement
=
"INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES ('Mac','"
+
SecureRandom
.uuid +
"', 20, 'M', 2000)"
puts
sqlStatement
@conn
.exec(
sqlStatement
)
puts
"Record has been created"
PG
::
Connection
puts
@conn
.class
end
def
updateEmployee
@conn
.prepare
'stm1'
,
"UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = $1"
@conn
.exec_prepared
'stm1'
,[
'M'
]
end
def
closeDBConnection
@conn
.close
if
@conn
#rescue nil
end
end
puts
dbhost
=
"127.0.0.1"
dbname
=
"test"
dbuser
=
"postgres"
dbpassword
=
"password"
tableName
=
"student"
pgdb
=
Pg_DB_Test
.new(
dbhost
,
dbname
,
dbuser
,
dbpassword
,
tableName
)
pgdb
.insertEmployee
pgdb
.
updateEmployee
pgdb
.closeDBConnection
#1 执行完后,控制台输出如下
C:\RailsInstaller\Ruby2.2.0\bin\ruby.exe -e $stdout.sync=true;$stderr.sync=true;load($0=ARGV.shift) D:/ruby/learnruby/pg_db_test.rb
Testing started at 6:44 PM ...
NOTICE: table "employee" does not exist, skipping
----------Created new table:employee-------
INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES ('Mac','d8886789-fcbc-407b-b2bc-8e65fcdcb1d8', 20, 'M', 2000)
Record has been created
PG::Connection
Process finished with exit code 0
Testing started at 6:44 PM ...
NOTICE: table "employee" does not exist, skipping
----------Created new table:employee-------
INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES ('Mac','d8886789-fcbc-407b-b2bc-8e65fcdcb1d8', 20, 'M', 2000)
Record has been created
PG::Connection
Process finished with exit code 0
#2 执行完后,其数据库表的数据如下,