ssm整合歌手小项目
数据库设计
创建数据库存放歌手
CREATE DATABASE ssmbuild;
USE ssmbuild;
DROP TABLE singer;
CREATE TABLE singer(
singer_id INT(10) NOT NULL AUTO_INCREMENT COMMENT '歌手ID',
singer_name VARCHAR(32) NOT NULL COMMENT '歌手姓名',
singer_sex VARCHAR(11) NOT NULL COMMENT '歌手性别',
singer_age INT(10) NOT NULL COMMENT '歌手年龄',
introduction VARCHAR(200) NOT NULL COMMENT '描述',
PRIMARY KEY (singer_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO singer(`singer_name`,`singer_sex`,`singer_age`,`introduction`) VALUES
('邓紫棋','女',30,'香港著名女歌手'),
('刘德华','男',45,'四大天王');
触发器
checksex:若输入不是男女则默认设置为男
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `ssmbuild`.`checksex` BEFORE INSERT
ON `ssmbuild`.`singer`
FOR EACH ROW BEGIN
IF(new.singer_sex<>'男' AND new.singer_sex<>'女')
THEN
SET new.singer_sex = '男';
END IF;
END$$
DELIMITER ;
checkage:输入年龄小于80岁,否则报错
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `ssmbuild`.`checkage` BEFORE INSERT
ON `ssmbuild`.`singer`
FOR EACH ROW BEGIN
IF new.singer_age>80
THEN
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT='年龄超出范围,必须小于80';
END IF;
END;
$$
DELIMITER ;
maven项目环境搭配
1.新建一Maven项目!ssmbuild , 添加web的支持
2.导入相关的pom依赖
<dependencies>
<!--Junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</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>
</dependencies>
3.Maven资源过滤设置
<build>
<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>
</build>
4.建立基本结构和配置框架
配置
-
mybatis-config.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> </configuration>
-
applicationContext.xml
<?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"> </beans>
基本包
- com.huayan.pojo
- com.huayan.dao
- com.huayan.service
- com.huayan.controller
Mybatis层
1.数据库配置文件 database.properties(连接数据库)
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssmbuild?useSSL=true&useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=root
2.IDEA关联数据库
3.编写MyBatis的核心配置文件
<?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>
<typeAliases>
<package name="com.huayan.pojo"/>
</typeAliases>
<mappers>
<mapper resource="com.huayan.dao.SingerMapper.xml"/>
</mappers>
</configuration>
4.编写数据库对应的实体类 com.kuang.pojo.Books
package com.huayan.pojo;
public class Singer {
//歌手id
private int singerId;
//歌手姓名
private String singerName;
//歌手性别
private String singerSex;
//歌手年龄
private int singerAge;
//歌手简介
private String introduction;
//get
//set
//有参
//无参
}
5.编写Dao层的 Mapper接口!
package com.huayan.dao;
import com.huayan.pojo.Singer;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface SingerMapper {
//增加
public int addSinger(Singer singer);
//删除
public int deleteSinger(@Param("singerId") int id);
//根据id查找
public Singer querySingerById(@Param("singerId") int id);
//改
public Singer updateSinger(Singer singer);
//查找所有
public List<Singer> queryAllSinger();
}
6.编写接口对应的 Mapper.xml 文件,导入MyBatis的包;
导入mabatis-config.xml
<mappers>
<mapper resource="com.huayan.dao.SingerMapper.xml"/>
</mappers>
Mapper.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">
<mapper namespace="com.huayan.dao.SingerMapper">
<resultMap id="SingerMapper" type="Singer">
<id column="singer_id" property="singerId"/>
<result column="singer_name" property="singerName"/>
<result column="singer_sex" property="singerSex"/>
<result column="singer_age" property="singerAge"/>
<result column="introduction" property="introduction"/>
</resultMap>
<sql id="Base_List">
singer_id,singer_name,singer_sex,singer_age,introduction
</sql>
<!--增加-->
<insert id="addSinger" parameterType="com.huayan.pojo.Singer">
insert into singer
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="singerId != null">
singer_id,
</if>
<if test="singerName != null">
singer_name,
</if>
<if test="singerSex != null">
singer_sex,
</if>
<if test="singerAge != null">
singer_age,
</if>
<if test="introduction != null">
introduction,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="singerId != null">
#{singerId},
</if>
<if test="singerName != null">
#{singerName},
</if>
<if test="singerSex != null">
#{singerSex},
</if>
<if test="singerAge != null">
#{singerAge},
</if>
<if test="introduction != null">
#{introduction},
</if>
</trim>
</insert>
<!--删除-->
<delete id="deleteSinger" parameterType="java.lang.Integer">
delete from singer where singer_id = #{singerId}
</delete>
<!--修改-->
<update id="updateSinger" parameterType="com.huayan.pojo.Singer">
update singer
<set>
<if test="singerName != null">
singer_name = #{singerName},
</if>
<if test="singerSex != null">
singer_sex = #{singerSex},
</if>
<if test="singerAge != null">
singer_age = #{singerAge},
</if>
<if test="introduction != null">
introduction = #{introduction},
</if>
</set>
</update>
<!--查找所有-->
<select id="queryAllSinger" resultMap="SingerMapper">
select <include refid="Base_List"/>
from singer
</select>
<!--根据id查找歌手-->
<select id="querySingerById" resultMap="SingerMapper">
select <include refid="Base_List"/>
from singer
where singer_id = #{singerId}
</select>
</mapper>
7.编写Service层的接口和实现类
接口
package com.huayan.service;
import com.huayan.pojo.Singer;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface SingerService {
//增加
public boolean addSinger(Singer singer);
//删除
public boolean deleteSinger(@Param("singerId") int id);
//改
public Singer updateSinger(Singer singer);
//根据id查找
public Singer querySingerById(@Param("singerId") int id);
//查找所有
public List<Singer> queryAllSinger();
}
实现类
package com.huayan.service.impl;
import com.huayan.dao.SingerMapper;
import com.huayan.pojo.Singer;
import com.huayan.service.SingerService;
import java.util.List;
public class SingerServiceImpl implements SingerService {
//调用dao层的操作,设置一个set接口,方便Spring管理
private SingerMapper singerMapper;
public void setSingerMapper(SingerMapper singerMapper) {
this.singerMapper = singerMapper;
}
public boolean addSinger(Singer singer) {
return singerMapper.addSinger(singer)>0;
}
public boolean deleteSinger(int id) {
return singerMapper.deleteSinger(id)>0;
}
public Singer updateSinger(Singer singer) {
return singerMapper.updateSinger(singer);
}
public Singer querySingerById(int id) {
return singerMapper.querySingerById(id);
}
public List<Singer> queryAllSinger() {
return singerMapper.queryAllSinger();
}
}
Spring层
1.配置Spring整合MyBatis()编写Spring整合Mybatis的相关的配置文件;spring-dao.xml
spring-dao.xml
<?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">
<!-- 配置整合mybatis -->
<!-- 1.关联数据库文件 -->
<context:property-placeholder location="classpath:database.properties"/>
<!-- 2.数据库连接池 -->
<!--数据库连接池
dbcp 半自动化操作 不能自动连接
c3p0 自动化操作(自动的加载配置文件 并且设置到对象里面)
-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- 配置连接池属性 -->
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- c3p0连接池的私有属性 -->
<property name="maxPoolSize" value="30"/>
<property name="minPoolSize" value="10"/>
<!-- 关闭连接后不自动commit -->
<property name="autoCommitOnClose" value="false"/>
<!-- 获取连接超时时间 -->
<property name="checkoutTimeout" value="10000"/>
<!-- 当获取连接失败重试次数 -->
<property name="acquireRetryAttempts" value="2"/>
</bean>
<!-- 3.配置SqlSessionFactory对象 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入数据库连接池 -->
<property name="dataSource" ref="dataSource"/>
<!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<!-- 4.配置扫描Dao接口包,动态实现Dao接口注入到spring容器中 -->
<!--解释 :https://www.cnblogs.com/jpfss/p/7799806.html-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- 注入sqlSessionFactory -->
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
<!-- 给出需要扫描Dao接口包 -->
<property name="basePackage" value="com.huayan.dao"/>
</bean>
</beans>
2.Spring整合service层
spring-service.xml
<?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
http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 扫描service相关的bean -->
<context:component-scan base-package="com.huayan.service" />
<!--SingerServiceImpl注入到IOC容器中-->
<bean id="SingerServiceImpl" class="com.huayan.service.impl.SingerServiceImpl">
<property name="singerMapper" ref="singerMapper"/>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- 注入数据库连接池 -->
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
SpringMVC层
1.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">
<!--DispatcherServlet-->
<servlet>
<servlet-name>DispatcherServlet</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<!--一定要注意:我们这里加载的是总的配置文件,之前被这里坑了!-->
<param-value>classpath:applicationContext.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>DispatcherServlet</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<!--encodingFilter-->
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>
org.springframework.web.filter.CharacterEncodingFilter
</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>utf-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!--Session过期时间-->
<session-config>
<session-timeout>15</session-timeout>
</session-config>
</web-app>
2.spring-mvc.xml
<?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"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/mvc
https://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!-- 配置SpringMVC -->
<!-- 1.开启SpringMVC注解驱动 -->
<mvc:annotation-driven />
<!-- 2.静态资源默认servlet配置-->
<mvc:default-servlet-handler/>
<!-- 3.配置jsp 显示ViewResolver视图解析器 -->
<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>
<!-- 4.扫描web相关的bean -->
<context:component-scan base-package="com.huayan.controller" />
</beans>
3.spring整合文件
applicationContext.xml
<?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="classpath:spring-dao.xml"/>
<import resource="classpath:spring-service.xml"/>
<import resource="classpath:spring-mvc.xml"/>
</beans>
Controller 和 视图层编写
SingerController
1.查询歌手 controller
@Controller
@RequestMapping("/singer")
public class SingerController {
@Autowired
@Qualifier("SingerServiceImpl")
private SingerService singerService;
@RequestMapping("/allSinger")
public String singers(Model model){
List<Singer> singers = singerService.queryAllSinger();
model.addAttribute("singers",singers);
return "allSinger";
}
2.首页 index.jsp
<%--
Created by IntelliJ IDEA.
User: 花言吖
Date: 2021/1/31
Time: 23:22
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>首页</title>
<style type="text/css">
a {
text-decoration: none;
color: black;
font-size: 18px;
}
h3 {
width: 180px;
height: 38px;
margin: 100px auto;
text-align: center;
line-height: 38px;
background: deepskyblue;
border-radius: 4px;
}
</style>
</head>
<body>
<h3>
<a href="${pageContext.request.contextPath}/singer/allSinger">点击进入</a>
</h3>
</body>
</html>
3.歌手列表页面 allSinger.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
Created by IntelliJ IDEA.
User: 花言吖
Date: 2021/1/31
Time: 23:22
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<!-- 新 Bootstrap 核心 CSS 文件 -->
<link href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<title>歌手页面</title>
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<div class="page-header">
<h1>
<small>歌手列表 —— 显示所有歌手</small>
</h1>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4 column">
<a class="btn btn-primary" href="${pageContext.request.contextPath}/singer/toAddSinger">新增</a>
</div>
</div>
<div class="row clearfix">
<div class="col-md-12 column">
<table class="table table-hover table-striped">
<thead>
<tr>
<th>歌手编号</th>
<th>歌手名字</th>
<th>歌手性别</th>
<th>歌手年龄</th>
<th>简介</th>
<th>操作</th>
</tr>
</thead>
<tbody>
<c:forEach var="singer" items="${singers}">
<tr>
<td>${singer.singerId}</td>
<td>${singer.singerName}</td>
<td>${singer.singerSex}</td>
<td>${singer.singerAge}</td>
<td>${singer.introduction}</td>
<td>
<a href="${pageContext.request.contextPath}/singer/toUpdateSinger?id=${singer.singerId}">更改</a> |
<a href="${pageContext.request.contextPath}/singer/deleteSinger/${singer.singerId}">删除</a>
</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>
4.添加歌手 controller
@RequestMapping("/toAddSinger")
public String toAddSinger (){
return "addSinger";
}
@RequestMapping("/addSinger")
public String add(Singer singer){
singerService.addSinger(singer);
return "redirect:/singer/allSinger";
}
5.添加歌手页面 addSinger.jsp
<%--
Created by IntelliJ IDEA.
User: 花言吖
Date: 2021/2/1
Time: 17:51
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>新增歌手</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- 引入 Bootstrap -->
<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<div class="page-header">
<h1>
<small>新增歌手</small>
</h1>
</div>
</div>
</div>
<form action="${pageContext.request.contextPath}/singer/addSinger" method="post">
歌手名字:<input type="text" name="singerName" required><br><br><br>
歌手性别:<input type="text" name="singerSex" required><br><br><br>
歌手年龄:<input type="text" name="singerAge" required><br><br><br>
歌手简介:<input type="text" name="introduction" required><br><br><br>
<input type="submit" value="添加">
</form>
</div>
</body>
</html>
6.修改歌手 controller
@RequestMapping("/toUpdateSinger")
public String toUpdateSinger(Model model,int id){
Singer singer = singerService.querySingerById(id);
model.addAttribute("Qsinger",singer);
return "/updateSinger";
}
@RequestMapping("/updateSinger")
public String updateSinger(Singer singer){
singerService.updateSinger(singer);
return "redirect:/singer/allSinger";
}
7.修改歌手页面 updateSinger.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>修改信息</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- 引入 Bootstrap -->
<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<div class="page-header">
<h1>
<small>修改信息</small>
</h1>
</div>
</div>
</div>
<form action="${pageContext.request.contextPath}/singer/updateSinger" method="post">
<input type="hidden" name="singerId" value="${Qsinger.singerId}"/>
歌手姓名:<input type="text" name="singerName" value="${Qsinger.singerName}"/>
歌手性别:<input type="text" name="singerSex" value="${Qsinger.singerSex}"/>
歌手年龄:<input type="text" name="singerAge" value="${Qsinger.singerAge}"/>
简介:<input type="text" name="introduction" value="${Qsinger.introduction}"/>
<input type="submit" value="修改"/>
</form>
</div>
8.删除歌手 controller
@RequestMapping("/deleteSinger/{singerId}")
public String deleteSinger(@PathVariable("singerId") int id){
singerService.deleteSinger(id);
return "redirect:/singer/allSinger";
}
9.搜索功能 controller
@RequestMapping("/querySinger")
public String querySinger(String singerName,Model model){
List<Singer> singers = singerService.queryByName("%" + singerName + "%");
if(singers.isEmpty()){
singers = singerService.queryAllSinger();
model.addAttribute("error","未查到您所想要的歌手");
}
model.addAttribute("singers",singers);
return "allSinger";
}
搜索框 jsp
<%--搜索框--%>
<div class="col-md-8 column">
<form method="post" class="form-inline" action="${pageContext.request.contextPath}/singer/querySinger" style="float: right;">
<span style="color: red;font-weight: bold">${error}</span>
<input type="text" name="singerName" class="form-control" placeholder="请输入要查询的歌手姓名">
<input type="submit" class="btn btn-primary" value="查询">
</form>
</div>