Tibero RDBMS

Neste exemplo demonstraremos como realizar uma chamada a um objeto do tipo REF CURSOR no banco de dados Tibero, criado em uma FUNCTION de uma PACKAGE no banco, a partir de um código Java utilizando o driver JDBC do Tibero.

Código de exemplo (RefCursorExample.java):

import java.sql.*;
import java.io.*;
import com.tmax.tibero.*;

/*
DML/DDL for this test:

create table TIBERO.TB_USER (id number, name varchar(50));

insert into tibero.tb_user values (01, 'Name 01');
insert into tibero.tb_user values (02, 'Name 02');
insert into tibero.tb_user values (03, 'Name 03');
insert into tibero.tb_user values (04, 'Name 04');
insert into tibero.tb_user values (05, 'Name 05');
insert into tibero.tb_user values (06, 'Name 06');
insert into tibero.tb_user values (07, 'Name 07');
insert into tibero.tb_user values (08, 'Name 08');
insert into tibero.tb_user values (09, 'Name 09');
insert into tibero.tb_user values (10, 'Name 10');
commit;

create or replace package TIBERO.JAVA_REFCURSOR
as
type myrctype is ref cursor return TIBERO.TB_USER%ROWTYPE;

function JOB_LISTING return myrctype;
end java_refcursor;

create or replace package body TIBERO.JAVA_REFCURSOR as
function JOB_LISTING return myrctype
is
rc myrctype;
begin
open rc for select * from TIBERO.TB_USER;
return rc;
end;
end java_refcursor;

Compiling Java test program: (Copy Tibero JDBC driver (tibero6-jdbc.jar) here before to compile)

javac -g -verbose -classpath .\tibero6-jdbc.jar RefCursorExample.java

Executing Java test program:

java -cp .\tibero6-jdbc.jar; RefCursorExample
*/
public class RefCursorExample
{
public static void main (String args []) throws SQLException
{
// Load the driver
DriverManager.registerDriver(new com.tmax.tibero.jdbc.TbDriver());

// Connect to the database
Connection conn = DriverManager.getConnection ("jdbc:tibero:thin:@100.100.100.100:8629:tibero", "TIBERO", "tmax");

// Prepare a PSM call
CallableStatement call = conn.prepareCall ("{ ? = call java_refcursor.job_listing }");

// Get the result set
call.registerOutParameter (1, TbTypes.CURSOR);
call.execute ();
ResultSet rset = (ResultSet)call.getObject(1);

// Dump the cursor
while (rset.next ())
{
System.out.println(rset.getString ("ID") + " - " + rset.getString ("NAME"));
}

// Close all the resources
rset.close();
call.close();
conn.close();
}
}

Neste exemplo mostraremos como conectar o Tibero no Apache TomCat v.7.x através da criação de um DataSource JNDI, usando o driver JDBC do Tibero.

Instruções:

1) Copy Tibero JDBC driver to TomCat "lib" directory:

c:\copy c:\TmaxData\tibero6_FS07_64_client\client\lib\jar\tibero6-jdbc.jar c:\apache-tomcat-7.0.92\lib

2) Add the following text to the "C:\apache-tomcat-7.0.92\conf\context.xml" file between the "Context" tag.
2.1) Change connection values according to your Tibero instance.

<Resource name="jdbc/Tibero" auth="Container" type="javax.sql.DataSource"
  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
  driverClassName="com.tmax.tibero.jdbc.TbDriver"
  url="jdbc:tibero:thin:@100.100.100.200:8629:tibero"
  username="sys"
  password="tibero"
  initialSize="10"
  minIdle="5"
  maxIdle="10"

  maxActive="10"
  maxWait="5000"
  validationQuery="SELECT 1 from dual"
  validationInterval="30000"
  testWhileIdle="true" />

3) Create a folder named "tibero" in webapps directory:

"C:\apache-tomcat-7.0.92\webapps\tibero"

4) Create a "tibero.jsp" file into this directory:

<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@ page import="javax.naming.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
  <title>Tibero Data Source Test</title>
</head>
<body>
<%
  String sql = "SELECT * FROM ALL_USERS";
  out.println("Tibero data source test using the script below:");
  out.write ("<br>");
  out.println(sql);
  out.write ("<br>");
  out.write ("<br>");

  Context ctx = null;
  Context env = null;
  DataSource ds = null;
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  ResultSetMetaData md = null;

  try
  {
      StringBuffer query = new StringBuffer();
      ctx = new InitialContext();
      env = (Context) ctx.lookup("java:comp/env");
    ds = (DataSource) env.lookup("jdbc/Tibero");
      con = ds.getConnection();
      stmt = con.createStatement();

      query.append(sql);

      rs = stmt.executeQuery(query.toString());
      md = rs.getMetaData();

      out.write("<table border=\"1\" style=\"width:100%\">");
      out.write("<tr>");

      for (int h = 1 ; h <= md.getColumnCount() ; h++)
      {
          out.write ("<th>");
          out.println(md.getColumnName(h));
          out.write ("</th>");
      }

      out.write ("</tr>");

      while (rs.next())
      {
          out.write ("<tr>");

          for (int r = 1 ; r <= md.getColumnCount() ; r++)
          {
              out.write ("<td>");
              out.println(rs.getString(r));
              out.write ("</td>");
          }

          out.write ("</tr>");
      }

      out.write("</table>");
  }
  catch (Exception e)
  {
      out.println(e);
  }
  finally
  {
      if (stmt != null)
      {
          try
          {
              stmt.close();
          }
          catch (Exception e)
          {
              out.println(e);
          }
      }
      if (con != null)
      {
          try
          {
              con.close();
          }
          catch (Exception e)
          {
              out.println(e);
          }
      }
  }
%>
</body>
</html>

5) Create WEB-INF directory: "C:\apache-tomcat-7.0.92\webapps\tibero\WEB-INF"
5.1) Create file "web.xml" in WEB-INF directory:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
  http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
  id="WebApp_ID" version="2.5">

  <resource-env-ref>
      <description>DB Connection</description>
      <resource-env-ref-name>jdbc/Tibero</resource-env-ref-name>
      <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
  </resource-env-ref>
</web-app>

6) Start TomCat:

C:\apache-tomcat-7.0.92\bin>startup.bat

Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Server version: Apache Tomcat/7.0.92
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Server built: Nov 9 2018 11:07:44 UTC
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Server number: 7.0.92.0
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: OS Name: Windows 8.1
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: OS Version: 6.3
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Architecture: amd64
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Java Home: C:\Program Files\Java\jdk1.7.0_72\jre
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: JVM Version: 1.7.0_72-b14
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: JVM Vendor: Oracle Corporation
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: CATALINA_BASE: C:\apache-tomcat-7.0.92
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: CATALINA_HOME: C:\apache-tomcat-7.0.92
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Djava.util.logging.config.file=C:\apache-tomcat-7.0.92\conf\logging.properties
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Djdk.tls.ephemeralDHKeySize=2048
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dignore.endorsed.dirs=
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dcatalina.base=C:\apache-tomcat-7.0.92
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dcatalina.home=C:\apache-tomcat-7.0.92
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Djava.io.tmpdir=C:\apache-tomcat-7.0.92\temp
Jan 21, 2019 4:56:43 PM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFO: Loaded APR based Apache Tomcat Native library 1.2.18 using APR version 1.6.5.
Jan 21, 2019 4:56:43 PM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFO: APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].
Jan 21, 2019 4:56:43 PM org.apache.catalina.core.AprLifecycleListener initializeSSL
INFO: OpenSSL successfully initialized (OpenSSL 1.1.1 11 Sep 2018)
Jan 21, 2019 4:56:43 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["http-apr-8080"]
Jan 21, 2019 4:56:43 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["ajp-apr-8009"]
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 444 ms
Jan 21, 2019 4:56:43 PM org.apache.catalina.core.StandardService startInternal
INFO: Starting service Catalina
Jan 21, 2019 4:56:43 PM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.92
Jan 21, 2019 4:56:43 PM org.apache.catalina.startup.HostConfig deployDescriptor
INFO: Deploying configuration descriptor C:\apache-tomcat-7.0.92\conf\Catalina\localhost\tibero.xml
Jan 21, 2019 4:56:44 PM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jan 21, 2019 4:56:44 PM org.apache.catalina.startup.HostConfig deployDescriptor
INFO: Deployment of configuration descriptor C:\apache-tomcat-7.0.92\conf\Catalina\localhost\tibero.xml has finished in 846 ms
Jan 21, 2019 4:56:44 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deploying web application directory C:\apache-tomcat-7.0.92\webapps\docs
Jan 21, 2019 4:56:44 PM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jan 21, 2019 4:56:44 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deployment of web application directory C:\apache-tomcat-7.0.92\webapps\docs has finished in 118 ms
Jan 21, 2019 4:56:44 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deploying web application directory C:\apache-tomcat-7.0.92\webapps\examples
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deployment of web application directory C:\apache-tomcat-7.0.92\webapps\examples has finished in 298 ms
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deploying web application directory C:\apache-tomcat-7.0.92\webapps\host-manager
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deployment of web application directory C:\apache-tomcat-7.0.92\webapps\host-manager has finished in 134 ms
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deploying web application directory C:\apache-tomcat-7.0.92\webapps\manager
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deployment of web application directory C:\apache-tomcat-7.0.92\webapps\manager has finished in 113 ms
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deploying web application directory C:\apache-tomcat-7.0.92\webapps\ROOT
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.TldConfig execute
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deployment of web application directory C:\apache-tomcat-7.0.92\webapps\ROOT has finished in 96 ms
Jan 21, 2019 4:56:45 PM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["http-apr-8080"]
Jan 21, 2019 4:56:45 PM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["ajp-apr-8009"]
Jan 21, 2019 4:56:45 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 1664 ms

7) Test it:

http://localhost:8080/tibero/tibero.jsp

* AS an alternative, you can use the custom context.xml for each application, to do that follow the instructions:

1) Copy context.xml with JDBC connection working to:

C:\apache-tomcat-7.0.92\conf\Catalina\localhost

2) Rename it to the same context name of your application. In this case:

tibero.xml