46
Hello,
I am trying to establish a connection between servlet and MySQL database. For this I have compiled my servlet which has table for inputting values into a database table in MySQL.
Here goes the code:
Expand|Select|Wrap|Line Numbers
- //Use this table for this program
- /*
- create table course(id int,
- name varchar(20),
- ctype varchar(20),
- duration varchar(20),
- semester int);
- */
- import java.io.*;
- import javax.servlet.*;
- import javax.servlet.http.*;
- import java.sql.*;
- import java.util.*;
- public class Course1 extends HttpServlet
- {
- Connection conn=null;
- Statement stmt = null;
- ResultSet rs;
- //---------------------------Register Driver-------------------
- public void init()
- {
- try
- {
- String un="root";
- String pass="admin";
- String url="jdbc:mysql://localhost:3036/BOOKS_DB";
- Class.forName("com.mysql.jdbc.Driver").newInstance();
- conn =DriverManager.getConnection(url,un,pass);
- System.out.println("Database Connection Established");
- }
- catch(Exception e)
- {
- System.out.println("Sorry failed to connect to the DataBase." +e.getMessage());
- }
- }
- public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException
- {
- HttpSession session=request.getSession();
- response.setContentType("text/html");
- PrintWriter out=response.getWriter();
- int y=0;
- int x=0;
- out.println("<html><head><title>Welcome to Institution</title>");
- //----------------java script---------------------------------
- out.println("<script>");
- out.println("function abc() {");
- out.println("if (document.thisform.name.value==0) {");
- out.println("alert('Invalid Name');");
- out.println("return false; }");
- out.println("if (document.thisform.ctype.value==0) {");
- out.println("alert('Invalid Type');");
- out.println("return false; }");
- out.println("if (document.thisform.duration.value==0) {");
- out.println("alert('Invalid Duration');");
- out.println("return false; }");
- out.println("if (document.thisform.semester.value==0) {");
- out.println("alert('Invalid Semester');");
- out.println("return false; }");
- out.println("document.thisform.submit();");
- out.println("return false; }");
- out.println("function setEditMode(id,cname,typ,durt,seme)");
- out.println("{");
- out.println("document.thisform.hiddenid.value=id;");
- out.println("document.thisform.name.value=cname;");
- out.println("document.thisform.ctype.value=typ;");
- out.println("document.thisform.duration.value=durt;");
- out.println("document.thisform.semester.value=seme;");
- out.println("document.thisform.hidMode.value='U';");
- out.println("}");
- out.println("function setDelMode()");
- out.println("{");
- out.println("document.thisform.hidMode.value='D';");
- out.println("formDeleteValues('hidSelDel');");
- out.println("}");
- out.println("function formDeleteValues(hidden)");
- out.println("{");
- out.println("var selval=\" \" ");
- out.println("for(i=0;i<document.forms[0].elements.length;i++)");
- out.println("{");
- out.println("if(document.forms[0].elements[i].type == \"checkbox\")");
- out.println("{");
- out.println("if(document.forms[0].elements[i].checked == true) {");
- out.println("selval=selval + document.forms[0].elements[i].value + \",\" ;");
- out.println("}");
- out.println("}");
- out.println("}");
- out.println("if(selval.length < 1)");
- out.println("{");
- out.println("alert(\"Please choose records you wish to delete.\");");
- out.println("}");
- out.println("else");
- out.println("{");
- out.println("selval=selval.substring(0,selval.length-1);");
- out.println("eval(\"document.forms[0].\" +hidden+\".value = '\"+selval+\"'\");");
- out.println("document.forms[0].submit(); } }");
- out.println("</script></head>");
- //-------------------------body----------------------------------
- out.println("<body bgcolor='rgb(255,210,117)'>");
- int sem=0,id=0;
- //--------------------Insert---------------------------------------
- if ("I".equals(request.getParameter("hidMode")) && conn != null)
- {
- String nm=request.getParameter("name");
- String ty=request.getParameter("ctype");
- String dur=request.getParameter("duration");
- String sem1=request.getParameter("semester");
- sem=Integer.parseInt(sem1);
- try
- {
- if(nm.length() > 1 && ty.length() > 1)
- {
- stmt = conn.createStatement();
- String sql=new String();
- sql="select max(id) from Course";
- rs=stmt.executeQuery(sql);
- boolean b = rs.next();
- if(b)
- {
- x = rs.getInt(1);
- }
- else
- {
- x = 0;
- }
- x=x+1;
- id=x;
- stmt=conn.createStatement();
- sql="Insert into Course values("+ id +",'"+ nm +"','"+ ty +"','"+ dur +"',"+ sem +")";
- System.out.println(sql);
- y=stmt.executeUpdate(sql);
- }
- else
- {
- out.println("Details cannot be left blank");
- }
- }
- catch(Exception e)
- {
- out.println("Sorry failed to insert values into the Database table." +e.getMessage());
- }
- }
- //----------------------------update--------------------------
- if("U".equals(request.getParameter("hidMode")) && conn != null)
- {
- String nm=request.getParameter("name");
- String ty=request.getParameter("ctype");
- String dur=request.getParameter("duration");
- String sem1=request.getParameter("semester");
- sem=Integer.parseInt(sem1);
- try
- {
- stmt=conn.createStatement();
- String sql4=new String();
- sql4="update Course set name='"+ nm +"',ctype='"+ ty + "',duration='"+ dur +"',semester="+ sem +" where id="+ Integer.parseInt(request.getParameter("hiddenid")) +" ";
- stmt.executeUpdate(sql4);
- }
- catch(Exception e)
- {
- out.println("Sorry failed to update values from the database table." + e.getMessage());
- }
- }
- //---------------------------Delete-----------------------------
- if("D".equals(request.getParameter("hidMode")) && conn != null)
- {
- try
- {
- stmt=conn.createStatement();
- String sql5=new String();
- sql5="delete from Course where id IN("+ request.getParameter("hidSelDel") +") ";
- System.out.println(sql5);
- stmt.executeUpdate(sql5);
- }
- catch(Exception e)
- {
- out.println("Sorry failed to delete values from the database table." + e.getMessage());
- }
- }
- //----------------------------html-------------------------------
- out.println("<form name='thisform' method='get' action='../servlet/Course1'>");
- out.println("<input type='hidden' name='hidMode' value='I'>");
- out.println("<input type='hidden' name='hiddenid'>");
- out.println("<input type='hidden' name='hidSelDel'>");
- out.println("<table align='center' bgcolor='rgb(255,210,117)' width='50%'>");
- out.println("<tr>");
- out.println("<td>");
- out.println("</td></tr><br><br><br><tr height='200'>");
- out.println("<td align='center' colspan='10'>");
- out.println("<table align='center' bgcolor='rgb(255,210,117)' border='1' bordercolor='maroon' cellpadding='2' cellspacing='0' width='100%'>");
- out.println("<tr><td align='left' colspan='2' bgcolor='maroon'>");
- out.println("<font color='rgb(255,210,117)' ><b>Course Details</b></font></td></tr>");
- out.println("<tr><th align='right'>Course Name</th>");
- out.println("<td><input type='text' name='name' size='25'></td></tr>");
- out.println("<tr><th align='right'>Type</th>");
- out.println("<td><input type='text' name='ctype' size='25'></td></tr>");
- out.println("<tr><th align='right'>Duration</th>");
- out.println("<td><input type='text' name='duration' size='25'></td></tr>");
- out.println("<tr><th align='right'>Semesters</th>");
- out.println("<td><select name='semester'><option value='1'>Sem1<option value='2'>Sem2<option value='3'>Sem3<option value='4'>Sem4<option value='5'>Sem5<option value='6'>Sem6<option value='7'>Sem7<option value='8'>Sem8</select></td></tr>");
- out.println("<tr><td colspan='2' align='right'><input type='submit' value='Save' onClick='return abc();'>");
- out.println("<input type='reset' value='Reset'></td></tr></table>");
- out.println("</td></tr></table>");
- if(conn != null)
- {
- try
- {
- stmt=conn.createStatement();
- String sql2=new String();
- sql2="Select * from Course";
- ResultSet rs1=stmt.executeQuery(sql2);
- out.println("<table align='center' border='1' width='50%' bordercolor='skyblue'>");
- out.println("<tr bgcolor='black'>");
- out.println("<td width='12%' align='center' ><input type='button' name='cmdDelete' value='Delete' onClick='setDelMode();'></td>");
- out.println("<td><font color='#FFFFFF'>Course Name</font></td>");
- out.println("<td><font color='#FFFFFF'>Type</font></td>");
- out.println("<td><font color='#FFFFFF'>Duration</font></td>");
- out.println("<td><font color='#FFFFFF'>Semesters</font></td>");
- out.println("</tr>");
- int ii,s1;
- String n1,t1,d1;
- if(rs1 != null)
- {
- while(rs1.next())
- {
- out.println("<tr>");
- ii=Integer.parseInt(rs1.getString("id"));
- n1=rs1.getString("name");
- t1=rs1.getString("ctype");
- d1=rs1.getString("duration");
- s1=Integer.parseInt(rs1.getString("semester"));
- out.println("<td><input type='checkbox' name='chk"+ii+"' value=" + ii +"></td>");
- out.println("<td style=\"cursor:pointer\" onMouseDown=\"setEditMode('"+ ii +"','"+ n1 +"','"+ t1 +"','"+ d1 +"','"+ s1 +"');\">" + n1 + "</td>");
- out.println("<td style=\"cursor:pointer\" onMouseDown=\"setEditMode('"+ ii +"','"+ n1 +"','"+ t1 +"','"+ d1 +"','"+ s1 +"');\">"+ t1 + "</td>");
- out.println("<td style=\"cursor:pointer\" onMouseDown=\"setEditMode('"+ ii +"','"+ n1 +"','"+ t1 +"','"+ d1 +"','"+ s1 +"');\">"+ d1 + "</td>");
- out.println("<td style=\"cursor:pointer\" onMouseDown=\"setEditMode('"+ ii +"','"+ n1 +"','"+ t1 +"','"+ d1 +"','"+ s1 +"');\">"+ s1 + "</td>");
- out.println("</tr>");
- }
- }
- }
- catch(Exception e)
- {
- out.println("Sorry Failed to execute the Query." +e.getMessage());
- }
- }
- out.println("</form>");
- out.println("</body></html>");
- }
- }
I have the following Environment variables set:
Path: installdir\bin
classpath: C:\MySQL Connector\mysql-connector-java-5.0.4-bin.jar
As in MySQL, I have also created database and table perfectly.
But the problem is that entering values in servlet table and saving it applies no changes to the databases, and also generates no errors.
Please help me fix it.
Regards,
xploreraj
