CODEWITHSUNDEEP
javamysqlsqlhibernate
Svision
Svision

Reputation: 13

sql query problems with hibernate

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.

Upvotes: 0

Views: 834

Answers (3)

SergeyB
SergeyB

Reputation: 9868

A few problems with your queries:

  1. 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=?");

  2. 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=?

Upvotes: 0

user3662273
user3662273

Reputation: 554

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.

Upvotes: 1

Kartik Jajal
Kartik Jajal

Reputation: 772

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

Upvotes: 0

Related Questions