Deleting a row from the mysql database in a web application

Go To StackoverFlow.com

0

Please suggest me the piece of code for deleting a row from a mysql database that contains three columns problemid, problem and solution. I want to delete it from a browser i.e. it is a web application.

2012-04-04 06:31
by P3M
Do you use straight JDBC or some ORM tool like Hibernate - Jaco Van Niekerk 2012-04-04 06:36
straight JDBC @JacoVanNieker - P3M 2012-04-04 06:50
You might need to look at http://stackoverflow.com/fa - Hardik Mishra 2012-04-04 08:20


1

You may consider using JDBC (Java Database Connectivity) API for your problem. I recommend you to take a close look at the following simple tutorials about developing Java Web Applications Using a MySQL Database.

https://blogs.oracle.com/JavaFundamentals/entry/creating_a_simple_web_application

http://www.javaguicodexample.com/javawebmysqljspjstljsf5.html

2012-04-04 06:39
by Korhan Ozturk
I need piece of code, not the tutoria - P3M 2012-04-04 07:06
The tutorials include sample codes and programs as well. Since you do not provide any of your code that you've developed so far nor you provide much detail on what you exactly trying to achieve, that's all I could do for now - Korhan Ozturk 2012-04-04 07:10


0

Here is a sample Servlet.

But please remember this is just to show you how to do it, you SHOULD NOT use this in a productive system!This is more for demonstration, look at it how and learn how to do it. This servlet should run fine, but there are some things you have to do!

Anyway you should read these documents, if you haven't already done it

http://docs.oracle.com/javaee/5/tutorial/doc/bnadp.html

http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

Things which i havent taken into account, while i wrote this:

Request Parameters

If one of the request parameters cannot be found, it will throw an Exception. You need a better way of handling with this situation.

Connection Pooling

This example will open a connection to the database on EVERY request. Opening a connection costs time. Therefore everyone use a connection pool. This library/server opens a specified amount of connections to the database. Everytime you need to access the database you fetch it from this pool and if you're finished you return it to the pool.

Security

Someone who knows the address of this servlet, could easily use it to delete any row in your table. This is your job to secure it.

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

    /**
     * DON'T USE IN PRODUCTION, JUST FOR LEARNING PURPOSES
    **/
public class MySqlServlet extends HttpServlet {

  public void doGet(HttpServletRequest request, HttpServletResponse response)
                               throws ServletException, IOException {

    long problemId;
    long problem;
    long solution;

    Object problemIdAsObject = request.getParameter("problemId");
    Object problemAsObject = request.getParameter("problem");
    Object solutionAsObject = request.getParameter("solution");

    if ( problemIdAsObject == null ){
        throw new ServletException("problemId has not been specified!");
    }

    if ( problemAsObject == null ){
        throw new ServletException("problem has not been specified!");
    }

    if ( solutionAsObject == null ){
        throw new ServletException("solution has not been specified!");
    }

    problemId = Long.valueOf( (String)problemIdAsObject );
    problem = Long.valueOf( (String)problemAsObject );
    solution = Long.valueOf( (String)solutionAsObject );    

    PreparedStatement statement = null;
    Connection connectionToDatabase = null;
    try{
        connectionToDatabase = getConnection();
        String sql = "DELETE FROM table WHERE problemid = ? and "+
                        "problem = ? and solution = ?";
        statement = connectionToDatabase.prepareStatement(sql);
        statement.setLong(1,problemId);
        statement.setLong(2,problem);
        statement.setLong(3,solution);
        statement.execute();
    }catch( SQLException sqle ){
        throw new ServletException(sqle);
    }catch( ClassNotFoundException cnfe ){
        throw new ServletException(cnfe);   
    }finally{
        try{
            statement.close();
            connectionToDatabase.close();
        }catch( SQLException sqle ){
            throw new ServletException(sqle);
        }
    }

    response.setContentType("text/html");
    PrintWriter out = response.getWriter();

    out.println("<HTML>");
    out.println("<BODY>");
    out.println("OK");
    out.println("</BODY></HTML>");
  }

  private Connection getConnection() 
          throws ClassNotFoundException,SQLException{

            String userName = "user";
    String password = "password";
    String databaseName = "database";
    String serverAddress = "localhost";

    String connectionString = "jdbc:mysql://"+serverAddress+"/"+databaseName+
                "?user="+userName+"&password="+password;
            //If this line is not working, use this instead:
            //Class.forName("com.mysql.jdbc.Driver").newInstance();
    Class.forName("com.mysql.jdbc.Driver");     
    Connection connection = DriverManager.getConnection(connectionString);  
    return connection;
  }

}
2012-04-04 07:37
by Adi


-2

It's so simple buddy, first of all you have to make a link to any servlet from jsp. And with that link you have to pass the deleted record id as parameter & write the code inside servlet for deleting the given row from database. And then return that same page where the previous link you have clicked.

2012-04-04 06:49
by xxxxxxxx d
Please observe how to write an answer - nobeh 2012-04-04 06:59
I did my best, but there is some problem i can't figure out, please answer the code with that query for deleting and the code that should be inside the servlet for deleting - P3M 2012-04-04 07:09
@prem: "there is some problem" is poor thing. As a programmer. you should never say like this. Always post code with erro - Hardik Mishra 2012-04-04 08:26
Ads