package servlets; import java.io.IOException; import java.io.PrintWriter; import java.sql.*; import java.util.Date; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; @WebServlet("/CRUDDatabase") public class CRUDDatabase extends HttpServlet { private static final long serialVersionUID = 1L; @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=utf-8"); request.setCharacterEncoding("utf8"); try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { } PrintWriter out = response.getWriter(); out.println(""); out.println(""); String title = "CRUD Database page"; out.println("" + title + ""); out.println(""); out.println(""); out.println(""); out.println("

" + title + "

"); // Сессия начало HttpSession session = request.getSession(true); String savedLastName = (String) session.getAttribute("savedLastName"); if ((savedLastName != null) && (savedLastName != "")) { String heading; Integer accessCount = new Integer(0); if (session.isNew()) { heading = "Welcome, Newcomer"; } else { heading = "Welcome Back"; Integer oldAccessCount = (Integer) session .getAttribute("accessCount"); if (oldAccessCount != null) { accessCount = new Integer(oldAccessCount.intValue() + 1); } } session.setAttribute("accessCount", accessCount); // Сессия конец // Сессия отображение начало out.println("
"); out.println("

" + heading + "

\n"); out.println("

Information on Your Session:

\n"); out.println("\n"); out.println("\n"); out.println("\n"); out.println("\n"); out.println("\n"); out.println("\n"); out.println("\n"); out.println("
Info TypeValue\n"); out.println("
Фамилия\n"); out.println("" + session.getAttribute("savedLastName") + "\n"); out.println("
ID\n"); out.println("" + session.getId() + "\n"); out.println("
Creation Time\n"); out.println("" + new Date(session.getCreationTime()) + "\n"); out.println("
Time of Last Access\n"); out.println("" + new Date(session.getLastAccessedTime()) + "\n"); out.println("
Number of Previous Accesses\n"); out.println("" + accessCount + "\n"); out.println("
\n"); out.println("
"); } // Сессия отображение конец out.println("
"); out.println("

Database Operations

"); out.println("
"); out.println("
"); out.println(""); out.println(" "); out.println("
"); out.println("
"); out.println("
"); out.println("

Table Operations

"); out.println("
"); out.println("
"); out.println(""); out.println(" "); out.println("
"); out.println("
"); out.println("
"); out.println("

Add New Record

"); out.println("
"); out.println("
"); out.println(" "); out.println(" "); out.println(""); out.println(""); out.println("
"); out.println("
"); out.println("
"); out.println("
"); String id = request.getParameter("id"); String record = request.getParameter("record"); String action = request.getParameter("action"); String sql; StringBuffer Records = new StringBuffer(2048); // Open the Records table try { Connection con2 = DriverManager.getConnection( "jdbc:mysql://localhost:3306/mysql", "root", ""); Statement s2 = con2.createStatement(); ResultSet rs; if (action != null && action.equals("Add Database")) { sql = "CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8 COLLATE utf8_general_ci"; s2.executeUpdate(sql); out.println("Database is created Successfully

"); s2.close(); con2.close(); } Connection con = DriverManager .getConnection( "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8", "root", ""); Statement s = con.createStatement(); if (action != null && action.equals("Drop Database")) { sql = "DROP DATABASE IF EXISTS mydb"; s.executeUpdate(sql); out.println("Database is droped Successfully

"); } if (action != null && action.equals("Add Table")) { sql = "CREATE TABLE IF NOT EXISTS Records (`recordID` int(11) " + "NOT NULL AUTO_INCREMENT, `recordName` tinytext " + "NOT NULL, PRIMARY KEY (`recordID`)) ENGINE=MyISAM " + "DEFAULT CHARSET=utf8 AUTO_INCREMENT=6"; s.executeUpdate(sql); out.println("Table is created Successfully

"); } if (action != null && action.equals("Drop Table")) { sql = "DROP TABLE IF EXISTS Records"; s.executeUpdate(sql); out.println("Table is droped Successfully!"); } if (action != null && action.equals("add")) { sql = "SELECT recordName FROM Records WHERE recordName='" + record + "'"; rs = s.executeQuery(sql); if (!rs.next()) { sql = "INSERT INTO Records (recordName) VALUES ('" + record + "')"; s.executeUpdate(sql); } else out.println("' " + record + " ' is already in the database

"); } else if (action != null && action.equals("delete")) { sql = "DELETE FROM Records WHERE recordID=" + id; s.executeUpdate(sql); } else if (action != null && action.equals("update")) { sql = "UPDATE Records SET recordName='" + record + "'" + " WHERE recordId=" + id; s.executeUpdate(sql); } sql = "SELECT recordID, recordName FROM Records ORDER BY recordName ASC"; rs = s.executeQuery(sql); // Now list all Records plus the DELETE and UPDATE buttons out.println("

"); out.println(""); out.println("Table Records"); out.println("
"); out.println(""); out.println(""); out.println(""); out.println(""); while (rs.next()) { id = rs.getString(1); record = rs.getString(2); out.println(""); out.println(""); out.println(""); out.println(""); Records.append("\n"); } out.println("
RecordAction
"); out.println("
"); out.println(""); out.println(""); out.println(""); out.println(""); out.println("
"); out.println("		"); out.println("
"); out.println(""); out.println(""); out.println(""); out.println("
"); out.println("
"); out.println(""); out.println("
"); s.close(); con.close(); } catch (SQLException e) { // out.println(e.toString()); } catch (Exception e) { out.println(e.toString()); } out.println(""); out.println(""); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }