SMBMS
資料庫:
專案如何搭建?
考慮使用不使用Maven?依賴,Jar
1、專案搭建準備工作
-
搭建一個maven web專案
-
配置Tomcat
-
測試專案是否能夠跑起來
-
匯入專案中會遇到的jar包;
jsp,Servlet,mysql驅動,jstl,standard...
-
建立專案包結構
-
編寫實體類;
ORM對映:表-類對映
-
編寫基礎公共類
-
資料庫配置檔案
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8&useSSL=true&setServiceTime=GMT username=root password=123456
-
編寫資料庫的公共類
//運算元據庫的公共類 public class BaseDao { private static String driver; private static String url; private static String username; private static String password; //靜態程式碼塊,類載入的時候就初始化了 static { Properties properties = new Properties(); //通過類載入器讀取對應的資源 InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties"); try { properties.load(is); } catch (IOException e) { e.printStackTrace(); } driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); } //獲取資料庫的連線 public static Connection getConnection(){ Connection connection = null; try { Class.forName("driver"); connection = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return connection; } //編寫查詢公共類 public static ResultSet execute(Connection connection,String sql,Object[] params,ResultSet resultSet,PreparedStatement preparedStatement) throws SQLException { //預編譯的sql,在後面直接執行就可以了 preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { //setObject,佔位符從1開始,但是我們的陣列是從0開始! preparedStatement.setObject(i+1,params[i]); } resultSet = preparedStatement.executeQuery(); return resultSet; } //編寫增刪改公共方法 public static int execute(Connection connection, String sql, Object[] params, PreparedStatement preparedStatement) throws SQLException { preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { //setObject,佔位符從1開始,但是我們的陣列是從0開始! preparedStatement.setObject(i+1,params[i]); } int updateRows = preparedStatement.executeUpdate(); return updateRows; } //釋放資源 public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){ boolean flag = true; if (resultSet!=null){ try { resultSet.close(); //GC回收 resultSet = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (preparedStatement!=null){ try { preparedStatement.close(); //GC回收 preparedStatement = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (connection!=null){ try { connection.close(); //GC回收 connection = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } return flag; } }
-
編寫字元編碼過濾器
-
-
匯入靜態資源
2、登入功能實現
-
編寫前端頁面
-
設定首頁
<!--設定歡迎頁面--> <welcome-file-list> <welcome-file>login.jsp</welcome-file> </welcome-file-list>
-
編寫dao層得到使用者登入的介面
DAO:data access object
//得到要登入的使用者 public User getLoginUser(Connection connection,String userCode) throws SQLException;
-
編寫dao介面的實現類
public class UserDaoImpl implements UserDao{ public User getLoginUser(Connection connection, String userCode) throws SQLException { PreparedStatement pstm = null; ResultSet rs = null; User user = null; if (connection!=null){ String sql = "select * from smbms_user where userCode=?"; Object[] params = {userCode}; rs = BaseDao.execute(connection, pstm, rs, sql, params); if (rs.next()){ user = new User(); user.setId(rs.getInt("id")); user.setUserCode(rs.getString("userCode")); user.setUserName(rs.getString("userName")); user.setUserPassword(rs.getString("userPassword")); user.setGender(rs.getInt("gender")); user.setBirthday(rs.getDate("birthday")); user.setPhone(rs.getString("phone")); user.setAddress(rs.getString("address")); user.setUserRole(rs.getInt("userRole")); user.setCreatedBy(rs.getInt("createdBy")); user.setCreationDate(rs.getTimestamp("creationDate")); user.setModifyBy(rs.getInt("modifyBy")); user.setModifyDate(rs.getTimestamp("modifyDate")); } BaseDao.closeResource(null,pstm,rs); } return user; } }
-
業務層介面
//使用者登入 public User login(String userCode,String password);
-
業務層實現類
public class UserServiceImpl implements UserService { //業務層都會呼叫dao層,所以我們要引入Dao層; private UserDao userDao; public UserServiceImpl() { userDao = new UserDaoImpl(); } public User login(String userCode, String password) { Connection connection = null; User user = null; connection = BaseDao.getConnection(); try { user = userDao.getLoginUser(connection, userCode); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection, null, null); } return user; } }
-
編寫Servlet
public class LoginServlet extends HttpServlet { //Servlet:控制層,呼叫業務層程式碼 @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { System.out.println("LoginServlet--start...."); //獲取使用者名稱和密碼 String userCode = req.getParameter("userCode"); String userPassword = req.getParameter("userPassword"); //和資料庫中的密碼進行對比,呼叫業務層; UserService userService = new UserServiceImpl(); User user = userService.login(userCode, userPassword); //這裡已經把登入的人給查出來了 if (user!=null){ //查有此人,可以登入 //將使用者的資訊放到Session中; req.getSession().setAttribute(Constants.USER_SESSION,user); //跳轉到主頁 resp.sendRedirect("jsp/frame.jsp"); }else {//查無此人,無法登入 //轉發回登入頁面,順帶提示它,使用者名稱或者密碼錯誤 req.setAttribute("error","使用者名稱或者密碼不正確"); req.getRequestDispatcher("login.jsp").forward(req,resp); } } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } }
-
註冊Servlet
<!--Servlet--> <servlet> <servlet-name>LoginServlet</servlet-name> <servlet-class>com.kuang.servlet.user.LoginServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LoginServlet</servlet-name> <url-pattern>/login.do</url-pattern> </servlet-mapping>
-
測試訪問,確保以上功能成功!
3、登入功能優化
登出功能:
思路:移除Session,返回登入頁面
public class LogoutServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//移除使用者的Constants.USER_SESSION
req.getSession().removeAttribute(Constants.USER_SESSION);
resp.sendRedirect(req.getContextPath()+"/login.jsp");//返回登入頁面
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
註冊xml
<servlet>
<servlet-name>LogoutServlet</servlet-name>
<servlet-class>com.kuang.servlet.user.LogoutServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LogoutServlet</servlet-name>
<url-pattern>/jsp/logout.do</url-pattern>
</servlet-mapping>
登入攔截優化
編寫一個過濾器並註冊
public class SysFilter implements Filter {
public void init(FilterConfig filterConfig) throws ServletException {
}
public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest) req;
HttpServletResponse response = (HttpServletResponse) resp;
//過濾器,從Session中獲取使用者,
User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);
if (user==null){ //已經被移除或者登出了,或者未登入
response.sendRedirect("/smbms/error.jsp");
}else {
chain.doFilter(req,resp);
}
}
public void destroy() {
}
}
<!--使用者登入過濾器-->
<filter>
<filter-name>SysFilter</filter-name>
<filter-class>com.kuang.filter.SysFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>SysFilter</filter-name>
<url-pattern>/jsp/*</url-pattern>
</filter-mapping>
測試,登入,登出許可權,都要保證OK!
4、密碼修改
-
匯入前端素材
<li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">密碼修改</a></li>
-
寫專案,建議從底層向上寫
-
UserDao介面
//修改當前使用者密碼 public int updatePwd(Connection connection,int id,int password) throws SQLException;
-
UserDao介面實現類
//修改當前使用者密碼 public int updatePwd(Connection connection, int id, int password) throws SQLException { PreparedStatement pstm = null; int execute = 0; if (connection!=null){ String sql = "update smbms_user set userPassword = ? where id = ?"; Object params[] = {password,id}; execute = BaseDao.execute(connection, pstm, sql, params); BaseDao.closeResource(null,pstm,null); } return execute; }
-
UserService層
//根據使用者ID修改密碼 public boolean updatePwd(int id, int pwd);
-
UserService實現類
public boolean updatePwd(int id, int pwd) { Connection connection = null; boolean flag = false; //修改密碼 try { connection = BaseDao.getConnection(); if (userDao.updatePwd(connection,id,pwd)>0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); }finally { BaseDao.closeResource(connection,null,null); } return flag; }
-
Servlet記得實現複用,需要提取出方法!
@Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String method = req.getParameter("method"); if (method.equals("savepwd")&&method!=null){ this.updatePwd(req, resp); } } public void updatePwd(HttpServletRequest req, HttpServletResponse resp){ //從Session裡面拿ID; Object o = req.getSession().getAttribute(Constants.USER_SESSION); String newpassword = req.getParameter("newpassword"); System.out.println("UserServlet"+newpassword); boolean flag = false; //if (o!=null && !StringUtils.isNullOrEmpty(newpassword)){ if (o!=null && newpassword!=null){ UserService userService = new UserServiceImpl(); flag = userService.updatePwd(((User) o).getId(), newpassword); if (flag){ req.setAttribute("message","修改密碼成功,請退出,使用新密碼登入"); //密碼修改成功,移除當前Session req.getSession().removeAttribute(Constants.USER_SESSION); }else { req.setAttribute("message","密碼修改失敗"); //密碼修改成功,移除當前Session } }else { req.setAttribute("message","新密碼有問題"); } try { req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp); } catch (ServletException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
<servlet> <servlet-name>UserServlet</servlet-name> <servlet-class>com.kuang.servlet.user.UserServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserServlet</servlet-name> <url-pattern>/jsp/user.do</url-pattern>
-
測試
優化密碼修改使用Ajax;
-
阿里巴巴的fastjson
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.79</version> </dependency>
-
後臺程式碼修改
//修改密碼 public void updatePwd(HttpServletRequest req, HttpServletResponse resp){ //從Session裡面拿ID; Object o = req.getSession().getAttribute(Constants.USER_SESSION); String newpassword = req.getParameter("newpassword"); System.out.println("UserServlet"+newpassword); boolean flag = false; //if (o!=null && !StringUtils.isNullOrEmpty(newpassword)){ if (o!=null && newpassword!=null){ UserService userService = new UserServiceImpl(); flag = userService.updatePwd(((User) o).getId(), newpassword); if (flag){ req.setAttribute("message","修改密碼成功,請退出,使用新密碼登入"); //密碼修改成功,移除當前Session req.getSession().removeAttribute(Constants.USER_SESSION); }else { req.setAttribute("message","密碼修改失敗"); //密碼修改成功,移除當前Session } }else { req.setAttribute("message","新密碼有問題"); } try { req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp); } catch (ServletException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //驗證舊密碼,session中有使用者的密碼 public void pwdModify(HttpServletRequest req, HttpServletResponse resp){ //從Session裡面拿oldpassword; Object o = req.getSession().getAttribute(Constants.USER_SESSION); String oldpassword = req.getParameter("oldpassword"); //萬能的Map : 結果集 HashMap<String, String> resultMap = new HashMap<String,String>(); if (o==null){ //Session失效了,session過期了 resultMap.put("result","sessionerror"); }else if (StringUtils.isNullOrEmpty(oldpassword)){ //輸入的密碼為空 resultMap.put("result","error"); }else { String userPassword = ((User) o).getUserPassword(); //Session中使用者的密碼 if (oldpassword.equals(userPassword)){ resultMap.put("result","true"); }else { resultMap.put("result","false"); } } try { resp.setContentType("application/json"); PrintWriter writer = resp.getWriter(); //JSONArray 阿里巴巴的JSON工具類,轉換格式 /* resultMap = ["result","sessionerror","result","error"] Json格式 = {key:value} */ writer.write(JSONArray.toJSONString(resultMap)); writer.flush(); writer.close(); } catch (IOException e) { e.printStackTrace(); } }
-
測試
5、使用者管理實現
思路:
-
匯入分頁的工具類
-
使用者列表頁面匯入
userlist.jsp
rollpage.jsp
1、獲取使用者數量
-
UserDao
//根據使用者名稱或者角色查詢使用者總數 public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
-
UserDaoImpl
//根據使用者名稱或者角色查詢使用者總數【最難理解的SQL】 public int getUserCount(Connection connection, String username, int userRole) throws SQLException { PreparedStatement pstm = null; ResultSet rs = null; int count = 0; if (connection!=null){ StringBuffer sql = new StringBuffer(); sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id"); ArrayList<Object> list = new ArrayList<Object>();//存放我們的引數 if (!StringUtils.isNullOrEmpty(username)){ //拼接sql語句 sql.append(" and u.userName like ?"); list.add("%"+username+"%"); //index:0 } if (userRole>0){ sql.append(" and u.userRole = ?"); list.add(userRole); //index:1 } //怎麼把list轉換為陣列 Object[] params = list.toArray(); System.out.println("UserDaoImpl->getUserCount"+sql.toString()); //輸出最後完整的SQL語句 rs = BaseDao.execute(connection, pstm, null, sql.toString(), params); if (rs.next()){ count = rs.getInt("count");//從結果集中獲取最終的數量 } BaseDao.closeResource(null,pstm,rs); } return count; }
-
UserService
//查詢記錄數 public int getUserCount(String username, int userRole);
-
UserServiceImpl
//查詢記錄數 public int getUserCount(String username, int userRole) { Connection connection = null; int count = 0; try { connection = BaseDao.getConnection(); count = userDao.getUserCount(connection, username, userRole); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return count; }
2、獲取使用者列表
-
userdao
//通過條件查詢-userList public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws Exception;
-
userdaoImpl
public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws Exception { PreparedStatement pstm = null; ResultSet rs = null; List<User> userList = new ArrayList<User>(); if (connection!=null){ StringBuffer sql = new StringBuffer(); sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id"); List<Object> list = new ArrayList<Object>();//存放我們的引數 if (!StringUtils.isNullOrEmpty(username)){ //拼接sql語句 sql.append(" and u.userName like ?"); list.add("%"+username+"%"); //index:0 } if (userRole>0){ sql.append(" and u.userRole = ?"); list.add(userRole); //index:1 } //在資料庫中,分頁使用 limit startIndex,pageSize; 總數 //當前頁 (當前頁-1)*頁面大小 //0,5 1 0 01234 //5,5 2 5 56789 //10,5 3 10 sql.append(" order by creationDate DESC limit ?,?"); currentPageNo = (currentPageNo-1)*pageSize; list.add(currentPageNo); list.add(pageSize); //怎麼把list轉換為陣列 Object[] params = list.toArray(); System.out.println("sql-->"+sql.toString()); //輸出最後完整的SQL語句 rs = BaseDao.execute(connection, pstm, null, sql.toString(), params); while (rs.next()){ User user = new User(); user.setId(rs.getInt("id")); user.setUserCode(rs.getString("userCode")); user.setUserName(rs.getString("userName")); user.setGender(rs.getInt("gender")); user.setBirthday(rs.getDate("birthday")); user.setPhone(rs.getString("phone")); user.setUserRole(rs.getInt("userRole")); user.setUserRoleName(rs.getString("userRoleName")); userList.add(user); } BaseDao.closeResource(null,pstm,rs); } return userList; }
-
userService
//通過條件查詢-userList public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
-
userServiceImpl
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) { Connection connection = null; List<User> userList = null; try { connection = BaseDao.getConnection(); userList = userDao.getUserList(connection, queryUserName, queryUserRole,currentPageNo,pageSize); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return userList; }
3、獲取角色操作
為了我們職責統一,可以把角色的操作單獨放在一個包中,和POJO類對應
RoleDao
public interface RoleDao {
//獲取角色列表
public List<Role> getRoleList(Connection connection) throws SQLException;
}
RoleDaoImpl
public List<Role> getRoleList(Connection connection) throws SQLException {
PreparedStatement pstm = null;
ResultSet resultSet = null;
ArrayList<Role> roleList = new ArrayList<Role>();
if (connection!=null){
String sql = "select * from smbms_role";
Object[] params = {};
resultSet = BaseDao.execute(connection, pstm, resultSet, sql, params);
while (resultSet.next()){
Role _role = new Role();
_role.setId(resultSet.getInt("id"));
_role.setRoleCode(resultSet.getString("roleCode"));
_role.setRoleName(resultSet.getString("roleName"));
roleList.add(_role);
}
BaseDao.closeResource(null,pstm,resultSet);
}
return roleList;
}
RoleService
//獲取角色列表
public List<Role> getRoleList();
RoleServiceImpl
public class RoleServiceImpl implements RoleService{
//引入Dao
private RoleDao roleDao = null;
public RoleServiceImpl(){
roleDao = new RoleDaoImpl();
}
public List<Role> getRoleList() {
Connection connection = null;
List<Role> roleList = null;
try {
connection = BaseDao.getConnection();
roleList = roleDao.getRoleList(connection);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection,null,null);
}
return roleList;
}
}
4、使用者顯示的Servlet
- 獲取使用者前端的資料(查詢)
- 判斷請求是否需要執行,看引數的值判斷
- 為了實現分頁,需要計算出當前頁面和總頁面,頁面大小...
- 使用者列表展示
- 返回前端
//重點,難點
public void query(HttpServletRequest req, HttpServletResponse resp){
//查詢使用者列表
//從前端獲取資料:
String queryUserName = req.getParameter("queryname");
String temp = req.getParameter("queryUserRole");
String pageIndex = req.getParameter("pageIndex");
int queryUserRole = 0;
//獲取使用者列表
UserServiceImpl userService = new UserServiceImpl();
List<User> userList = null;
//第一次走這個請求,一定是第一頁,頁面大小固定的;
int pageSize = 5;//可以把這個寫到配置檔案中,方便後期修改;
int currentPageNo = 1;
if (queryUserName == null){
queryUserName = ""; //不手動賦值會產生空指標異常
}
if (temp != null && !temp.equals("")){
queryUserRole = Integer.parseInt(temp); //給查詢賦值!0,1,2,3
}
if (pageIndex != null){
currentPageNo = Integer.parseInt(pageIndex);
}
//獲取使用者的總數(分頁:上一頁,下一頁的情況)
int totalCount = userService.getUserCount(queryUserName,queryUserRole);
//總頁數支援
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = pageSupport.getTotalPageCount(); //總共有幾頁
//控制首頁和尾頁
//如果頁面要小於1了,就顯示第一頁的東西
if (totalPageCount<1){
currentPageNo = 1;
}else if (currentPageNo>totalPageCount){ //當前頁面大於了最後一項;
currentPageNo = totalPageCount;
}
//獲取使用者列表展示
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
req.setAttribute("userList",userList);
RoleServiceImpl roleService = new RoleServiceImpl();
List<Role> roleList = roleService.getRoleList();
req.setAttribute("roleList",roleList);
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
req.setAttribute("queryUserName",queryUserName);
req.setAttribute("queryUserRole",queryUserRole);
//返回前端
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
小黃鴨除錯法