how to put ampersand (&) in a jdbc url?

Question

I have a dynamic application with glassfish server and using EclipseLink (JPA 2.1). I used to could put jdbc configuration in the persistence.xml directly and didn't have any problem. But now it forces me to create a datasource name for glassfish and putting jdbc configuration into glassfish-resources.xml file.

the problem is that I want to set character encoding for mysql to utf8 and thus, I used below url for jdbc url:

jdbc:mysql://localhost:3306/fastfood?zeroDateTimeBehavior=convertToNull;&characterEncoding=UTF-8;

but i get this exception:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: The connection property 'zeroDateTimeBehavior' only accepts values of the form: 'exception', 'round' or 'convertToNull'. The value 'convertToNull;' is not in this set.

I want to know how to add multiple parameters in the url with ampersand? :D

also I want not to create a JNDI data source, instead I put the jdbc properties in the persistence.xml manually and ensured that I specified the JDBC driver correctly and added the MySQL JDBC driver to library, but I got the error message saying:

"no suitable driver found"

the complete content of the glassfish-resource is as following:

<resources>
    <jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="mysql_rootPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
        <property name="serverName" value=""/>
        <property name="User" value="root"/>
        <property name="Password" value=""/>
        <property name="URL" value="jdbc:mysql://localhost:3306/fastfood?zeroDateTimeBehavior=convertToNull;&amp;characterEncoding=UTF-8;"/>
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    </jdbc-connection-pool>
    <jdbc-resource enabled="true" jndi-name="Fastfood" object-type="user" pool-name="mysql_rootPool"/>
</resources>

Tiphani

Have you already tried just the ampersand? I don't see why that would fail, given that it's an attribute and not text. – Undulation

Answer 1

Try to turn;& into just & and make sure to remove the leading semicolon. I think it is trying to read the value of convertToNull as convertToNull;, which is invalid...

So...

jdbc:mysql://localhost:3306/fastfood?zeroDateTimeBehavior=convertToNull;&characterEncoding=UTF-8;

would become:

jdbc:mysql://localhost:3306/fastfood?zeroDateTimeBehavior=convertToNull&characterEncoding=UTF8;

edit: also try removing the dash from utf-8 per https://mcmap.net/q/514006/-jdbc-character-encoding

There's a lot of information out there but I can't personally help you troubleshoot why it isn't taking the setting. Your initial error was because the querystring was incorrect. This is now a settings problem...

JDBC MySQL UTF-8 string writing problem
problem with utf8 in java

Redingote

use characterEncoding=utf8 and remove the dash – Redingote

sorry the dash is just in my question, I removed the convertToNull and changed it to just: jdbc:mysql://localhost:3306/fastfood?characterEncoding=utf8 but it doesn't change – Tiphani

take a look at this SO post and see if that can help you troubleshoot it – Redingote

Ampersand in XML file is a special character so you should escape it using & . – Catheterize

When you put this in an XML file, the Ampersand must be escaped, so needs to be &. At least with my current Java XML parsers the file otherwise will not get parsed (just had that). Maybe this is tolerated in some parsers, as this answer seems to have worked for you and @Catheterize writes "should", but the correct answer is @Crandall 's. – Archiearchiepiscopacy

Answer 2

The problem is the semicolon before the ampersand escape. Just remove it.

Crandall

Answer 3

jdbc:mysql://1.1.1.1:3306/dev?useUnicode=yes&characterEncoding=UTF-8

connection string above works for me. so like @EJP said, remove the first semi-colon.

Babble