Servlets - SPLessons

Servlet Database Access

Chapter 35

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

Servlet Database Access

Servlet Database Access

shape Description

Servlet Database Access, The term JDBC stands for Java Database Connectivity. It is a standard Java API for database and used for connecting the wide range database and the Java programming language. In servlet connecting to the database is an important task why because while dealing with big projects more databases will be used. All the databases will support, but the class should be mentioned in the code and username and password of the database are needed. The following task can be done using the JDBC library class.

shape Conceptual
figure

The image below explains the connection.

shape Example

Following is an example to connect with the database.

Example Servlet JDBC Insertion on Statement Interface:

index.html

<html>
   <HEAD>
      <TITLE>A Servlet</TITLE>
   </HEAD>
   <body>
      <form action="./welcome" method="get">
         <table border="1" bgcolor="ffff">
            <tr>
               <td>ID</td>
               <td>firstname</td>
               <td>lastname</td>
               <td>Start_date(YYYYMMDD)</td>
               <td>End_date(YYYYMMDD)</td>
               <td>Salary</td>
               <td>city</td>
            </tr>
            <tr>
               <td><input type="text" name="Id"/></td>
               <td><input type="text" name="firstName"/></td>
               <td><input type="text" name="lastName"/></td>
               <td><input type="text" name="Start_date"/></td>
               <td><input type="text" name="end_date"/></td>
               <td><input type="text" name="salary"/></td>
               <td><input type="text" name="city"/></td>
            </tr>
         </table>
         <input type="submit" value="submit"/>
      </form>
   </body>
</html>

web.xml

<web-app>
  <servlet>
  
    <servlet-name>ServletInsertion</servlet-name>
    <servlet-class>ServletInsertion</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>ServletInsertion</servlet-name>
    <url-pattern>/welcome</url-pattern>
  </servlet-mapping>
  
</web-app>

ServletJdbcInsert.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ServletInsertion extends HttpServlet {

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

		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		Connection con = null;
		
		String id = request.getParameter("Id");
		int Id = Integer.parseInt(id);
		String firstName = request.getParameter("firstName");
		String lastName = request.getParameter("lastName");
		String start_date = request.getParameter("Start_date");
		String end_date = request.getParameter("end_date");
		String salary = request.getParameter("salary");
		String city = request.getParameter("city");
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		    con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
			Statement statement = con.createStatement(); 
			
			int result = statement.executeUpdate("insert into employee values('"+Id+"',"+"'"+firstName+"',"+"'"+lastName+"',to_date('"+start_date+"','yyyymmdd'),to_date('"+end_date+"','yyyymmdd'),'"+salary+"','"+city+"')");
			
			out.println(result +" row sccessfully inserted your data");
			

			out.flush();
			out.close();
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {
			
			e.printStackTrace();
		
		}finally{
			try {
				con.close();
			} catch (SQLException e) {
			
				e.printStackTrace();
			}
			
		}
		
	}

}

Servlet Database Access – In doGet(), the parameters are appended to the URL and sent along with the header information. Sets the content type of the response being sent to the client, if the response has not been committed yet.The given content type may include a character encoding specification. The request.getParameter() method in the servlet class, to retrieve the input values from HTML page. The Class.forName(“”) loads the class in the event that it not effectively stacked. The JVM monitors every one of the classes that have been beforehand stacked. This technique utilizes the classloader of the class that conjures it.
Once the JDBC driver class is stacked, users are prepared to associate with a SQL Server by utilizing the DriverManager.getConnection(connection_url) technique. The JDBC driver implements java.sql.Driver. The Class#forName() loads them by name which cause them to register themselves with DriverManager#registerDriver() inside a static initializer. The DriverManager#getConnection() in turn tests for every registered driver if Driver#acceptsURL() returns true for the given URL and then calls Driver#connect() on the driver which will then return the concrete Connection implementation.
Output
Program should be compiled from HTML page, where enter the values and click submit.

When click on submit rows will be effected.

Summary

shape Key Points

  • Servlet Database Access- While performing task on database make sure to install any database like MySQL, Oracle.
  • Servlet Database Access – For practical understanding create a table and perform all the tasks on that table.
  • Using of query browser gives efficient knowledge