<?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>
  <environments default="e1">
    <environment id="e1">
      <transactionManager type="JDBC"></transactionManager>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/bank?serverTimezone=CST"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
    <environment id="e2">
      <transactionManager type=""></transactionManager>
      <dataSource type=""></dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="mapper/UserMapper.xml"></mapper>
  </mappers>
</configuration>

<?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="dao.UserDao">
  <insert id="insert">
    INSERT ATM VALUES(#{account}, #{password}, #{balance})
  </insert>
  <update id="update">
    update atm set account=#{account},password=#{password},balance=#{balance} where account=#{account};
  </update>
  <delete id="delete">
    DELETE FROM ATM WHERE account=#{account};
  </delete>

  <select id="selectAll" resultType="domain.User">
    SELECT *FROM atm;
  </select>

  <select id="getTotalCount" resultType="integer">
    SELECT COUNT(*) FROM atm;
  </select>
  <select id="gettotal" resultType="hashmap">
    select count(*),max(balance) from atm;
  </select>


  <select id="selectAllByAcc" resultType="domain.User">
    SELECT *FROM atm order by ${flag} ${order};
  </select>
</mapper>

public class UserDao {

  //增加一條記錄，user對(duì)象是我創(chuàng)建的實(shí)體類
    //mapper.xml文件的sql語(yǔ)句如下：
    //update atm set account=#{account},password=#{password},balance=#{balance} where account=#{account};
    //#{key}中的key要和實(shí)體類user的屬性名一致
    //如果傳遞的是基本數(shù)據(jù)類型或者String，且#{key}只有一個(gè)，那么名字可以不一致
    //如果傳入的是map集合，#{key}中的key要和map集合里的key一致
  public void insert(User user){
    //MYBATIS操作流程
    //獲取工廠Builder對(duì)象
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    //獲取configuration.xml配置文件的輸入流
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    //builder根據(jù)is獲取SqlSessionFactory工廠對(duì)象
    SqlSessionFactory factory = builder.build(is);
    //獲取SqlSession對(duì)象
    SqlSession sqlSession = factory.openSession();
    //執(zhí)行sql語(yǔ)句操作
      //insert是在mapper.xml下，insert標(biāo)簽的id名
      //user是我要插入的數(shù)據(jù)（動(dòng)態(tài)操作），替換掉sql語(yǔ)句的#{}
    sqlSession.insert("insert", user);

    sqlSession.commit();
  }

  //改：修改記錄，user是要修改的數(shù)據(jù)
    //mapper.xml文件的sql語(yǔ)句如下：
    //update atm set account=#{account},password=#{password},balance=#{balance} where account=#{account};
  public void update(User user){
    //MYBATIS操作流程
    //獲取工廠Builder對(duì)象
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    //獲取configuration.xml配置文件的輸入流
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    //builder根據(jù)is獲取SqlSessionFactory工廠對(duì)象
    SqlSessionFactory factory = builder.build(is);
    //獲取SqlSession對(duì)象
    SqlSession sqlSession = factory.openSession(true);
    //執(zhí)行sql語(yǔ)句操作
    sqlSession.update("update",user);
  }
  
  //刪除：根據(jù)account刪除
  //mapper.xml的sql語(yǔ)句如下：
    // DELETE FROM ATM WHERE account=#{account};
  public void delete(String account){
    //MYBATIS操作流程
    //獲取工廠Builder對(duì)象
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    //獲取configuration.xml配置文件的輸入流
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    //builder根據(jù)is獲取SqlSessionFactory工廠對(duì)象
    SqlSessionFactory factory = builder.build(is);
    //獲取SqlSession對(duì)象
    SqlSession sqlSession = factory.openSession(true);
    //執(zhí)行sql語(yǔ)句操作
    sqlSession.update("delete",account);
  }
  
  
  //===============================查詢====================================
  //查詢：查找記錄總條數(shù)以及balance的最大值
  //sql語(yǔ)句如下：select count(*),max(balance) from atm;
  //select標(biāo)簽上的resultType表示的是返回值，指的是一條記錄的類型，最后包裹成list集合
    //我這里用的hashMap
  public List<Map<String, Object>> gettotal(){
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = factory.openSession(true);
    return sqlSession.selectList("gettotal");
  }
  
  //查詢：獲取總記錄數(shù)
  //sql語(yǔ)句： SELECT COUNT(*) FROM atm;
  //resultType類型：integer
  public Integer getTotalCount(){
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = factory.openSession(true);
    return sqlSession.selectOne("getTotalCount");
  }
  
  //查詢：獲取表格的所有記錄
  //sql語(yǔ)句：SELECT *FROM atm;
  //resultType類型：domain.User （如果是自己寫的實(shí)體類，需要類名的具體路徑，除非做了配置）
  public List<User> selectAll(){
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = factory.openSession(true);
    return sqlSession.selectList("selectAll");
  }

  //查詢：查詢所有該表所有記錄，并根據(jù)flag列進(jìn)行order排序
      //flag不確定，order可能是升序也可能是降序(由調(diào)用這個(gè)方法的用戶決定)
  //sql語(yǔ)句： SELECT *FROM atm order by ${flag} ${order};
    //這里用的是${}而不是#{}
    //#{}和${}的區(qū)別：
    //#{}:當(dāng)我們的動(dòng)態(tài)參數(shù)是用作條件的時(shí)候我們用這個(gè)，比如說(shuō)account=#{key}
    //${}：當(dāng)我們的動(dòng)態(tài)參數(shù)表示的是一個(gè)普通字符串，比如說(shuō)表名、列名或者關(guān)鍵字我們用這個(gè)
  //resultType：domain.User(domain包下的User類）
  public List<User> selectAllByAcc(String flag, String order){
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("configuration.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = factory.openSession(true);
    Map<String, Object> map = new HashMap<>();
    map.put("flag", flag);
    map.put("order", order);
    return sqlSession.selectList("selectAllByAcc", map);
  }
}