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.
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
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;
}
}
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.