Why when I execute a query with jooq parsing connection I get You can see in the attached files the code of the server and the connection :
- jOOQ:3.15 <dependency> <groupId>org.jooq.pro</groupId> <artifactId>jooq</artifactId> <version>3.15.0</version> </dependency> <dependency> <groupId>org.jooq.pro</groupId> <artifactId>reactivestreams</artifactId> <version>3.15.0</version> </dependency> - Java:8 - Database :Postgresql - OS:open suse - JDBC Driver:4.2 -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4Xxin-Mw6jky9q-3O3pP3Fh6h6hYw3f4pDp%3DaS9x07KRbFg%40mail.gmail.com.
test
Description: Binary data
/*
* @(#)TbsDatasourceDbConnection.java 1.0 le 8 août 2011.
*
* Copyright 2006-2009 TBS-Solutions, Tout droit réservé.
*/
package com.tbs.db;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.sql.DataSource;
import org.apache.commons.io.FileUtils;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.ExecuteContext;
import org.jooq.ExecuteListener;
import org.jooq.ParseListener;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamCastMode;
import org.jooq.conf.ParamType;
import org.jooq.conf.ParseUnknownFunctions;
import org.jooq.conf.Settings;
import org.jooq.conf.TransformUnneededArithmeticExpressions;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultExecuteListener;
import org.jooq.tools.jdbc.DefaultConnection;
import com.tbs.log.TbsLog;
import com.tbs.utils.TbsTechnicalException;
public class PostgreSQLDBCPDatasourceDbConnection implements TbsDbConnectionIfc {
private Map<Pattern, String> patterns = new LinkedHashMap<Pattern, String>();
private void initPatterns() {
List<String> list;
String regEx;
String type;
try {
File requestsFile = new File("/home/wchaib/git/veos2-global/veos2-kernel/dict.txt");
list = FileUtils.readLines(requestsFile);
} catch (IOException e) {
throw new RuntimeException(e);
}
for (String l : list) {
int colonIndex = l.indexOf("->");
regEx = l.substring(0, colonIndex);
type = l.substring(colonIndex + 2);
patterns.put(Pattern.compile(regEx), type);
}
}
private DataSource dataSource;
private ExecuteListener jooqListener;
public PostgreSQLDBCPDatasourceDbConnection(final String dataSourceName) throws TbsTechnicalException {
initPatterns();
init(dataSourceName);
jooqListener = new DefaultExecuteListener() {
/**
*
*/
private static final long serialVersionUID = 1L;
@Override
public void executeStart(ExecuteContext ctx) {
// Create a new DSLContext for logging rendering purposes
// This DSLContext doesn't need a connection, only the SQLDialect...
DSLContext create = DSL.using(ctx.dialect(),
// ... and the flag for pretty-printing
new Settings().withRenderFormatted(true));
// If we're executing a query
if (ctx.query() != null) {
TbsLog.info("jooq : "+create.renderInlined(ctx.query()));
}
// If we're executing a routine
else if (ctx.routine() != null) {
TbsLog.info("jooq : "+create.renderInlined(ctx.routine()));
}
}
@Override
public void start(ExecuteContext ctx) {
// Register the start time to the current context
ctx.data("jooq : time", System.nanoTime());
}
@Override
public void end(ExecuteContext ctx) {
// Extract the start time from the current context
Long time = (Long) ctx.data("time");
TbsLog.info("jooq : Execution time : " + ((System.nanoTime() - time) / 1000 / 1000.0) + "ms. Query : " + ctx.sql());
}
};
}
private void init(final String dataSourceName) throws TbsTechnicalException {
try {
final Context initContext = new InitialContext();
final Context envContext = (Context) initContext.lookup("java:/comp/env");
if (envContext == null) {
throw new ServletException("Error: No Context");
}
final DataSource dataSourceOrig = (DataSource) envContext.lookup(dataSourceName);
if (dataSourceOrig == null) {
throw new TbsTechnicalException("Error: No DataSource");
}
dataSource = dataSourceOrig;
} catch (final Exception e) {
e.printStackTrace();
throw new TbsTechnicalException(e);
}
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#end()
*/
@Override
public void end() {
dataSource = null;
}
private DataSource getDataSource() {
return dataSource;
}
public Connection getProxyConnection() throws TbsTechnicalException {
Connection c2 = getJooqConnection();
final List<Pattern> list = patterns.keySet().stream().collect(Collectors.toList());
final List<String> types = patterns.values().stream().collect(Collectors.toList());
try (Connection c1 = getJooqConnection()) {
c2 = new DefaultConnection(c1) {
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
sql = sql.toLowerCase();
for (int i = 0; i < list.size(); i++) {
String type = types.get(i);
Pattern p = list.get(i);
Matcher matcher = p.matcher(sql);
while (matcher.find()) {
if ("type1".equals(type))
sql = matcher
.replaceAll(matcher.group(2) + "=" + "to_char(" + matcher.group(3) + ") ");
if ("type2".equals(type))
sql = matcher
.replaceAll(matcher.group(3) + "=" + "to_char(" + matcher.group(2) + ") ");
}
}
TbsLog.info("Request executed via Jooq: " +sql);
return super.prepareStatement(sql);
}
};
}
catch(Exception e) {
throw new TbsTechnicalException(e);
}
return c2;
}
public Connection getJooqConnection() throws TbsTechnicalException {
try {
final Settings settings = new Settings()
.withParseDialect(SQLDialect.ORACLE)
.withInterpreterDialect(SQLDialect.POSTGRES_12)
.withParseUnknownFunctions(ParseUnknownFunctions.IGNORE)
.withTransformTableListsToAnsiJoin(true)
.withTransformUnneededArithmeticExpressions(TransformUnneededArithmeticExpressions.ALWAYS)
.withTransformRownum(true)
.withParamType(ParamType.NAMED)
.withParamCastMode(ParamCastMode.ALWAYS);
final Configuration jooqConfig = new DefaultConfiguration()
.set(SQLDialect.POSTGRES_12)
.set(getDataSource())
.set(settings)
.set(jooqListener)
.set(ParseListener.onParseField(ctx -> {
if (ctx.parseKeywordIf("SYSDATE")) {
return DSL.field("localtimestamp");
}
return null;
}));
return DSL.using(jooqConfig).parsingConnection();
} catch (final Exception e) {
throw new TbsTechnicalException(e);
}
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#getConnection()
*/
@Override
public Connection getConnection() throws TbsTechnicalException {
try (Connection c = getProxyConnection()) {
return c ;
} catch (final SQLException e) {
throw new TbsTechnicalException(e);
}
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#closeConnection(java.sql.Connection)
*/
@Override
public void closeDbConn(final Connection dbConn) {
try {
dbConn.unwrap(Connection.class).close();
dbConn.close();
} catch (final Exception e) {
e.printStackTrace();
}
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#closeDbConn(java.sql.Connection, boolean)
*/
@Override
public void closeDbConn(final Connection dbConn, final boolean autoCommit) {
try {
dbConn.setAutoCommit(autoCommit);
} catch (final Exception e) {
e.printStackTrace();
}
closeDbConn(dbConn);
}
//TODO rectifier cette abstraction.
//l'interface implémentée a comme but de faire abstraction des détails de connexion alors que les méthodes suivante
//néessitent de retourner ces détails.
@Override
public String getURL() throws TbsTechnicalException {
return "jdbc:postgresql://xxxx:5432/ia2301";
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#getDriverName()
*/
@Override
public String getDriverName() throws TbsTechnicalException {
return "org.postgresql.Driver";
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#getUserName()
*/
@Override
public String getUserName() throws TbsTechnicalException {
return "IA2301";
}
/*
* (non-Javadoc)
*
* @see com.tbs.db.TbsDbConnectionIfc#getPassword()
*/
@Override
public String getPassword() throws TbsTechnicalException {
return "IA2301";
}
}
<?xml version="1.0" encoding="UTF-8"?>
<Server port="8232" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.startup.VersionLoggerListener"/>
<Listener SSLEngine="on" className="org.apache.catalina.core.AprLifecycleListener"/>
<Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
<Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener"/>
<GlobalNamingResources>
<Resource auth="Container" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase" pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase"/>
</GlobalNamingResources>
<Service name="Catalina">
<Connector compressableMimeType="text/html,text/xml,text/plain,text/css,text/javascript,text/json,application/x-javascript,application/javascript,application/json" compression="on" compressionMinSize="2048" connectionTimeout="20000" noCompressionUserAgents="gozilla, traviata" port="8230" protocol="org.apache.coyote.http11.Http11NioProtocol" redirectPort="8443"/>
<Engine defaultHost="localhost" jvmRoute="WebApp1" name="Catalina">
<Realm className="org.apache.catalina.realm.LockOutRealm">
<Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>
</Realm>
<Host appBase="webapps" autoDeploy="false" name="localhost" unpackWARs="false" xmlNamespaceAware="false" xmlValidation="false">
<Alias>tbs.dev.com</Alias>
<Valve className="org.apache.catalina.valves.ErrorReportValve" errorCode.0="wtpwebapps/veos2-web/WEB-INF/error.html"/>
<Context docBase="veos2-web" override="true" path="/" reloadable="false" source="org.eclipse.jst.jee.server:TbsWeb">
<Loader delegate="true" loader="org.apache.catalina.Loader" loaderClass="com.tbs.bootstrap.classloader.TbsCatalinaClassLoader" reloadable="false"/>
<!--<Resource auth="Container" connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource" connectionWaitTimeout="20" description="UCP Pool in Tomcat" factory="oracle.ucp.jdbc.PoolDataSourceImpl" inactiveConnectionTimeout="10" initialPoolSize="1" initialSize="1" maxPoolSize="20" maxStatements="0" minPoolSize="1" name="jdbc/TbsDataSource" password="WCHAIB" timeoutCheckInterval="5" type="oracle.ucp.jdbc.PoolDataSource" url="jdbc:oracle:thin:@xxxx:1521:dbTBSdev" user="WCHAIB" validateConnectionOnBorrow="true"/>-->
<Resource auth="Container" name="jdbc/TbsDataSource" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://xxxx:5432/ia2301" username="IA2301" password="IA2301" maxWaitMillis="3000" initialSize="10" maxTotal="20" maxIdle="20" minIdle="10" removeAbandonedTimeout="10" removeAbandonedOnBorrow="true" logAbandoned="true"/>
<Parameter name="com.tbs.runtime.env" override="false" value="DEV"/>
<Parameter name="com.tbs.blob.cloud.mode" override="false" value="true"/>
<Parameter name="com.tbs.cloud.os4j.uri" override="false" value="v3@https://auth.cloud.ovh.net/v3@GRA@0353ea2aa1024bb6b92fe14662174632@AvhMJUtmuhgd@w29twSUTmhqhjEcWWwuvQemeVwZYxkMg@default"/>
<Parameter name="com.tbs.force.redirect.email" override="false" value="[email protected]"/>
<Parameter name="com.tbs.SELF_DEPLOY_NODES" override="false" value="WebApp1;WebService1;WorkflowEngine"/>
<Parameter name="com.tbs.mongodb.uri" override="false" value="mongodb://xxxx:9115"/>
<Parameter name="com.tbs.mail.server" override="false" value="smtpmb1.iga-emailing.fr"/>
<Parameter name="com.iga.partners.mdp.perdu" override="false" value="[email protected]"/>
<Parameter name="oo.server.folder" override="false" value="C:/Program Files (x86)/LibreOffice 5/program"/>
<Parameter name="com.tbs.ws.forceBaseAddress" override="false" value="http://tbs.dev.com:90"/>
<Parameter name="tbs.velocity.log.path" override="false" value="${catalina.base}/logs/tbsVelocity.log"/>
<Parameter name="com.tbs.mongodb.nodes.port" override="false" value="WebApp1:8233;WebService1:8234;WorkflowEngine:8235"/>
<Parameter name="com.tbs.force.mongodb.name" override="false" value="wchaibDev"/>
<Parameter name="doc.leasing.url" override="false" value="http://tbs.dev.com:90/tbsDocLeasing.tbs?id=$(idDocLeasing)"/>
<Parameter name="com.tbs.fileImp.tempDir" override="false" value="/home/wchaib/tmp/print"/>
<Parameter name="com.tbs.fileUpload.tempDir" override="false" value="/home/wchaib/tmp/tbsUpload"/>
<Parameter name="com.tbs.iga.arco.datasureu.ftp.local" override="false" value="yes"/>
<CookieProcessor sameSiteCookies="strict"/>
</Context>
<Context docBase="veos2-web" path="/veos2-web" reloadable="true" source="org.eclipse.jst.jee.server:veos2-web"/></Host>
</Engine>
</Service>
</Server>10:38:14,825 INFO XX_XX anonymous [com.tbs.TbsGlobalInitializer] [-1] ** Fin variables permanentes ** 10:39:34,226 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] java.sql.SQLException: Connection is null. 10:39:34,253 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.checkOpen(DelegatingConnection.java:600) 10:39:34,272 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:293) 10:39:34,293 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.jooq.impl.ParsingConnection.lambda$prepareStatement$1(ParsingConnection.java:133) 10:39:34,315 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.jooq.impl.ParsingConnection.lambda$prepareAndBind$0(ParsingConnection.java:110) 10:39:34,338 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.jooq.impl.ParsingStatement.prepareAndBind(ParsingStatement.java:329) 10:39:34,354 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.jooq.impl.ParsingStatement.executeQuery(ParsingStatement.java:339) 10:39:34,370 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at com.tbs.TbsGlobalInitializer.testJooq(TbsGlobalInitializer.java:164) 10:39:34,387 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at com.tbs.TbsGlobalInitializer.init(TbsGlobalInitializer.java:106) 10:39:34,408 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at javax.servlet.GenericServlet.init(GenericServlet.java:158) 10:39:34,429 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1134) 10:39:34,448 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1089) 10:39:34,469 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:983) 10:39:34,492 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4902) 10:39:34,516 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5211) 10:39:34,537 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) 10:39:34,559 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384) 10:39:34,582 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374) 10:39:34,604 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at java.util.concurrent.FutureTask.run(FutureTask.java:266) 10:39:34,627 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75) 10:39:34,671 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134) 10:39:34,714 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909) 10:39:34,756 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:843) 10:39:34,778 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) 10:39:34,801 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384) 10:39:34,823 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374) 10:39:34,844 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at java.util.concurrent.FutureTask.run(FutureTask.java:266) 10:39:34,861 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75) 10:39:34,878 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134) 10:39:34,894 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909) 10:39:34,915 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262) 10:39:34,935 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) 10:39:34,955 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardService.startInternal(StandardService.java:434) 10:39:34,984 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) 10:39:35,025 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:930) 10:39:35,044 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) 10:39:35,059 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.startup.Catalina.start(Catalina.java:772) 10:39:35,075 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 10:39:35,097 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 10:39:35,119 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 10:39:35,142 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at java.lang.reflect.Method.invoke(Method.java:498) 10:39:35,165 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:342) 10:39:35,188 INFO XX_XX anonymous [java.lang.Throwable$WrappedPrintStream] [-1] at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:473)
