hasini silva hasini silva - 1 month ago 12
Java Question

Spring mvc- dropdown box options from database

This is what I implemented for create dropdown box using spring mvc.
This is working. But I need to get all the children belongs to

motherID="M-1"
in database records. This gives me only the first child which belongs to M-1.

Where I have to fix? I'm a beginner for spring mvc.

my_children.jsp



<form method="post" >
<div class="div_box">
<select id="child_name" name="Child Name" >
<option value="top">Select your child</option>
<option value="">${firstName} ${lastName}</option>

</select>
<br>
<div align ="justify">
<button type="button" class="btn btn-success active">View Details</button>
</div>
</div>
</form>


controller



@RequestMapping(value="/my_children", method = RequestMethod.GET)
public void viewMyChild(ModelMap modelMap) {
ChildNameAccess childNameDAO = new ChildNameAccess();
try{
Child child = childNameDAO.getChildDataByMotherId("M-1");


modelMap.addAttribute("firstName",child.getFirstName());
modelMap.addAttribute("lastName",child.getLastName());
}

catch(SQLException e) {
e.printStackTrace();
}
}


dataAccess class



package com.emidwife.web.models.dataAccess;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.emidwife.web.models.entities.Child;
import com.emidwife.web.models.utilities.Database;


public class ChildNameAccess {
private Database connection = new Database();
Child child = new Child();

public Child getChildDataByMotherId(String motherID) throws SQLException {
connection.openConnection();
try{
ResultSet resultSet = connection.getData("SELECT * FROM childdetails WHERE MotherID=\'" + motherID + "\'");
resultSet.next();
//child.setChildId("1");
child.setMotherId(resultSet.getString("MotherID"));
child.setFirstName(resultSet.getString("FirstName"));
child.setLastName(resultSet.getString("LastName"));
//child.setDateOfBirth(resultSet.getDate("DOB"));

} catch (SQLException e) {
e.printStackTrace();
}
finally{
connection.closeConnection();
}

return child;
}


}

Answer

The main problem is not with spring mvc. You must do correct select statement from database. If you want to get all children by 'M-1' you can make following changes in your dataAccess class.

package com.emidwife.web.models.dataAccess;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

import com.emidwife.web.models.entities.Child;
import com.emidwife.web.models.utilities.Database;


public class ChildNameAccess {
    private Database connection = new Database();

    public List<Child> getChildDataByMotherId(String motherID) throws SQLException {
        connection.openConnection();
        List<Child> children = new ArrayList<Child>();
        try{
            ResultSet resultSet = connection.getData("SELECT * FROM childdetails WHERE MotherID=\'" + motherID + "\'");
            while(resultSet.next()){
            Child child = new Child();
            child.setMotherId(resultSet.getString("MotherID"));
            child.setFirstName(resultSet.getString("FirstName"));
            child.setLastName(resultSet.getString("LastName"));
            children.add(child);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            connection.closeConnection();
        }

        return children;
    }
}

And also you must change your controller like following.

@RequestMapping(value="/my_children", method = RequestMethod.GET)
public void viewMyChild(ModelMap modelMap) {
    ChildNameAccess childNameDAO = new ChildNameAccess();
    try{
        List<Child> children = childNameDAO.getChildDataByMotherId("M-1");

        modelMap.addAttribute("children ",children);
    }

    catch(SQLException e) {
        e.printStackTrace();
    }
} 

And jsp:

<form method="post" >
            <div class="div_box">
                <select id="child_name" name="Child Name" >
                    <option value="top">Select your child</option>
                    <c:forEach items="${children}" var="child">
                    <option value="">${child.firstName} ${child.lastName}</option>
                    </c:forEach>
                </select>
                    <br>
            <div align ="justify">
            <button type="button" class="btn btn-success active">View Details</button>
           </div>
            </div>
    </form>