Hot questions for Using GlassFish in derby

Question:

I`m just installed a Glassfish 4.1 on Ubuntu 14.04 and trying to ping DerbyPool but it fails with java.net.ConnectException.

Ping Connection Pool failed for DerbyPool. Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused. Please check the server.log for more details.

server.log:

[2015-01-02T20:51:12.361+0200] [glassfish 4.1] [WARNING] [test.connection.pool.failed] [javax.enterprise.resource.resourceadapter.com.sun.enterprise.connectors.service] [tid: _ThreadID=42 _ThreadName=admin-listener(4)] [timeMillis: 1420224672361] [levelValue: 900] [[ RAR8054: Exception while creating an unpooled [test] connection for pool [ DerbyPool ], Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused.]]

[2015-01-02T20:51:12.366+0200] [glassfish 4.1] [SEVERE] [] [org.glassfish.admingui] [tid: _ThreadID=40 _ThreadName=admin-listener(2)] [timeMillis: 1420224672366] [levelValue: 1000] [[ RestResponse.getResponse() gives FAILURE. endpoint = 'http://localhost:4848/management/domain/resources/ping-connection-pool.json'; attrs = '{id=DerbyPool}']]

The same problem is occurring on another computer with Glassfish 4.1 and Ubuntu 14.04 installed on it.


Answer:

I guess you didn't start the Derby server.

To start it, go to the command line and navigate to a folder where you can run asadmin (probably something like /glassfish/bin) and run the following:

asadmin start-database

Then try pinging again.

Question:

I use Netbeans 8.0.2 on Windows. I wrote an example JSP page

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h2>Hello, World!</h2>
        <sql:query var="allRows" dataSource="jdbc/sample">
            SELECT name, city, state FROM APP.customer
        </sql:query>
        <table border="1">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Location</th>
                </tr>
            </thead>
            <tbody>
                <c:forEach var="currentRow" items="${allRows.rows}">
                    <tr>
                        <td>"${currentRow.name}"</td>
                        <td>"${currentRow.city}", "${currentRow.state}"</td>
                    </tr>
                </c:forEach>
            </tbody>
        </table>
    </body>
</html>

but if I execute this page, I get

javax.servlet.ServletException: 
            SELECT name, city, state FROM APP.customer
        : Table/view 'APP.CUSTOMER' is not exist.

sample database is a demo Derby database. I use GlassFish Server 4.1, JDK 7, Java EE 7. All these are in the default installation of Netbeans. I use default settings of GlassFish Server.

I see sample database connection at Services tab. It is jdbc:derby://localhost:1527/sample. I see this url in SamplePool connection pool properties of GlassFish. This connection pool is used in jdbc/sample JDBC resource. APP.CUSTOMER table is exist in sample database.

What do I do wrong?


Answer:

I'm not sure what is wrong but try the following:

  • copy derbyclient.jar from glassfish4/javadb/lib to glassfish4/glassfish/lib/endorsed
  • restart Glassfish
  • change your code to the following:

    <sql:setDataSource var="snapshot" driver="org.apache.derby.jdbc.ClientDataSource"
                       url="jdbc:derby://localhost:1527/sample"
                       user="app"  password="app"/>
    <sql:query var="allRows" dataSource="${snapshot}">
        SELECT name, city, state FROM APP.customer
    </sql:query>
    

To play with a basic example this should be sufficient, but you may not want to use it in production.

Question:

I'm trying to create a @NamedQuery for a web service so that one can return a list of customers that have a given date of birth.

GlassFish reports the following error:

Exception Description: Problem compiling [SELECT c FROM Customer c WHERE c.dob = to_date(:dob, 'dd-Mon-yyyy')]. [38, 45] The identification variable 'to_date' is not defined in the FROM clause.

@NamedQuery(name = "Customer.findByDob", query = "SELECT c FROM Customer c WHERE c.dob = to_date(:dob, 'dd-Mon-yyyy')")

@GET
    @Path("findByDob/{dob}")
    @Produces({"application/json"})
    public List<Customer> findByDob(@PathParam("dob") String dob) {
        Query q = em.createNamedQuery("Customer.findByDob");
        q.setParameter("dob", dob);
        return q.getResultList(); 
}

Any feedback is much appreciated.


Answer:

You can delegate the transformation from Date to String database format in the entity manager. Something like this should works:

@NamedQuery(name = "Customer.findByDob", query = "SELECT c FROM Customer c WHERE c.dob = :dob")

        @GET
        @Path("findByDob/{dob}")
        @Produces({"application/json"})
        public List<Customer> findByDob(@PathParam("dob") String dob) {
            SimpleDateFormat sdf = new SimpleDateFormat("dd-Mon-yyyy");
            Date dobDate = sdf.parse(dob); //Create a new Java Date object. 

            Query q = em.createNamedQuery("Customer.findByDob");
            q.setParameter("dob", dobDate); //The entity manager will transform the Date to query needs.
            return q.getResultList(); 
    }

Question:

I wrote the following persistence.xml for a glassfish webapp:

<?xml version="1.0" encoding="UTF-8" ?>
<persistence 
    xmlns="http://java.sun.com/xml/ns/persistence" 
    version="2.1">

   <persistence-unit name="mypu" transaction-type="RESOURCE_LOCAL">
     <description>my</description>
     <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
     <class>my.OntClass</class>

     <properties>

       <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/user_lindenb"/>
       <property name="javax.persistence.jdbc.user" value="appuser"/>
       <property name="javax.persistence.jdbc.password" value="apppassword"/>
       <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
       <property name="eclipselink.weaving" value="false"/><!--  http://java.dzone.com/articles/jpa-performance-optimization -->
        <property name="eclipselink.target-database" value="MySQL"/>
        <property name="eclipselink.logging.level" value="ALL"/>
        <property name="eclipselink.logging.parameters" value="false"/>
     </properties>
   </persistence-unit>

</persistence>

The MySQL server is running and I can connect to the MySQL server from a servlet using a:

@Resource (name="jdbc/MysqlDS" )
javax.sql.DataSource mysqlDS;

Now, I want to use the persistence API. But when I want to deploy my application it raises the following error:

remote failure: Error occurred during deployment: Exception while preparing the app : Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused.

As far as I understand, it tries to connect to Derby instead of MySQL?

glassfish log:
(...) 
Caused by: org.apache.derby.client.am.DisconnectException: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused.
    at org.apache.derby.client.net.NetAgent.<init>(Unknown Source)
    at org.apache.derby.client.net.NetConnection.newAgent_(Unknown Source)
    at org.apache.derby.client.am.Connection.initConnection(Unknown Source)
    at org.apache.derby.client.am.Connection.<init>(Unknown Source)
    at org.apache.derby.client.net.NetConnection.<init>(Unknown Source)
    at org.apache.derby.client.net.NetConnection40.<init>(Unknown Source)
    at org.apache.derby.client.net.ClientJDBCObjectFactoryImpl40.newNetConnection(Unknown Source)
    at org.apache.derby.jdbc.ClientDataSource.getConnectionX(Unknown Source)
    ... 71 more
Caused by: java.net.ConnectException: Connection refused
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:579)
    at java.net.Socket.connect(Socket.java:528)
    at java.net.Socket.<init>(Socket.java:425)
    at java.net.Socket.<init>(Socket.java:208)
    at javax.net.DefaultSocketFactory.createSocket(SocketFactory.java:271)
    at org.apache.derby.client.net.OpenSocketAction.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    ... 79 more
(...)

How can I fix this?

Thanks.


Answer:

replaced

 <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

by

 <jta-data-source>jdbc/MysqlDS</jta-data-source>

seems to work now...

Question:

I have a Derby Database Table with the following fields. The database ID fields automatically generates and increments.

create table "Yaya".SERVICELOG
(
ID INTEGER default AUTOINCREMENT: start 1 increment 1 not null primary key,
TXTIME TIMESTAMP,
EVENTLEVEL INTEGER,
EVENTTYPE VARCHAR(255),
DESCRIPTION VARCHAR(4096)
)

Using NetBeans I am trying to use Hibernate as an ORM. I have used Linq2SQL and EF before...This is becoming traumatic.

Here is my generated POJO, I believe the annotations are correct.

// Generated Sep 19, 2014 12:26:20 PM by Hibernate Tools 4.3.1


import java.util.Date;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

/**
 * Servicelog generated by hbm2java
 */
public class Servicelog  implements java.io.Serializable {

@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
 private int id;
 private Date txtime;
 private Integer eventlevel;
 private String eventtype;
 private String description;

public Servicelog() {
}


public Servicelog(int id) {
    this.id = id;
}
public Servicelog(int id, Date txtime, Integer eventlevel, String eventtype, String description) {
   this.id = id;
   this.txtime = txtime;
   this.eventlevel = eventlevel;
   this.eventtype = eventtype;
   this.description = description;
}

public int getId() {
    return this.id;
}

public void setId(int id) {
    this.id = id;
}
public Date getTxtime() {
    return this.txtime;
}

public void setTxtime(Date txtime) {
    this.txtime = txtime;
}
public Integer getEventlevel() {
    return this.eventlevel;
}

public void setEventlevel(Integer eventlevel) {
    this.eventlevel = eventlevel;
}
public String getEventtype() {
    return this.eventtype;
}

public void setEventtype(String eventtype) {
    this.eventtype = eventtype;
}
public String getDescription() {
    return this.description;
}

public void setDescription(String description) {
    this.description = description;
}
}

Here is my code for the insert.

    Session session = StartupBean.sessionFactory.openSession();
    session.beginTransaction();
    Servicelog sl = new Servicelog();
    sl.setDescription("IN IT TO WIN IT");
    session.save(sl);
    session.getTransaction().commit();
    session.close();

It bombs out with the following in the glassfish server console on the session.getTransaction().commit();

Info: HHH000041: Configured SessionFactory: null WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!) Info: HHH000401: using driver [org.apache.derby.jdbc.ClientDriver] at URL [jdbc:derby://localhost:1527/yayadb] Info: HHH000046: Connection properties: {user=yayaadmin, password=****} Info: HHH000006: Autocommit mode: false Info: HHH000115: Hibernate connection pool size: 20 (min=1) Info: HHH000400: Using dialect: org.hibernate.dialect.DerbyDialect WARN: HHH000430: The DerbyDialect dialect has been deprecated; use one of the version-specific dialects instead Info: HHH000399: Using default transaction strategy (direct JDBC transactions) Info: HHH000397: Using ASTQueryTranslatorFactory Info: Loading application [yaya] at [/yaya] Info: yaya was successfully deployed in 6,323 milliseconds.

WARN: SQL Error: -1, SQLState: 42Z23

ERROR: Attempt to modify an identity column 'ID'.

Warning: StandardWrapperValve[DbTest]: Servlet.service() for servlet DbTest threw exception org.hibernate.exception.SQLGrammarException: could not prepare statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:96) at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.buildBatchStatement(AbstractBatchImpl.java:152) at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.getBatchStatement(AbstractBatchImpl.java:141) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3102) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3587) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:103) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:453) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:345) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1218) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:421) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177) at com.ya.ya.zzzyaya.DbTest.processRequest(DbTest.java:97) at com.ya.ya.zzzyaya.DbTest.doGet(DbTest.java:116) at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673) at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188) at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191) at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168) at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189) at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119) at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288) at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206) at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136) at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114) at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77) at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838) at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135) at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564) at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544) at java.lang.Thread.run(Thread.java:745)

Caused by: java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'ID'.

at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:103) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) ... 45 more Caused by: org.apache.derby.client.am.SqlException: Attempt to modify an identity column 'ID'. at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source) at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source) ... 48 more

I've been at it for a few hours and I have no idea how to proceed. What do I have to change in hibernate so it is smart enough to not fling in a primary key?


Answer:

OK, I figured it out, thanks in part to Rp-'s comment

private int id;

had to be changed to

private Integer id; 

And in the Servicelog.hbm.xml

    <id name="id" type="int">
        <column name="ID" />
        <generator class="identity" />
    </id>

to

    <id name="id" type="java.lang.Integer">
        <column name="ID" />
        <generator class="assigned" />
    </id>

Question:

In web.xml i try to change my data-source from derby to PostgreSQL, so how can i change this data-source to another data-source using PostgreSQL and GlassFish.

<data-source>
    <name>java:global/jdbc/CargoTrackerDatabase</name>
    <class-name>org.apache.derby.jdbc.EmbeddedDriver</class-name>
    <url>jdbc:derby:${webapp.databaseTempDir}/cargo-tracker-database;create=true</url>
</data-source>

So what should I change in my code to make a data-source to PostgreSQL?


Answer:

In case of PostgreSQL, the driver class name is now org.postgresql.Driver and the JDBC URL is of type jdbc:postgresql://$host:$port/$db-name knowing that the default port is 5432.

You can find more details about all the existing parameters that you can provide at the JDBC URL level here.