태그 보관물: jdbc

ms jdbc를 이용한 db 연결하기 with mssql 2008

MS에서 제공하는 JDBC Type 4 드라이버인 sqljdbc4.jar를 통해서 DB에 연결하기 위한 내용입니다.
아래는 mssql 2008의 db에 접근하기 위한 connection url string 입니다..
MS의 jdbc사용에 대한 내용은 http://msdn.microsoft.com/en-us/library/ms378526.aspx 에서 참고하시면 됩니다. 그리고, 아래의 4147포트는 SqlServer Configuration Manager를 통해서 TCP/IP 프로토콜에 할당된
포드입니다.

Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
String connUrl = “jdbc:sqlserver://HOST\\SQLEXPRESS:4147;databaseName=testdb;integratedSecurity=true;”;
Connection connection = DriverManager.getConnection(connUrl, “id”, “pw”);

jtds를 이용해서 db 연결하기 with mssql 2008

아융..
DB 연결하기 위해서 Connection Url 세팅하는게 은근히 짜증나네요.. ^^;;
아래는 mssql 2008의 db에 접근하기 위한 connection url string 입니다..
그리고, 아래의 4147포트는 SqlServer Configuration Manager를 통해서 TCP/IP 프로토콜에 할당된 포드입니다.

Class.forName(“net.sourceforge.jtds.jdbc.Driver”);
String connUrl = “jdbc:sqlserver://HOST\\SQLEXPRESS:4147;databaseName=testdb;integratedSecurity=true;”;
Connection connection = DriverManager.getConnection(connUrl, “id”, “pw”);

JDBC Type 별 내용

JDBC 1,2,3,4의 타입별 드라이버의 동작 방식과 설명이다. 개인적으로는 가능한 JDBC4 타입으로 사용하는 것을 선호한다.

 Type

 Tier  Driver mechanism  Description
 1  Two  JDBC-ODBC  This driver converts JDBC calls to ODBC calls through JDBC-ODBC Bridge driver which in turn converts to database calls. Client requires ODBC libraries.
 2  Two  Native API – Partly –
Java driver
 This driver converts JDBC calls to database specific native calls. Client requires database specific libraries.
 3  Three  JDBC – Net -All Java
driver
 This driver passes calls to proxy server through network protocol which in turn converts to database calls and passes through database specific protocol. Client doesn’t require any driver.
 4  Two  Native protocol – All –
Java driver
 This driver directly calls database. Client doesn’t require any driver.

* Reference
– http://www.precisejava.com/javaperf/j2ee/JDBC.htm#JDBC102

mssql 2008 jdbc 세팅하기..

ibatis를 사용해서 mssql 2008에 연결하는 설정이다. 예전의 mssql 서버의 default port는 1443 이었는데, 4147로 바뀌었나 보다. 이 내용은, Sql Server Configuration Manager –> SQL Server 네트워크 구성 –> TCP/IP 사용의 속성창을 보면 확인 할 수 있다. IPALL의 TCP 동적포트로 연결 포트를 설정하면 된다.

<sqlMapConfig>
<transactionManager type=”JDBC” commitRequired=”false”>
<dataSource type=”SIMPLE”>
<property name=”JDBC.Driver” value=”com.microsoft.sqlserver.jdbc.SQLServerDriver”/>
<property name=”JDBC.ConnectionURL” value=”jdbc:sqlserver://computer-name\SQLEXPRESS:4147;databaseName=testdb;integratedSecurity=true;”/>
<property name=”JDBC.Username” value=”id”/>
<property name=”JDBC.Password” value=”password”/>
<property name=”Pool.MaximumIdleConnections” value=”5″/>
<property name=”Pool.MaximumCheckoutTime” value=”120000″/>
<property name=”Pool.TimeToWait” value=”500″/>
</dataSource>
</transactionManager>
<sqlMap resource=”article.xml”/>
</sqlMapConfig>

그리고, 혹시 경고: Failed to load the sqljdbc_auth.dll 메세지를 보게되면,플랫폼에 맞는 sqljdbc_auth.dll를 windows\system32폴더에 복사하면 된다.

자바에서 사용하는 다양한 JDBC Driver들

자바를 사용해서 연결할 수 있는 다양한 데이터베이스의 JDBC 드라이버 설정 내용입니다.

DB2

Vendor Site http://www.ibm.com/db2/
Driver Name com.ibm.db2.jcc.DB2Driver
Sample URL jdbc:db2://127.0.0.1:50000/dbname

Derby

(since Cayenne 1.2)

Vendor Site http://db.apache.org/derby/
Embedded
Driver Name org.apache.derby.jdbc.EmbeddedDriver
Sample URL jdbc:derby:path-to-hsql-db-files;create=true
Remote Server
Driver Name org.apache.derby.jdbc.ClientDriver
Sample URL jdbc:derby://127.0.0.1/testdb

FireBird (Interbase)

Vendor Site http://www.firebirdsql.org/
Driver Name org.firebirdsql.jdbc.FBDriver
Sample URL jdbc:firebirdsql:127.0.0.1/10007:/path-to-dbname.fdb
Notes FirebirdAdapter hasn’t been well-tested for some time. Looking for volunteer FireBird users to help us.

FrontBase

(since Cayenne 1.2)

Vendor Site http://www.frontbase.com/
Driver Name jdbc.FrontBase.FBJDriver
Sample URL jdbc:FrontBase://127.0.0.1/dbname

HSQLDB

Vendor Site http://hsqldb.org/
Driver Name org.hsqldb.jdbcDriver
Embedded
Sample URL jdbc:hsqldb:file:/path-to-hsql-db-files
Remote Server
Sample URL jdbc:hsqldb:hsql://127.0.0.1

MySQL

Vendor Site http://www.mysql.com/
Driver Name com.mysql.jdbc.Driver
Sample URL jdbc:mysql://127.0.0.1/dbname

OpenBase

Vendor Site http://www.openbase.com/
Driver Name com.openbase.jdbc.ObDriver
Sample URL jdbc:openbase://127.0.0.1/dbname

Oracle (10i)

Vendor Site http://oracle.com/
Driver Name oracle.jdbc.driver.OracleDriver
Sample URL jdbc:oracle:thin:@//127.0.0.1:1521/dbname

Oracle (8, 8i, 9i)

Vendor Site http://oracle.com/
Driver Name oracle.jdbc.driver.OracleDriver
Sample URL jdbc:oracle:thin:@127.0.0.1:1521:dbname

PostgreSQL (7.4, 8.*)

Vendor Site http://www.postgresql.org/
Driver Name org.postgresql.Driver
Sample URL jdbc:postgresql://127.0.0.1:5432/dbname

SQLServer

Microsoft Driver – SQL Server 2005
Vendor Site http://www.microsoft.com/sql/default.asp
Driver Name com.microsoft.sqlserver.jdbc.SQLServerDriver
Sample URL jdbc:sqlserver://127.0.0.1;databaseName=dbname
Microsoft Driver – SQL Server prior to 2005
Vendor Site http://www.microsoft.com/sql/default.asp
Driver Name com.microsoft.jdbc.sqlserver.SQLServerDriver
Sample URL jdbc:microsoft:sqlserver://127.0.0.1;databaseName=dbname;SelectMethod=cursor
jTDS Driver
Vendor Site http://jtds.sourceforge.net/
Driver Name net.sourceforge.jtds.jdbc.Driver
Sample URL jdbc:jtds:sqlserver://127.0.0.1:5000/dbname

Sybase

Sybase Driver
Vendor Site http://www.sybase.com/
Driver Name com.sybase.jdbc3.jdbc.SybDriver
Sample URL jdbc:sybase:Tds:127.0.0.1:5000/dbname
jTDS Driver
Vendor Site http://jtds.sourceforge.net/
Driver Name net.sourceforge.jtds.jdbc.Driver
Sample URL jdbc:jtds:sybase://127.0.0.1:5000/dbname;useLOBs=false;TDS=4.2

* Reference
https://cayenne.apache.org/docs/3.0/database-support.html