spring+springmvc+mybaits综合
spring+springmvc+mybatis环境下完成
第一阶段
查询所有账户余额
查询某个账户余额
充值 给指定账户充值
提现 从指定账户提现
第二阶段
转账
在单表操作基础上,加入多表关联功能
账户表
id username money
订单表
id 名称 下单时间 金额 账户id
多表关联 查询 1对多 多对一
第三阶段
级联新增 form 用户信息、订单信息 同时保存到用户表和订单表
级联删除 删除用户信息 如果存在订单,级联删除订单
创建项目并创建对应的包结构
pom.xml
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context-support -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.1.4.RELEASE</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/aopalliance/aopalliance -->
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
springmvc-servlet.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" xmlns:p="http://www.springframework.org/schema/p"
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">
<!--定义注解扫描的包-->
<context:component-scan base-package="com.lanou.controller"></context:component-scan>
<!--内部视图资源解析器-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/jsp/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
<!--
解决返回json数据乱码问题
-->
<mvc:annotation-driven>
<mvc:message-converters register-defaults="true">
<bean class="org.springframework.http.converter.StringHttpMessageConverter">
<property name="supportedMediaTypes">
<list>
<value>text/html;charset=UTF-8</value>
<value>application/json;charset=UTF-8</value>
<value>text/plain;charset=UTF-8</value>
<value>application/xml;charset=UTF-8</value>
</list>
</property>
</bean>
</mvc:message-converters>
</mvc:annotation-driven>
<!--处理文件上传-->
<!--multipartResolver配置-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"
p:defaultEncoding="UTF-8"
p:maxUploadSize="5242880"
p:uploadTempDir="file:/d:/temp"
/>
<mvc:resources mapping="/static/**" location="/static/" ></mvc:resources>
</beans>
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>
<settings>
<!--开启下划线到驼峰式命名法的自动映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--是否启用缓存 默认值true-->
<setting name="cacheEnabled" value="true"></setting>
<!--是否启用延迟加载功能 多对象关联-->
<setting name="lazyLoadingEnabled" value="true"></setting>
<!--是否积极加载所有属性-->
<setting name="aggressiveLazyLoading" value="false"></setting>
<!--是否使用自动生成主键-->
<setting name="useGeneratedKeys" value="true"></setting>
<!--配置mybatis日志输出-->
<!-- <setting name="logImpl" value="LOG4J"></setting>-->
</settings>
<typeAliases>
<!--该包下所有的类统一起别名-->
<package name="com.lanou.pojo"></package>
</typeAliases>
<!-- 引入 pageHelper插件 -->
<!--注意这里要写成PageInterceptor, 5.0之前的版本都是写PageHelper, 5.0之后要换成PageInterceptor-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--reasonable:分页合理化参数,默认值为false,直接根据参数进行查询。
当该参数设置为 true 时,pageNum<=0 时会查询第一页, pageNum>pages(超过总数时),会查询最后一页。-->
<!--<property name="reasonable" value="true"/>-->
</plugin>
</plugins>
<mappers>
<mapper resource="com/lanou/mapper/ArAccountMapper.xml"/>
<mapper resource="com/lanou/mapper/OrdersMapper.xml"/>
</mappers>
</configuration>
ArAccountMapper.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.lanou.mapper.ArAccountMapper">
<!--查询全部-->
<select id="findAll" resultType="ArAccount">
select * from ar_account;
</select>
<select id="findById" resultType="ArAccount">
select * from ar_account
<where>
<if test="id!=null">
id =#{id}
</if>
</where>
</select>
<!--查询单个通过id和username-->
<select id="findByIdAndUsername" resultType="ArAccount">
select * from ar_account
<where>
<if test="id!= null">
id = #{id}
</if>
<if test="username!=null and username !=''">
and username = #{username}
</if>
</where>
</select>
<update id="addMoney">
update ar_account set money = money + #{money} where id = #{id}
</update>
<update id="minusMoney">
update ar_account set money = money - #{money} where id = #{id}
</update>
db.properties
jdbc.jdbcUrl = jdbc:mysql://localhost:3306/mybaits?serverTimezone=GMT
jdbc.driverClass = com.mysql.jdbc.Driver
jdbc.user = root
jdbc.password = 123456
lo4j.properties
# Global logging configuration
log4j.rootLogger=TRACE, stdout
# MyBatis logging configuration...
log4j.logger.com.lanou.mapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
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"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:p="http://www.springframework.org/schema/p"
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/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<context:annotation-config></context:annotation-config>
<context:component-scan base-package="com.lanou.service,com.lanou.pojo,com.lanou.mapper"></context:component-scan>
<!--开启aop注解-->
<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
<context:property-placeholder location="classpath:db.properties"></context:property-placeholder>
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
p:jdbcUrl="${jdbc.jdbcUrl}"
p:driverClass = "${jdbc.driverClass}"
p:user="${jdbc.user}"
p:password="${jdbc.password}">
</bean>
<!-- bean jdbcTemplate -->
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--平台事务管理器-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--创建SqlSessionFactory-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--数据源-->
<property name="dataSource" ref="dataSource"></property>
<!--mybatis核心配置文件位置-->
<property name="configLocation" value="classpath:mybatis-config.xml"></property>
</bean>
<!-- Mapper接口所在包名,Spring会自动查找其下的Mapper -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lanou.mapper"></property>
</bean>
</beans>
arAccountList.html
在web下创建static包在static包下创建js包和一个静态页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>账户</title>
<script type="application/javascript" src="js/jquery-3.4.1.js"></script>
</head>
<script type="application/javascript">
$(function () {
$.get("/arAccount/findAll",function (data) {
$.each(data,function (index,obj) {
var tr = "<tr>" +
"<td>"+obj.id+"</td>" +
"<td>"+obj.username+"</td>" +
"<td>"+obj.money+"</td>" +
"</tr>";
console.log(data)
$("tbody").append(tr);
});
},"json");
});
//查单个用户
$(function () {
$("#btn").click(function () {
console.log($(":text[name='id']").val());
console.log($(":text[name='username']").val())
$.get("/arAccount/findByIdAndUsername",
{"id":$(":text[name='id']").val(),"username":$(":text[name='username']").val()},
function (data) {
if(data!=null){
$("#msg").html("账户余额:"+data.money);
console.log(data)
}else{
$("#msg").html("账户信息不存在,请重新输入")
}
},"json");
});
});
//充值钱
$(function () {
$("#addBtn").on("click",function () {
var id = $("#aid").val();
console.log(id);
var money = $("#aMoney").val();
if ((id == null || id =="")||(money == null || money=="" ||money <=0)){
$("#msg2").html("<p style='color: red'>输入有误!!</p>")
return;
}else {
$.get("/arAccount/addMoney",{"id":id,"money":money},
function (data) {
$("#msg2").html("<p style='color: green'>"+data+"</p>");
//console.log(data);
},"json");
}
});
});
//提现功能
$(function () {
$("#minusBtn").click(function () {
var id = $("#tid").val();
var money = $("#tMoney").val();
//判断是否是否为空
if(id == null || id=="" || money == null || money=="" || money<=0){
alert("输入有误!");
return;
}else{
$.get("/arAccount/minusMoney",{"id":id,"money":money},function (data) {
console.log(data);
alert(data);
window.location.reload();
},"json");
}
});
});
$(function () {
$("#transferBtn").click(function () {
var fromId = $("#fromId").val();
var toId = $("#toId").val();
var money = $("#money").val();
//console.log(money)
if (fromId == null || fromId == 0 || toId == null ||toId==0||money == null || money<= 0){
$("#msg4").html("<p style='color: red'>输入有误!!!</p>")
} else{
$.get("/arAccount/transferMoney",{"toId":toId,"fromId":fromId,"money":money},
function (data) {
$("#msg4").html("<p style='color: green'>"+data+"</p>")
// window.location.reload();
},"json");
}
});
});
$(function () {
});
</script>
<body>
账户列表功能:
<table>
<tr>
<td>id</td>
<td>账户名</td>
<td>资金</td>
</tr>
</table>
<hr>
搜索功能
<form>
id:<input type="text" name="id"><br>
账户:<input type="text" name="username"><br>
<input type="button" value="搜索" id="btn">
</form>
<div id="msg" style="color: red"></div>
<hr>
充值功能
<form>
id:<input type="text" id="aid"><br>
充值金额:<input type="text" id="aMoney"><br>
<input type="button" value="充值" id="addBtn">
</form>
<div id="msg2"></div>
<hr>
取钱功能
<form>
id:<input type="text" id="tid"><br>
取出金额:<input type="text" id="tMoney"><br>
<input type="button" value="取钱" id="minusBtn">
</form>
<div id="msg3"></div>
<hr>
转账功能
<form>
转账人id:<input type="text" id="fromId"><br>
收款人id:<input type="text" id="toId"><br>
金额:<input type="text" id="money"><br>
<input type="button" value="转账" id="transferBtn">
</form>
<div id="msg4"></div>
</body>
</html>
在pojo中创建对应的实体类
比如ArAccount
controller包
ArAccountController控制层
import com.alibaba.fastjson.JSON;
import com.lanou.pojo.ArAccount;
import com.lanou.service.ArAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
@RequestMapping("/arAccount")
@Controller
public class ArAccountController {
@Autowired
private ArAccountService arAccountService;
@RequestMapping("/findAll")
@ResponseBody
public String findAll(){
List<ArAccount> list = arAccountService.findAll();
//System.out.println(JSON.toJSONString(list));
return JSON.toJSONString(list);
}
@RequestMapping("/findByIdAndUsername")
@ResponseBody
public String findByIdAndUsername(Integer id, String username){
if((id==null || id == 0 )&& (username == null || username == "")){
return null;
}
ArAccount arAccount = arAccountService.findByIdAndUsername(id, username);
//System.out.println(JSON.toJSONString(arAccount));
return JSON.toJSONString(arAccount);
}
//
@RequestMapping("/addMoney")
@ResponseBody
public String addMoney(Integer id,Double money){
String mesage = "账户不存在,请重新输入!";
if((id==null || id == 0 )&& (money == null || money <= 0)){
mesage = "用户输入有误,重新输入!";
return null;
}
int num = arAccountService.addMoney(id, money);
if (num >= 0) {
ArAccount arAccount = arAccountService.findById(id);
mesage = "操作成功。账户余额为:" + arAccount.getMoney();
}
return JSON.toJSONString(mesage);
}
//查询通过id
@RequestMapping("findById")
@ResponseBody
public String findById(Integer id){
ArAccount arAccount = arAccountService.findById(id);
return JSON.toJSONString(arAccount);
}
//取钱
@RequestMapping("/minusMoney")
@ResponseBody
public String minusMoney(Integer id,Double money){
String message = "当前账户不存在,或者余额不足,操作失败";
if((id == null || id == 0)&&(money == null || money <= 0)){
message = "用户输入有误,重新输入!";
}else{
//查看当前账户余额是否充足
ArAccount arAccount = arAccountService.findByIdAndUsername(id,null);
if(arAccount != null && arAccount.getMoney() > money){
int num = arAccountService.minusMoney(id,money);
if(num > 0){
message = "操作成功!";
}
}
}
return JSON.toJSONString(message);
}
//转账
@RequestMapping("/transferMoney")
@ResponseBody
/*
fromId:转账人id
toId:收款人id
money:金额
*/
public String transferMoney(Integer fromId,Integer toId,Double money){
String mesage = "操作失败";
if((fromId == null || fromId == 0) || (toId == null || toId ==0 ) ||(money == null || money<= 0)){
mesage = "用户输入有误,请重新输入!";
}else{
//查看转帐人是否余额充足
ArAccount fromAccount = arAccountService.findById(fromId);
//查看收款方账户是否存在
ArAccount toAccount = arAccountService.findById(toId);
if (fromAccount != null && fromAccount.getMoney()> money && toAccount!=null){
int num = arAccountService.transferMoney(fromId, toId, money);
if (num > 0){
mesage = "操作成功!";
}
}
}
return JSON.toJSONString(mesage);
}
}
服务层(service包)
ArAccount接口
import com.lanou.pojo.ArAccount;
import java.util.List;
public interface ArAccountService {
//查询所有
List<ArAccount> findAll();
//查询通过id
ArAccount findById(Integer id);
//查询单个通过id和username
ArAccount findByIdAndUsername(Integer id,String username);
//存钱
int addMoney(Integer id,Double money);
//取钱
int minusMoney(Integer id,Double money);
//转账
int transferMoney(Integer fromId, Integer toId, Double money);
}
ArAccountServiceImpl实现类
import com.lanou.mapper.ArAccountMapper;
import com.lanou.pojo.ArAccount;
import com.lanou.service.ArAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class ArAccountServiceImpl implements ArAccountService {
@Autowired
private ArAccountMapper arAccountMapper;
@Override
public List<ArAccount> findAll() {
return arAccountMapper.findAll();
}
@Override
public ArAccount findById(Integer id) {
return arAccountMapper.findById(id);
}
@Override
public ArAccount findByIdAndUsername(Integer id, String username) {
return arAccountMapper.findByIdAndUsername(id, username);
}
@Override
@Transactional
public int addMoney(Integer id, Double money) {
return arAccountMapper.addMoney(id, money);
}
@Override
@Transactional
public int minusMoney(Integer id, Double money) {
return arAccountMapper.minusMoney(id,money);
}
@Override
@Transactional
public int transferMoney(Integer fromId, Integer toId, Double money) {
int num = arAccountMapper.minusMoney(fromId,money);
int num2 = arAccountMapper.addMoney(toId,money);
int i = 1;
if(num == 0 || num2 == 0){
//转账失败
num = 0;
//自定义一个异常 回滚事务 这时候最后自己创建一个异常类
System.out.println(1/0);
}
return num;
}
}
dao层(mapper包)
import com.lanou.pojo.ArAccount;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface ArAccountMapper {
//查询全部
List<ArAccount> findAll();
//查询单个通过id
ArAccount findById(@Param("id") Integer id);
//查询单个通过id和账户名字
ArAccount findByIdAndUsername(@Param("id") Integer id, @Param("username") String username);
//通过id加钱
int addMoney(@Param("id") Integer id,@Param("money") Double money);
//提现 从指定账户提现
int minusMoney(@Param("id") Integer id,@Param("money") Double money);
}
功能实现
取钱功能
转账功能