sql query problems with hibernate

Question

I am simply trying to load my user from my user table via the username and password. However the properties of the user are not getting initialized. I have set fetch="join" (eager loading) in my mapping files. Here is my query:

SQLQuery q = session.createQuery("SELECT u.* FROM users WHERE username=? AND password=?);
q.setString(0, "valid_username");
q.setString(0, "valid_password");

List users = q.list();

The various properties of my users such as its department, user level, etc.. is not getting initialized properly.

My second query: I am simply trying to get the list of tickets from the db using the same method

SQLQuery q = session.createQuery("SELECT t.* FROM tickets WHERE assignee_id=?);
    q.setString(0, "valid_assignee_id");

    List users = q.list();

Same thing is happening... the fields of my ticket such as its status, priority, etc.. are not getting initialized. Once again i have the fetch=join property set for the necessary mapping files. Can anyone point me in the right direction, thanks!!

Edit: Below is my database structure and pojo files for reference"

   users table
    -----------
    u_id
    username
    password
    salt
    email
    firstName
    lastName
    department_id
    userlevel_id

    tickets table
    -------------
    ticket_id
    title
    description
    department_id 
    status_id
    priority_id
    assignee_id
    creator_id

public class Ticket implements java.io.Serializable {

    private Integer tickId;
    private Department department;
    private Status status;
    private User creatorUser;
    private User assignedUser;
    private Priority priority;
    private String title;
    private String description;
    private Set ticketNotes = new HashSet(0);
    private Set attachments = new HashSet(0);

//Getters and setters
}

public class User  implements java.io.Serializable {


     private Integer UId;
     private Department department;
     private Userlevel userlevel;
     private String username;
     private String password;
     private String salt;
     private String email;
     private String firstName;
     private String lastName;
     private Set createdTickets = new HashSet(0);
     private Set assignedTickets = new HashSet(0);

    public User() {
    }

Note im using the hibernate .hbm.xml mapping structure and not annotations.

user3662273 · Accepted Answer · 2014-06-13 18:57:18Z

1

I think, these all are correct queries.you have to define your objects(u,t) like this in the query. it will fetch all the columns in the table

SELECT u FROM users u WHERE u.username=? AND u.password=?

and

SELECT t FROM tickets t WHERE t.assignee_id=?

Yes, i agree with kartik. The parameter values should be in the correct position

q.setString(0, "valid_username");

q.setString(1, "valid_password");

this also needs.

edited Jun 13, 2014 at 18:57

answered Jun 13, 2014 at 18:41

user3662273

5544 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kartik Jajal · Accepted Answer · 2014-06-13 18:53:42Z

0

Also need to provide correct position of the parameters in the query. You have set value of both the parameter to 0 position. That is the reason why its not getting proper data.

q.setString(0, "valid_username");

q.setString(1, "valid_password");

answered Jun 13, 2014 at 18:53

Kartik Jajal

7624 gold badges10 silver badges16 bronze badges

Comments

marc_s · Accepted Answer · 2016-01-07 19:38:29Z

0

A few problems with your queries:

Your are missing closing quotes at the end of your queries

SQLQuery q = session.createQuery("SELECT u.* FROM users WHERE username=? AND password=?);

should be

SQLQuery q = session.createQuery("SELECT u.* FROM users WHERE username=? AND password=?");

You want to retrieve all columns from aliases u and r but you never define them in your queries, should be:

SELECT u.* FROM users u WHERE username=? AND password=?

or

SELECT * FROM users WHERE username=? AND password=?

edited Jan 7, 2016 at 19:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 13, 2014 at 19:06

SergeyB

9,9384 gold badges40 silver badges49 bronze badges

Collectives™ on Stack Overflow

sql query problems with hibernate

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related