سلام. این بیشتر شبیه home work عه! پیاده سازی همش رو ما بگیم؟ سعی کنید خودتون سرچ کنید و پیاده سازیش رو انجام بدین.
من یه قسمت گرفتن لیست کاربران و پیمایش روی لیست رو بهتون کمک میکنم.
برای مثال تیبل کاربرامون این شکلیه :
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
اول باید کلاس های لایه مدل و بیزنس رو بنویسیم. (از JDBC استفاده میکنیم)
model :
package com.sample.model;
/**
* @author ahmad
*/
public final class User {
private long id;
private String firstName;
private String lastName;
public User(long id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
@Override
public String toString() {
return "{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
'}';
}
}
کلاس های لایه bl :
package com.sample.bl;
import java.util.List;
/**
* @author ahmad
*/
public interface IRepository<E> {
boolean save(E e);
E get(long id);
List<E> loadAll();
boolean delete(E e);
}
package com.sample.bl;
import com.sample.model.User;
/**
* @author ahmad
*/
public interface IUserRepository extends IRepository<User> {
}
package com.sample.bl;
import com.sample.model.User;
import com.sample.util.JDBCTemplate;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author ahmad
*/
public final class UserRepository extends JDBCTemplate implements IUserRepository, RowMapper<User> {
public UserRepository(Connection connection) {
super(connection);
}
@Override
public boolean save(User user) {
try (PreparedStatement statement = prepareStatement(PreparedQueries.INSERT_USER)) {
statement.setString(1, user.getFirstName());
statement.setString(2, user.getLastName());
return statement.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
@Override
public User get(long id) {
try (PreparedStatement statement = prepareStatement(PreparedQueries.SELECT_BY_ID)) {
statement.setLong(1, id);
ResultSet rs = statement.executeQuery();
if (rs.next()) {
return mapRow(rs);
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<User> loadAll() {
try (Statement statement = createStatement()) {
ResultSet rs = statement.executeQuery(PreparedQueries.SELECT_ALL) ;
if (rs.next()) {
List<User> users = new ArrayList<>();
do {
users.add(mapRow(rs));
} while (rs.next());
return users;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public boolean delete(User user) {
try (PreparedStatement statement = prepareStatement(PreparedQueries.DELETE_BY_ID)) {
statement.setLong(1, user.getId());
return statement.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
@Override
public User mapRow(ResultSet rs) throws SQLException {
return new User(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"));
}
}
package com.sample.bl;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author ahmad
*/
public interface RowMapper<E> {
E mapRow(ResultSet rs) throws SQLException;
}
package com.sample.bl;
/**
* @author ahmad
*/
public final class PreparedQueries {
private PreparedQueries() {
}
public static final String INSERT_USER = "INSERT INTO users (first_name, last_name) VALUES (?, ?)";
public static final String SELECT_BY_ID = "SELECT * FROM users WHERE id = ?";
public static final String SELECT_ALL = "SELECT * FROM users";
public static final String DELETE_BY_ID = "DELETE FROM users WHERE id = ?";
}
کلاس های util :
package com.sample.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author ahmad
*/
public final class JDBCUtil {
private static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
private JDBCUtil() {
}
static {
try {
Class.forName(DRIVER_CLASS_NAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
}
public static Connection getConnection(String url, String username, String password) throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void close(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException ignored) {
}
}
}
package com.sample.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author ahmad
*/
public class JDBCTemplate {
private final Connection connection;
public JDBCTemplate(Connection connection) {
this.connection = connection;
}
public Connection getConnection() {
return connection;
}
public Statement createStatement() throws SQLException {
return connection.createStatement();
}
public PreparedStatement prepareStatement(String sql) throws SQLException {
return connection.prepareStatement(sql);
}
}
برای اینکه یه لیست از کاربر هارو بگیریم و نمایش بدیم میشه از چند روش استفاده کرد. مثلا مستقیما توی خود سرولت یا صفحه jsp مون لیست رو بگیریم و همونجا روش iteration انجام بدیم که جالب نیست.
یا اینکه لیست رو به صورت json بفرستیم و با javascript کارمونو بکنیم یا ...
روشی که ما میخوایم استفاده کنیم اینه که توی سرولت لیست رو از دیتابیس بگیریم و دیسپچ کنیم به صفحه jsp مون. اونجا با taglib روی لیست foreach بزنیم و ...
اول سرولت رو تعریف میکنیم :
package com.sample.controller;
import com.sample.bl.IUserRepository;
import com.sample.bl.UserRepository;
import com.sample.model.User;
import com.sample.util.JDBCUtil;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* @author ahmad
*/
public final class UserController extends HttpServlet {
private Connection connection;
private IUserRepository userRepository;
@Override
public void init(ServletConfig config) throws ServletException {
final ServletContext context = config.getServletContext();
String url = context.getInitParameter("jdbc.url");
String username = context.getInitParameter("jdbc.username");
String password = context.getInitParameter("jdbc.password");
try {
connection = JDBCUtil.getConnection(url, username, password);
userRepository = new UserRepository(connection);
} catch (SQLException e) {
e.printStackTrace();
System.exit(1);
}
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
List<User> users = userRepository.loadAll();
req.setAttribute("users", users);
req.getRequestDispatcher("UsersResponse.jsp").forward(req, resp);
}
@Override
public void destroy() {
JDBCUtil.close(connection);
}
}
web.xml :
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<context-param>
<param-name>jdbc.url</param-name>
<param-value>jdbc:mysql://localhost:3306/jsp_taglib_sample</param-value>
</context-param>
<context-param>
<param-name>jdbc.username</param-name>
<param-value>user</param-value>
</context-param>
<context-param>
<param-name>jdbc.password</param-name>
<param-value>pass</param-value>
</context-param>
<servlet>
<servlet-name>userController</servlet-name>
<servlet-class>com.sample.controller.UserController</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>userController</servlet-name>
<url-pattern>/get-users-list</url-pattern>
</servlet-mapping>
</web-app>
صفحه های jsp :
index.jsp :
<%--
@author ahmad
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title></title>
</head>
<body>
<a href="${pageContext.request.contextPath}/get-users-list">Show list of users</a>
</body>
</html>
UsersResponse.jsp :
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
@author ahmad
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title></title>
</head>
<body>
<div>
<label>
List of users :
<select>
<option selected>select user</option>
<c:forEach items="${requestScope.users}" var="user">
<option>${user.firstName} ${user.lastName}</option>
</c:forEach>
</select>
</label>
</div>
</body>
</html>