27

Lets say I have one table with two columns firstname and lastname with String datatype. Normally I write my hql query like 

"select firstname,lastname from contact"

Can I write a hql query which concatenates both property ?

Maybe something like "select firstname+lastname as fullname from Contact"

abiieez
  • 3,139
  • 14
  • 57
  • 110

5 Answers5

54
select concat(c.firstname, c.lastname) as fullname from Contact c

or, if you want a separator:

select concat(c.firstname, ' ', c.lastname) as fullname from Contact c

See the documentation.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
24

You may create a calculated column in your entity:

@Formula(value = " concat(first_name, ' ', last_name) ")
private String fullName;

And in your HQL you just refer to this field as you would do to any other.

In your case, you can do:

"select fullName from Contact"
martins.tuga
  • 1,662
  • 1
  • 16
  • 20
  • does this result in an actual database column ? or is this more of an in-memory thing ? – bvdb Oct 21 '20 at 11:28
  • @bvdb i'm not a database expert, but I would say it depends on the DB engine. What you can do is to log the SQL statement and execute an explain plan. – martins.tuga Oct 21 '20 at 18:17
5

You can also use || concatenation operator:

"select c.firstName || ' ' || c.lastName as fullName from Contact"

allthough it may be confusing to read.

Radek Wroblewski
  • 279
  • 2
  • 8
3

I did it so with hql

public List<Contract> findContracts(String fullName) {
    Query q = sessionFactory.getCurrentSession().createQuery("from Contract c where :fullName = concat(c.firstname, ' ', c.lastname)");
     q.setString("fullName", fullName);
     return q.list();}
Dosi Bingov
  • 61
  • 5
0

public String getname(){return first_name+last_name;}

use this method will you see answer

Vishva
  • 1