inserting java array values into mysql by using prepared statement

Question

Hi friends in my code I am receciving the values by using request.getParameterValues() from JSP page in the form of array and im passing it to getter setter from there im passing to DAO but i dont know how to insert the values of each arrary element into the database by using prepared statement . my doubt is how to insert remarks,quantity,startTime, endTime for each iteration my code is

  Servlet Code : 

  String[] remarks = request.getParameterValues("txtRemarks");
  String[] quantity = request.getParameterValues("txtQty");
  String[] startHrs = request.getParameterValues("txtStartTime");
  String[] endHrs = request.getParameterValues("txtEndtime");

  getter setter :

  public String[] getremarks() {  
  return getremarks;  
  }  
  public void setremarks(String[] newremarks) {  
  remarks = newremarks;  
  } 

 UserDAO :


         String query = "insert into table(remarks,quantity,startTime,endTime) values (?,?,?,?) "
         currentCon = ConnectionManager.getConnection();
         ps = currentCon.prepareStatement(query);
         rs = ps.executeQuery();

Try going through documentation - docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — Nayan Wadekar
– Nayan Wadekar, Commented May 13, 2014 at 11:16
@NayanWadekar could you please suggest me some working examples please — user3626977
– user3626977, Commented May 13, 2014 at 11:25
It's already there, have you gone through that & tried something; if encountering issues, update post. — Nayan Wadekar
– Nayan Wadekar, Commented May 14, 2014 at 6:06
@NayanWadekar sorry i'm new to java so i dono how to do map entry — user3626977
– user3626977, Commented May 14, 2014 at 8:52

tmarwen · Accepted Answer · 2014-05-15 08:38:51Z

0

Although I think you should update your application design, more specifically the way you are getting the data from servlet. There should be a bean with name e.g. MyBean to hold the data for each entity you will store into database not having separated arrays of attributes:

class MyBean {
  String remark;
  String quantity;
  String startTime; //You said it is a String but maybe should be a timestamp
  String endTime;

  //Getters & Setters
}

So back to your code now, and you should note that this is a wild guess since I can really figure out how you want the data to go under your tables: * Do you want to store an array of remarks in a single column? * Or those remarks stand each for a separate record?

Supposing that you are going for the second approach, your data access layer service can be as below:

public void insertMyEntity(String remark,
  String quantity,
  String startTime,
  String endTime) throws SQLException {

  Connection con = ConnectionManager.getConnection();
  PreparedStatement insertStatement = null;
  String insertString =
    "insert into YOUR_TABLE(remarks,quantity,startTime,endTime) values (?,?,?,?)";

  try {
    insertStatement = con.prepareStatement(insertString);
    insertStatement.setString(1, remark);
    insertStatement.setString(1, quantity);
    insertStatement.setString(1, startTime);// use setTimestamp() if it is a timestamp
    insertStatement.setString(1, endTime);
    insertStatement.executeUpdate();
  } catch (SQLException e ) {
      System.out.println(e.getMessage());
  } finally {
    if (insertStatement != null) {
      insertStatement.close();
    }
    if (con != null) {         
      con.close();
    }
  }
}

Then when you have retrieved your data from client request in your controller (Servlet), you can use a loop to iterate over them and insert to database:

public class MySservlet extends HttpServlet
{
  protected void doGet(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException
  {
    ...
    String[] remarks = request.getParameterValues("txtRemarks");
    String[] quantity = request.getParameterValues("txtQty");
    String[] startHrs = request.getParameterValues("txtStartTime");
    String[] endHrs = request.getParameterValues("txtEndtime");

    for (int i = 0; i < remarks.length; i++)
    {
      insertMyEntity(remarks[i], quantity[i], startHrs[i], endHrs[i]);
    }
    response.sendRedirect("resourceuri");//replace the resource with servlet, jsp or html URL
  }
}

edited May 15, 2014 at 8:38

answered May 14, 2014 at 9:15

tmarwen

16.5k5 gold badges48 silver badges65 bronze badges

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

6 Comments

user3626977 Over a year ago

thank you but i want to redirect the page once it is inserted how that is possible within for loop for each iteration

tmarwen Over a year ago

How does it come to redirect after each iteration? If you add the suggested line by @Selva, only the first statement will be inserted then the client will be redirected.

user3626977 Over a year ago

no i need to redirect the page once the entire iteration has been completed

tmarwen Over a year ago

So you have to move the redirect line out of the iteration, I will uppdate my answer and you can mark it as accepted and even upvote so it is eligible for future visitors ;)

Karen Goh Over a year ago

Hi tmarwen, can I know if I can use getPost in this case for MySservlet ?

|

Selva · Accepted Answer · 2014-05-22 06:14:31Z

0

tmarwen code will works fine, you just replace the try like below

try {
    insertStatement = con.prepareStatement(insertString);
    insertStatement.setString(1, remark);
    insertStatement.setString(1, quantity);
    insertStatement.setString(1, startTime);// use setTimestamp() if it is a timestamp
    insertStatement.setString(1, endTime);
    insertStatement.executeUpdate();
  } catch (SQLException e ) {
      System.out.println(e.getMessage());
  } finally {
    if (insertStatement != null) {
      insertStatement.close();
    } if (con != null) {         
      con.close();
    }
  }
response.sendRedirect("servlet/jsp");//Where ever you require to redirect

edited May 22, 2014 at 6:14

answered May 14, 2014 at 9:46

Selva

5565 gold badges13 silver badges35 bronze badges

4 Comments

user3626977 Over a year ago

can i have your mail id please and myself also from coimbatore

Selva Over a year ago

@user3626977 First don't ask the code like this effort to google it and sample for searching my link will facilitate you and if there any issues search relative answer to that if not post the question.

Selva Over a year ago

@user3626977 I don't mean to hurt you, you have mistaken, if search goes answer comes this is my thought..

Selva Over a year ago

@user3626977 How did you search for this solution? what are the keywords you have used to find the solution, my SearchLink is here...

Collectives™ on Stack Overflow

inserting java array values into mysql by using prepared statement

2 Answers 2

6 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related