O "Tibero" é um excelente gerenciador de banco de dados relacional desenvolvido pela empresa sul-corena TmaxSoft. Possui tecnologia inovadora, voltada para soluções corporativas de missão crítica. É a melhor alternativa ao banco de dados Oracle por ser extremamente compatível. Fornece alto desempenho, muita segurança, alta disponibilidade em ambiente clusterizado com disco compartilhado e melhor relação custo-benefício.
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>");
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
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.
