title: Openshift - How to configure mysql connection using persistence.xml - problems with openshift variables
body: <p>I want to create web application on Openshift which use Tomcat, Spring and Hibernate. </p>
  
  <p>First I have tried to configure the database using persistence.xml like this</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"&gt;
       &lt;persistence-unit name="springshop_pu" transaction-type="RESOURCE_LOCAL"&gt;      &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
        &lt;class&gt;com.codeonblue.springshop.model.Categoria&lt;/class&gt;
        &lt;class&gt;com.codeonblue.springshop.model.Produto&lt;/class&gt;
        &lt;class&gt;com.codeonblue.springshop.model.Usuario&lt;/class&gt;    &lt;properties&gt;
        &lt;property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" /&gt;
        &lt;property name="hibernate.hbm2ddl.auto" value="update"/&gt;
        &lt;property name="hibernate.show_sql" value="true" /&gt;
        &lt;property name="hibernate.format_sql" value="true"/&gt;    &lt;property name="hibernate.connection.charset" value="UTF-8" /&gt;    
        &lt;property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/&gt;
        #{systemEnvironment[OPENSHIFT_MYSQL_DB_HOST]}
        &lt;property name="javax.persistence.jdbc.user" value="#{systemEnvironment[OPENSHIFT_MYSQL_DB_USERNAME]}"/&gt;
        &lt;property name="javax.persistence.jdbc.password" value="#{systemEnvironment[OPENSHIFT_MYSQL_DB_PASSWORD]}"/&gt;
        &lt;property name="javax.persistence.jdbc.url" value="jdbc:mysql://#{systemEnvironment[OPENSHIFT_MYSQL_DB_HOST]}:#{systemEnvironment[OPENSHIFT_MYSQL_DB_PORT]}/#{systemEnvironment[OPENSHIFT_APP_NAME]}"/&gt;   
  
      &lt;/properties&gt;   
  </code></pre>
  
  <p> 
  </p>
  
  <p>using the following  but when I try to create an <strong>EntityManagerFactory</strong> i get this error:</p>
  
  <pre><code>java.lang.NumberFormatException: For input string: "#{systemEnvironment[OPENSHIFT_MYSQL_DB_PORT]}"
  </code></pre>
  
  <p>Later I tried to modify my persistence.xml like this:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"&gt; 
    &lt;persistence-unit name="springshop_pu" transaction-type="RESOURCE_LOCAL"&gt;
      &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
        &lt;class&gt;com.codeonblue.springshop.model.Categoria&lt;/class&gt;
        &lt;class&gt;com.codeonblue.springshop.model.Produto&lt;/class&gt;
        &lt;class&gt;com.codeonblue.springshop.model.Usuario&lt;/class&gt;
      &lt;properties&gt;
        &lt;property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" /&gt;
        &lt;property name="hibernate.hbm2ddl.auto" value="update"/&gt;
        &lt;property name="hibernate.show_sql" value="true" /&gt;
        &lt;property name="hibernate.format_sql" value="true"/&gt;
        &lt;property name="hibernate.connection.charset" value="UTF-8" /&gt;    
        &lt;property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/&gt;
        &lt;property name="javax.persistence.jdbc.user" value="${env.OPENSHIFT_MYSQL_DB_USERNAME}"/&gt;
        &lt;property name="javax.persistence.jdbc.password" value="${env.OPENSHIFT_MYSQL_DB_PASSWORD}"/&gt;
        &lt;property name="javax.persistence.jdbc.url" value="jdbc:mysql://${env.OPENSHIFT_MYSQL_DB_HOST}:${env.OPENSHIFT_MYSQL_DB_PORT}/${env.OPENSHIFT_APP_NAME}"/&gt;    
      &lt;/properties&gt;
    &lt;/persistence-unit&gt;
  &lt;/persistence&gt;  
  </code></pre>
  
  <p>But I have got this error:</p>
  
  <pre><code>java.sql.SQLException: Must specify port after ':' in connection string
  </code></pre>
  
  <p>It seems to me that the openshift database variables are null.  </p>
  
  <p>This is the servlet that I do the testing:</p>
  
  <pre><code> @WebServlet("/GenerateTables")
  public class GenerateTables extends HttpServlet {
      private static final long serialVersionUID = 1L;
  
      public GenerateTables() {
          super();
          // TODO Auto-generated constructor stub
      }
  
      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // TODO Auto-generated method stub
  
  
          System.out.println("Host: " + System.getenv("OPENSHIFT_MYSQL_DB_HOST"));
          System.out.println("Port: " + System.getenv("OPENSHIFT_MYSQL_DB_PORT"));
          System.out.println("Username: " + System.getenv("OPENSHIFT_MYSQL_DB_USERNAME"));
          System.out.println("Password: " + System.getenv("OPENSHIFT_MYSQL_DB_PASSWORD"));
  
  
          EntityManagerFactory factory = Persistence
                  .createEntityManagerFactory("springshop_pu");
          factory.close();
          System.out.println("Let's see if the tables were generated");
      }
  
      protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // TODO Auto-generated method stub
      }
  
  }
  </code></pre>
  
  <p>However this commands in the servlet</p>
  
  <pre><code>System.out.println("Host: " + System.getenv("OPENSHIFT_MYSQL_DB_HOST"));
  System.out.println("Port: " + System.getenv("OPENSHIFT_MYSQL_DB_PORT"));
  System.out.println("Username: " + System.getenv("OPENSHIFT_MYSQL_DB_USERNAME"));
  System.out.println("Password: " + System.getenv("OPENSHIFT_MYSQL_DB_PASSWORD"));
  </code></pre>
  
  <p>show the variables values in the Tomcat Log</p>
  
  <p>What i am doing wrong here?</p>
  
  <p>Please help me out</p>