Sidebar

How to configure the JDBC connection to the QIE database for MySql?

0 votes
551 views
asked Aug 27, 2020 by ben-s-7515 (12,320 points)
I am trying to configure QIE to use a MySql database. What is the correct way to configure the connection? Specifically what values should I use for:

-Dconnection.driver=
-Dconnection.url=
-Dhibernate.dialect=
-Dconnection.username=
-Dconnection.password=

1 Answer

0 votes

That depends on several variables.

    1. The version of QIE
    2. The version of the MySql database
    3. The version of the JDBC Driver for MySQL (Connector/J)
    4. How the MySql server is configured.  (secure connections, authentication plugin, unicode support, timezone, etc.  See resources below for more or the database documentation for more info.)
    etc.

When downloading the MySQL driver for use in an HA environment such as Docker, Kubernetes or just multiple VM's, the driver needs to be downloaded and placed into the qieHome directory under the folder jdbcDriver.  The .jar file will need to be unzipped into that folder. (Example: /java/qie/jdbcDriver> unzip mysql-connector-java-{version}.jar)

For QIE version .49, with MySql 8.0.20, and MySql Connector/J 8.0.20:

    -Dconnection.driver=com.mysql.cj.jdbc.Driver
    -Dconnection.url=jdbc:mysql://[your mysql instance name]:[port to your mysql instance]/[your database name]?serverTimezone=[timezone matching your mysql instance]&sslMode=DISABLED
    -Dhibernate.dialect=com.qvera.qie.persistence.MySQL8UnicodeDialect
    -Dconnection.username={user_with_access_to_database_schema}
    -Dconnection.password={user_password}

For QIE version .49, with MySql 5.7.x, and MySql Connector/J 8.0.20:
 
    -Dconnection.driver=com.mysql.cj.jdbc.Driver
    -Dconnection.url=jdbc:mysql://[your mysql instance name]:[port to your mysql instance]/[your database name]?serverTimezone=[timezone matching your mysql instance]&sslMode=DISABLED
    -Dhibernate.dialect=com.qvera.qie.persistence.MySQL8UnicodeDialect
    -Dconnection.username={user_with_access_to_database_schema}
    -Dconnection.password={user_password}

For QIE version .49, with MySql 5.7.x, and MariaDB Connector/J 2.x:

    -Dconnection.driver=org.mariadb.jdbc.Driver
    -Dconnection.url=jdbc:mariadb://[your mysql instance name]:[port to your mysql instance]/[your database name]
    -Dhibernate.dialect=com.qvera.qie.persistence.MySQL57UnicodeDialect
    -Dconnection.username={user_with_access_to_database_schema}
    -Dconnection.password={user_password}

For QIE version .48, with MySql 5.7.x, and MySql Connector/J 5.1x:

    -Dconnection.driver=com.mysql.jdbc.Driver
    -Dconnection.url=jdbc:mysql://[your mysql instance name]:[port to your mysql instance]/[your database name]?useUnicode=true&characterEncoding=UTF-8
    -Dhibernate.dialect=com.qvera.qie.persistence.MySQLUnicodeDialect
    -Dconnection.username={user_with_access_to_database_schema}
    -Dconnection.password={user_password}

For QIE version .48, with MySql 5.7.x, and MariaDB Connector/J 5.1x:

    -Dconnection.driver=org.mariadb.jdbc.Driver
    -Dconnection.url=jdbc:mariadb://[your mysql instance name]:[port to your mysql instance]/[your database name]
    -Dhibernate.dialect=com.qvera.qie.persistence.MySQLUnicodeDialect
    -Dconnection.username={user_with_access_to_database_schema}
    -Dconnection.password={user_password}

Note:  In the MySql examples above, one used com.mysql.jdbc.Driver for the driver class and the others used com.mysql.cj.jdbc.Driver. This is because in the latest MySql Connector/J the com.mysql.cj.jdbc.Driver class was added.  The old class, even though still available, is deprecated.
It is recommended to use the new class.

See also:
    What dialects are available in QIE?

Resources:
    https://dev.mysql.com/doc/connectors/en/connector-j-upgrading-to-8.0.html
    https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html
    https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html#connector-j-reference-set-config

answered Aug 27, 2020 by ben-s-7515 (12,320 points)
edited Sep 17, 2021 by ben-s-7515
...