在项目开始前先将数据库中的关系表建立,先分析需要几个模块表,用户,图书,类别,租借,购买,简单的分成这五个。
用户表
CREATE TABLE IF NOT EXISTS `user`(
user_id INT(11) PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(20),
`password` VARCHAR(20),
grade INT(11),
phone VARCHAR(20),
user_type INT(11)
);
图书表
CREATE TABLE IF NOT EXISTS books (
book_id INT(11) PRIMARY KEY AUTO_INCREMENT,
book_name VARCHAR(50),
price DOUBLE(10,2),
store INT(11),
des VARCHAR(50),
book_type INT(11)
);
类别
CREATE TABLE IF NOT EXISTS `booktype`(
type_id INT(11) PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20),
default_date INT(11),
delay_money_per_day DOUBLE(10,2)
);
租借
CREATE TABLE IF NOT EXISTS borrow(
borrow_id INT(11) PRIMARY KEY AUTO_INCREMENT,
book_id INT(11),
borrow_date DATE,
back_date DATE,
delay_money DOUBLE(10,2)
);
购买
CREATE TABLE IF NOT EXISTS shop(
buy_id INT(11) PRIMARY KEY AUTO_INCREMENT,
book_id INT(11),
record_id INT(11),
buy_date DATE,
buy_num INT(11),
total_price DOUBLE(10,2)
);
在表中简单插入数据
建立工程
1,配置环境导入jar包
创建resources文件
db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.56.29:3306/qMall //连接的数据库地址
user=kb07 //数据库登陆用户名
pwd=ok //数据库登陆用户密码
mybatis-cfg.xml文件配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"></properties>//读取数据库属性
<typeAliases>
//起别名
<typeAlias type="cn.kgc.kb07.entity.User" alias="user"/>
<typeAlias type="cn.kgc.kb07.entity.Books" alias="book"/>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${pwd}"/>
</dataSource>
</environment>
</environments>
<mappers>//读取映射文件
<mapper resource="cn/kgc/kb07/dao/UserMapper.xml"></mapper>
<mapper resource="cn/kgc/kb07/dao/BookMapper.xml"></mapper>
</mappers>
</configuration>
2,建立实体类、实现接口和xml文件
这里以User为例
user实体类
public class User {
private int user_id;
private String user_name;
private int grade;
private String password;
private String phone;
private int user_type;
public int getUser_id() {return user_id;}
public String getPassword() {return password;}
public void setPassword(String password) {this.password = password;}
@Override
public String toString() {
return "User{" +
"user_id=" + user_id +
", user_name='" + user_name + '\'' +
", grade=" + grade +
", password='" + password + '\'' +
", phone='" + phone + '\'' +
", user_type=" + user_type +
'}';
}
public void setUser_id(int user_id) {this.user_id = user_id;}
public String getUser_name() { return user_name;}
public void setUser_name(String user_name) {this.user_name = user_name;}
public int getGrade() {return grade;}
public void setGrade(int grade) {this.grade = grade;}
public String getPhone() { return phone;}
public void setPhone(String phone) { this.phone = phone; }
public int getUser_type() {return user_type;}
public void setUser_type(int user_type) {this.user_type = user_type;}
}
UserDao接口
public interface UserDao {
User queryUserByNameAndPwd(@Param("user_name") String user_name, @Param("password") String password);
List<User> queryAllUser();
}
xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace指定关联的接口-->
<mapper namespace="cn.kgc.kb07.dao.UserDao">
<select id="queryUserByNameAndPwd" resultType="user" parameterType="string">
//对应接口的查询sql语句,id要与接口方法名一致,resultType为返回值类型,parameterType为传入值类型
select * from user where user_name=#{user_name} and password=#{password};
</select>
<select id="queryAllUser" resultType="user">
select * from user where user_type!=99;
</select>
</mapper>
实现登录功能
1,Sqlsession配置文件使用单例模式
通过这个接口你可以执行命令,得到映射,并且管理事务,SqlSession可以被SqlSessionFactory实例创建,这个SqlSessionFactory包括了所有的创建SqlSession实例的方法,SqlSessionFactoryBuilder可以通过XML文档,注释文件,或者手工编写的类来创建SqlSessionFactory实例.
public class MapperConfig {
private static SqlSessionFactory factory;
static{
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
try {
InputStream is= Resources.getResourceAsStream("mybatis-cfg.xml");
factory=builder.build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSession(){
return factory.openSession(true);
}
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
private MapperConfig(){ }
}
2,建立登陆jsp文件
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>登录页面</title>
</head>
<body>
<%
Object msg=session.getAttribute("msg");
Object o=session.getAttribute("user");
if(msg!=null&& msg.equals("登录成功")&&o!=null){
User u=(User)o;
%>
<h1>恭喜登录:${user.user_name}
${user.user_type==99?"超级管理员":""}</h1>
<%
if(u.getUser_type()==99){
%>
<p><a href="userList.do">点击展示所有用户</a></p>
<%
}else{
%>
<p><a href="bookList.jsp">客户入口</a> </p>
<%
}
} else{
%>
<form action="login.do" method="get">
<p>用户名</p>
<P><input type="text" name="username" required></P>
<p>密码</p>
<p><input type="text" name="password" required></p>
<p><input type="submit"></p>
</form>
<%
session.removeAttribute("msg");
}
%>
</body>
</html>
3,建立UserService,UserServlet文件
UserService实体文件
public class UserServiceImpl implements UserService{
private SqlSession session;
public UserServiceImpl(){
session= MapperConfig.getSession();
}
@Override
public User queryUser(String user_name,String password) {
User user=session.getMapper(UserDao.class).queryUserByNameAndPwd(user_name,password);
MapperConfig.closeSession(session);
return user;
}
}
UserServlet实体文件
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String user_name=req.getParameter("username");
String password=req.getParameter("password");
UserService service=new UserServiceImpl();
User user=service.queryUser(user_name,password);
System.out.println(user);
if(user!=null){
req.getSession().setAttribute("msg", "登录成功");
req.getSession().setAttribute("user", user);
resp.sendRedirect("index.jsp");
}else{
System.out.println(2);
req.getSession().setAttribute("msg", "登录失败");
resp.sendRedirect("index.jsp");
}
}
}
3,配置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_4_0.xsd"
version="4.0">
<servlet>
<servlet-name>userServlet</servlet-name>
<servlet-class>cn.kgc.kb07.servlet.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>userServlet</servlet-name>
<url-pattern>/login.do</url-pattern>
</servlet-mapping>
</web-app>
运行tomcat测试
提交后跳转本页面显示登陆成功,显示客户入口的链接,点击将展示所有图书详情