tomcat - Openshift - 如何使用 persistence.xml 配置 mysql 连接 - openshift 变量问题

Question

我想在 Openshift 上创建使用 Tomcat、Spring 和 Hibernate 的 Web 应用程序。

首先我尝试像这样使用 persistence.xml 配置数据库

<?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
     <persistence-unit name="springshop_pu" transaction-type="RESOURCE_LOCAL">      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>com.codeonblue.springshop.model.Categoria</class>
      <class>com.codeonblue.springshop.model.Produto</class>
      <class>com.codeonblue.springshop.model.Usuario</class>    <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
      <property name="hibernate.hbm2ddl.auto" value="update"/>
      <property name="hibernate.show_sql" value="true" />
      <property name="hibernate.format_sql" value="true"/>    <property name="hibernate.connection.charset" value="UTF-8" />    
      <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      #{systemEnvironment[OPENSHIFT_MYSQL_DB_HOST]}
      <property name="javax.persistence.jdbc.user" value="#{systemEnvironment[OPENSHIFT_MYSQL_DB_USERNAME]}"/>
      <property name="javax.persistence.jdbc.password" value="#{systemEnvironment[OPENSHIFT_MYSQL_DB_PASSWORD]}"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://#{systemEnvironment[OPENSHIFT_MYSQL_DB_HOST]}:#{systemEnvironment[OPENSHIFT_MYSQL_DB_PORT]}/#{systemEnvironment[OPENSHIFT_APP_NAME]}"/>   

    </properties>   

使用以下但当我尝试创建 EntityManagerFactory 时出现此错误:

java.lang.NumberFormatException: For input string: "#{systemEnvironment[OPENSHIFT_MYSQL_DB_PORT]}"

后来我尝试像这样修改我的persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"> 
  <persistence-unit name="springshop_pu" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>com.codeonblue.springshop.model.Categoria</class>
      <class>com.codeonblue.springshop.model.Produto</class>
      <class>com.codeonblue.springshop.model.Usuario</class>
    <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
      <property name="hibernate.hbm2ddl.auto" value="update"/>
      <property name="hibernate.show_sql" value="true" />
      <property name="hibernate.format_sql" value="true"/>
      <property name="hibernate.connection.charset" value="UTF-8" />    
      <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.user" value="${env.OPENSHIFT_MYSQL_DB_USERNAME}"/>
      <property name="javax.persistence.jdbc.password" value="${env.OPENSHIFT_MYSQL_DB_PASSWORD}"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://${env.OPENSHIFT_MYSQL_DB_HOST}:${env.OPENSHIFT_MYSQL_DB_PORT}/${env.OPENSHIFT_APP_NAME}"/>    
    </properties>
  </persistence-unit>
</persistence>  

但是我遇到了这个错误:

java.sql.SQLException: Must specify port after ':' in connection string

在我看来，openshift 数据库变量为空。

这是我进行测试的 servlet:

 @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
    }

}

但是这个命令在 servlet 中

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"));

在 Tomcat 日志中显示变量值

我做错了什么？

请帮帮我

Answer 1

我放弃了使用 openshift 环境变量。我放弃了该项目并再次从头开始，但这次我使用了 openshift 提供的预配置上下文 (MySQLDS)，并且效果很好。

首先我在数据库中插入了一些数据:

--
-- Table structure for table `names`
--
CREATE TABLE IF NOT EXISTS `names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first` varchar(50) NOT NULL,
  `last` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Data for table `names`
--
INSERT INTO `names` (`id`, `first`, `last`) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe'),
(3, 'John', 'Smith'),
(4, 'Jane', 'Smith');

然后我创建了文件 mysql.jsp 如下:

<%@ page language="java" contentType="text/html; charset=US-ASCII" pageEncoding="US-ASCII"%>
<%@ page import='java.sql.*' %>
<%@ page import='javax.sql.*' %>
<%@ page import='javax.naming.*' %>
<!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=US-ASCII">
<title>Tomcat 6 &amp; Tomcat 7 (JBOSS EWS) Example</title>
</head>
<body>
<%
Connection result = null;
try {
    InitialContext ic = new InitialContext();
    Context initialContext = (Context) ic.lookup("java:comp/env");
    DataSource datasource = (DataSource) initialContext.lookup("jdbc/MySQLDS");
    result = datasource.getConnection();
    Statement stmt = result.createStatement() ;
    String query = "select * from names;" ;
    ResultSet rs = stmt.executeQuery(query) ;
    while (rs.next()) {
        out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + "<br />");
    }
} catch (Exception ex) {
    out.println("Exception: " + ex + ex.getMessage());
}
%>
</body>
</html>

上面的代码运行良好。

如果有人需要更多详细信息，请按照此链接中的说明进行操作: https://help.openshift.com/hc/en-us/articles/202399720-How-to-use-the-pre-configured-MySQLDS-and-PostgreSQLDS-data-sources-in-the-Java-cartridges

希望对其他人有帮助。

干杯!