@Test
public void insOne() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
UserDao ud = ss.getMapper(UserDao.class);
User user = new User("逗子23", 22);
System.out.println(user);
int c = ud.insOne(user);
System.out.println(user);
System.out.println(c);
ss.commit();
ss.close();
}
@Test
public void upOne() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
UserDao ud = ss.getMapper(UserDao.class);
User user = new User(6, "逗子7", 22);
int c = ud.upOne(user);
System.out.println(c);
ss.commit();
ss.close();
}
@Test
public void delOne() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
UserDao ud = ss.getMapper(UserDao.class);
int c = ud.delOne(8);
System.out.println(c);
ss.commit();
ss.close();
}
@Test
public void findnameage() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
UserDao ud = ss.getMapper(UserDao.class);
User user = ud.findByNameAge("王逗子", 22);
System.out.println(user);
ss.commit();
ss.close();
}
2.对数据库一些进阶的操作:
如图,数据库的 字段名与实体类属性名不相同的冲突
我们实体类取简单的来写
别忘了添加映射问价你的注册
映射文件:
使用resultMap重新来绑定字段(别忘了ResultMap的id,来进行绑定)
业务操作Sql代码:
insert into orders(order_no, order_price)
values (#{no}, #{price});
接口层
测试类:
@Test
public void findnameage() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
OrderDao od = ss.getMapper(OrderDao.class);
Order one = od.findOne(1);
System.out.println(one);
ss.commit();
ss.close();
}
@Test
public void addOne() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
OrderDao od = ss.getMapper(OrderDao.class);
Order order = new Order(null,"douzi",22);
int c = od.addOne(order);
System.out.println(c);
ss.commit();
ss.close();
}
ok没问题
这样的话必能避免数据库的字段名与实体类属性名不相同的冲突
动态sql:
User表
User的实体类:
UserDao接口:
(添加 查询)
UserMapper的映射文件
where 与 if 标签
where标签嵌套if标签以后 if判断哪个可以就执行哪个sql
select * from tbl_user
and name = #{name};
and pwd = #{pwd};
insert into tbl_user(name, pwd)
values (#{name}, #{pwd});
如果 name!=null and name!=' ' 则执行:
select * from tbl_user WHERe name = #{name};
测试类:
@Test
public void findAll() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
UserDao ud = ss.getMapper(UserDao.class);
List list = ud.findByNamePwd("刘德华", null);
System.out.println(list);
ss.commit();
ss.close();
}
Set标签 :
这个配合if标签一起用,一般用在修改语句。如果传递的参数值为null,那么应该不修改该列的值。
update account
name=#{name},
money=#{money},
isdeleted=#{isdeleted},
created=#{created},
updated=#{updated},
where id=#{id}
foreach标签:
select * from account where id
#{id}
以此表为例:
Account实体类:
package cn.wd.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account {
private Integer id;
private String name;
private Double money;
private Integer isdeleted;
private Date created;
private Date updated;
}
AccountDao 接口:
写了 一个查询 和 修改功能
查询的话 使用集合多个查询
AccountMapper映射文件:
update account
name=#{name},
money=#{money},
isdeleted=#{isdeleted},
created=#{created},
updated=#{updated},
where id=#{id}
select * from account where id
#{id}
#{id}
测试类:
@Test
//查询业务
public void select() throws Exception {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession ss = ssf.openSession();
AccountDao ad = ss.getMapper(AccountDao.class);
Integer[] ids = {1, 2, 3, 4}; //定义一个集合
List byIds = ad.findByIds(ids);
System.out.println(byIds);
ss.commit();
ss.close();
}
一对多关联:
班级表:
学生表:
需要: 查出学生的数据并且绑定上班级的信息
学生和班级的实体类
这里注意的是 学生的话需要把班级类作为参数写进来
private CClass cclass;
接口层:
StuMapper的映射文件
s.id,stu_name,stu_age,stu_sex,class_id,cid,cname
select
from tb_stu s
join cclass c on s.class_id = c.cid
where s.id = #{id};