[大数据]mybatis使用DAO接口（多参，SQL语句特殊字符，列名和属性名不一致）及其优化。事务

mybatis 的概念及创建架构请参考：mybatis简介及实例_五月拾一的博客-CSDN博客

一，实际开发中使用DAO ,例：

?（1）根据表可以自己定义方法，定义一个相关实体类和接口。

????????实体类：

// 实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
// 以上三者需要导包
public class User {
    private int id;
    private  String name;
    private  String author;
    private  int price;
    private  String pub;

}

????????DAO接口：

public interface UserDao {
    public List<User> fall();              //查询全部记录
    public User fid (int id);               //查询单条记录
    public   int insert (User user);       //插入单条记录
    public  int update (User user);       //修改信息
    public int dalete (int id);          //根据id删除单条记录
    public List<User> betw(@Param("min") int min, @Param("max") int max); //多参价格范围查询
    public List<User> selectByCondition(@Param("name") String name, @Param("price") int price);     //根据书名和价格多参查询

}

????? (2)建立映射文件，（含多参传递，sql语句特殊字符处理，）

<?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.wk.dao.UserDao">
    <resultMap id="ho" type="com.wk.User.User">
        <id property="id" column="book_id"/>
        <result property="name" column="book_name"/>
        <result property="author" column="book_author"/>
        <result property="price" column="book_price"/>
        <result property="pub" column="book_pub"/>
    </resultMap>
    <select id="fall" resultMap="ho">    //查询所有
        select  * from book_info
    </select>
    <select id="fid" resultMap="ho">   //根据ID查询
        select * from  book_info where  book_id=#{id}
    </select>
    <insert id="insert">    //插入记录
        insert  into book_info(book_name,book_author,book_price,book_pub)  values(#{name},#{author},#{price},#{pub})
    </insert>
    <update id="update">      //根据ID修改记录
        update  book_info set book_name=#{name},book_author=#{author},book_price=#{price},book_pub=#{pub} where book_id=#{id}
    </update>
    <delete id="dalete">    //ID删除记录
        delete  from book_info where book_id=#{id}
    </delete>
  <select id="selectByCondition" resultType="com.wk.User.User">   //多参，根据书名和价格查询，如果DAO接口方法没有加@Param,SQL语句赋值要符合一定格式，例：[arg1, arg0, param1, param2]
       select * from book_info where book_name=#{name} and book_price=#{price}
    </select>
    <select id="betw" resultMap="ho">   //根据价格范围查询
      <![CDATA[  select * from book_info where book_price>#{min} and book_price<#{max}]]>
    </select>
</mapper>

（3）SQL语句中一些特殊字符

?为了方便记忆个人推荐使用<![CDATA[SQL语句]]>

 <select id="betw" resultMap="ho">
      <![CDATA[  select * from book_info where book_price>#{min} and book_price<#{max}]]>
    </select>

（4）建立测试

public class Tester {
    SqlSession session=null;
    @Before
    public void befor()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml"); //mybatis.xml:mybatis的配置文件名
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(reader);
       session= build.openSession();
    }
    @Test
    public void fall(){   //查询全部
        UserDao dao = session.getMapper(UserDao.class);
        List<User> list=dao.fall();
        System.out.println(list);
    }
    @Test
    public void fid(){    //根据ID查询记录
        UserDao dao = session.getMapper(UserDao.class);
        User list=dao.fid(1001);
        System.out.println(list);
    }
 @Test
    public void insert(){   //插入一条记录
        UserDao dao = session.getMapper(UserDao.class);
        User u=new User();
        u.setName("老鲁花花世界");
       u.setAuthor("老鲁");
       u.setPrice(41);
       u.setPub("老鲁出版社");
       int list=dao.insert(u);
        System.out.println(list);
        session.commit();  
    }
    @Test
    public void update(){  //根据ID修改信息
        UserDao dao = session.getMapper(UserDao.class);
        User u=new User();
        u.setName("老庄世界");
        u.setAuthor("老庄");
       u.setPrice(12);
        u.setPub("老庄出版社");
       u.setId(1008);
        int list=dao.update(u);
        System.out.println(list);
        session.commit();
    }
     @Test
    public void del(){   //根据ID删除一条记录
        UserDao dao = session.getMapper(UserDao.class);
       int list=dao.dalete(1017);
        System.out.println(list);
        session.commit();
    }


    @Test
    public void betw(){  //多参根据价格范围查询
        UserDao dao = session.getMapper(UserDao.class);
        List<User> users=dao.betw(20,35);
        System.out.println(users);
    }
    @Test
    public  void selectByCondition(){   //多参根据书名和价格查询
    UserDao dao = session.getMapper(UserDao.class);
    List<User> users=dao.selectByCondition("西游记",35);
    System.out.println(users);
}


}

代码中? session.commit(); 意为写入数据库；查询不需要，改动数据内容需要加入。

（5）解决列名和属性名不一致。

第一种: 为查询的列起别名；让别名和属性名一致。

<mapper namespace="com.wk.dao.UserDao">
    <select id="fall" resultType="com.wk.User.User">
        select book_id  id,book_name  name,book_author  author,book_price  price,book_pub  pub from book_info where book_id=#{id}
    </select>
</mapper>

?第二种: 使用resultMap标签 来完成属性和列的映射关系。

<mapper namespace="com.wk.dao.UserDao">
    <resultMap id="ho" type="com.wk.User.User">
        <id property="id" column="book_id"/>
        <result property="name" column="book_name"/>
        <result property="author" column="book_author"/>
        <result property="price" column="book_price"/>
        <result property="pub" column="book_pub"/>
    </resultMap>
    <select id="fid" resultMap="ho">
        select * from  book_info where  book_id=#{id}
    </select>
</mapper>

二， mybatis的优化

（1）引入db属性文件以及日志，定义一个数据库属性文件. properties

?db.properties文件代码：

jdbc.url=jdbc:mysql://localhost:3306/数据库名称?serverTimezone=Asia/Shanghai   
jdbc.driverName=com.mysql.cj.jdbc.Driver
jdbc.username=root   //账号
jdbc.password=root   //密码，如果没有密码可为：jdbc.password=

(2)在mybatis配置文件中引入属性文件并使用相应的key

代码如下：

<?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="db.properties"/>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!--数据库连接池:何为数据库连接池！
                    创建一个池子并且池子中会存放若干个连接对象。当需要连接数据库时，只需要从池子中获取。
                    当使用完连接对象时 会把该对象放入连接池中。
            -->
            <dataSource type="POOLED">
                <!--${获取属性文件中key对应的值}-->
                <property name="driver" value="${jdbc.driverName}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>

    <!--引入相关的映射文件-->
    <mappers>
        <mapper resource="mapper/imp.xml"/>
    </mappers>
</configuration>

（3）配置log4j.properties

首先在pom.xml中导入log4j的jar包：

   <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.12</version>
    </dependency>

其一配置log4j.properties：在控制台输出信息

log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

其二配置log4j.properties：生成文件：

### 设置###
log4j.rootLogger = debug,stdout,D,E

### 输出信息到控制抬 ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### 输出DEBUG 级别以上的日志到=E://logs/error.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = D://logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG 
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### 输出ERROR 级别以上的日志到=E://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =D://logs/error.log 
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR 
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

?三，事务

• 事务是由一系列动作组成，这些动作要么都完成，要么都不完成。jdbc默认事务是自动提交。现在mybatis事务需要手动提交。

实例：

Connection connection=null;
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    connection= DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai",
            "root","root");
    connection.setAutoCommit(false);
    PreparedStatement ps=connection.prepareStatement("update users set price=price-10 where name='小宇'");
    ps.executeUpdate();


    ps=connection.prepareStatement("update users set price=price+10 where name='小宁'");
    ps.executeUpdate();
    //connection.commit();//提交
}catch (Exception e){
    try {
        connection.rollback();//回滚
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    e.printStackTrace();
}finally {

}