Connecting web application form with simple mysql command

[Farshan Airy Airan]

Hello guys,

Anyone here know how to insert and update record using simple prepared statement command (i'm using mysql). To insert record in jsp webform. I know there's other way, but it looks bit complicated to me (servlet,javabean,etc). The example code something similar down here :

<%
Class.forName("com.mysql.jdbc.Driver");

Connection Conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/test","root","bbb213");
Statement Stmt = Conn.createStatement();

ResultSet resultset= Stmt.executeQuery
("SELECT * FROM student" WHERE StudID="?);

%>

<BR><table width="500" border="1" align="center" bgcolor="#99CCCC">
<tr bgcolor="#FFFFCC">
<td width="165"><strong>Student Info</strong></td>
<td width="8"> </td>
<td width="310"> </td>
</tr>
<tr>
<td>Student Name:</td>
<td>:</td>
</tr>
<tr>
<td>Student ID:</td>
<td>:</td>
</tr>


Pls, i need a simple jsp that can insert and update record properly.Thanks in advance.

[Richard Green]

JSP is a view technology - you should not put any database / servlet code in it. This is how I would do it.

1. Create a new Servlet.

public void doPost(HttpServletRequest req,HttpServletResponse res) {
     String name = request.getParameter("student_name");
     int id = (int) request.getParameter("student_id");
     if (session.getAttribute("database_connection")==null){
                       // create connection and put it in the session
     }
     Connection conn = (Connection)session.getAttribute("database_connection");
    
    // check whether we need to do a insert or update
    String sql = "SELECT count(1) as student_count from student where studentid = "?;
    // based on the student_count, do a select or insert.
   // ..................
   // ...........
   response.sendRedirect("/success.jsp");
}

2. In your web.xml map the above servlet to the url "/processStudent" or something like that.

3. This is how your jsp should look like

 <form method="post" action=[B]"processStudent"[/B]>
<table class="tbl">
<tr class="trodd">
<td>
<label for="student_name">Student Name</label>
</td>
<td>
<input name="student_name" value="">
</td>
</tr>
<tr class="treven">
<td>
<label for="student_id">Student ID</label>
</td>
<td>
<input name="student_id" value="">
</td>
</tr>
            </table>
 </form>

[Adeel Ansari]

Database access code should not be in your servlet either. Fabricate a seperate set of classes to handle db related stuff. you can google for DAO pattern.

For prototyping stuff JSTL SQL tags is your best bet.