Please i need some help with my programing assigment in Java. l. Create a 3 laye
ID: 3810475 • Letter: P
Question
Please i need some help with my programing assigment in Java.
l. Create a 3 layer application in Java, where the top layer is the Uscr Interface, the middle layer is the 'business' layer, and the bottom layer is the database connectivity The user interface will present a GUI to the user, asking them to enter some information, and to interact with the stored data. Refer to your previous assignments. You can use your code or start from scratch. (The exact functionality provided to the user is deliberately vague here.) 3. Create a connection to a relational database using SQLite or MySQL 4. Create a single database table to hold information. Go back to the previous assignments, and re-use your classes, or modify them as you like. Recall that you will create the DB only once You can just comment out the code that creates it if you like. Some people find a way to detect if the DB is already there. That's fine, but not required 5. These requirements are taken from the previous assignment. This time the user is to be presented with a GUI that allows these things to be donc. So the data is not hard coded, the user will fill in a GUI click a button to interact with the DB. When you retrieve the data from the database it should populate the GUI to demonstrate that it is working 6. Demonstrate the insertion of a record into the database. Create a method in the Data layer that takes a Person as a parameter, and puts that data in the database. Insert several records 7. Demonstrate the retrieval of information from the database. Use SQL Select statements 8. Write a method called getPerson that returns a Person object. This method retrieves the data for a Person from the database. We also need to pass a parameter to identify what person You can use "name' if you like, or if you find it easier to use the database generated ID that's fine too. This method returns the object that represents that person. This will require that you extract the data that is returned from the database, and call the Person constructor. Note that this is the data-exchange between the relational database and the business layer 9. Write a method called findAllPeople that returns an ArrayList of objects containing all the people in the database 10. Write a method called deletePerson that removes a person from the database. The parameters ill be first name and last name. Print out on the console the data from the record that is being deleted. Use your findAllPeople method to verify that that person has been removed from the database. Consider what this method should return. Suppose the person is not found. Should the method return that information somehoExplanation / Answer
Explanation:
Create a dynamic web project in eclipse.
We have one user page(UserPage.jsp) where we are taking inputs like name,age,address.
Those inputs are delivered to MyServlet using jsp-servlet mapping provided in web.xml
in MyServlet we have get and post method.. in get method we are taking the request making a mysql access to select all user details , making an arrayList of users and sending the list to the result.jsp.
in MyServlet post method, we are checking the action type-insert or delete. based on that accessing mysql and inserting or deleting accordingly(using JDBC Connection,sql,preparedStatement and all). And then returning arraylist of users remaining. Those arraylist of users are delivered to result.jsp from the post method.
in result.jsp we take the ArrayList<user> from the myServlet and render and display.
findAllPerson() ,deletePerson() I have renamed as getAllUsers() and deleteFromDatabase() and so on. U can just change the names if you want
Code:
UserPage.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>User Page
</title>
</head>
<body>
Insert User Details:
<form action="/myServlet" method="post">
Name:<input type="text" name="name" />
Age:<input type="text" name="age" />
Address:<input type="text" name="address" />
Action:<input type="text" name="action" />
<input type="submit"/>
</form>
<form action="/myServlet" method="get">
<input type="submit">Get ALLUsers</submit>
</form>
</body>
</html>
result.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.util.ArrayList" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
//creating arraylist object of type category class
ArrayList<User> list =(ArrayList<User>)request.getAttribute("allUsers");
for(int i = 0; i < list.size(); i++) {
User user = list.get(i);
out.print( user.getName()+" ");
out.print(user.getAge()+" ");
out.print(user.getAddress()+" " );
out.println();
}
%>
</body>
</html>
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<display-name>My Application</display-name>
<description>
This is a simple web application
</description>
<servlet>
<servlet-name>MyServlet</servlet-name>
<servlet-class>MyServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>MyServlet</servlet-name>
<url-pattern>/myServlet</url-pattern>
</servlet-mapping>
</web-app>
MyServlet
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class MyServlet
*/
@WebServlet("/MyServlet")
public class MyServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* Default constructor.
*/
public MyServlet() {
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
ArrayList<User> userlist;
try{
SQLAccess access=new SQLAccess();
userlist=access.getAllUsers();
request.setAttribute("allUsers", userlist);
getServletConfig().getServletContext().getRequestDispatcher("/result.jsp").forward(request,response);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
String address = request.getParameter("address");
String action= request.getParameter("action");
ArrayList<User> userlist=null;
try{
SQLAccess access=new SQLAccess();
if(action.equals("insert")){
userlist=access.updateDataBase(name, age, address);
}else if(action.equals("delete")){
userlist=access.deleteFromDataBase(name);
}
request.setAttribute("allUsers", userlist);
getServletConfig().getServletContext().getRequestDispatcher("/result.jsp").forward(request,response);
}catch(Exception e){
e.printStackTrace();
}
}
}
SQLAcess class:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
public class SQLAccess {
private Connection connect = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
final private String host = "xxxxxxxxxxxxxxxxxxxxxxxx";
final private String user = "xxxxxxxx";
final private String passwd = "xxxxxxxxxxx";
public ArrayList<User> updateDataBase(String name,int age,String address) throws Exception {
ArrayList<User> userlist;
try {
// This will load the MySQL driver
Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager
.getConnection("jdbc:mysql://" + host + "/MyDB?"
+ "user=" + user + "&password=" + passwd );
preparedStatement = connect
.prepareStatement("insert into MyDB.user values (? , ?, ?)");
preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.setString(3, address);
preparedStatement.executeUpdate();
preparedStatement = connect
.prepareStatement("SELECT * from MyDB.user");
resultSet = preparedStatement.executeQuery();
userlist=writeResultSet(resultSet);
} catch (Exception e) {
throw e;
} finally {
close();
}
return userlist;
}
public ArrayList<User> deleteFromDataBase(String name) throws Exception {
ArrayList<User> userlist;
try {
Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager
.getConnection("jdbc:mysql://" + host + "/MyDB?"
+ "user=" + user + "&password=" + passwd );
preparedStatement = connect
.prepareStatement("delete from MyDB.user where name= ? ; ");
preparedStatement.setString(1, name);
preparedStatement.executeUpdate();
resultSet = statement
.executeQuery("select * from MyDB.user");
userlist=writeResultSet(resultSet);
} catch (Exception e) {
throw e;
} finally {
close();
}
return userlist;
}
public ArrayList<User> getAllUsers() throws Exception {
ArrayList<User> userlist;
try {
Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager
.getConnection("jdbc:mysql://" + host + "/MyDB?"
+ "user=" + user + "&password=" + passwd );
resultSet = statement
.executeQuery("select * from MyDB.user");
userlist=writeResultSet(resultSet);
} catch (Exception e) {
throw e;
} finally {
close();
}
return userlist;
}
private ArrayList<User> writeResultSet(ResultSet resultSet) throws SQLException {
ArrayList<User> userlist=new ArrayList<User>();
while (resultSet.next()) {
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String address = resultSet.getString("address");
System.out.println("UserName: " + name);
System.out.println("age: " + age);
System.out.println("address: " + address);
User user=new User(name,age,address);
userlist.add(user);
}
return userlist;
}
private void close() {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connect != null) {
connect.close();
}
} catch (Exception e) {
}
}
}