matlab的安装请参考 :
本文将介绍如何使用matlab通过jdbc连接PostgreSQL数据库.
首先要确认java版本 :
然后到 http://jdbc.postgresql.org/ 下载java版本对应的postgresql jdbc驱动.
将这个驱动拷贝到matlab的本地目录
把这个驱动加到matlab的classpath.txt配置文件中.
在这行后面添加如下 :
启动matlab查看classpath配置是否生效
在数据库中创建测试表和测试数据.
在matlab中连接postgresql数据库
在PostgreSQL中可以查到这个连接.
正确的连接应该是 :
从游标中获取10条记录
打印行
关闭游标和数据库连接
[参考]
pg90@db-172-16-3-150-> java -version
java version "1.7.0_09-icedtea"
OpenJDK Runtime Environment (rhel-2.3.4.1.el6_3-x86_64)
OpenJDK 64-Bit Server VM (build 23.2-b09, mixed mode)然后到 http://jdbc.postgresql.org/ 下载java版本对应的postgresql jdbc驱动.
[root@db-172-16-3-150 soft_bak]# wget http://jdbc.postgresql.org/download/postgresql-9.2-1003.jdbc4.jar将这个驱动拷贝到matlab的本地目录
[root@db-172-16-3-150 jar]# cd /opt/matlab/2013a/java/jar
[root@db-172-16-3-150 jar]# cp /opt/soft_bak/postgresql-9.2-1003.jdbc4.jar ./
[root@db-172-16-3-150 local]# cd /opt/matlab/2013a/toolbox/local把这个驱动加到matlab的classpath.txt配置文件中.
[root@db-172-16-3-150 local]# vi classpath.txt
# Java classpath entries for shared_controllib_general
$matlabroot/java/jar/toolbox/shared/controllib.jar在这行后面添加如下 :
# Java classpath entries for postgresql
$matlabroot/java/jar/postgresql-9.2-1003.jdbc4.jar启动matlab查看classpath配置是否生效
[root@db-172-16-3-150 ~]# matlab -nodesktop -nodisplay
< M A T L A B (R) >
Copyright 1984-2013 The MathWorks, Inc.
R2013a (8.1.0.604) 64-bit (glnxa64)
February 15, 2013
To get started, type one of these: helpwin, helpdesk, or demo.
For product information, visit www.mathworks.com.
>> javaclasspath
......... 略 , 可以看到已经加载了postgresql jdbc驱动文件
/opt/matlab/2013a/java/jar/toolbox/shared/controllib.jar
/opt/matlab/2013a/java/jar/postgresql-9.2-1003.jdbc4.jar 在数据库中创建测试表和测试数据.
pg90@db-172-16-3-150-> psql
psql (9.0.13)
Type "help" for help.
digoal=# create role digoal nosuperuser login encrypted password 'digoal123';
CREATE ROLE
digoal=# grant all on database digoal to digoal;
GRANT
digoal=# \c digoal digoal
You are now connected to database "digoal" as user "digoal".
digoal=> create schema digoal authorization digoal;
CREATE SCHEMA
digoal=> create table test(id int , info text , crt_time timestamp);
CREATE TABLE
digoal=> insert into test select generate_series(1,1000), md5(random()::text), clock_timestamp();
INSERT 0 1000在matlab中连接postgresql数据库
[root@db-172-16-3-150 ~]# matlab -nodesktop -nodisplay
< M A T L A B (R) >
Copyright 1984-2013 The MathWorks, Inc.
R2013a (8.1.0.604) 64-bit (glnxa64)
February 15, 2013
To get started, type one of these: helpwin, helpdesk, or demo.
For product information, visit www.mathworks.com.
不要模仿以下连接方式, 有问题.
>> conn = database('digoal', 'digoal', 'digoal123', 'org.postgresql.Driver', 'jdbc:postgresql://127.0.0.1:1999/digoal?connect_timeout=10&application_name=myapp')在PostgreSQL中可以查到这个连接.
postgres=# select * from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------
datid | 16384
datname | digoal
procpid | 6070
usesysid | 24576
usename | digoal
application_name |
client_addr | 127.0.0.1
client_port | 59559
backend_start | 2013-09-27 16:08:07.019998+08
xact_start |
query_start | 2013-09-27 16:08:07.052041+08
waiting | f
current_query | <IDLE>
但是url中的其他参数(
?connect_timeout=10&application_name=myapp'
)未生效, 所以就不需要指定了.
并且由于第一个参数就是库名, 所以url中的库名也不需要.
例如, 以下方式连接
反而有问题.
>> conn = database('abc', 'digoal', 'digoal123', 'org.postgresql.Driver', 'jdbc:postgresql://127.0.0.1:1999/digoal')
abc和 digoal 叠加了.
conn =
Instance: 'abc'
UserName: 'digoal'
Driver: []
URL: []
Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
Message: 'FATAL: database "digoalabc" does not exist'
Handle: 0
TimeOut: 0
AutoCommit: 'off'
Type: 'Database Object'正确的连接应该是 :
>> conn = database('digoal', 'digoal', 'digoal123', 'org.postgresql.Driver', 'jdbc:postgresql://127.0.0.1:1999/')
conn =
Instance: 'digoal'
UserName: 'digoal'
Driver: 'org.postgresql.Driver'
URL: 'jdbc:postgresql://127.0.0.1:1999/'
Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
Message: []
Handle: [1x1 org.postgresql.jdbc4.Jdbc4Connection]
TimeOut: 0
AutoCommit: 'on'
Type: 'Database Object'
从数据库中获取数据.
建立游标
>> curs = exec(conn, ['select * from test'])
curs =
Attributes: []
Data: 0
DatabaseObject: [1x1 database]
RowLimit: 0
SQLQuery: 'select * from test'
Message: []
Type: 'Database Cursor Object'
ResultSet: [1x1 org.postgresql.jdbc4.Jdbc4ResultSet]
Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
Statement: [1x1 org.postgresql.jdbc4.Jdbc4Statement]
Fetch: 0从游标中获取10条记录
>> row = fetch(curs, 10)
row =
Attributes: []
Data: {10x3 cell}
DatabaseObject: [1x1 database]
RowLimit: 0
SQLQuery: 'select * from test'
Message: []
Type: 'Database Cursor Object'
ResultSet: [1x1 org.postgresql.jdbc4.Jdbc4ResultSet]
Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
Statement: [1x1 org.postgresql.jdbc4.Jdbc4Statement]
Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]打印行
>> row.[TAB可以补齐]
attr columnnames Data fetch querytimeout rows subsasgn
Attributes Contents DatabaseObject fetchmulti ResultSet set subsref
close cursor dbtbx get rowlimit SQLQuery Type
cols Cursor Fetch Message RowLimit Statement width
>> row.Data
ans =
[ 1] 'fb16393ce8dddeb794a84df08224bdd3' '2013-09-27 16:00:20.089714'
[ 2] '3323f8b123975444440b68142b2ff662' '2013-09-27 16:00:20.089879'
[ 3] 'be14e9e0a6e5e3bde71d88769d630be5' '2013-09-27 16:00:20.089889'
[ 4] '5d495efd2777df0c973e8dbe121566b3' '2013-09-27 16:00:20.089895'
[ 5] '07a2f7a23698a1460d5b9f56ee65bf17' '2013-09-27 16:00:20.089901'
[ 6] 'ec89d2ab88f874b4967e954132514a45' '2013-09-27 16:00:20.089907'
[ 7] '2f73aa414f6001e9f70f97e7deba328e' '2013-09-27 16:00:20.089913'
[ 8] '459e2aa0ca1e4515372eaa9ab7053af1' '2013-09-27 16:00:20.089918'
[ 9] 'e74da999ec0de81cd577d776e61a63a4' '2013-09-27 16:00:20.089924'
[10] 'a548ec8602b8bdc53b379404fbf9f529' '2013-09-27 16:00:20.08993' 关闭游标和数据库连接
>> help /cursor/close
CLOSE Close cursor.
CLOSE(CURSOR) closes the database cursor.
CURSOR is a cursor structure with all elements defined.
See also FETCH.
Reference page in Help browser
doc database/close
>> help /database/close
CLOSE Close database connection.
CLOSE(CONNECT) closes the database connection.
CONNECT is a database connection object returned by
DATABASE.
See also DATABASE.
Reference page in Help browser
doc database/close
所有数据库相关帮助见本文末尾.
例如关闭数据库连接 :
>> close(conn)
在数据库中就看不到matlab过来的连接了
postgres=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start |
xact_start | query_start | waiting | current_query
-------+----------+---------+----------+----------+------------------+-------------+-------------+-------------------------------+--
----------------------------+------------------------------+---------+---------------------------------
11826 | postgres | 5870 | 10 | postgres | psql | | -1 | 2013-09-27 16:01:03.994593+08 | 2
013-09-27 16:20:57.09995+08 | 2013-09-27 16:20:57.09995+08 | f | select * from pg_stat_activity;
(1 row)
3. matlab 帮助
6. matlab help database
>> help database
Database Toolbox
Version 4.1 (R2013a) 13-Feb-2013
General functions:
chkprops - Database object properties.
logintimeout - Set or get time allowed to establish database connection.
setdbprefs - Set preferences for database actions for handling null values.
dexplore - Configure, explore and import database data using Database Explorer
Database Access functions:
/database/catalogs - Get database catalog names.
/database/clearwarnings - Clear warnings for database connection.
/database/close - Close database connection.
/database/columns - Get database table column names.
/database/commit - Make database changes permanent.
/database/database - Connect to database.
/database/exec - Execute SQL statement and open cursor.
/database/fetch - Import data into MATLAB using connection handle.
/database/get - Get database property.
/database/insert - Export MATLAB cell array data to database table.
/database/isconnection - Detect if database connection is valid.
/database/isreadonly - Detect if database connection is read-only.
/database/ping - Get status information about database connection.
/database/rollback - Undo database changes.
/database/runstoredprocedure - Stored procedures with input and output parameters.
/database/schemas - Get database schema names.
/database/set - Set properties for database connection.
/database/sql2native - Convert JDBC SQL grammar into system's native SQL grammar.
/database/tables - Get database table names.
/database/update - Replace data in database table with data from MATLAB cell array.
Database Cursor Access functions:
/cursor/attr - Get attributes of columns in fetched data set.
/cursor/close - Close cursor.
/cursor/cols - Get number of columns in fetched data set.
/cursor/columnnames - Get names of columns in fetched data set.
/cursor/fetch - Import data into MATLAB.
/cursor/get - Get property of cursor object.
/cursor/querytimeout - Get time allowed for a database SQL query to succeed.
/cursor/rows - Get number of rows in fetched data set.
/cursor/set - Set RowLimit for cursor fetch.
/cursor/width - Get width of column in fetched data set.
Database Toolbox Object functions:
/dbtbx/dbtbx - Construct Database Toolbox object.
/dbtbx/display - Database Toolbox object display method.
/dbtbx/subsasgn - Subscripted assignment for Database Toolbox object.
/dbtbx/subsref - Subscripted reference for Database Toolbox object.
Database Toolbox Database MetaData functions:
/dmd/bestrowid - Get database table unique row identifier.
/dmd/columnprivileges - Get database column privileges.
/dmd/columns - Get database table column names.
/dmd/crossreference - Get information about primary and foreign keys.
/dmd/dmd - Construct database metadata object.
/dmd/exportedkeys - Get information about exported foreign keys.
/dmd/get - Get database metadata properties.
/dmd/importedkeys - Get information about imported foreign keys.
/dmd/indexinfo - Get indices and statistics for database table.
/dmd/primarykeys - Get primary key information for database table or schema.
/dmd/procedurecolumns - Get catalog's stored procedure parameters and result columns.
/dmd/procedures - Get catalog's stored procedures.
/dmd/supports - Detect if property is supported by database metadata.
/dmd/tableprivileges - Get database table privileges.
/dmd/tables - Get database table names.
/dmd/versioncolumns - Get automatically updated table columns.
Database Toolbox Driver functions:
/driver/driver - Construct database driver object.
/driver/get - Get database driver properties.
/driver/isdriver - Detect if driver is a valid JDBC driver object.
/driver/isjdbc - Detect if driver is JDBC-compliant.
/driver/isurl - Detect if database URL is valid.
/driver/register - Load database driver.
/driver/unregister - Unload database driver.
Database Toolbox Drivermanager functions:
/drivermanager/drivermanager - Construct database drivermanager object.
/drivermanager/get - Get database drivermanager properties.
/drivermanager/set - Set database drivermanager properties.
Database Toolbox Resultset functions:
/resultset/clearwarnings - Clear the warnings for the resultset.
/resultset/close - Close resultset object.
/resultset/get - Get resultset properties.
/resultset/isnullcolumn - Detect if last record read in resultset was null.
/resultset/namecolumn - Map resultset column name to resultset column index.
/resultset/resultset - Construct resultset object.
Database Toolbox Resultset MetaData functions:
/rsmd/rsmd - Construct resultset metadata object.
/rsmd/get - Get resultset metadata properties.
Database Toolbox bulk insert examples:
/dbdemos/mssqlserverbulkinsert - MS SQL Server bulk insert example.
/dbdemos/mysqlbulkinsert - MySQL bulk insert example.
/dbdemos/oraclebulkinsert - Oracle bulk insert example.
Visual Query Builder functions:
/vqb/confds - Configure data source (UNIX only).
/vqb/getdatasources - Return valid data sources on system.
/vqb/loginconnect - Datasource connection.
/vqb/parsebinary - Write binary object to disk.
/vqb/qbhelp - Query Builder help string.
/vqb/querybuilder - Start visual SQL query builder.
/vqb/showdata - Display data in interactive window.
/vqb/showdatacallbacks - Visual Query Builder data display callbacks.
/vqb/vqbdemo - Visual Query Builder demonstrations.
database is both a directory and a function.
DATABASE Connect to database.
CONNECT = DATABASE(INSTANCE,USERNAME,PASSWORD)
returns a database connection object. Connection is established using
an ODBC driver. INSTANCE is the name of the ODBC data source set up,
USERNAME and PASSWORD are the required credentials for access to the
database.
CONNECT = DATABASE(INSTANCE,USERNAME,PASSWORD,DRIVER,DATABASEURL)
returns a database connection object. Connection is established using a
JDBC driver. INSTANCE is the name of the database, USERNAME and
PASSWORD are the required credentials for access to the database.
DRIVER is a JDBC driver name and DATABASEURL is the connection URL for
the database.
CONNECT = DATABASE(INSTANCE,USERNAME,PASSWORD,'PARAM','VALUE')
returns a database connection object. Connection is established using a
JDBC driver. PARAM-VALUE pairs are:
Parameter Value Default
--------- ----- -------
VENDOR Must be provided. Needs to be one of: None
'MySQL','Oracle','Microsoft SQL Server',
'PostGreSQL'. Use this along with zero or
more of the following params as required
to establish a connection. If connecting
to a database system not listed here,
use the DRIVER and DATABASEURL syntax
SERVER Name/Address of the database server localhost
PORTNUMBER number of the port that the server vendor
is listening on specific
AUTHTYPE one of:'Server','Windows'. Only valid Server
for SQL Server. Specify 'Windows'
for Windows Authentication.
DRIVERTYPE one of: 'thin','oci'. Only valid for None
Oracle.
URL Connection URL.If URL is specified, None
no other properties may be required
Use LOGINTIMEOUT before DATABASE to set the maximum time for a
connection attempt.
Example:
JDBC-ODBC connection:
conn=database('oracle','scott','tiger')
where:
'oracle' is the ODBC datasource name for an ORACLE database.
'scott' is the user name.
'tiger' is the password.
JDBC connection:
conn=database('oracle','scott','tiger',
'oracle.jdbc.driver.OracleDriver','jdbc:oracle:oci7:')
where:
'oracle' is the database name.
'scott' is the user name.
'tiger' is the password.
'oracle.jdbc.driver.OracleDriver' is the JDBC driver to be used
to make the connection.
'jdbc:oracle:oci7:' is the URL as defined by the Driver vendor
to establish a connection with the database.
Using Param-Value pairs:
conn=database('oracle','scott','tiger','Vendor', 'Oracle',
'DriverType', 'oci', 'Server', 'remotehost', 'PortNumber', 1234)
where:
remotehost: Database Server machine name
1234: Port Number which the server is listening on
See also CLOSE.
Reference page in Help browser
doc database
