一.数据库设计
1.图书(id,书名,总数,书的描述)
create table books(
bookid int primarykey auto_increment,
bookname varchar(30) not null,
bookcounts varchar(30) not null,
bookdetail varchar(30));
2.用户(id,用户名,密码,借阅图书数量,借阅图书权限)
create table user(
userid int primarykey auto_increment,
username varchar(30) not null,
password varchar(30) not null,
userbookcounts int,
userenable int);
3.管理员(管理员名,密码)
create table manager(
managername varchar(30) primary key,
managerpassword varchar(30) not null);
4.图书用户关系表
create table userbook(
userbookuserid int,
userbookbookid int,
userbookdate date);
//创建联合主键
alter table userbook add constraint pk_userbook primary key(userbookuserid,userbookbookid);
//创建两个外键
alter table userbook add constraint fk_user foreign key(userbookuserid)
references user(userid);
alter table userbook add constraint fk_book foreign key(userbookbookid)
references book(bookid);
5.管理员用户关系表
create table manageruser(
managerusername varchar(30),
manageruserid int,
manageruserenable int);
//创建联合主键
alter table userbook add constraint pk_manageruser primary key(manageruserid,managerusername);
//创建两个外键
alter table userbook add constraint fk_usr foreign key(manageruserid)
references user(userid);
alter table userbook add constraint fk_book foreign key(managerusername)
references manager(managername);
二.工程创建
利用maven创建工程,导入需要的依赖,并处理静态资源导出问题。
<!--依赖:junit,数据库驱动,连接池,servlet,jsp,mybatis,mybatis-spring,spring-->
<dependencies>
<dependency>
<!--junit测试-->
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
<!--Servlet Jsp-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.2</version>
</dependency>
<!--Spring-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<!--Lombok 是添加注解直接生成构造函数-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!--AOP切面操作导入-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.13</version>
</dependency>
</dependencies>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
三.创建与数据库对应的实体类
分别创建各个数据库对应的实体类,对应字段,这比较简单。
在ssm框架中,dao层负责进行数据库的持久化操作,pojo是数据库对象实体类的集合,service层是将dao层的操作接口方法统一管理使用,contorller层主要负责将service接口方法使用并进行业务管理,实现前端页面和后台数据的交互。
四.配置文件
spring核心配置文件(主要进行service,dao,mvc的资源导入)
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<import resource="spring-dao.xml"/>
<import resource="spring-service.xml"/>
<import resource="spring-mvc.xml"/>
<!--spring核心配置文件-->
</beans>
spring-dao.xml配置文件(主要进行数据库配置文件的导入,sqlsessionfactory bean的属性导入,mybatis的配置文件的绑定)
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
<!--通过context名称空间实现配置文件的内容读取-->
<context:property-placeholder location="classpath:database.properties"/>
<!--1.关联数据库的配置文件-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" >
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!--私有属性-->
<property name="maxPoolPreparedStatementPerConnectionSize" value="30"/>
</bean>
<!--2.sqlSessionFactory-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!--绑定mybatis的配置文件-->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<!--配置dao接口扫描包,动态实现dao接口可以注入到spring容器中-->
<!--在这里实现了对dao包下的bookmapper的ioc注入-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!--注入 sqlSessionFactory-->
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
<!--扫描dao包-->
<property name="basePackage" value="com.hao.dao"/>
</bean>
</beans>
spring-mvc.xml(使用mvc名称空间进行controller层和前端页面的绑定)
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
<!--1.注解驱动-->
<mvc:annotation-driven/>
<!--2.静态资源过滤-->
<mvc:default-servlet-handler/>
<!--3.扫描包:controller-->
<context:component-scan base-package="com.hao.controller"/>
<!--4.视图解析器-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="viewClass" value="org.springframework.web.servlet.view.JstlView"/>
<property name="prefix" value="/WEB-INF/jsp/"/>
<property name="suffix" value=".jsp"/>
</bean>
</beans>
mybatis-config.xml(主要进行pojo层和mapper的绑定)
<?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>
<!--mybatis核心配置文件-->
<!--配置数据源 ,交给spring去做-->
<!--这个包下的东西自己取别名-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.hao.pojo"/>
</typeAliases>
<mappers>
<mapper class="com.hao.dao.BookMapper"/>
</mappers>
</configuration>
spring-service.xml(主要进行service层的bean扫描,以及对service层的mapper注入还有事务的配置)
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:component="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
<!--扫描service包下的所有资源-->
<component:component-scan base-package="com.hao.service"/>
<!--2.将所有的业务类,注入spring-->
<bean id = "bookServiceImpl" class="com.hao.service.BookServiceImpl">
<property name="bookMapper" ref="bookMapper"/>
</bean>
<bean id = "userServiceImpl" class="com.hao.service.UserServiceImpl">
<property name="userMapper" ref="userMapper"/>
</bean>
<!--3.声明式事务配置-->
<bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
在dao层bookmapper接口对应的.xml中先实现book的crud
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--一个mapper对应一个接口-->
<mapper namespace="com.hao.dao.BookMapper">
<insert id="addBook" parameterType="Books">
insert into ssmbuild.books(bookname, bookcounts, bookdetail)
values(#{bookName},#{bookCounts},#{bookDetail});
</insert>
<delete id="deleteBookById" parameterType="int">
delete from ssmbuild.books
where bookid = #{bookId};
</delete>
<update id="updateBook" parameterType="Books">
update ssmbuild.books set
bookname = #{bookName},bookcounts = #{bookCounts},bookdetail = #{bookDetail}
where bookid = #{bookId};
</update>
<select id="queryBookById" resultType="Books">
select * from ssmbuild.books
where bookid = #{bookId};
</select>
<select id="queryAllBooks" resultType="Books">
select * from ssmbuild.books;
</select>
<select id="queryBookByName" resultType="Books">
select * from ssmbuild.books where bookname=#{bookName};
</select>
</mapper>
controller层实现数据交互与页面跳转
package com.hao.controller;
import com.hao.pojo.Books;
import com.hao.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.List;
@Controller
@RequestMapping("/book")
public class BookController {
//controller层调service层
@Autowired
@Qualifier("bookServiceImpl")
private BookService bookService;
//查询全部书籍,并返回到数据展示页面
@RequestMapping("/allBook")
public String list(Model model){
List<Books> books = bookService.queryAllBooks();
model.addAttribute("list",books);
return "allBook";
}
//进行添加书籍页面的展示
@RequestMapping("/toAddBook")
public String toAddPaper(){
return "addBook";
}
//书籍添加,之后进行页面展示的跳转
@RequestMapping("/addBook")
public String addBook(Books book){
bookService.addBook(book);
return "redirect:/book/allBook";//必须进行重定向,再次请求。
}
@RequestMapping("/delBook")
public String deleteBookById(int id){
bookService.deleteBookById(id);
return "redirect:/book/allBook";
}
@RequestMapping("/toUpdateBook")
public String toUpdateBook(int id,Model model){
Books book = bookService.queryBookById(id);
model.addAttribute("qbook",book);
return "updateBook";
}
@RequestMapping("/updateBook")
public String updateBook(Books books){
bookService.updateBook(books);
return "redirect:/book/allBook";
}
@RequestMapping("/queryBook")
public String queryBook(String queryBookName,Model model){
List<Books> books = bookService.queryBookByName(queryBookName);
model.addAttribute("list",books);
return "allBook";
}
}