2011년 6월 13일 월요일

C-JDBC - 2

C-JDBC Option
FailOver Option
DriverManager.getConnection("jdbc:cjdbc://c1.objectweb.org,c2.objectweb.org/tpcw");
DriverManager.getConnection("jdbc:cjdbc://localhost,remote.objectweb.org:2048/tpcw");
DriverManager.getConnection("jdbc:cjdbc://smpnode.com:25322,smpnode.com:1098/tpcw");
두 번째 주소에는 FailOver시 사용할 주소를 넣는다.

URL Option
DriverManager.getConnection("jdbc:cjdbc://host/db?user=me&password=secret")
DriverManager.getConnection("jdbc:cjdbc://host/db;user=me;password=secret")
connectionPooling: 이 옵션은 connection.close()을 호출했을 때 물리적으로 close 하지 않고 5초 안에 들어온 요청에 대해서 재 사용한다.
debugLevel: debug, info, off(default) 3개 중에 하나의 값을 가지며 standard output으로 정보를 출력한다.
user: user login
password: user password
preferredController: 여러 노드가 URL에 등록 되어있을 때 사용 정책을 설정한다.
  • jdbc:cjdbc://node1,node2,node3/myDB?preferredController=ordered : 항상 node1로 접속하며, node1에 장애가 발생했을 경우에는 node2에 접속 또 node2에 장애가 발생하였을 경우에는 node3에 접속하게 된다.
  • jdbc:cjdbc://node1,node2,node3/myDB?preferredController=random: Default 정책으로 node1, node2, node3을 Random하게 접속한다.
  • jdbc:cjdbc://node1,node2,node3/myDB?preferredController=node2,node3 : node2, node3을 Round-robin 방식으로 접속하며 node2와 node3가 동시에 장애가 발생하면 node1에 접속한다.
  • jdbc:cjdbc://node1,node2,node3/myDB?preferredController=roundRobin: node1, node2, node3 을 Round-robin 방식으로 접속한다.

retryIntervalInMs: Controller에 연결을 하지 못 할 경우 지정된 시간마다 재 연결을 시도한다. Default 값은 5000ms(5초)

Blobs(Binary Large Objects)와 Clobs(Character Large Objects)
Blob Type은 MySQL에서는 text type 을 사용하여도 되며, PostgreSQL은 bytea type이다.
Sample 프로그램을 테스트 하기위해서 Mysql을 사용하였으며 아래와 같은 Table을 생성하였다.
CREATE TABLE BLOBCLOB(bb BLOB, cc Text)


Sample Program
1. Controller-blobclob.xml è Controller 파일
<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE C-JDBC-CONTROLLER PUBLIC "-//ObjectWeb//DTD C-JDBC-CONTROLLER 2.0.2//EN" "http://c-jdbc.objectweb.org/dtds/c-jdbc-controller-2.0.2.dtd">
<C-JDBC-CONTROLLER>
<Controller port="25322">
<Report hideSensitiveData="true" generateOnShutdown="true" generateOnFatal="true" enableFileLogging="true" />
<JmxSettings>
<RmiJmxAdaptor/>
</JmxSettings>
<VirtualDatabase configFile="mysql-blobclob.xml" virtualDatabaseName="myDB" autoEnableBackends="force"/>
</Controller>
</C-JDBC-CONTROLLER>


2. mysql-blobclob.xml è Virtual Database 파일
<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE C-JDBC PUBLIC "-//ObjectWeb//DTD C-JDBC 2.0.2//EN" "http://c-jdbc.objectweb.org/dtds/c-jdbc-2.0.2.dtd">
<C-JDBC>
<VirtualDatabase name="myDB">
<AuthenticationManager>
<Admin>
<User username="admin" password=""/>
</Admin>
<VirtualUsers>
<VirtualLogin vLogin="user" vPassword=""/>
</VirtualUsers>
</AuthenticationManager>
<DatabaseBackend name="LocalMySQL" driver="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost:3306/test"
connectionTestStatement="select 1">
<ConnectionManager vLogin="user" rLogin="root" rPassword="userpasswd">
<VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
</ConnectionManager>
</DatabaseBackend>
<RequestManager>
<RequestScheduler>
<RAIDb-1Scheduler level="passThrough"/>
</RequestScheduler>
<LoadBalancer>
<RAIDb-1>
<WaitForCompletion policy="first"/>
<RAIDb-1-LeastPendingRequestsFirst/>
</RAIDb-1>
</LoadBalancer>
</RequestManager>
</VirtualDatabase>
</C-JDBC>


3. C-JDBC 실행
“controller.bat -f ..configcontrollercontroller-blobclob.xml”

4. Clob Test
package aricle.cjdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.objectweb.cjdbc.driver.Clob;
public class ClobHander
{
public static void main(String[] args) {
Connection con = null;
try
{
Class.forName("org.objectweb.cjdbc.driver.Driver");
con = DriverManager.getConnection("jdbc:cjdbc://localhost:25322/myDB?debugLevel=debug&user=user&password=");
String sql2 = "insert into blobclob(cc) values(?)";
PreparedStatement stmt2 = con.prepareStatement(sql2);
String dds = "Hurrukku~~~~~~";
Clob clob = new Clob(dds);
stmt2.setClob(1,clob);
stmt2.executeUpdate();
Statement stmt = con.createStatement();
String sql = "select cc from blobclob";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
Clob blisette = (Clob)rs.getClob(1);
String lisette = blisette.getSubString((long) 0, (int) blisette.length());
System.out.println(lisette);
}
}
catch( ClassNotFoundException e )
{
e.printStackTrace();
}
catch( SQLException e )
{
e.printStackTrace();
}
finally {
if(con != null)
try
{
con.close();
}
catch( SQLException e )
{
e.printStackTrace();
}
}
}
}


5. Blob Test
package aricle.cjdbc;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.objectweb.cjdbc.driver.Blob;
public class BlobHander
{
public static void main(String[] args) throws IOException {
Connection con = null;
try
{
Class.forName("org.objectweb.cjdbc.driver.Driver");
con = DriverManager.getConnection("jdbc:cjdbc://localhost:25322/myDB?debugLevel=debug&user=user&password=");
String sql2 = "insert into blobclob(bb) values(?)";
PreparedStatement stmt2 = con.prepareStatement(sql2);
File f = new File("greem.jpg");
FileInputStream fis = new FileInputStream(f);
int length = 0;
byte[] b = new byte[1024];
ByteArrayOutputStream bout = new ByteArrayOutputStream();
while((length = fis.read(b,0,b.length)) != -1)
bout.write(b, 0, length);
Blob blob = new Blob(bout.toByteArray());
stmt2.setBlob(1,blob);
stmt2.executeUpdate();
Statement stmt = con.createStatement();
String sql = "select bb from blobclob where bb is not null";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
java.sql.Blob blisette = (java.sql.Blob)rs.getBlob(1);
byte[] bi = blisette.getBytes(1, (int)blisette.length());
FileOutputStream fos = new FileOutputStream("blob.jpg");
fos.write(bi);
fos.close();
}
}
catch( ClassNotFoundException e )
{
e.printStackTrace();
}
catch( SQLException e )
{
e.printStackTrace();
}
finally {
if(con != null)
try
{
con.close();
}
catch( SQLException e )
{
e.printStackTrace();
}
}
}
}


WAS(Web Application Server) C-JDBC 설정
Configuring C-JDBC with JOnAS
c-jdbc-driver.jar 파일을 JonAS 클래스 패스에 추가한 후 다음과 같이 설정한다.
###################### C-JDBC DataSource configuration example #
datasource.name jdbc_1
datasource.url jdbc:cjdbc://someMachine/someDatabase
datasource.classname org.objectweb.cjdbc.driver.Driver
datasource.username your-username
datasource.password your-password


Configuring C-JDBC with JBoss
Jboss 3.x 이상 버전에서는 c-jdbc-driver.jar 파일을 JBOSS_HOME/server/default/lib에 2.x에서는 JBOSS_HOME/jboss/lib/ext 디렉토리에 복사한 후 다음과 같이 설정한다.
<?xml version="1.0" encoding="UTF-8"?>
<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->
<!-- ===================================================================== -->
<!-- Datasource config for C-JDBC -->
<!-- ===================================================================== -->
<datasources>
<local-tx-datasource>
<jndi-name>cjdbc-DS</jndi-name>
<connection-url>jdbc:cjdbc://localhost:25322/lscluster</connection-url>
<driver-class>org.objectweb.cjdbc.driver.Driver</driver-class>
<user-name>user</user-name>
<password>tagada</password>
</local-tx-datasource>
</datasources>


Configuring C-JDBC with BEA Weblogic Server 7.x/8.x
c-jdbc-driver.jar 파일을 클래스패스에 추가한후 다음과 같이 설정한다.
<JDBCConnectionPool
DriverName="org.objectweb.cjdbc.driver.Driver"
InitialCapacity="1" MaxCapacity="15"
Name="cjdbcPool" Properties="user=username;password=password"
ShrinkingEnabled="true" SupportsLocalTransaction="true"
Targets="wlservername" URL="jdbc:cjdbc://192.168.0.1/vdb"
XAPreparedStatementCacheSize="0"/>
Next, create the required TXDataSources:
<JDBCTxDataSource EnableTwoPhaseCommit="true"
JNDIName="cjdbc-DS" Name="C-JDBC TX Data Source"
PoolName="cjdbcPool" RowPrefetchEnabled="true" Targets="wlservername"/>


Configuring C-JDBC with Hibernate
## C-JDBC
hibernate.dialect net.sf.hibernate.dialect.SybaseDialect
hibernate.connection.driver_class org.objectweb.cjdbc.driver.Driver
hibernate.connection.username user
hibernate.connection.password pass
hibernate.connection.url jdbc:cjdbc://localhost:25322/test

댓글 없음:

댓글 쓰기

ETL 솔루션 환경

ETL 솔루션 환경 하둡은 대용량 데이터를 값싸고 빠르게 분석할 수 있는 길을 만들어줬다. 통계분석 엔진인 “R”역시 하둡 못지 않게 관심을 받고 있다. 빅데이터 역시 데이터라는 점을 볼때 분산처리와 분석 그 이전에 데이터 품질 등 데이...