SMBMS
数据库:
项目如何搭建?
考虑适用不使用Maven?依赖.jar
项目搭建
1.搭建一个Maven web项目
2.配置Tomcat
3.测试项目是否能够跑起来
4.导入项目中会用到的java包
jsp、servlet、mysql驱动、jstl、stand
5.创建项目包结构
6.编写实体类
ORM映射:表-类映射
7.编写基本公共类
1.数据库配置文件
drivter=com.mysql.jdbc.Driver username=root password=123456 url=jdbc:mysql://localhost:3306?useUnicode=true&charcaterEncoding=utf-8
2.编写公共类
package com.atguigu.dao;
import java.io.IOException;
import java.io.InputStream;
import java.security.interfaces.RSAKey;
import java.sql.*;
import java.util.Properties;
/**
* @author tom
* @create 2022-05-11 16:06
*/
public class BaseDao {
//获取数据库的连接
public static Connection getConnection() {
//通过类加载器读取对应的资源
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
Properties pros = new Properties();
Connection conn=null;
try {
pros.load(is);
String url = pros.getProperty("url");
String driver = pros.getProperty("driver");
String username = pros.getProperty("username");
String password = pros.getProperty("password");
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//编写查询公共类
public static ResultSet execute(Connection conn,String sql,Object[] args,PreparedStatement ps) throws SQLException {
//预编译的sql,在后面直接调用
ps = conn.prepareStatement(sql);
for (int i=0;i<args.length;i++){
//占位符从1开始,数组从0开始
ps.setObject(i+1,args[i]);
}
//执行sql语句
ResultSet rs = ps.executeQuery();
// //获取元数据
// ResultSetMetaData metaData = rs.getMetaData();
return rs;
}
//编写增删改的公共方法
public static int AUD(Connection conn,String sql,Object[] args,PreparedStatement ps) throws SQLException {
ps = conn.prepareStatement(sql);
for (int i=0;i<args.length;i++){
//占位符从1开始,数组从0开始
ps.setObject(i+1,args[i]);
}
//执行sql语句
int i = ps.executeUpdate();
return i;
}
//释放资源
public static boolean closeResource(Connection conn, PreparedStatement ps, ResultSet rs){
boolean isFlag=true;
if (conn!=null){
try {
conn.close();
//GC回收
conn=null;
} catch (SQLException e) {
e.printStackTrace();
isFlag=false;
}
}
if (ps!=null){
try {
ps.close();
//GC回收
ps=null;
} catch (SQLException e) {
e.printStackTrace();
isFlag=false;
}
}
if (rs!=null){
try {
rs.close();
//GC回收
rs=null;
} catch (SQLException e) {
e.printStackTrace();
isFlag=false;
}
}
return isFlag;
}
}
3.编写字符编码过滤器
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
// response.setContentType("text/html;charset=utf-8");
chain.doFilter(request,response);
}
注意:如果加上response.setContentType("text/html;charset=utf-8");清除浏览器缓存或使用其他客户端登录时css样式将被解析为text/html
8. 导入静态资源
登录功能的实现
1.编写前端页面
2.设置欢迎页
<!--设置欢迎页面-->
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
3.编写dao层登录用户登录的接口
//得到要登录的用户
public smbms_user getLoginUser(Connection conn, String UserCode,String password) throws SQLException;
4.编写dao接口的实现类
public class UserDaoImpl implements UserDao {
public smbms_user getLoginUser(Connection conn, String userCode,String password) throws SQLException {
PreparedStatement ps=null;
ResultSet rs=null;
smbms_user user=null;
if (conn!=null){
String sql="select * from smbms_user where userCode=? and userPassword =?";
Object[] args={userCode,password};
rs = BaseDao.execute(conn, ps,rs,sql,args);
if (rs.next()){
user = new smbms_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,ps,rs);
return user;
}
return null;
}
}
5.业务层接口
//用户登录
public smbms_user login(String userCode, String passWord) throws SQLException;
6.业务层实现类
public class UserServiceImpl implements userService {
private UserDaoImpl ud;
public UserServiceImpl() {
ud = new UserDaoImpl();
}
public smbms_user login(String userCode, String passWord) {
Connection conn = null;
smbms_user user = null;
try {
conn = BaseDao.getConnection();
user = ud.getLoginUser(conn, userCode,passWord);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(conn, null, null);
}
return user;
}
//测试功能
// @Test
// public void test(){
// UserServiceImpl us = new UserServiceImpl();
// smbms_user wen = us.login("wen", "123");
// System.out.println(wen);
//
// }
}
7.编写Servlet
public class LoginServlet extends HttpServlet {
//servlet:控制层,调用业务层代码
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
//获取用户名和密码
String userCode = req.getParameter("userCode");
String passWord = req.getParameter("userPassword");
//和数据库中的密码进行对比,调用业务层
userService us = new UserServiceImpl();
smbms_user user = null;//查出登陆的人的信息
try {
user = us.login(userCode, passWord);
} catch (SQLException e) {
e.printStackTrace();
}
if (user != null) {
req.getSession().setAttribute(Constants.USER_SESSION, user);
resp.sendRedirect("/smbms/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);
}
}
8.注册Servlet
<!--注册登录页面的Servlet-->
<servlet>
<servlet-name>userLogin</servlet-name>
<servlet-class>com.atguigu.servlet.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>userLogin</servlet-name>
<url-pattern>/login.do</url-pattern>
</servlet-mapping>
url根据前端请求的来
3.登录功能优化
注销功能
思路:移除Session或置空Session,并返回登录页面
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//移除Session
HttpSession session = req.getSession();
if (session!=null){
session.removeAttribute(Constants.USER_SESSION);
//重定向至首页
resp.sendRedirect("/smbms");
}else {
resp.sendRedirect("/smbms");
}
}
注册Servlet
<servlet>
<servlet-name>userLogOut</servlet-name>
<servlet-class>com.atguigu.servlet.LogOutServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>userLogOut</servlet-name>
<url-pattern>/jsp/logout.do</url-pattern>
4.登录拦截优化
使用户不能直接进入登陆成功页面。
过滤器
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
HttpServletRequest req= (HttpServletRequest) request;
HttpServletResponse resp= (HttpServletResponse) response;
smbms_user session = (smbms_user) req.getSession().getAttribute(Constants.USER_SESSION);
if (session==null){
//重定向至错误页面
resp.sendRedirect("/smbms/error.jsp");
}
chain.doFilter(request,response);
}
注册过滤器
<!--注册filter阻止非授权访问-->
<filter>
<filter-name>LoginFilter</filter-name>
<filter-class>com.atguigu.filter.frameFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>LoginFilter</filter-name>
<url-pattern>/jsp/*</url-pattern>
</filter-mapping>
注意:url一定要配置好,/smbms不用加,不然总是无法拦截
测试、登录、注销、权限都要保证OK!
5.密码修改
1.导入前端素材
2.画流程图
3.UserDao接口
//修改密码
public int updateUPW(Connection conn, String UserCode,String passWord) throws SQLException;
4.UserDao接口实现类
//修改密码的实现类
public int updateUPW(Connection conn, String UserCode, String passWord) throws SQLException {
int i = 0;
String sql = "update smbms_user set userPassword=? where userCode=?";
PreparedStatement ps = conn.prepareStatement(sql);
Object args[] = {passWord, UserCode};
if (conn != null) {
i = BaseDao.AUD(conn, sql, args, ps);
BaseDao.closeResource(conn, ps, null);
}
return i;
}
5.UserService接口
//密码修改服务
public int updateUPW(Connection conn, String userCode, String passWord);
6.UserService的实现类
public int updateUPW(Connection conn, String userCode, String passWord) {
int i = 0;
try {
//执行密码修改操作
i = ud.updateUPW(conn, userCode, passWord);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭连接
BaseDao.closeResource(conn, null, null);
}
//返回修改结果
return i;
}
此时userPassword旧密码被删除,现密码为空
8.测试
注意:在这里我遇到了Idea 启动Tomcat没反应,不调起控制台,运行按钮由绿色变灰色,经过检索最终在这里发现了解决方案,亲测有效,setting--->plugins--->输入Groovy,将其后面的勾去掉--->保存--->重启--->问题解决Idea 启动Tomcat没反应,不调起控制台,运行按钮由绿色变灰色没有调起控制台 - 简书 (jianshu.com)https://www.jianshu.com/p/1b3771818d16
登录优化这里我卡了很久,因输入旧密码后ajax没有正常工作,随后我进行了排查,发现请求地址与Servlet配置的地址不一致,改为一致后url其后加入method=pwdmodify后功能正常,错误原因为:no-referrer-when-downgrade
总条数查询SQL这里记得在and、order
前面加一个空格,我因为没加每次报错SQL错误,最后打印出来发现and和前面的字符连接在了一起,导致无法识别
使用构造器初始化时,正确写法:测试一切正常
private UserDaoFinalImpl udfi;
public userServiceFinalImpl() {
udfi = new UserDaoFinalImpl();
}
使用构造器初始化时,错误写法:测试不通过,报空指针异常
private UserDaoFinalImpl udfi;
public userServiceFinalImpl() {
UserDaoFinalImpl udfi = new UserDaoFinalImpl();
}
error页面五秒后跳转到首页,转自:jsp中的页面延时跳转+遇到的一些小问题_霓裳cc的博客-CSDN博客
//五秒后返回首页
<meta http-equiv="refresh" content="5;url=login.jsp">
<script type="text/javascript">
var t=5;
setInterval('jump()',1000);
function jump(){
if(t==0){
location="login.jsp";
}
document.getElementById('show').innerHTML=""+t+"秒后跳转到登陆页面";
t--;
}
</script>
<span id=show></span>
处理Date类型的数据
//处理Date
if ("creationDate".equals(columnLabel) | "modifyDate".equals(columnLabel) | "creationDate".equals(columnLabel)) {
//判断列值是否为空
if (obj != null) {
// 再次使用结果集rs,直接获取为Timestamp时间类型
Timestamp date = rs.getTimestamp(i + 1);
//创建日期格式
SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//格式化日期
String time = format1.format(date);
//Date类型转String类型
obj = format1.parse(time);
}
}
在编写bill的Servlet时出现:JasperException异常
后经排查是因bill这个javabean中少声明了一个变量,导致jsp获取不到值报错
//在bill中加入providerName变量及其get、set方法,并重写构造器与toString方法 private String providerName;将SQL由:select * from smbms_provider
改为:select b.*,p.proName as providerName from smbms_bill b,smbms_provider p where b.providerId=p.id
问题解决!
lower_case_table_names 是mysql设置大小写是否敏感的一个参数,0:区分大小写,1:不区分大小写
Alter TABLE 表名
Alter COLUMN 列名 varchar( 100 ) COLLATE Chinese_PRC_CS_AS