英文:
How to retrieve host, port, sid, user and password informations in java.sql.Connection
问题
我有一个Oracle连接（存储在java.sql.Connection对象中），这个连接是从一个属性文件（ApplicationResources.properties）中获取的。
我该如何获取关于主机、端口、SID、用户名和密码的信息？（我需要这些信息来通过我的Java应用程序调用特定的数据库函数）
以下是我的代码：
private void connectToDatabase() {
ServiceLocator sl = new ServiceLocator();
InitialContext ic = null;
DataSource dataSource = null;
try {
if (conn == null) {
ic = new InitialContext();
dataSource = (DataSource) ic.lookup(sl.getInfo().getString("JNDI"));
conn = dataSource.getConnection();
conn.setAutoCommit(true);
// 我需要主机、端口、SID和密码信息
System.out.println("USER " + conn.getMetaData().getUserName());
}
} catch (SQLException e) {
logger.error("Error while trying to open database connection.", e);
} catch (NamingException e) {
logger.error("Error while trying to open database connection.", e);
}
if (conn != null)
rastrear.definePredicate(conn);
}
英文:
I have an Oracle connection (in a java.sql.Connection object) that is mounted from a properties file (ApplicationResources.properties).
How do I capture information about host, port, sid, user and password? (I need this information to call a specific database function through my java application)
Below my code:
private void conectaBanco() {
ServiceLocator sl = new ServiceLocator();
InitialContext ic = null;
DataSource dataSource = null;
try {
if (conn == null) {
ic = new InitialContext();
dataSource = (DataSource) ic.lookup(sl.getInfo().getString("JNDI"));
conn = dataSource.getConnection();
conn.setAutoCommit( true );
// I need host, port, sid and password information
System.out.println("USER " + conn.getMetaData().getUserName());
}
} catch (SQLException e) {
logger.error("Erro ao tentar abrir conexao com o banco de dados.", e);
} catch (NamingException e) {
logger.error("Erro ao tentar abrir conexao com o banco de dados.", e);
}
if(conn != null)
rastrear.definePredicate(conn);
}
答案1
得分: 0
SESSION SID:
SELECT sys_context('USERENV', 'SID') FROM DUAL;
ORACLE_SID:
SELECT sys_context('userenv','instance_name') FROM dual;
HOST (Database machine) :
SELECT UTL_INADDR.get_host_name FROM dual;
Password: See [http://www.dba-oracle.com/t_password_storage.htm][1]
CLIENT SIDE Port:
Select port from v$session;
SERVER OS PID of Oracle server process connected to client process
SELECT p.spid
FROM v$process p, v$session s
WHERE s.paddr = p.addr and
sys_context('USERENV', 'SID') = s.sid;
Server side port:
This is really difficult since the server process port is mapped to a different port then the initial - for example port 12102 in listener.ora/tnsnames.ora get mapped by listener to a arbitrary free one (by the way: thats the reason why often firewalls need to be shutdown and while connections cannot get after mapping through firewall. This can be fixed but this is another story)
Below code
- grants required privileges
- defines a java stored function "Util.RunThis" which executes on database server a OS command passed into function and return string.
- "Util.RunThis" is mapped to PL/SQL function "RUN_CMD"
- "GET_PORT" is a PL/SQL function returning a numeric value of the used Port of the database server process connected to client session. Inside GET_PORT the SELECT is used to determine database server process pid replacing [SPID] in command below
/usr/sbin/lsof -Pan -p [SPID] -i
- finally invoking a simple select we get the Port of database server process attached to current session
connect / as sysdba
grant select on sys.v_$process to scott;
grant select on sys.v_$session to scott;
begin
dbms_java.grant_permission
('SCOTT',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
end;
/
connect scott/tiger@foo.com
create or replace and compile java source named "Util"
as
import java.io.*;
import java.lang.*;
import java.nio.charset.Charset;
import java.nio.*;
public class Util extends Object
{
public static String RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
String rc = args;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
{
String xxx = new String(buffer, Charset.forName("UTF-8"));
rc = rc + xxx;
}
p.waitFor();
rc = rc + "ABC";
}
catch (Exception e)
{
e.printStackTrace();
rc = "Exception!!!";
}
finally
{
return rc;
}
}
}
/
create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
as language java
name 'Util.RunThis(java.lang.String) return java.lang.String';
/
create or replace function GET_PORT return number
as
SPID NUMBER;
retval varchar2(32000);
y varchar2(1024);
cmd VARCHAR2(256);
begin
SELECT
p.spid
INTO
SPID
FROM
sys.v_$process p,
sys.v_$session s
WHERE
s.paddr = p.addr and
sys_context('USERENV', 'SID') = s.sid;
cmd := '/usr/sbin/lsof -Pan -p [SPID] -i';
/* raw string data returned from Shell executing cmd */
retval := run_cmd(replace(cmd,'[SPID]', SPID));
/* get last occurance of : marking redirected port */
y := substr(retval,INSTR(retval,':', -1)+1,1024);
/* return the numeric port by stripping info like " (ESTABLISHED)" */
return to_number(substr(y,1,INSTR(y, ' ')-1));
end;
/
show errors
select get_port from dual;
/*-------------------- OUTPUT -------------------------- */
SQL> connect / as sysdba
grant select on sys.v_$process to scott;
grant select on sys.v_$session to scott;
begin
dbms_java.grant_permission
('SCOTT',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
end;
/Connected.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL> 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> connect scott/tiger@foo.com
Connected.
SQL> create or replace and compile java source named "Util"
2 as
import java.io.*;
import java.lang.*;
import java.nio.charset.Charset;
3 4 5 6 import java.nio.*;
7 public class Util extends Object
8 {
9 public static String RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
10 11 12 String rc = args;
13
14 try
15 {
16
17 Process p = rt.exec(args);
18 19 int bufSize = 4096;
20 BufferedInputStream bis =
21 new BufferedInputStream(p.getInputStream(), bufSize);
22 int len;
byte buffer[] = new byte[bufSize];
23 24
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
25 26 27 {
28 String xxx = new String(buffer, Charset.forName("UTF-8"));
29 rc = rc + xxx;
30 }
p.waitFor();
31 32
rc = rc + "ABC";
33 34 35 }
36 catch (Exception e)
37 {
38 e.printStackTrace();
39 rc = "Exception!!!" ;
40 }
41
42 finally
43 {
44 return rc;
45 }
}
46 47 }
/ 48
Java created.
SQL> create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
as language java
name 'Util.RunThis(java.lang.String) return java.lang.String';
/ 2 3 4
Function created.
<details>
<summary>英文:</summary>
SESSION SID:
SELECT sys_context('USERENV', 'SID') FROM DUAL;
ORACLE_SID:
SELECT sys_context('userenv','instance_name') FROM dual;
HOST (Database machine) :
SELECT UTL_INADDR.get_host_name FROM dual;
Password: See [http://www.dba-oracle.com/t_password_storage.htm][1]
CLIENT SIDE Port:
Select port from v$session;
SERVER OS PID of Oracle server process connected to client process
SELECT p.spid
FROM v$process p, v$session s
WHERE s.paddr = p.addr and
sys_context('USERENV', 'SID') = s.sid;
Server side port:
This is really difficult since the server process port is mapped to a different port then the initial - for example port 12102 in listener.ora/tnsnames.ora get mapped by listener to a arbitrary free one (by the way: thats the reason why often firewalls need to be shutdown and while connections cannot get after mapping through firewall. This can be fixed but this is another story)
Below code
- grants required privileges
- defines a java stored function "Util.RunThis" which executes on database server a OS command passed into function and return string.
- "Util.RunThis" is mapped to PL/SQL function "RUN_CMD"
- "GET_PORT" is a PL/SQL function returning a numeric value of the used Port of the database server process connected to client session. Inside GET_PORT the SELECT is used to determine database server process pid replacing [SPID] in command below
/usr/sbin/lsof -Pan -p [SPID] -i
- finally invoking a simple select we get the Port of database server process attached to current session
connect / as sysdba
grant select on sys.v_$process to scott;
grant select on sys.v_$session to scott;
begin
dbms_java.grant_permission
('SCOTT',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
end;
/
connect scott/tiger@foo.com
create or replace and compile java source named "Util"
as
import java.io.*;
import java.lang.*;
import java.nio.charset.Charset;
import java.nio.*;
public class Util extends Object
{
public static String RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
String rc = args;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
{
String xxx = new String(buffer, Charset.forName("UTF-8"));
rc = rc + xxx;
}
p.waitFor();
rc = rc + "ABC";
}
catch (Exception e)
{
e.printStackTrace();
rc = "Exception!!!" ;
}
finally
{
return rc;
}
}
}
/
create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
as language java
name 'Util.RunThis(java.lang.String) return java.lang.String';
/
create or replace function GET_PORT return number
as
SPID NUMBER;
retval varchar2(32000);
y varchar2(1024);
cmd VARCHAR2(256);
begin
SELECT
p.spid
INTO
SPID
FROM
sys.v_$process p,
sys.v_$session s
WHERE
s.paddr = p.addr and
sys_context('USERENV', 'SID') = s.sid;
cmd := '/usr/sbin/lsof -Pan -p [SPID] -i';
/* raw string data returned from Shell executing cmd */
retval := run_cmd(replace(cmd,'[SPID]', SPID));
/* get last occurance of : marking redirected port */
y := substr(retval,INSTR(retval,':', -1)+1,1024);
/* return the numeric port by stripping info like " (ESTABLISHED)" */
return to_number(substr(y,1,INSTR(y, ' ')-1));
end;
/
show errors
select get_port from dual;
/*-------------------- OUTPUT -------------------------- */
SQL> connect / as sysdba
grant select on sys.v_$process to scott;
grant select on sys.v_$session to scott;
begin
dbms_java.grant_permission
('SCOTT',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
end;
/Connected.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL> 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> connect scott/tiger@foo.com
Connected.
SQL> create or replace and compile java source named "Util"
2 as
import java.io.*;
import java.lang.*;
import java.nio.charset.Charset;
3 4 5 6 import java.nio.*;
7 public class Util extends Object
8 {
9 public static String RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
10 11 12 String rc = args;
13
14 try
15 {
16
17 Process p = rt.exec(args);
18 19 int bufSize = 4096;
20 BufferedInputStream bis =
21 new BufferedInputStream(p.getInputStream(), bufSize);
22 int len;
byte buffer[] = new byte[bufSize];
23 24
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
25 26 27 {
28 String xxx = new String(buffer, Charset.forName("UTF-8"));
29 rc = rc + xxx;
30 }
p.waitFor();
31 32
rc = rc + "ABC";
33 34 35 }
36 catch (Exception e)
37 {
38 e.printStackTrace();
39 rc = "Exception!!!" ;
40 }
41
42 finally
43 {
44 return rc;
45 }
}
46 47 }
/ 48
Java created.
SQL> create or replace function RUN_CMD( p_cmd in varchar2) return VARCHAR2
as language java
name 'Util.RunThis(java.lang.String) return java.lang.String';
/ 2 3 4
Function created.
SQL> create or replace function GET_PORT return number
as
SPID NUMBER;
retval varchar2(32000);
2 3 4 5 y varchar2(1024);
cmd VARCHAR2(256);
begin
6 7 8 SELECT
p.spid
INTO
9 10 11 SPID
FROM
sys.v_$process p,
sys.v_$session s
WHERE
12 13 14 15 16 s.paddr = p.addr and
sys_context('USERENV', 'SID') = s.sid;
cmd := '/usr/sbin/lsof -Pan -p [SPID] -i';
17 18 19 20 21 /* raw string data returned from Shell executing cmd */
retval := run_cmd(replace(cmd,'[SPID]', SPID));
22 23 24 /* get last occurance of : marking redirected port */
y := substr(retval,INSTR(retval,':', -1)+1,1024);
/* return the numeric port by stripping info like " (ESTABLISHED)" */
25 26 27 28 return to_number(substr(y,1,INSTR(y, ' ')-1));
end;
/
show errors 29 30
Function created.
SQL>
No errors.
SQL> select get_port from dual;
GET_PORT
----------
36586
[1]: http://www.dba-oracle.com/t_password_storage.htm
</details>
# 答案2
**得分**: 0
我通过在我的Weblogic配置JDBC上添加附加参数（主机、用户、端口、SID - 密码会自动返回）来解决了这个问题。我通过下面的代码获得了这些值：
```java
import weblogic.jdbc.wrapper.PoolConnection;
import java.util.Properties;
(...)
public Properties retornaPropriedadesConexao(){
PoolConnection pc = (PoolConnection)conn;
Properties p = pc.getConnectionEnv().getDriverProperties();
return p;
}
英文:
I solve this problem adding on my Weblogic configuration JDBC the additional parameters (host, user, port, sid - the password is returned automaticaly).
And I got this values throw this code below:
import weblogic.jdbc.wrapper.PoolConnection;
import java.util.Properties;
(...)
public Properties retornaPropriedadesConexao(){
PoolConnection pc = (PoolConnection)conn;
Properties p = pc.getConnectionEnv().getDriverProperties();
return p;
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论