前言
项目5-练习项目
0.mysql5.7.35
https://dev.mysql.com/downloads/windows/installer/5.7.html
(mysql-installer-community-5.7.35.0.msi)
1.需求分析
1.记账管理
2.数据库创建
表名:bills 中文表名称:账单信息表
主键:id
序号 字段名称 字段说明 类型 长度 属性 备注
1 id 账单id number 10 主键 使用序号赋值
2 title 账单标题 varchar2 50 非空
3 bill_time 记账时间 date 非空
4 type_id 账单类别 varchar2 10 非空 引用bill_type表的 主键
5 price 账单金额 number 10,2 非空
explain 账单说明 varchar2 500
表名:bill_type 中文表名称:账单类型表
主键:id
序号 字段名称 字段说明 类型 长度 属性 备注
1 id 类型id number 10 主键 使用序列赋值
2 name 类型名称 varchar2 50 非空
功能补充;
在"说明"列后加一列, 叫"操作". 用户可以删除或修改数据
分页显示:上一页 下一页 首页 尾页 当前页码 总页数 总条数
数据库:mytest
查询
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50722
Source Host : localhost:3306
Source Database : mytest
Target Server Type : MYSQL
Target Server Version : 50722
File Encoding : 65001
Date: 2020-06-11 19:45:57
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `bills`
-- ----------------------------
DROP TABLE IF EXISTS `bills`;
CREATE TABLE `bills` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) DEFAULT NULL,
`billtime` date DEFAULT NULL,
`typeid` int(11) DEFAULT NULL,
`price` double DEFAULT NULL,
`explains` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of bills
-- ----------------------------
INSERT INTO `bills` VALUES ('2', '快递费2', '2000-02-01', '1', '11', '邮寄快递费用');
INSERT INTO `bills` VALUES ('3', '快递费3', '2000-03-01', '1', '22', '邮寄快递费用');
INSERT INTO `bills` VALUES ('4', '快递费4', '2000-04-01', '2', '33', '邮寄快递费用');
INSERT INTO `bills` VALUES ('5', '快递费5', '2000-05-01', '2', '44', '邮寄快递费用');
INSERT INTO `bills` VALUES ('6', '快递费6', '2000-06-01', '3', '55', '邮寄快递费用');
INSERT INTO `bills` VALUES ('7', '快递费7', '2000-07-01', '3', '66', '邮寄快递费用');
INSERT INTO `bills` VALUES ('8', '快递费8', '2000-08-01', '4', '77', '邮寄快递费用');
-- ----------------------------
-- Table structure for `billtype`
-- ----------------------------
DROP TABLE IF EXISTS `billtype`;
CREATE TABLE `billtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bname` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of billtype
-- ----------------------------
INSERT INTO `billtype` VALUES ('1', '支出');
INSERT INTO `billtype` VALUES ('2', '收入');
INSERT INTO `billtype` VALUES ('3', '转账');
INSERT INTO `billtype` VALUES ('4', '借出');
INSERT INTO `billtype` VALUES ('5', '借入');
INSERT INTO `billtype` VALUES ('6', '还入');
INSERT INTO `billtype` VALUES ('7', '还出');
添加初始数据
参考主页面
billtype
1 支出
2 收入
3 转账
4 借出
5 借入
6 还入
6 还出
bills
3.环境搭建
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xzk</groupId>
<artifactId>ssm_project</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>ssm_project Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
<springversion>5.0.8.RELEASE</springversion>
</properties>
<dependencies>
<!-- 加入ServletAPI -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<!-- MySQL依赖 start -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!-- 加入MyBatis 依赖 start -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<!-- 引入Spring(包含SpringMVC) 依赖 start -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${springversion}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${springversion}</version>
</dependency>
<!-- 引用插件依赖：MyBatis整合Spring,如果mybatis版本在3.4及以上版本
mybatis-spring的版本要在1.3以上 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- JSTL -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- 德鲁伊数据连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
<!-- pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
<!--处理json-->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.9.5</version>
</dependency>
<!--javaee-->
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>7.0</version>
<scope>provided</scope>
</dependency>
<!--文件上传下载-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
</dependencies>
<build>
<finalName>ssm_project</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.5</version>
<configuration>
<!--配置文件的路径-->
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
</configuration>
<dependencies>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
</dependencies>
</plugin>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
resources/spring.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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:tx="http://www.springframework.org/schema/tx"
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
http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
">
<!-- 1.配置数据源-->
<bean id="db" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf-8"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!--2.创建sqlSessionFactory-->
<bean id="fac" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="db"></property>
<property name="configLocation" value="classpath:mybatis.xml"></property> <!--指定mybatis的配置文件路径-->
<property name="mapperLocations" value="classpath:mapper/*.xml"></property><!--指定mybatis的mapper文件路径-->
</bean>
<!--3.创建MapperScannerConfigurer,用于省略dao的实现类-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="fac"></property>
<property name="basePackage" value="com.yhp.dao"></property><!--接口所在包的路径-->
</bean>
<!--4.配置事务-->
<bean id="mytx" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="db"></property>
</bean>
<tx:annotation-driven transaction-manager="mytx"></tx:annotation-driven>
<!--5.启用springmvc注解-->
<mvc:annotation-driven></mvc:annotation-driven>
<!--6.配置视图解析器-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/"/>
<property name="suffix" value=".jsp"></property>
</bean>
<!--7.扫描注解包-->
<context:component-scan base-package="com.yhp"></context:component-scan>
<!--8.配置静态资源访问-->
<mvc:default-servlet-handler></mvc:default-servlet-handler>
</beans>
mybatis.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>
<typeAliases>
<!--<typeAlias type="com.yhp.bean.Student" alias="stu"></typeAlias>-->
<package name="com.yhp.bean"></package>
</typeAliases>
<plugins>
<!-- PageHelper4.1.6 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
</configuration>
generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<!-- 配置生成器 -->
<generatorConfiguration>
<!--数据库驱动jar -->
<classPathEntry
location="F:\kaikeba\maven\maven_repository\mysql\mysql-connector-java\5.1.46\mysql-connector-java-5.1.46.jar" />
<context id="MyBatis" targetRuntime="MyBatis3">
<!--去除注释 -->
<commentGenerator>
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!--数据库连接 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mytest"
userId="root"
password="123456">
</jdbcConnection>
<!--生成实体类 指定包名 以及生成的地址 （可以自定义地址，但是路径不存在不会自动创建
使用Maven生成在target目录下，会自动创建） -->
<javaModelGenerator targetPackage="com.yhp.bean"
targetProject="D:\ssm_project\src\main\java">
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!--生成SQLmapper文件 -->
<sqlMapGenerator targetPackage="mapper"
targetProject="D:\ssm_project\src\main\resources">
</sqlMapGenerator>
<!--生成Dao文件,生成接口 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.yhp.dao"
targetProject="D:\ssm_project\src\main\java">
</javaClientGenerator>
<table tableName="bills" enableCountByExample="false"
enableUpdateByExample="false" enableDeleteByExample="false"
enableSelectByExample="false" selectByExampleQueryId="false">
</table>
<table tableName="billtype" enableCountByExample="false"
enableUpdateByExample="false" enableDeleteByExample="false"
enableSelectByExample="false" selectByExampleQueryId="false">
</table>
</context>
</generatorConfiguration>
resources/mapper/BillsMapper.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.yhp.dao.BillsMapper">
<resultMap id="BaseResultMap" type="com.yhp.bean.Bills">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="title" jdbcType="VARCHAR" property="title" />
<result column="billtime" jdbcType="DATE" property="billtime" />
<result column="typeid" jdbcType="INTEGER" property="typeid" />
<result column="price" jdbcType="DOUBLE" property="price" />
<result column="explains" jdbcType="VARCHAR" property="explains" />
</resultMap>
<resultMap id="rs1" type="bills">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="title" jdbcType="VARCHAR" property="title" />
<result column="billtime" jdbcType="DATE" property="billtime" />
<result column="typeid" jdbcType="INTEGER" property="typeid" />
<result column="price" jdbcType="DOUBLE" property="price" />
<result column="explains" jdbcType="VARCHAR" property="explains" />
<association property="billtype" javaType="com.yhp.bean.Billtype">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="bname" jdbcType="VARCHAR" property="bname" />
</association>
</resultMap>
<select id="getBills" resultMap="rs1">
select * from bills b,billtype t where b.typeid=t.id
<if test="tid!=-1">
and t.id=#{tid}
</if>
<if test="begin!=null and begin!=''">
and b.billtime>=#{begin}
</if>
<if test="end!=null and end!=''">
and b.billtime <![CDATA[ <= ]]> #{end}
</if>
</select>
<sql id="Base_Column_List">
id, title, billtime, typeid, price, explains
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from bills
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from bills
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.yhp.bean.Bills">
insert into bills (id, title, billtime,
typeid, price, explains
)
values (#{id,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}, #{billtime,jdbcType=DATE},
#{typeid,jdbcType=INTEGER}, #{price,jdbcType=DOUBLE}, #{explains,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="com.yhp.bean.Bills">
insert into bills
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="title != null">
title,
</if>
<if test="billtime != null">
billtime,
</if>
<if test="typeid != null">
typeid,
</if>
<if test="price != null">
price,
</if>
<if test="explains != null">
explains,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="title != null">
#{title,jdbcType=VARCHAR},
</if>
<if test="billtime != null">
#{billtime,jdbcType=DATE},
</if>
<if test="typeid != null">
#{typeid,jdbcType=INTEGER},
</if>
<if test="price != null">
#{price,jdbcType=DOUBLE},
</if>
<if test="explains != null">
#{explains,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.yhp.bean.Bills">
update bills
<set>
<if test="title != null">
title = #{title,jdbcType=VARCHAR},
</if>
<if test="billtime != null">
billtime = #{billtime,jdbcType=DATE},
</if>
<if test="typeid != null">
typeid = #{typeid,jdbcType=INTEGER},
</if>
<if test="price != null">
price = #{price,jdbcType=DOUBLE},
</if>
<if test="explains != null">
explains = #{explains,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.yhp.bean.Bills">
update bills
set title = #{title,jdbcType=VARCHAR},
billtime = #{billtime,jdbcType=DATE},
typeid = #{typeid,jdbcType=INTEGER},
price = #{price,jdbcType=DOUBLE},
explains = #{explains,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
resources/mapper/BilltypeMapper.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.yhp.dao.BilltypeMapper">
<resultMap id="BaseResultMap" type="com.yhp.bean.Billtype">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="bname" jdbcType="VARCHAR" property="bname" />
</resultMap>
<select id="getTypes" resultMap="BaseResultMap">
select * from billtype
</select>
<sql id="Base_Column_List">
id, bname
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from billtype
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from billtype
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.yhp.bean.Billtype">
insert into billtype (id, bname)
values (#{id,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.yhp.bean.Billtype">
insert into billtype
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="bname != null">
bname,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="bname != null">
#{bname,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.yhp.bean.Billtype">
update billtype
<set>
<if test="bname != null">
bname = #{bname,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.yhp.bean.Billtype">
update billtype
set bname = #{bname,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
com.yhp.bean-
Bills
package com.yhp.bean;
import java.util.Date;
//多方
public class Bills {
private Integer id;
private String title;
private Date billtime;
private Integer typeid;
private Double price;
private String explains;
public Billtype getBilltype() {
return billtype;
}
public void setBilltype(Billtype billtype) {
this.billtype = billtype;
}
private Billtype billtype;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title == null ? null : title.trim();
}
public Date getBilltime() {
return billtime;
}
public void setBilltime(Date billtime) {
this.billtime = billtime;
}
public Integer getTypeid() {
return typeid;
}
public void setTypeid(Integer typeid) {
this.typeid = typeid;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getExplains() {
return explains;
}
public void setExplains(String explains) {
this.explains = explains == null ? null : explains.trim();
}
}
Billtype
package com.yhp.bean;
import java.util.List;
//一方
public class Billtype {
private Integer id;
private String bname;
public List<Bills> getBillsList() {
return billsList;
}
public void setBillsList(List<Bills> billsList) {
this.billsList = billsList;
}
private List<Bills> billsList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBname() {
return bname;
}
public void setBname(String bname) {
this.bname = bname == null ? null : bname.trim();
}
}
com.yhp.dao-
BillsMapper
package com.yhp.dao;import com.yhp.bean.Bills;import java.util.List;import java.util.Map;public interface BillsMapper { //查询所有的账单 public List<Bills> getBills(Map map); int deleteByPrimaryKey(Integer id);//主键删除 int insert(Bills record);//新增 int insertSelective(Bills record);//动态新增 Bills selectByPrimaryKey(Integer id);//主键查询 int updateByPrimaryKeySelective(Bills record); //动态更新语句 int updateByPrimaryKey(Bills record);//更新}
BilltypeMapper
package com.yhp.dao;import com.yhp.bean.Billtype;import java.util.List;public interface BilltypeMapper { //查询所有的账单 public List<Billtype> getTypes(); int deleteByPrimaryKey(Integer id); int insert(Billtype record); int insertSelective(Billtype record); Billtype selectByPrimaryKey(Integer id); int updateByPrimaryKeySelective(Billtype record); int updateByPrimaryKey(Billtype record);}
+MavenName:mybatisCommand line: mybatis-generator:generate -e
执行指令
4.查询数据1
web.xml
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaeehttp://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5"> <display-name>Archetype Created Web Application</display-name> <!--1.配置前端控制器--> <servlet> <servlet-name>aa</servlet-name> <servlet-class> org.springframework.web.servlet.DispatcherServlet </servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:spring.xml</param-value> </init-param> </servlet> <servlet-mapping> <servlet-name>aa</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <!--2.处理post乱码--> <filter> <filter-name>bb</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>bb</filter-name> <url-pattern>/*</url-pattern> </filter-mapping></web-app>
index.jsp
<html><body><script type="text/javascript"> location.href="/gettypes";</script></body></html>
controller/BillsController
@RequestMapping("/gettypes") public String gettypes(ModelMap map){ //1.查询所有的类型 List<Billtype> types = typesService.getTypes(); //2.查询所有的账单 PageInfo<Bills> info = billsService.getBills(-1, null, null, 1, PageUtil.PAGESIZE); //3.保存数据给前台 map.addAttribute("types",types); map.addAttribute("info",info); return "show"; }
show.jsp
<h1>记账管理</h1>
service/BillsService
//查询所有的账单public List<Bills> getBills();
service/BillTypesService
//查询所有的账单public List<Bills> getTypes();
service/impl/BillsServiceImpl
@Overridepublic List<Bills> getBills(){ return null;}
service/impl/BillTypeServiceImpl
@Override List<Billtype> getTypes() { return null;}
dao/BilltypeMapper
//查询所有的账单public List<Billtype> getTypes();
dao/BillsMapper
//查询所有的账单public List<Bills> getTypes();
逐层调用
controller/BillsController
@Controllerpublic class BillsController { @Resource private BillTypesService typesService; @Resource private BillsService billsService; @RequestMapping("/gettypes") public String gettypes(ModelMap map){
service/impl/BillTypesServiceImpl
@Servicepublic class BillTypesServiceImpl implements BillTypesService { @Resource private BilltypeMapper billtypeMapper; @Override public List<Billtype> getTypes() { return billtypeMapper.getTypes(); }}
service/impl/BillsServiceImpl
@Servicepublic class BillsServiceImpl implements BillsService { @Resource private BillsMapper billsMapper; @Override public List<Bills> getBills() { return billsMapper.getBills(); }
BillsMapper.xml
<select id="getBills" resultMap="BaseResultMap"> select * from bills</select>
BillTypesMapper.xml
<select id="getTypes" resultMap="BaseResultMap"> select * from billtype</select>
BillsController
@Controllerpublic class BillsController { @Resource private BillTypesService typesService; @Resource private BillsService billsService; @RequestMapping("/gettypes") public String gettypes(ModelMap map){
show.jsp
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %><p> <form> 类型: <select> <option value="-1">不限</option> <c:forEach items="${types}" var="tp"> <option value="${tp.id}">${tp.bname}</option> </c:forEach> </select> 时间: 从<input type="text" name="begin">到<input type="text" name="end"> <input type="submit" value="搜索"> </form> <input type="button" value="记账"></p><table border="1" width="500"> <tr> <td>标题</td> <td>时间</td> <td>类别</td> <td>金额</td> <td>说明</td> <td>操作</td> </tr> <c:forEach items="${info.list}" var="bill"> <tr> <td>${bill.title}</td> <td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate></td> <td>类别</td> <td>${bill.price</td> <td>${bill.explains</td> <td>删除 修改</td> </tr> </c:forEach></table>
启动服务器
5.查询数据2
类别问题
一方和多方
Bills && Billtype
BillsMapper.xml
<resultMap id="rs1" type="bills"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="title" jdbcType="VARCHAR" property="title" /> <result column="billtime" jdbcType="DATE" property="billtime" /> <result column="typeid" jdbcType="INTEGER" property="typeid" /> <result column="price" jdbcType="DOUBLE" property="price" /> <result column="explains" jdbcType="VARCHAR" property="explains" /> <association property="billtype" javaType="com.yhp.bean.Billtype"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="bname" jdbcType="VARCHAR" property="bname" /> </association> </resultMap> <select id="getBills" resultMap="rs1"> select * from bills b,billtype t where b.typeid=t.id </select>
show.jsp
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<p>
<form>
类型:
<select>
<option value="-1">不限</option>
<c:forEach items="${types}" var="tp">
<option value="${tp.id}">${tp.bname}</option>
</c:forEach>
</select>
时间:
从<input type="text" name="begin">到<input type="text" name="end">
<input type="submit" value="搜索">
</form>
<input type="button" value="记账">
</p>
<table border="1" width="500">
<tr>
<td>标题</td>
<td>时间</td>
<td>类别</td>
<td>金额</td>
<td>说明</td>
<td>操作</td>
</tr>
<c:forEach items="${info.list}" var="bill">
<tr>
<td>${bill.title}</td>
<td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate></td>
<td>${bill.billtype.bname}</td>
<td>${bill.price</td>
<td>${bill.explains</td>
<td>删除 修改</td>
</tr>
</c:forEach>
</table>
6.查询数据3
金额问题
show.jsp
<p>
<form>
类型:
<select>
<option value="-1">不限</option>
<c:forEach items="${types}" var="tp">
<option value="${tp.id}">${tp.bname}</option>
</c:forEach>
</select>
时间:
从<input type="text" name="begin">到<input type="text" name="end">
<input type="submit" value="搜索">
</form>
<input type="button" value="记账">
</p>
<table border="1" width="500">
<tr>
<td>标题</td>
<td>时间</td>
<td>类别</td>
<td>金额</td>
<td>说明</td>
<td>操作</td>
</tr>
<c:forEach items="${info.list}" var="bill">
<tr>
<td>${bill.title}</td>
<td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate> </td>
<td>${bill.billtype.bname}</td>
<td>
<c:choose>
<c:when test="${bill.billtype.bname=='支出'||bill.billtype.bname=='借出'||bill.billtype.bname=='还出'}">
-${bill.price}
</c:when>
<c:when test="${bill.billtype.bname=='收入'||bill.billtype.bname=='借入'||bill.billtype.bname=='还入'}">
+${bill.price}
</c:when>
<c:otherwise>
${bill.price}
</c:otherwise>
</c:choose>
</td>
<td>${bill.explains}</td>
<td>
删除 修改
</td>
</tr>
</c:forEach>
</table>
7.查询数据4
模糊查询问题
show.jsp
<p> <form action="/getAllBills" method="post"> 类型: <select name="typeid"> <option value="-1">不限</option> <c:forEach items="${types}" var="tp"> <option value="${tp.id}">${tp.bname}</option> </c:forEach> </select> 时间: 从<input type="text" name="begin">到<input type="text" name="end"> <input type="submit" value="搜索"> </form> <input type="button" value="记账"></p><table border="1" width="500"> <tr> <td>标题</td> <td>时间</td> <td>类别</td> <td>金额</td> <td>说明</td> <td>操作</td> </tr> <c:forEach items="${info.list}" var="bill"> <tr> <td>${bill.title}</td> <td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate> </td> <td>${bill.billtype.bname}</td> <td> <c:choose> <c:when test="${bill.billtype.bname=='支出'||bill.billtype.bname=='借出'||bill.billtype.bname=='还出'}"> -${bill.price} </c:when> <c:when test="${bill.billtype.bname=='收入'||bill.billtype.bname=='借入'||bill.billtype.bname=='还入'}"> +${bill.price} </c:when> <c:otherwise> ${bill.price} </c:otherwise> </c:choose> </td> <td>${bill.explains}</td> <td> 删除 修改 </td> </tr> </c:forEach></table>
controller/BillsController
service/BillsService
service/impl/BillsServiceImpl
@Resourceprivate BIllsMapper billsMapper;@Override public List<Bills> getBills(int typeid,String begin,String end) { Map params=new HashMap(); params.put("tid",typeid); params.put("begin",begin); params.put("end",end); return billsMapper.getBills(params); }
dao/BillsMapper
BillsMapper.xml
<select id="getBills" resultMap="rs1"> select * from bills b,billtype t where b.typeid=t.id <if test="tid!=-1"> and t.id=#{tid} </if> <if test="begin!=null and begin!=''"> and b.billtime>=#{begin} </if> <if test="end!=null and end!=''"> and b.billtime <![CDATA[ <= ]]> #{end} </if> </select>
controller/BillsController
@RequestMapping("/gettypes")public String gettypes(ModelMap map){
运行,没有问题
数据回显问题
controller/BillsController
@RequestMapping("/gettypes")public String gettypes(ModelMap map){
<option value="${tp.id}" ${tid==tp.id?'selected':''}>${tp.bname}</option>时间: 从<input type="text" name="begin" value="${begintime}">到<input type="text" name="end" value="${endtime}">
show.jsp
<p>
<form action="/getAllBills" method="post">
类型:
<select name="typeid">
<option value="-1">不限</option>
<c:forEach items="${types}" var="tp">
<option value="${tp.id}" ${tid==tp.id?'selected':''}>${tp.bname}</option>
</c:forEach>
</select>
时间:
从<input type="text" name="begin" value="${begintime}">到<input type="text" name="end" value="${endtime}">
<input type="submit" value="搜索">
</form>
<input type="button" value="记账">
</p>
<table border="1" width="500">
<tr>
<td>标题</td>
<td>时间</td>
<td>类别</td>
<td>金额</td>
<td>说明</td>
<td>操作</td>
</tr>
<c:forEach items="${info.list}" var="bill">
<tr>
<td>${bill.title}</td>
<td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate> </td>
<td>${bill.billtype.bname}</td>
<td>
<c:choose>
<c:when test="${bill.billtype.bname=='支出'||bill.billtype.bname=='借出'||bill.billtype.bname=='还出'}">
-${bill.price}
</c:when>
<c:when test="${bill.billtype.bname=='收入'||bill.billtype.bname=='借入'||bill.billtype.bname=='还入'}">
+${bill.price}
</c:when>
<c:otherwise>
${bill.price}
</c:otherwise>
</c:choose>
</td>
<td>${bill.explains}</td>
<td>
删除 修改
</td>
</tr>
</c:forEach>
</table>
运行,
类型问题
类型不显示
controller/BillsController
@RequestMapping("/gettypes")public String gettypes(ModelMap map){
运行
不存在的问题
页面加判断
show.jsp
<c:if test="${bills.size()>0}">
<c:forEach items="${info.list}" var="bill">
<tr>
<td>${bill.title}</td>
<td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate> </td>
<td>${bill.billtype.bname}</td>
<td>
<c:choose>
<c:when test="${bill.billtype.bname=='支出'||bill.billtype.bname=='借出'||bill.billtype.bname=='还出'}">
-${bill.price}
</c:when>
<c:when test="${bill.billtype.bname=='收入'||bill.billtype.bname=='借入'||bill.billtype.bname=='还入'}">
+${bill.price}
</c:when>
<c:otherwise>
${bill.price}
</c:otherwise>
</c:choose>
</td>
<td>${bill.explains}</td>
<td>
删除 修改
</td>
</tr>
</c:forEach>
</c:if>
<c:if test="${bills.size==0}">
<tr>
<td colspan="6"><h3>
没有找到任何数据
</h3></td>
</tr>
</c:if>
运行
8.查询数据5
分页问题
show.jsp
<tr>
<td colspan="6">
首页
上一页
下一页
尾页
总页数
总条数
</td>
</tr>
<tr>
<td colspan="6">
<a href="/getAllBills?">首页</a>
<a href="/getAllBills?index=">上一页</a>
<a href="/getAllBills?index=1">下一页</a>
<a href="/getAllBills">尾页</a>
总页数
总条数
</td>
</tr>
service/BillsService
service/impl/BillsServiceImpl
@Servicepublic class BillsServiceImpl implements BillsService { @Resource private BillsMapper billsMapper; @Override public PageInfo<Bills> getBills(int typeid, String begin, String end, int index, int size) { Map params=new HashMap(); params.put("tid",typeid); params.put("begin",begin); params.put("end",end);
controller/BillsController
@RequestMapping("/gettypes")
public String gettypes(ModelMap map){
List<Billtype> types = typesService.getTypes();
PageInfo<Bills> info = billsService.getBills(-1, null, null, 1, PageUtil.PAGESIZE);
map.addAttribute("types",types);
map.addAttribute("info",info);
return "show";
}
@RequestMapping("/getAllBills")
public String getBills(@RequestParam(defaultValue = "1") int index,Integer typeid,String begin,String end,ModelMap map){
PageInfo<Bills> info = billsService.getBills(typeid,begin,end,index,PageUtil.PAGESIZE);
map.addAttribute("info",info);
List<Billtype> types = typesService.getTypes();
map.addAttribute("types",types);
map.addAttribute("tid",typeid);
map.addAtrribute("begintime",begin);
map.addAttribute("endtime",end);
return "show";
}
info.list
show.jsp
<c:if test="${info.list.size()>0}">
<c:forEach items="${info.list}" var="bill">
<tr>
<td>${bill.title}</td>
<td><fmt:formatDate value="${bill.billtime}" pattern="yyyy-MM-dd"></fmt:formatDate> </td>
<td>${bill.billtype.bname}</td>
<td>
<c:choose>
<c:when test="${bill.billtype.bname=='支出'||bill.billtype.bname=='借出'||bill.billtype.bname=='还出'}">
-${bill.price}
</c:when>
<c:when test="${bill.billtype.bname=='收入'||bill.billtype.bname=='借入'||bill.billtype.bname=='还入'}">
+${bill.price}
</c:when>
<c:otherwise>
${bill.price}
</c:otherwise>
</c:choose>
</td>
<td>${bill.explains}</td>
<td>
<a href="/deleteById?bid=${bill.id}">删除</a>
<a href="/findById?bid=${bill.id}">修改</a>
</td>
</tr>
</c:forEach>
</c:if>
<c:if test="${info.list.size()==0}">
<tr>
<td colspan="6"> <h3>没有找到任何数据</h3> </td>
</tr>
</c:if>
<tr>
<td colspan="6">
<a href="/getAllBills?">首页</a>
<a href="/getAllBills?index=${info.prePage}">上一页</a>
<a href="/getAllBills?index=${info.nextPage}">下一页</a>
<a href="/getAllBills?index=${info.pages}">尾页</a>
总页数:${info.pages}
总条数:${info.pages}
</td>
</tr>
util/PageUtil
public interface PageUtil { public int PAGESIZE=3;}
分页插件pom.xml
<!-- pagehelper --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>4.1.6</version> </dependency>
mybatis.xml
<plugins> <!-- PageHelper4.1.6 --> <plugin interceptor="com.github.pagehelper.PageHelper"> <property name="dialect" value="mysql"/> </plugin> </plugins>
运行
下一页,报错
Integer int
controller/BillsController
上一页,有点问题
show.jsp
<tr> <td colspan="6"> <a href="/getAllBills?">首页</a> <a href="/getAllBills?index=${info.prePage==0?1:info.prePage}">上一页</a> <a href="/getAllBills?index=${info.nextPage==0?info.pages:info.nextPage}">下一页</a> <a href="/getAllBills?index=${info.pages}">尾页</a> 总页数:${info.pages} 总条数:${info.pages} </td></tr>
类型,翻页出现问题
show.jsp
<tr>
<td colspan="6">
<a href="/getAllBills?typeid=${tid}&begin=${begintime}&end=${endtime}">首页</a>
<a href="/getAllBills?index=${info.prePage==0?1:info.prePage}&typeid=${tid}&begin=${begintime}&end=${endtime}">上一页</a>
<a href="/getAllBills?index=${info.nextPage==0?info.pages:info.nextPage}&typeid=${tid}&begin=${begintime}&end=${endtime}">下一页</a>
<a href="/getAllBills?index=${info.pages}&typeid=${tid}&begin=${begintime}&end=${endtime}">尾页</a>
总页数:${info.pages}
总条数:${info.total}
</td>
</tr>
9.新增数据
记账功能
类型标题日期 金额说明
show.jsp
记账按钮
<input type="button" value="记账" onclick="javascript:location.href='/getBillType'">
controller/BillsController
add.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<h1>记账</h1>
<form action="insertBill" method="post">
<p>类型:
<c:forEach items="${types}" var="ty">
<input type="radio" value="${ty.id}" name="typeid">${ty.bname}
</c:forEach>
</p>
<p>标题:<input type="text" style="width: 500px" name="title" > </p>
<p>日期：<input type="text" name="billtime"> 金额:<input type="text" name="price"> </p>
<p>说明:<textarea cols="50" rows="4" name="explains"></textarea></p>
<input type="reset" value="重置">
<input type="submit" value="保存">
</form>
</body>
</html>
运行,看下添加页面是否显示
dao/BillsMapper
int insert(Bills record);//新增
service/BillsService
int insert(Bills record);
service/impl/BillsServiceImpl
@Override @Transactional public int insert(Bills record) { return billsMapper.insert(record); }
BillsMapper.xml
<insert id="insert" parameterType="com.yhp.bean.Bills"> insert into bills (id, title, billtime, typeid, price, explains ) values (#{id,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}, #{billtime,jdbcType=DATE}, #{typeid,jdbcType=INTEGER}, #{price,jdbcType=DOUBLE}, #{explains,jdbcType=VARCHAR} ) </insert>
运行
spring.xml
<property name="url" value="jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf-8"></property>
运行
10.更新数据
先查询,后修改
show.jsp
<a href="/findById?bid=${bill.id}">修改</a>
controller/BillsController
@RequestMapping("/findById")
public String findById(int bid,ModelMap map){
Bills bills = billsService.selectByPrimaryKey(bid);
List<Billtype> types = typesService.getTypes();
map.addAttribute("types",types);
map.addAttribute("bills",bills);
return "update";
}
dao/BillsMapper
Bills selectByPrimaryKey(Integer id);//主键查询
service/BillsService
Bills selectByPrimaryKey(Integer id);//主键查询
service/impl/BillsServiceImpl
@Override public Bills selectByPrimaryKey(Integer id) { return billsMapper.selectByPrimaryKey(id); }
update.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %><%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><html><head> <title>Title</title></head><body> <h1>更新</h1> <form action="updateBill" method="post"> <input type="hidden" name="id" value="${bills.id}"> <p>类型: <c:forEach items="${types}" var="ty"> <input type="radio" value="${ty.id}" ${ty.id==bills.typeid?'checked':''} name="typeid">${ty.bname} </c:forEach> </p> <p>标题:<input type="text" style="width: 500px" name="title" value="${bills.title}" > </p> <p>日期：<input type="text" name="billtime" value="<fmt:formatDate value="${bills.billtime}" pattern="yyyy/MM/dd"></fmt:formatDate>"> 金额:<input type="text" name="price" value="${bills.price}"> </p> <p>说明:<textarea cols="50" rows="4" name="explains">${bills.explains}</textarea></p> <input type="reset" value="重置"> <input type="submit" value="保存"> </form></body></html>
运行
保存
controller/BillsController
dao/BillsMapper
int updateByPrimaryKeySelective(Bills record); //动态更新语句 int updateByPrimaryKey(Bills record);//更新
BillsMapper.xml
<update id="updateByPrimaryKey" parameterType="com.yhp.bean.Bills"> update bills set title = #{title,jdbcType=VARCHAR}, billtime = #{billtime,jdbcType=DATE}, typeid = #{typeid,jdbcType=INTEGER}, price = #{price,jdbcType=DOUBLE}, explains = #{explains,jdbcType=VARCHAR} where id = #{id,jdbcType=INTEGER} </update>
service/BillsService
int updateByPrimaryKey(Bills record);//更新
service/impl/BillsServiceImpl
@Override
@Transactional
public int updateByPrimaryKey(Bills record) {
return billsMapper.updateByPrimaryKey(record);
}
update.jsp
隐藏域有安全隐患的
<input type="hidden" name="id" value="${bills.id}">
运行
11.删除数据
show.jsp
<a href="/deleteById?bid=${bill.id}">删除</a>
controller/BillsController
//删除操作
@RequestMapping("/deleteById")
public void delete(int bid, HttpServletResponse response){
int i = billsService.deleteByPrimaryKey(bid);
response.setContentType("text/html;charset=utf-8");
try {
PrintWriter writer = response.getWriter();
if(i>0){
writer.print("<script>alert('删除成功');location.href='/gettypes'</script>");
return;
}
writer.print("<script>alert('删除失败');location.href='/gettypes'</script>");
} catch (IOException e) {
e.printStackTrace();
}
}
dao/BillsMapper
int deleteByPrimaryKey(Integer id);//主键删除
service/BillsService
int deleteByPrimaryKey(Integer id);//主键删除
service/impl/BillsServiceImpl
@Override
@Transactional
public int deleteByPrimaryKey(Integer id) {
return billsMapper.deleteByPrimaryKey(id);
}
总结
|