Encapsulate JDBC setup into public DBConnection.java and afterwards in any JSP working with database we should be able import this class and invoke :-
Connection conn = DBConnection.createConnection();
[tomcat@sever33fedora src]$ cat DBConnection.java
package net.javaguides.jsp.jdbc.database;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static Connection createConnection()
{
String url = "jdbc:mysql://localhost/ebookshop?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC";
String username = "root";
String password = "root";
out.println("In DBConnection.java class ");
Connection conn = null;
try {
try {
Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn ;
}
}
[tomcat@sever33fedora GuruVerify]$ cat WebUpdateExt.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>
<%@page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
String query = "update books set author = ?, title = ? where id = ?";
try(Connection conn=DBConnection.createConnection(); PreparedStatement Stat = conn.prepareStatement(query))
{
Statement stmt = conn.createStatement();
String avtor = request.getParameter("param1") ;
String nazv = request.getParameter("param2") ;
String code = request.getParameter("param3") ;
int key = Integer.parseInt(code);
Stat.setString(1, avtor);
Stat.setString(2, nazv);
Stat.setInt(3, key);
Stat.executeUpdate();
String strSelect = "select id, author, title, price, qty from books";
out.println("The SQL statement is: " + strSelect + "\n");
out.println("<br>");
out.println("<br>");
ResultSet rset = stmt.executeQuery(strSelect);
out.println("The records selected are:");
int rowCount = 0;
out.println("<html>");
out.println("<body bgcolor=\"C0C0C0\">");
out.println("<table border=\"2\">");
while(rset.next()) {
int id = rset.getInt("id");
String author = rset.getString("author");
String title = rset.getString("title");
double price = rset.getDouble("price");
int qty = rset.getInt("qty");
out.println("<tr>");
out.println("<td>");
out.println(Integer.toString(id)+','+author+','+title + ", " + price + ", " + qty);
out.println("</td>");
out.println("</tr>");
++rowCount;
}
} catch (SQLException e) {
throw new ServletException("SQL failed!", e);
}
out.println("</table>");
out.println("</body>");
out.println("</html>");
%>
[tomcat@sever33fedora GuruVerify]$ cat WebInsertExt.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>
<%@page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
String query = "INSERT INTO books(id,title,author,price,qty) VALUES(?,?,?,?,?)";
try(Connection conn = DBConnection.createConnection(); PreparedStatement Stat = conn.prepareStatement(query))
{
Statement stmt = conn.createStatement();
String code = request.getParameter("param1") ;
String nazv = request.getParameter("param2") ;
String avtor = request.getParameter("param3") ;
String price = request.getParameter("param4") ;
String quantity = request.getParameter("param5") ;
int key = Integer.parseInt(code);
double dprice = Double.parseDouble(price+"d");
int qntity = Integer.parseInt(quantity);
Stat.setInt(1, key);
Stat.setString(2, nazv);
Stat.setString(3, avtor);
Stat.setDouble(4, dprice);
Stat.setInt(5, qntity);
Stat.executeUpdate();
String strSelect = "select id, author, title, price, qty from books";
out.println("The SQL statement is: " + strSelect + "\n");
out.println("<br>");
out.println("<br>");
ResultSet rset = stmt.executeQuery(strSelect);
out.println("The records selected are:");
int rowCount = 0;
out.println("<html>");
out.println("<body bgcolor=\"C0C0C0\">");
out.println("<table border=\"2\">");
while(rset.next()) {
int id = rset.getInt("id");
String author = rset.getString("author");
String title = rset.getString("title");
double price1 = rset.getDouble("price");
int qty = rset.getInt("qty");
out.println("<tr>");
out.println("<td>");
out.println(Integer.toString(id)+','+author+','+title + ", " + price1 + ", " + qty);
out.println("</td>");
out.println("</tr>");
++rowCount;
}
} catch (SQLException e) {
throw new ServletException("SQL failed!", e);
}
out.println("</table>");
out.println("</body>");
out.println("</html>");
%>
[tomcat@sever33fedora GuruVerify]$ cat WebWorkExt.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.SQLException"%>
<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>
<%@page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
try(Connection conn = DBConnection.createConnection(); Statement stmt = conn.createStatement())
{
String strSelect = "select id, author, title, price, qty from books";
out.println("The SQL statement is: " + strSelect + "\n");
out.println("<br>");
out.println("<br>");
ResultSet rset = stmt.executeQuery(strSelect);
out.println("The records selected are:");
int rowCount = 0;
out.println("<html>");
out.println("<body bgcolor=\"C0C0C0\">");
out.println("<table border=\"2\">");
while(rset.next()) {
int id = rset.getInt("id");
String author = rset.getString("author");
String title = rset.getString("title");
double price = rset.getDouble("price");
int qty = rset.getInt("qty");
out.println("<tr>");
out.println("<td>");
out.println(Integer.toString(id)+','+author+','+title + ", " + price + ", " + qty);
out.println("</td>");
out.println("</tr>");
++rowCount;
}
} catch (SQLException e) {
throw new ServletException("SQL failed!", e);
}
out.println("</table>");
out.println("</body>");
out.println("</html>");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Collecting Three Parameters</TITLE></HEAD>
<BODY BGCOLOR="#C0C0C0">
<H1 ALIGN="CENTER">Updating MySQL Table via JDBC</H1>
<FORM ACTION="WebUpdateExt.jsp">
<TABLE BORDER="2">
<tr>
<td>
Update Author: <INPUT TYPE="TEXT" NAME="param1">
</td>
</tr>
<tr>
<td>
Update Title: <INPUT TYPE="TEXT" NAME="param2">
</td>
</tr>
<tr>
<td>
Key Record's Code : <INPUT TYPE="TEXT" NAME="param3">
</td>
</tr>
<tr>
<td>
<CENTER><INPUT TYPE="SUBMIT"></CENTER>
</td>
</tr>
</TABLE>
</FORM>
</BODY></HTML>
