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>
<properties resource="config.properties" />
<typeAliases>
<typeAlias alias="Card" type="com.dayuan.data.Card" />
<typeAlias alias="Person" type="com.dayuan.data.Person" />
<typeAlias alias="MoneyStream" type="com.dayuan.data.MoneyStream" />
<typeAlias alias="Transfer" type="com.dayuan.data.Transfer" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.dayuan.data" />
</mappers>
</configuration>
config.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/atm?useSSL=true
username=root
password=123123
CardMapper.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.dayuan.data.CardMapper">
<!--通过银行卡号查找Card信息 -->
<select id="selectCardById" parameterType="String" resultType="Card">
select * from a_card where card_id = #{Cardid}
</select>
<!--减钱乐观锁 -->
<update id="WithdrawMoney" parameterType="Card">
update a_card set
money= money-#{money} ,version = version+1 where card_id=#{card_id}
and version =#{version}
</update>
<!--加钱乐观锁 -->
<update id="SaveMoney" parameterType="Card">
update a_card set money=
money+#{money} ,version = version+1 where card_id=#{card_id} and
version =#{version}
</update>
<!-- 悲观锁查询 -->
<select id="selectCardById2" parameterType="int" resultType="Card">
select * from a_card where id = #{id} for update
</select>
<!--减钱悲观锁 -->
<update id="WithdrawMoney2" parameterType="Card">
update a_card set money= money-#{money} ,version = version+1 where id=#{id}
</update>
<!--加钱悲观锁-->
<update id="SaveMoney2" parameterType="Card">
update a_card set money= money+#{money} ,version = version+1 where id=#{id}
</update>
<!--查询今日取款金额 -->
<select id="selectMoneyStreamById" resultType="int">
<![CDATA[
select IFNULL(SUM(oper_money),0) from a_money_stream where card_id = #{card_id} and oper_type = 2 and oper_time >= #{oper_time1}'00:00:00'
and oper_time <= #{oper_time2}'23:59:59'
]]>
</select>
</mapper>
CardMapper.java
package com.dayuan.data;
import org.apache.ibatis.annotations.Param;
public interface CardMapper {
//通过Card_id查询Card所有信息
public Card selectCardById(String Cardid);
//减去账户钱
public int WithdrawMoney(Card card);
//账户加钱
public int SaveMoney(Card card);
//悲观锁查询
public Card selectCardById2(int id);
//悲观锁减钱
public int WithdrawMoney2(Card card);
//悲观锁加钱
public int SaveMoney2(Card card);
//限额
public int selectMoneyStreamById(@Param("card_id") int id,@Param("oper_time1") String oper_time1,@Param("oper_time2") String oper_time2);
}
连接数据库
package com.dayuan.util;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class DbDriver {
private DbDriver(){}
static SqlSessionFactory sqlSessionFactory = null;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//System.out.println("数据库驱动加载成功");
}
public static SqlSessionFactory getstart() {
return sqlSessionFactory;
}
public static SqlSession start(boolean bool){
try {
//System.out.println("数据库连接成功");
return sqlSessionFactory.openSession(bool);
} catch (Exception e) {
// TODO: handle exception
}
return null;
}
//关闭所有流
public static void closeAll(AutoCloseable... objs) {
for (AutoCloseable obj : objs) {
try {
if (obj != null) {
try {
obj.close();
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
//获取年月日
public static String gettime(){
Date date = new Date();
SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
String time = sd.format(date);
return time;
}
}