#StackBounty: #java #sql #hibernate #jpa Hibernate CompositeUserType that is comparible in JPA-QL (or HQL) query

Bounty: 50

I’ve created a custom type for Hibernate to store an OffsetDateTime‘s timestamp and offset (because the default JPA 2.2 / Hibernate 5.2 with java 8 support implementation loses the offset information):

public class OffsetDateTimeHibernateType implements CompositeUserType {

    public Class returnedClass() {
        return OffsetDateTime.class;

    public String[] getPropertyNames() {
        return new String[] {"dateTime", "zoneOffset"};

    public Type[] getPropertyTypes() {
        // Not sure if we should use LocalDateTimeType.INSTANCE instead of TIMESTAMP
        return new Type[]{StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER};

    public Object getPropertyValue(Object o, int propertyIndex) {
        if (o == null) {
            return null;
        OffsetDateTime offsetDateTime = (OffsetDateTime) o;
        switch (propertyIndex) {
            case 0:
                return Timestamp.valueOf(offsetDateTime.toLocalDateTime());
            case 1:
                return offsetDateTime.getOffset().getTotalSeconds();
                throw new IllegalArgumentException("The propertyIndex (" + propertyIndex
                        + ") must be 0 or 1.");

    public OffsetDateTime nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner)
            throws SQLException {
        if (resultSet == null) {
            return null;
        Timestamp timestamp = (Timestamp) StandardBasicTypes.TIMESTAMP.nullSafeGet(resultSet, names[0], session, owner);
        if (timestamp == null) {
            throw new IllegalStateException("The timestamp (" + timestamp + ") for an "
                    + OffsetDateTime.class.getSimpleName() + "cannot be null.");
        LocalDateTime localDateTime = timestamp.toLocalDateTime();
        Integer zoneOffsetSeconds = (Integer) StandardBasicTypes.INTEGER.nullSafeGet(resultSet, names[1], session, owner);
        if (zoneOffsetSeconds == null) {
            throw new IllegalStateException("The zoneOffsetSeconds (" + zoneOffsetSeconds + ") for an "
                    + OffsetDateTime.class.getSimpleName() + "cannot be null.");
        return OffsetDateTime.of(localDateTime, ZoneOffset.ofTotalSeconds(zoneOffsetSeconds));

    public void nullSafeSet(PreparedStatement statement, Object value, int parameterIndex, SessionImplementor session)
            throws SQLException {
        if (value == null) {
            statement.setNull(parameterIndex, StandardBasicTypes.TIMESTAMP.sqlType());
            statement.setNull(parameterIndex, StandardBasicTypes.INTEGER.sqlType());
        OffsetDateTime offsetDateTime = (OffsetDateTime) value;
        statement.setTimestamp(parameterIndex, Timestamp.valueOf(offsetDateTime.toLocalDateTime()));
        statement.setInt(parameterIndex, offsetDateTime.getOffset().getTotalSeconds());

    // ************************************************************************
    // Mutable related methods
    // ************************************************************************

    public boolean isMutable() {
        return false;

    public Object deepCopy(Object value) {
        return value; // OffsetDateTime is immutable

    public Object replace(Object original, Object target, SessionImplementor session, Object owner) {
        return original; // OffsetDateTime is immutable

    public void setPropertyValue(Object component, int property, Object value) {
        throw new UnsupportedOperationException("A OffsetDateTime is immutable.");

    // ************************************************************************
    // Other methods
    // ************************************************************************

    public boolean equals(Object a, Object b) {
        if (a == b) {
            return true;
        } else if (a == null || b == null) {
            return false;
        return a.equals(b);

    public int hashCode(Object o) {
        if (o == null) {
            return 0;
        return o.hashCode();

    public Serializable disassemble(Object value, SessionImplementor session) {
        return (Serializable) value;

    public Object assemble(Serializable cached, SessionImplementor session, Object owner) {
        return cached;


Now, I want to be able to compare it, so this JPA-QL query works:

       @NamedQuery(name = "Shift.myQuery",
                   query = "select sa from Shift sa" +
                           " where sa.endDateTime >= :startDateTime" +
                           " and sa.startDateTime < :endDateTime")

on this model:

public class Shift {

    @Type(type = "...OffsetDateTimeHibernateType")
    @Columns(columns = {@Column(name = "startDateTime"), @Column(name="startDateTimeOffset")})
    private OffsetDateTime startDateTime;
    @Type(type = "...OffsetDateTimeHibernateType")
    @Columns(columns = {@Column(name = "endDateTime"), @Column(name="endDateTimeOffset")})
    private OffsetDateTime endDateTime;



But that fails with:

HHH000177: Error in named query: Shift.myQuery: org.hibernate.hql.internal.ast.QuerySyntaxException: >= operator not supported on composite types. [select sa from org.optaplanner.openshift.employeerostering.shared.shift.Shift sa where sa.endDateTime >= :startDateTime and sa.startDateTime < :endDateTime]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79)
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:218)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
    at org.hibernate.internal.NamedQueryRepository.checkNamedQueries(NamedQueryRepository.java:155)
    at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:796)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:492)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:422)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:880)

How can I make my CustomUserType comparable?

Get this bounty!!!

#StackBounty: #sql #spring-boot #h2 H2 MERGE INTO … USING with multiple values, do NOT update if exists

Bounty: 50

I am currently writing a data.sql SQL-script for my spring-boot application. I want it to fill in default values for various tables (e.g. on initial start-up).

I have set spring.jpa.hibernate.ddl-auto=update in the application.properties to keep the contents of the database, but the data.sql is executed each time the service boots up.

Now I am looking for a way to insert rows if they do not exist without updating them if they do exist.

I am using H2Dialect (and a H2-database).

Initially I wanted to use some sort of “IF NOT EXISTS“-statement along with SELECT COUNT(*) FROM table, but it seems H2 does not support this directly.

So the next best thing to do is to use MERGE INTO, but this does not seem to work as expected and I do not understand why so.

This is what my script looks like, but sadly it does not work as expected:

        ('en', 'English', 'USA', TRUE, TRUE),
        ('de', 'German', 'Deutschland', FALSE, FALSE);

I’d like to add these values either if they are absent (which is probably easier) or if there are no values present in the Languages-table (which is what I would prefer).

This is the (shortened) exception I receive when I start my spring-boot application:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "MERGE INTO LANGUAGES AS[*] T USING (SELECT * FROM LANGUAGES) AS S ON (T.ID = S.ID) WHEN NOT MATCHED THEN INSERT (LANGUAGE_CODE, NAME, I18N_NAME, LOCALIZE_UI, CAN_CHOOSE) VALUES ('en', 'English', 'USA', TRUE, TRUE), ('de', 'German', 'Deutschland', FALSE, FALSE) "; erwartet "., (, KEY, VALUES, (, WITH, SELECT, FROM"
Syntax error in SQL statement "MERGE INTO LANGUAGES AS[*] T USING (SELECT * FROM LANGUAGES) AS S ON (T.ID = S.ID) WHEN NOT MATCHED THEN INSERT (LANGUAGE_CODE, NAME, I18N_NAME, LOCALIZE_UI, CAN_CHOOSE) VALUES ('en', 'English', 'USA', TRUE, TRUE), ('de', 'German', 'Deutschland', FALSE, FALSE) "; expected "., (, KEY, VALUES, (, WITH, SELECT, FROM"; SQL statement:
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.getSyntaxError(DbException.java:205) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.getSyntaxError(Parser.java:541) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseSelectSimple(Parser.java:2073) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseSelectSub(Parser.java:1940) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1755) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseSelect(Parser.java:1743) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseMerge(Parser.java:1053) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parsePrepared(Parser.java:423) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:321) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:297) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.prepareCommand(Parser.java:258) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareLocal(Session.java:578) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareCommand(Session.java:519) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:176) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) ~[h2-1.4.196.jar:1.4.196]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_121]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.27.jar:na]
    at com.sun.proxy.$Proxy97.execute(Unknown Source) ~[na:na]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:470) ~[spring-jdbc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    ... 78 common frames omitted

The exception stacktrace is huge, but the “caused by”-parts basically say, that beans could not be created due to the deepest exception, the “caused by”-clause of which I posted above. If anyone needs more details, please let me know.

Edit: or is there a best practice for initializing data (such as default user or default anything)? Preferably one I can reuse if a database reset is performed.

Get this bounty!!!

#StackBounty: #php #mysql #sql #ajax #recursive-query Order hierarchical query for comments with parent-child relation?

Bounty: 50

I have a comments system that uses the adjacency list to store hierarchical data (i believe), for example: MySql table has the columns id, parent_id, date, … , where a comment without a parent_id is a main comment and a comment with a parent_id is naturally a reply.

On initial page load i make an Ajax call that loads all the comments without a parent_id, so all the main comments.

SELECT * FROM comms WHERE parent_id IS NULL

Now if any of the comments have replies a button like “load replies” appears for that comment and on click another call is made that loads all the comments that have as parent_id that comment id, then the recursive query loads the replies of replies and so on. And that works pretty well the problem is that from the order they are loaded you can’t really tell what is a replay to what.

So what i want is to order them so that a replay is under the comment that it belongs.

Now is this possible only from sql doing something like ORDER BY id = parent_id, ordering them so that they somewhat make sense or should i handle this from php? Or should i just start over and store them in a different way?

Edit: part of the second query (example taken from this answer i found a while back)

SELECT date_p, parent_id, id
   FROM (SELECT * FROM comms) rec,
   (SELECT @pv := 14) initialisation
   WHERE find_in_set(parent_id, @pv) > 0 
   AND @pv := concat(@pv, ',', id) ORDER BY ?

If i would use the “Alternative 1” provided in the answer i liked for, would the method for ordering be different or better?

This is what I am trying to achive:

<p>Main comm 1</p>
  <p>reply to main comm 1</p>
  <p>another reply to main comm 1</p>
    <p> replay to reply of main comm 1</p>
  <p> yet another reply to main comm 1</p>
<p>Main comm 2</p>
<p>Main comm 3</p>

Get this bounty!!!

#StackBounty: #sql #database-design #data-warehouse #storage Best way to store physical stock data in a SQL db

Bounty: 50

I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain location at a certain time. the variables we want to look at are, therefore: skus, sites, day, hour and quantity.

The problem is that there are approximately: 100k skus and 200 stores. If I store data for every hour I would get 100k x 200 x 24 = 480,000,000 lines a day. I understand that not every sku has a quantity > 0 at every site. Let’s assume sparsity is as high as 80% and when the quantity is 0 we don’t store it so we remain with 96,000,000 rows a day. Moreover, even though we have websites and sales won’t stop at night we may assume that night hours will have very little changes in quantities and that not every sku is sold every hour. Let’s assume that on average a sku is sold only on 3 different hours a day. This brings us down to 12,000,000.

This is 2.5% of what we initially looked at, however, it is still a considerable amount. 12,000,000 rows every day is going to occupy a lot of space after a few months. A partial solution to the problem is what Kimball suggests in The Data Warehouse Toolkit which is to store data for each hour for the last month or so and then only daily for the last n months and then remove older records. Considering that we are already assuming that on average we sell a sku only on 3 different hours per day switching to daily data from hourly data will reduce only by two thirds. Let’s assume I want to save stock data for the last six month. One month at hourly level and five months at daily level (let’s also assume 30 days months to ease the math) this is going to be: (12,000,000 X 30) + (4,000,000 X 150) = 960,000,000 rows.

I really can’t think of a better way to do this, had anyone had to work with physical stock data before? Any useful lesson you learned? Is there a more efficient way to store this data?


Get this bounty!!!

#StackBounty: #sql #database-design stock management – need suggestion

Bounty: 50

I am trying to make a stock management software according to my client’s requirement, where I bought products by weight or qty depending on product type. Which has following database structure.

User { id, name, address, role (Customer, Vendor, Labor etc)}
Product {id, name, type}

Types of product:

  1. Sheet
  2. Circle (After cutting)
  3. Rolled Circle (After rolling)
  4. Dark Circle (After Buffing)
  5. Ready Circle (After Polish)

The issue is the product I purchase may go through 4 process:

  1. Cutting
  2. Rolling
  3. Moulding
  4. Polish

The above process depends on what type of product I purchase. If I purchase raw product then I may give it to labour who will cut it and give it back to me (i.e. cutting). In this process some material goes to scrap.
As you can see if I buy sheet which is purchased on weight is gone for cutting and turned circle (product type 2). Product type 2 is calculated as per qty.

I can ask client to put approximate weight of qty they received but issue is how can I manage the stock how much is remain at labour place.

Consider I am distributor I purchase products from multiple vendors, the products are classified in several types which is mentioned above. Every vendor has his price for products.

After purchase the product may go for processing or it may be sold. If it goes to process there will be 4 stages, according to client he can decide to sell product during any stage.
For e.g.

  1. Client bought/purchased a product which is calculated as per weight in kg.
  2. The product is given for cutting (stage 1) to multiple labours in parts for e.g. If I have 100 kg raw material, I gave 50kg to labour1, 20kg to labour2 and remaining is given to labour3, during this stage the product turned into multiple pieces (number of pcs are not same every time due to scrap generated during this process is not fixed so I cannot calculate the number of pcs by formula as of now we can expect the approximate pcs which may vary +/- 10 to 100).
  3. Now labour will send me the pcs in parts for e.g. Labour1 got 50kg raw material which turned 1000 pcs he sent me these pcs in parts i.e. 500 pcs were sent day before yesterday 200 pcs were sent on yesterday 100 pcs were sent today Rest pcs are remaining at labour1 side

Issues I am facing

  1. I am unable to manage how much stock I have given to labours
  2. How much remain at their end (i.e. labours end)
  3. How to maintain stock details for selling purpose at it is not managed through one table.

What I have done till now

I created following schema to solve my issue but I am not sure how much it is correct according to rules of DBMS.

{ id, vendor_id, prod_id, weight, qty, focusOn (weight / qty), 
  rate, amount, remainingWeight, remainingQty }

{ id, purchase_id, prod_id, labour_id, weight, qty, focusOn (weight / qty) }

{ id, job_id, prod_id, weight, qty }

focus on parameter check whether to check weight of returned product or qty to make sure accountability.

Get this bounty!!!

#StackBounty: #sql-server #sql #replication #azure-sql-database Replicating on-premise SQL Server instance with hundreds of databases t…

Bounty: 50

I am wanting to replicate an entire SQL Server 2016 on-premise instance of databases (300+) to an Azure SQL Managed Instance for redundancy of a read-replica in case of on-premise downtime. It appears as though I can use Azure Database Migration Service in order to deploy multiple databases, but this seems to imply that this is a one-time migration.

This will not be a one-time migration, and I am wanting to perform transaction replication with a Publish (on-premise SQL) and Subscriber (Azure SQL). Microsoft has outlined this method, but Publications only allow for a single database to be selected. I would like to explore publishing the full instance of databases, and shipping the logs at a particular interval.

Each of these databases share the same schema, so merging the data into a single database poses problems surrounding application configurations in the event we need to fail-over for read purposes. This is also a temporary solution until we are able to replicate our VMware cluster for Disaster Recovery, and our transactional throughput is on the lower side and is constrained to set working hours.

Is my only option to create publications for each and every database, or can the Azure Data Migration Service be extended for replicating at regular intervals?

Edit: Another option I’ve been toying with is creating a single database with each individual tenant as a security schema and file group, and developing an ETL pipeline to pass the tenants to these matching security schemas. In essence I would mimic the database-per-tenant through tenant-per-schema, allowing me to only need to push a single database to the Azure SQL. This might cause more headaches than it would solve, though. Does anyone have experience with this at scale?

Get this bounty!!!

#StackBounty: #sql-server #sql #insert #bulk-insert Multiple inserts of batch insert over the wire?

Bounty: 100

I am generating about 500 records.

What I want to do is INSERT them all at the same time because:

  • The server is located elsewhere and I would rather send one big payload over.
  • This seems like less overhead than sending 500 separate insert queries over the wire.

But because they’re batch inserting, I don’t want to rollback everything if one insert fails – I want to try to insert the rest in there and record the failure(s).

What’s the best way to do this in SQL Server? Is BULK INSERT what I want?

Get this bounty!!!

#StackBounty: #sql #google-bigquery Stack / Append the table column-wise in BigQuery

Bounty: 50

I have a table in BigQuery which gets new data daily and appends it to the current table. This table is called : score_FULL. Additionally, I keep the individual scores on a daily basis, which can be accessed by score_20180125 if we choose today’s date.

EDIT: In a sense, the question is if I can append the score data column-wise.

score_FULL contains:

visitorID         score
#Older visits first
1                 0.15
2                 0.78
3                 0.12
6                 0.90
1                 0.44
2                 0.22
6                 0.65
7                 0.61
10                0.24
1                 0.31
2                 0.41
10                0.12
#Newest visits appended

—- Just show the data appended from next day.

In short, I would like to plot the score changes for each visitorID e.g.

visitorID     day1      day2       day3
1             0.15      NAN        0.31

I know that since we are working with 1 table, day2 = NAN will not be possible, and hence it’s okay to just show score changes when they exist, not day specific, so the ideal solution is:

visitorID     score1      score2       score3
1             0.15        0.44         0.31 
2             0.78        0.22         0.41
3             0.12 

E.g. a table that grows horizontally (new scores) AND vertically (new users)

I can do this using a sequence of Left Joins on individual tables, but this would only give me the visitor data from 1st table we run left joins from.

Note: I can add another column Date which will simply repeat the
same date for all values, if it makes things easier.

Get this bounty!!!

#StackBounty: #mysql #sql #concat Concat multiple row from several tables

Bounty: 300


id_ticket |id_ticket_category |
1         |8                  |
2         |8                  |
3         |13                 |


id_category |id_parent |label                             |id_category_type |
1           |-1        |Demande d'information             |1                |
2           |-1        |Réclamation                       |1                |
3           |1         |Autre                             |2                |
4           |1         |Mairie                            |2                |
5           |1         |Stationnement                     |2                |
6           |2         |Autre                             |2                |
8           |3         |Autre                             |3                |
9           |4         |Réservation de salle              |3                |
10          |4         |Autre                             |3                |
11          |5         |Horaires de stationnement         |3                |
12          |5         |Autre                             |3                |
13          |6         |Autre                             |3                |
16          |7         |Autre                             |3                |

Notice that the id_ticket_category column in the Ticket table refers to the id_category in the Category table.

I want to return the count and concat row who i have a id_ticket_category (id_category) with id_parentof element.


For the id_ticket_category = 8 i look on the Category table

id_category |id_parent |label                             |id_category_type |
8           |3         |Autre                             |3                |

I look is id_parent is not equal at -1

id_category |id_parent |label                             |id_category_type |
3           |1         |Autre                             |2                |

I look is id_parent is not equal at -1

id_category |id_parent |label                             |id_category_type |
1           |-1        |Demande d'information             |1                |

id_parent is equal -1 i print my result

Category                 |count|
Autre-Demande Information| 2   | 

Actually i have this request:

SELECT CONCAT(c.label, '-', parents.label), c.id_parent  
(SELECT id_category AS id, label FROM category c WHERE id_parent = -1) AS parents
ON parents.id = c.id_parent 
WHERE  c.id_category_type < 3 
ORDER BY c.id_category_type;

so does anyone know how to improve this?

Get this bounty!!!

#StackBounty: #microsoft-excel #microsoft-excel-2013 #sql #power-query #salesforce Excel – Using SQL command on Power Query data

Bounty: 100

Using Power Query, I pull in two reports from Salesforce using Power Query > Get external Data > From Online Services.
I load them into tables in two different sheets, and then create a third worksheet and table using Data > Get external Data > From other sources > From MS Query > From Excel referencing the workbook itself.

Now I can change the SQL Command text under Connection Properties and reference the Salesforce reports by using the Worksheet they’re in (e.g. [Sheet2$])

I am successfully able to change my Salesforce reports online and have the change show up in my Excel, and using SQL, I can do things with the data in a way that I’m familiar with.

My question: Can I do the same thing, without loading the Salesforce reports into worksheets? Without MS Query’ing the woorkbook itself?

Get this bounty!!!