CODEWITHSUNDEEP
javascriptjavahtmlmysqljdbc
NewJavaEnthusiast
NewJavaEnthusiast

Reputation: 103

How to insert dynamically created field values to MySQL db using Java/JDBC?

Hey guys I have two tables in my db.

User_DB

user table

and

Phone_Numbers_DB

phone numbers table

I have a form with user's fields to submit to database. However, using jQuery you can add multiple phone numbers for a user. My question is: How can I submit these multiple fields to the database table, when adding new user?

function AddPhoneNumbers() {
    let counterPhoneNums = $(".phoneNumInnerDivClass").length;
    if (counterPhoneNums <= 30) {
        ++counterPhoneNums;
        let phonesToAppend = 
        '<div id="phoneNumInnerDiv' + counterPhoneNums + '" class="phoneNumInnerDivClass">' +
        '<input type="text" name="phoneNum' + counterPhoneNums + '"class="phoneNumInputFields" placeholder="Phone Number">' +
        '</div>';
        $(phonesToAppend).appendTo("#phoneNumOutterDiv");
    } else {
        alert("LIMIT!");
    }
}

function RemovePhoneNumbers() {
    let counterPhoneNums = $(".phoneNumInnerDivClass").length;
    if (counterPhoneNums > 0) {
        $("#phoneNumInnerDiv" + counterPhoneNums).remove();
        --counterPhoneNums;
    } else {
        alert("NO MORE TO REMOVE!");
    }
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet"/>
<html>
<head>
    <title>Add new user</title>
    
</head>
<body>
<div class="container" style="margin-bottom: 50px;">
    <h2>Add new user</h2>

    <form class="text-center" action="/admin/user/add" method="post">
        <div class="form-group">
            <label for="username">Username</label>
            <input type="text" class="form-control" name="username" id="username">
        </div>
        <div class="form-group">
            <label for="email">E-mail</label>
            <input type="text" class="form-control" name="email" id="email">
        </div>
        <div class="form-group">
            <label for="password">Password</label>
            <input type="password" class="form-control" name="password" id="password">
        </div>
        <div class="form-group">
            <div id="phoneNumOutterDiv">
                <label for="phoneNumber">User's Phone Number/s</label>
                <div id="phoneNumInnerDiv">
                    <input type="text" name="phoneNum" placeholder="Phone Number" />        
                </div>
            </div>
            <br/>
            <div class="btnsDiv" style="margin-top: 15px;">
                 <button type="button" class="btn btn-sm btn-success" onclick="AddPhoneNumbers()">
                     Add
                 </button>
                 <button type="button" class="btn btn-sm btn-danger" onclick="RemovePhoneNumbers()">
                     Remove
                 </button>
            </div>          
        </div>
        <hr/><hr/>
        <button class="btn btn-lg btn-primary rounded-0" type="submit">Add user</button>
    </form>
</div>

</body>
</html>

And this is my java code:

User POJO

public class User {
    private int id;
    private String username;
    private String email;
    private String password;
    
    public User() {
    }

    public User(String username, String email, String password) {
        this.username = username;
        this.email = email;
        this.password = password;
    }

    /* Getters / Setters */

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +
                "} \n";
    }
}

UserDAO

public class UserDao {
    private static final String CREATE_USER_QUERY = "INSERT INTO demo_db.users (email, username, password) VALUES (?, ?, ?)";
    
    public User create(User user) {
        try (Connection connection = DbUtil.getConnection()) {
            PreparedStatement statement = connection.prepareStatement(CREATE_USER_QUERY, Statement.RETURN_GENERATED_KEYS);

            statement.setString(1, user.getEmail());
            statement.setString(2, user.getUsername());
            statement.setString(3, user.getPassword());

            statement.executeUpdate();

            try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                if (generatedKeys.first()) {
                    int generatedKey = generatedKeys.getInt(1);
                    user.setId(generatedKey);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }   
}

User Servlet

@WebServlet("/admin/user/add")
public class AddUser extends HttpServlet {
    
    private static final long serialVersionUID = 1L;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        UserDao userDao = new UserDao();

        String username = request.getParameter("username");
        String email = request.getParameter("email");
        String password = request.getParameter("password");

        User user = new User(username, email, password, groupName, userPhoneNumber);
        userDao.create(user);

        response.sendRedirect("/admin/panel");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
        throws ServletException, IOException {        
        getServletContext().getRequestDispatcher("/web/user/addUser.jsp").forward(request, response);
    }
}

Upvotes: 2

Views: 1057

Answers (2)

jccampanero
jccampanero

Reputation: 53381

There are several things you can do to improve your solution.

I will follow the order in which you presented your information.

First, the function used to add a new phone number in javascript:

// Instead of using the number of actual phone numbers, I would use a simple counter.
var nextPhoneNumberId = 0;
function AddPhoneNumbers() {
    let counterPhoneNums = $(".phoneNumInnerDivClass").length;
    if (counterPhoneNums <= 30) {
        let phonesToAppend = 
        '<div id="phoneNumInnerDiv' + nextPhoneNumberId++ + '" class="phoneNumInnerDivClass">' +
        '<input type="text" name="phoneNum" class="phoneNumInputFields" placeholder="Phone Number">' +
        // Please, consider include some icon, etcetera
        '<a href="javascript:void(0);" class="phone-number-remove">Remove</a>' +
        '</div>';
        $(phonesToAppend).appendTo("#phoneNumOutterDiv");
    } else {
        alert("LIMIT!");
    }
}

Please, observe that I included an anchor in order to remove that specific phone number. Also, all the phone numbers will have the same name: it will allow you to use request.getParameterValues("phoneNum") later in the servlet.

With this anchor in place, instead of the function RemovePhoneNumbers, register the following click live handler:

$(document).ready(function(){
  //...
  $(document).on('click', '.phone-number-remove', function(){
    $(this).closest('.phoneNumInnerDivClass').remove();
  });
}

Now, the Java code. First, perform the necessary changes in the User class:

public class User {
    private int id;
    private String username;
    private String email;
    private String password;
    private List<String> phoneNumbers = new ArrayList<>();
    
    public User() {
    }

    public User(String username, String email, String password, List<String> phoneNumbers) {
        this.username = username;
        this.email = email;
        this.password = password;
        this.phoneNumbers = phoneNumbers;
    }

    /* Getters / Setters */

    public void addPhoneNumber(String phoneNum) {
      if (this.phoneNumbers == null) {
        this.phoneNumbers = new ArrayList<>();
      }

      this.phoneNumbers.add(phoneNum);
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +
                ", phoneNumbers=" + phoneNumbers +
                "} \n";
    }
}

Please, allow me now to define first the servlet:

@WebServlet("/admin/user/add")
public class AddUser extends HttpServlet {
    
    private static final long serialVersionUID = 1L;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        UserDao userDao = new UserDao();

        String username = request.getParameter("username");
        String email = request.getParameter("email");
        String password = request.getParameter("password");
        // Process phone numbers
        String[] phoneNumberArray = request.getParameterValues("phoneNum");
        List<String> phoneNumbers = new ArrayList<>();
        if (phoneNumberArray != null) {
          phoneNumbers = Arrays.asList(phoneNumberArray);
        }

        User user = new User(username, email, password, phoneNumbers);
        userDao.create(user);

        response.sendRedirect("/admin/panel");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
        throws ServletException, IOException {        
        getServletContext().getRequestDispatcher("/web/user/addUser.jsp").forward(request, response);
    }
}

And finally your UserDao. You can create a separate class to handle phone number insertion if you wish, but I think that in your use case it is not necessary. Please, see:

public class UserDao {
    private static final String CREATE_USER_QUERY = "INSERT INTO demo_db.users (email, username, password) VALUES (?, ?, ?)";
    private static final String INSERT_USER_PHONE_NUMBERS_QUERY = "INSERT INTO demo_db.user_phone_number (phone_number, user_id) VALUES (?, ?)";
    
    public User create(User user) {
        try (Connection connection = DbUtil.getConnection()) {
            PreparedStatement statement = connection.prepareStatement(CREATE_USER_QUERY, Statement.RETURN_GENERATED_KEYS);

            statement.setString(1, user.getEmail());
            statement.setString(2, user.getUsername());
            statement.setString(3, user.getPassword());

            statement.executeUpdate();

            try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                if (generatedKeys.first()) {
                    int generatedKey = generatedKeys.getInt(1);
                    user.setId(generatedKey);
                }
            }

            // Handle user phone numbers
            // We are doing this in the same method, fell free to extract this logic to a different one if necessary
            // We are using batch insert for performance purposes
            List<String> phoneNumbers = user.getPhoneNumbers();
            PreparedStatement insertPhoneNumberStatement = null;
            if (phoneNumbers != null && !phoneNumbers.isEmpty()) {
              try {
                insertPhoneNumberStatement = connection.prepareStatement(INSERT_USER_PHONE_NUMBERS_QUERY);

                for (String phoneNumber : phoneNumbers) {
                    insertPhoneNumberStatement.setString(1, phoneNumber);
                    insertPhoneNumberStatement.setInt(2, user.getId());
                    insertPhoneNumberStatement.addBatch();
                }
                // Execute batch
                insertPhoneNumberStatement.executeBatch();
              } catch catch (SQLException e) {
                  e.printStackTrace();
              } finally {
                if (insertPhoneNumberStatement != null) {
                  try {
                    insertPhoneNumberStatement.close();
                  }catch catch (SQLException sqle) {
                    sqle.printStackTrace();
                  }
                }
              }

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

Upvotes: 5

seqwait
seqwait

Reputation: 199

just create PhoneDao like below


public class UserPhone{
  public long id;  //userId
  public String phoneNumber;
  public xxxx
}

public class PhoneDao{
   int addBatchPhoneNumber(List<UserPhone> phones){
        //your insert code
   }
}

Upvotes: 1

Related Questions