MySQL database connectivity in JSP/Servlet

Database connectivity with Java require some steps to be followed. If you have not done database connectivity in Java previously then it will be difficult to connect to database only using coding.

To do database connectivity in Java with MySQL, we require connector- more specific JDBC driver.

You can download it from here. It a jar file name some like mysql-connector-java-5.1.39.jar.

To install/load this file for use refer to this documentation, or follow instruction given below.

To load this driver, you have to put it at proper location. If you are working on project, you should put it into lib directory of your project (If you are using any IDE like Netbeans or Eclipse, you can find lib directory in your project).

But if you are not working on any project or IDE. Then this file (mysql-connector-java-5.1.39.jar) should be pasted in folder your java location\Java\jdk1.8.0_77\jre\lib\ext directly.

Now we are ready to use MySQL database.

1. Now in JSP/Servlet program we have to import java.sql.* package to use database interfaces to connect with database.

We can import this in JSP using

                             <%@ page import="java.sql.*"%>

and in Servlet
                                   import java.sql.*;

2. Now we require to register the JDBC driver using code
       Class.forName("com.mysql.jdbc.Driver");

3. Open a connection using

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/database","user","password");

4. Now we ready to execute query

stmt = con.createStatement();
String sql="select username, password, type from users where username='"+username+"' and password='"+password+"'";// where tname like '"+search+"'";
ResultSet rs = stmt.executeQuery(sql);

5. Extract data from result set

while(rs.next()){
                out.println(rs.getString(table column index or name)); //if data is string
                out.println(rs.getInt(table column index or name)); //if data is Integer
}

6. After-all close all connections

                        rs.close();
                        stmt.close();
                        con.close();

Complete servlet Example

// login servlet
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class Login extends HttpServlet {
  
  public void doPost(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    res.setContentType("text/plain");
    PrintWriter out = res.getWriter();
Connection conn = null;
Statement stmt=null;
try
{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","user","password");
//db.dbConnect();
stmt = conn.createStatement();
String sql="select * from table_name";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
out.println(rs.getString(1));
        out.println(rs.getString(2));
}
rs.close();
stmt.close();
conn.close();
}
catch(Exception e)
{
out.println(e);
}
     }
}

 If you know JSP you can use same code in JSPs accordingly and vice-versa.