使用接口
定义一个相关接口自己定义方法
public interface Bookdao {
//查询所有
public List<Book> selectall();
//查询价格区间
public List<Book> selectprice(@Param("min") int min,@Param("max") int max);
//根据id查询
public Book selectid(int id);
//添加
public int insert(Book book);
//修改
public int update(Book book);
//删除
public int delete(int id);
}
在接口中的方法传递多个参数
加上@Param("name")注解就可以了 例如:
//查询价格区间
//@Param("name")name必须要和 参数名一致
public List<Book> selectprice(@Param("min") int min,@Param("max") int max);
数据库连接
这里连接数据库与上一篇mybatis入门有些不同是因为在在实际开发中我们习惯把mybatis连接数据库的配置文件写到一个单独的(.properties)文件中如下:
日志文件
同样也是在(.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
实体类
映射文件
再写映射文件是我们需要注意
<mapper namespac="..."></mapper>路径必须对应相应的dao接口
<?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">
<!--namespace:命名空间:它的值现在可以随便写。 以后必须和dao接口对应。 -->
<mapper namespace="com.aaa.qy145.ninth.rqk.dao.Bookdao">
<resultMap id="Mymap" type="com.aaa.qy145.ninth.rqk.entity.Book" >
<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="selectall" resultMap="Mymap">
select * from book_info
</select>
根据价格区间查询
<select id="selectprice" resultMap="Mymap">
<![CDATA[select * from book_info where book_price>#{min} and book_price<#{max}]]>
</select>
根据id查询
<select id="selectid" resultMap="Mymap">
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">
update book_info set book_name=#{name},book_author=#{author},book_price=#{price},book_pub=#{pub} where book_id=#{id}
</update>
删除
<delete id="delete">
delete from book_info where book_id=#{id}
</delete>
</mapper>
特殊字符
< | < | 小于号 |
> | > | 大于号 |
& | & | 和 |
' | ’ | 单引号 |
" | " | 双引号 |
最简单实用的方法
<![CATAT[特殊符号或sql语句]]> 例如上文映射文件中的:
<select id="selectprice" resultMap="Mymap">
<![CDATA[select * from book_info where book_price>#{min} and book_price<#{max}]]>
</select>
解决列名和属性名不一致
第一种发放就是在sql语句中的查询列中起别名
<select id="selectById" resultType="com.ykq.entity.Order">
select order_id id,order_no no,order_price price from orders where order_id=#{id}
</select>
第二种使用resultMap标签完成属性和列名的映射关系
//id为sql语句中要引用resultmap的值 type:为映射的实体类
<resultMap id="Mymap" type="com.aaa.qy145.ninth.rqk.entity.Book" >
//id必须写,因为id是主键(主键必须写)其他属性名如果对照这里可以不用写(主键必须写)
//property:实体类中的属性名 column:数据库表中相对性的列名
<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>
测试
增删改
对表操作修改删除添加时要提交事务才会对表进行相应的操作(默认手动提交事务,是对数据库缓存区的一种操作)
第一种:将SqlSession session=build.openSession(true);默认为false(手动提交)
第二种:在执行完增删改之后提交事务 执行完之后加上这个代码
session.commit();
/**
* @program: Mybatis03
* @description:
* @author: 任乾坤
* @create: 2021-12-01 17:18
**/
public class BookTest {
private SqlSession session;
@Before
public void Befo01()throws Exception{
Reader resourceAsReader = Resources.getResourceAsReader("Bookbatis.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsReader);
session = build.openSession(true);
}
@Test
public void selectall()throws Exception{
Bookdao mapper = session.getMapper(Bookdao.class);
List<Book> selectall = mapper.selectall();
System.out.println(selectall);
}
@Test
public void selectid()throws Exception{
Bookdao mapper = session.getMapper(Bookdao.class);
Book selectid = mapper.selectid(1001);
System.out.println(selectid);
}
@Test
public void selectprice()throws Exception{
Bookdao mapper = session.getMapper(Bookdao.class);
List<Book> selectprice = mapper.selectprice(20, 50);
System.out.println(selectprice);
}
@Test
public void insert()throws Exception{
Bookdao mapper = session.getMapper(Bookdao.class);
Book book = new Book("假如给我三天光明", "海伦凯勒", 35, "新华出版社");
int insert = mapper.insert(book);
System.out.println(insert);
}
@Test
public void update() throws Exception{
Bookdao mapper = session.getMapper(Bookdao.class);
Book book = new Book(1014,"假如给我三天光明", "海伦凯勒", 35, "北京大学出版社");
int update = mapper.update(book);
System.out.println(update);
}
@Test
public void delete() throws Exception{
Bookdao mapper = session.getMapper(Bookdao.class);
int delete = mapper.delete(1012);
System.out.println(delete);
}
}
事务(事务处理)
1.事务是由一系列动作组成,这些动作要么都完成,要么都不完成。
jdbc默认事务是自动提交。
现在mybatis事务需要手动提交。
事务回滚:如果事务中出现意外自动回滚到指定位置,否则提交事务:
//转账-----老王(-100)---->小红(+100)
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 age=age-10 where name='张天龙'");
ps.executeUpdate();
ps=connection.prepareStatement("update users set age=age+10 where name='汪伟宁'");
ps.executeUpdate();
//connection.commit();//提交
}catch (Exception e){
try {
connection.rollback();//回滚
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
}finally {
}