How to implement DAO update method

[Maciej Olborski]

Hi,

I am working on an application and I have been stuck for few days on implementing complete DAO for my User model. After a lot of trying I managed to get everything working except the update method for User.

That is my interface:

public interface UserDAO {
    
    public boolean authenticate(String eMail, String password) throws SQLException;
    public void create(User user) throws SQLException;
    public void update(User user) throws SQLException;
    public void delete(int id) throws SQLException;
    public void truncateTable() throws SQLException;
    public User getDetailsById(int id) throws SQLException;
    public List<User> findAll() throws SQLException;
    
}

That is my DAO Implementation:

public class UserDAOImpl implements UserDAO {

    Connection conn = null;
    private static final String AUTHENTICATE = "SELECT * FROM users WHERE email=? and password=?";
    private static final String DELETE = "DELETE FROM users WHERE id=?";
    private static final String TRUNCATE = "TRUNCATE users";
    private static final String FIND_BY_ID = "SELECT * FROM users WHERE id=?";
    private static final String FIND_ALL = "SELECT * FROM users ORDER BY id";
    private static final String INSERT = "INSERT INTO users (id, firstName, lastName, joinDate, cardId, email, password, phoneNumber, accountType, location) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE = "UPDATE users SET firstName=?, lastName=?, joinDate=?, cardId=?, email=?, password=?, phoneNumber=?, accountType=?, location=? WHERE id=?";

    @Override
    public boolean authenticate(String eMail, String password) {

        Boolean result = false; // Set result to false   

        try {

            PreparedStatement ps = conn.prepareStatement(AUTHENTICATE);
            ps.setString(1, eMail); // Set 1st WHERE to eMail
            ps.setString(2, password); // Set 2nd WHERE to password

            ResultSet rs = ps.executeQuery(); // Execute Query

            //Check if there is a db result matching the email and password
            if (!rs.next() && rs.getRow() == 0) {
                result = false; // If there is no result set false
                System.out.println("User details are incorrect.");
            } else {
                result = true; // If there is a result set true
                System.out.println("User logged in.");
            }
        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }

        return result;

    }

    @Override
    public void create(User user) {

        try {

            PreparedStatement ps = conn.prepareStatement(INSERT);

            ps.setString(1, user.getFirstName());
            ps.setString(2, user.getLastName());
            ps.setString(3, user.getJoinDate());
            ps.setString(4, user.getCardId());
            ps.setString(5, user.geteMail());
            ps.setString(6, user.getPassword());
            ps.setString(7, user.getPhoneNumber());
            ps.setString(8, user.getAccountType());
            ps.setString(9, user.getLocation());

            ps.executeUpdate();
            ps.close();

            System.out.println("User with following details was saved in DB: " + user.toString());

        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }

    }

    @Override
    public void update(User user) {
        
        try {

            PreparedStatement ps = conn.prepareStatement(UPDATE);

            ps.setString(1, user.getFirstName());
            ps.setString(2, user.getLastName());
            ps.setString(3, user.getJoinDate());
            ps.setString(4, user.getCardId());
            ps.setString(5, user.geteMail());
            ps.setString(6, user.getPassword());
            ps.setString(7, user.getPhoneNumber());
            ps.setString(8, user.getAccountType());
            ps.setString(9, user.getLocation());
            ps.setInt(10, user.getId());
            
            ps.executeUpdate();
            ps.close();

            System.out.println("User with id " + user.getId() + " was updated in DB with following details: " + user.toString());

        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }
        
    }

    @Override
    public void delete(int id) {

        try {

            PreparedStatement ps = conn.prepareStatement(DELETE);

            ps.setInt(1, id);

            ps.executeUpdate();
            ps.close();

            System.out.println("User with id: " + id + " was sucesfully deleted from DB.");

        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }

    }

    @Override
    public void truncateTable() {

        try {

            Statement statement = conn.createStatement();

            statement.executeUpdate(TRUNCATE);
            statement.close();

            System.out.println("Users database was truncated.");

        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Override
    public User getDetailsById(int id) {

        User user = null;

        try {
            PreparedStatement ps = conn.prepareStatement(FIND_BY_ID);
            ps.setInt(1, id); // Set 1st WHERE to int

            ResultSet resultSet = ps.executeQuery();

            if (resultSet.next()) {

                user = new User();

                user.setFirstName(resultSet.getString("firstName"));
                user.setLastName(resultSet.getString("lastName"));
                user.setJoinDate(resultSet.getString("joinDate"));
                user.setCardId(resultSet.getString("cardId"));
                user.seteMail(resultSet.getString("email"));
                user.setPassword(resultSet.getString("password"));
                user.setPhoneNumber(resultSet.getString("phoneNumber"));
                user.setAccountType(resultSet.getString("accountType"));
                user.setLocation(resultSet.getString("location"));

            }

            resultSet.close();
            ps.close();

        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }

        return user;

    }

    @Override
    public List<User> findAll() {

        List<User> users = new LinkedList<User>();

        try {
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(FIND_ALL);

            User user = null;

            while (resultSet.next()) {
                user = new User();

                user.setId(resultSet.getInt("id"));
                user.setFirstName(resultSet.getString("firstName"));
                user.setLastName(resultSet.getString("lastName"));
                user.setJoinDate(resultSet.getString("joinDate"));
                user.setCardId(resultSet.getString("cardId"));
                user.seteMail(resultSet.getString("email"));
                user.setPassword(resultSet.getString("password"));
                user.setPhoneNumber(resultSet.getString("phoneNumber"));
                user.setAccountType(resultSet.getString("accountType"));
                user.setLocation(resultSet.getString("location"));

                System.out.println(user.toString());

                users.add(user);
            }

            resultSet.close();
            statement.close();


        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }

        return users;

    }

    public Connection getConnection() {

        String dbURL = "jdbc:mysql://localhost:3306/db";
        String dbUser = "root";
        String dbPassword = "";

        try {
            if (conn == null) {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(dbURL, dbUser, dbPassword);
            }
        } catch (Exception e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }

        return conn;

    }

    public void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                //e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }
}

I have also tried to use only update(User user) and getting id through User object though the outcome was the same.

Here is my User model:

public class User {
    
    private int id;
    private String firstName;
    private String lastName; 
    private String joinDate;
    private String cardId;
    private String eMail;
    private String password;
    private String phoneNumber;
    private String accountType;
    private String location;

    /**
     * @return the id
     */
    public int getId() {
        return id;
    }

    /**
     * @param id the id to set
     */
    public void setId(int id) {
        this.id = id;
    }

    /**
     * @return the firstName
     */
    public String getFirstName() {
        return firstName;
    }

    /**
     * @param firstName the firstName to set
     */
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    /**
     * @return the lastName
     */
    public String getLastName() {
        return lastName;
    }

    /**
     * @param lastName the lastName to set
     */
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    /**
     * @return the joinDate
     */
    public String getJoinDate() {
        return joinDate;
    }

    /**
     * @param joinDate the joinDate to set
     */
    public void setJoinDate(String joinDate) {
        this.joinDate = joinDate;
    }

    /**
     * @return the cardId
     */
    public String getCardId() {
        return cardId;
    }

    /**
     * @param cardId the cardId to set
     */
    public void setCardId(String cardId) {
        this.cardId = cardId;
    }

    /**
     * @return the eMail
     */
    public String geteMail() {
        return eMail;
    }

    /**
     * @param eMail the eMail to set
     */
    public void seteMail(String eMail) {
        this.eMail = eMail;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * @return the phoneNumber
     */
    public String getPhoneNumber() {
        return phoneNumber;
    }

    /**
     * @param phoneNumber the phoneNumber to set
     */
    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    /**
     * @return the accountType
     */
    public String getAccountType() {
        return accountType;
    }

    /**
     * @param accountType the accountType to set
     */
    public void setAccountType(String accountType) {
        this.accountType = accountType;
    }

    /**
     * @return the location
     */
    public String getLocation() {
        return location;
    }

    /**
     * @param location the location to set
     */
    public void setLocation(String location) {
        this.location = location;
    }

    @Override
    public String toString() {
        return "accountType " + accountType + " " + "cardId " + cardId + " " + "eMail " + eMail + " " + 
                "firstName " + firstName + " " + "id " + id + " " + "joinDate " + joinDate + " " + "lastName " + lastName + " " + 
                "location " + location + " " + "password " + password + " " + "phoneNumber " + phoneNumber;
    }   
}

And here is my main class:

public static void main(String[] args) {
        
        // Create instance of User DAO
        UserDAOImpl userDAO = new UserDAOImpl();
        
        // Create instance of User object
        User user = new User();
                
        // Set user details before creating them
        user.setFirstName("Test");
        user.setLastName("LastName");
        user.setJoinDate("03-03-2015");
        user.setCardId("S1452512");
        user.seteMail("[e-mail]");
        user.setPassword("password1");
        user.setPhoneNumber("07714862101");
        user.setAccountType("Student");
        user.setLocation("GCU");
        
        // Get MySQL Connection
        userDAO.getConnection();
        
        // Save user in DB
        userDAO.create(user); 
        
        // Authenticate User
        userDAO.authenticate("[e-mail]", "password1");
        
        // Display all Users         
        userDAO.findAll();
        
        // Get details of user with ID = 1
        user = userDAO.getDetailsById(1);
        
        // Display users firstName and lastName
        System.out.println("Firstname: " + user.getFirstName() + " Lastname: " + user.getLastName());
        
        // Change user password from password 1 to password 2
        user.setPassword("password2");

        // Execute update
        userDAO.update(user);
        
        // Get details of user with ID = 1
        user = userDAO.getDetailsById(1);
        
        // Display users password
        System.out.println("Password: " + user.getPassword());
        
        // Delete user with ID = 1
        userDAO.delete(1);
        
        // Truncate users table so that id ai resets to 1
        userDAO.truncateTable();
        
        // Close Connection
        userDAO.closeConnection();
    }

My expected output is:
User with following details was saved in DB: accountType Student cardId S1452512 eMail [email protected] firstName Test id 0 joinDate 03-03-2015 lastName LastName location GCU password password1 phoneNumber 07714862101
User logged in.
accountType Student cardId S1452512 eMail [email protected] firstName Test id 1 joinDate 03-03-2015 lastName LastName location GCU password password1 phoneNumber 07714862101
Firstname: Test Lastname: LastName
User with id 1 was updated in DB with following details: accountType Student cardId S1452512 eMail [email protected] firstName Test id 0 joinDate 03-03-2015 lastName LastName location GCU password password2 phoneNumber 07714862101
Password: password2
User with id: 1 was sucesfully deleted from DB.
Users database was truncated.

Given output is:
User with following details was saved in DB: accountType Student cardId S1452512 eMail [email protected] firstName Test id 0 joinDate 03-03-2015 lastName LastName location GCU password password1 phoneNumber 07714862101
User logged in.
accountType Student cardId S1452512 eMail [email protected] firstName Test id 1 joinDate 03-03-2015 lastName LastName location GCU password password1 phoneNumber 07714862101
Firstname: Test Lastname: LastName
User with id 0 was updated in DB with following details: accountType Student cardId S1452512 eMail [email protected] firstName Test id 0 joinDate 03-03-2015 lastName LastName location GCU password password2 phoneNumber 07714862101
Password: password1
User with id: 1 was sucesfully deleted from DB.
Users database was truncated.

I have highlighted the area of problem in output by using bold font.
I am helpless on this one. I know its a long one but I really need help on it or at least some guidance where to go from here.

Thanks in advance,
Mac

[Dave Tolls]

Inside your update method, check how many rows were updated.
This is the value returned by the executeUpdate method.

Failing that, comment out everything in main() after the call to update and then check the database.

[Maciej Olborski]

Okay, I have done what you mentioned. The output is 0 rows affected.

I understand that something is wrong in implementation and I have again looked through UPDATE SQL statement, the interface and the implementation of DAO and it all looks okey.

Any suggestions?

New DAOImpl:

private static final String UPDATE = "UPDATE users SET firstName=?, lastName=?, joinDate=?, cardId=?, email=?, password=?, phoneNumber=?, accountType=?, location=? WHERE id=?";

@Override
    public void update(User user) {
        
        try {

            PreparedStatement ps = conn.prepareStatement(UPDATE);

            ps.setString(1, user.getFirstName());
            ps.setString(2, user.getLastName());
            ps.setString(3, user.getJoinDate());
            ps.setString(4, user.getCardId());
            ps.setString(5, user.geteMail());
            ps.setString(6, user.getPassword());
            ps.setString(7, user.getPhoneNumber());
            ps.setString(8, user.getAccountType());
            ps.setString(9, user.getLocation());
            ps.setInt(10, user.getId());
            
            int rowsAffected = ps.executeUpdate();
            ps.close();

            System.out.println(rowsAffected + " Rows affected.");
            System.out.println("User with id " + user.getId() + " was updated in DB with following details: " + user.toString());

        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException(e);
        }
        
    }

I am assuming this is to do with the id in the output being 0 but not quite sure why that is a 0.

Mac

[Dave Tolls]

Maciej Olborski wrote:I am assuming this is to do with the id in the output being 0 but not quite sure why that is a 0.

Mac

Ah!
Code blindness on my part!

You're not setting that id when you read the user out of the database in the findById method, so it is still the default 0.

[Maciej Olborski]

Ahh, I've just noticed that too! Thank you a lot.

Do you think that my implementation of DAO seems okey for a desktop kind of application?

Maciej.

[Dave Tolls]

So long as you're not planning on having multiple threads accessing the DAO at once it looks OK.
The only thing is the closing of resources on SQLExceptions.
You should either use the try-with-resources structure:

try (PreparedStatement ps = conn.prepareStatement(UPDATE)) {
// do stuff
} catch etc

or close things like ResultSets and Statements in a finally block.

[Maciej Olborski]

Thank, you, big up for you!

Mac

[Roel De Nijs]

Is there any reason why you opt for implementing DAO completely yourself and not using JPA with an ORM like Hibernate. And you could even throw Spring Data JPA into the mix.

[Maciej Olborski]

Roel De Nijs wrote:Is there any reason why you opt for implementing DAO completely yourself and not using JPA with an ORM like Hibernate. And you could even throw Spring Data JPA into the mix.

Hi,

I am implementing DAO completely by myself because its an assigment and we've got to show off our code.

Mac

[Roel De Nijs]

Maciej Olborski wrote:I am implementing DAO completely by myself because its an assigment and we've got to show off our code.

Fair enough!

Let me give you a few other tips/hints/suggestions:

you should definitely use automatic resource management (try-with-resources), because your current code could have resource leaks. Only possible if you are using Java 7 or later (mentioned by Dave Tolls as well)

use of prepared statements is really great

the conn instance variable must be private (encapsulation)

in authenticate: don't use Boolean, use boolean instead

getConnection and closeConnection should definitely not be part of the public API of UserDAO

If you really want to show off a really good class design, then getConnection and closeConnection should not even be part of the UserDAOImpl class. Why not? Because each class (and even each method) should have 1 specific purpose, the more specific this purpose, the better (in OO terms, that's called "high cohesion"). So your UserDAO should be responsible for the user management (reading, creating, updating, deleting,...) but not for connection management (creating, opening, closing,...). That's the responsibility of a connection factory class (or even better a connection pool).

I could share all the required code, but it's your assignment so you have to do the coding I'll just give some pointers:

1/ create a connection factory class

make it a singleton

in (private) constructor (it's a singleton) you already load the (mysql) driver

create a (private) createConnection method which gets a connection using url, user and password

create a (public) static getConnection method which invokes the createConnection method (on the single instance of this class)

One of the major benefits of introducing this class: you can reuse this class for other DAOs as well (code reuse).

2/ rework your UserDAOImpl class
If you have finished the 1st step, you have to redo every method of this class using this connection factory class and in the end you'll be able to remove the conn instance variable and all connection-related methods from this class. Yay! If you combine with using the Java 7 try-with-resources you'll get very clean code. Your code will end up looking like this:

try (Connection connection = ConnectionFactory.getConnection();
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(aQuery)) {
    // do dao-related stuff
} catch (SQLException e) {
    throw new RuntimeException(e);
}

It will be slightly more difficult using a PreparedStatement, because you have to set its parameters (which can't be done in the try-with-resources block). But you could solve this using a private method. For example:

private PreparedStatement createFindById(Connection connection, int id) throws SQLException {
    PreparedStatement ps = connection.prepareStatement(FIND_BY_ID);
    ps.setInt(1, id); // Set 1st WHERE to int
    return ps;
}

And then you can call this method from the try-with-resources block.

3/ rework your main method
After you have finished the 2nd step, you'll have to fix a few compiler errors in this code And one other important OO rule: you should always program to an interface (or an abstract class). So you should write UserDAO userDAO = new UserDAOImpl(); instead.

If you would encounter any problems while implementing any of these improvements, just let us now and we are happy to help you!

Hope it helps!
Kind regards,
Roel

[Sravan Ramadalai]

which java file we should write the main class?