Spring Data JPA入门及深入

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = "classpath:applicationContext.xml")

public class Client {

    //注入数据

    @Autowired

    @Qualifier(value = "studentDao")

    private StudentDao sd;

    /****************************查询操作*****************************/

    @Test   //查询并排序

    public void findtestA() {

        //查询全部 先对age排序后在进行id排序

        Sort sort = new Sort(Sort.Direction.DESC, "age", "id");

        List<Student> students = sd.findAll(sort);

        //打印省略

    }

    @Test   //查询并分页

    public void findtestB() {

        //查询分页  page是第几页 size 每页个数  当前是第2页查询3个   相当limit 6,3

        Pageable pageable = new PageRequest(2, 3);

        Page<Student> page = sd.findAll(pageable);

        System.out.println("当前页："+page.getNumber());

        System.out.println("每页显示条目数："+page.getSize());

        System.out.println("总页数："+page.getTotalPages());

        System.out.println("结果集总数量："+page.getTotalElements());

        System.out.println("是否是首页："+page.isFirst());

        System.out.println("是否是尾页："+page.isLast());

        System.out.println("是否有上一页："+page.hasPrevious());

        System.out.println("是否有下一页："+page.hasNext());

        System.out.println("结果集："+page.getContent());

        /*

            当前页：2

            每页显示条目数：3

            总页数：3

            结果集总数量：7

            是否是首页：false

            是否是尾页：true

            是否有上一页：true

            是否有下一页：false

            结果集：[Student{id=7, name='马克思', age=20, money=2222.8, address='安徽大别山'}]

         */

        //总结：以后做分页操作变容易了呀

    }

    @Test   //查询指定学生

    public void findtesC() {

        //创建一个集合   List就是一个Iterable可迭代容器对象，因为实现了Iterable接口

        List<Integer> list = new ArrayList<Integer>() {{

            add(1);add(5);add(3);

        }};

        List<Student> students = sd.findAll(list);

        //打印省略

    }

    /****************************更新操作*****************************/

    @Test   //更新多个学生 更新学生全部地址为 安徽合肥

    @Transactional

    @Rollback(value = false)

    public void savetestA() {

        //创建2个集合  第一个集合查询全部数据，然后把集合里对象地址改为新的放入新集合上，后面迭代更新

        List<Student> list = sd.findAll();

        System.out.println(list.size());

        List<Student> newlist = sd.findAll();

        for (int i = 0; i < list.size(); i++) {

            Student student = list.get(i);

            student.setAddress("安徽合肥");

            System.out.println(student);

            newlist.add(student);

        }

        List<Student> save = sd.save(newlist);

        //打印略

    }

    /****************************删除操作*****************************/

    @Test   //删除指定学生

    public void deletetest() {

        List<Integer> list = new ArrayList<Integer>() {{

            add(1);add(5);add(3);

        }};

        List<Student> all = sd.findAll(list);

        sd.deleteInBatch(all);

    }

    /**

     *  删除方法介绍：

     *  void deleteAll()：底层会一条一条删除

     *  void delete(Iterable<? extends T> entities)：底层会一条一条删除

     *  void deleteAllInBatch()：底层会生成一个带or语句删除

     *  void deleteInBatch(Iterable<T> entities)：底层会生成一个带or语句删除

     *  如：Hibernate: delete from student where sid=? or sid=? or sid=?

     */

}

@Test   //查询单个  立即加载

    public void findOneTest(){

        Student student = sd.findOne(3);

        System.out.println("-------分割线-------");

        System.out.println("打印对象："+student);

        //控制台打印：

        /*

            Hibernate: select student0_.sid as sid1_0_0_, student0_.saddress as saddress2_0_0_,

            student0_.sage as sage3_0_0_, student0_.smoney as smoney4_0_0_, student0_.sname as

            sname5_0_0_ from student student0_ where student0_.sid=?    【SQL语句】

            -------分割线-------

            打印对象：Student{id=3, name='李小二', age=23, money=6666.8, address='安徽大别山'}

        */

        //说明：在一执行到方法时就会去数据库查询，然后把数据封装到实体类对象上

        //底层调用： EntityManager里面的find()方法

    }

    @Test   //查询单个  懒加载【延迟加载】

    @Transactional  //此包下import org.springframework.transaction.annotation.Transactional;

    public void getOneTest(){

        Student student=sd.getOne(3);

        System.out.println("-------分割线-------");

        System.out.println("打印对象："+student);

        //控制台打印

        /*

            -------分割线-------

            Hibernate: select student0_.sid as sid1_0_0_, student0_.saddress as saddress2_0_0_,

            student0_.sage as sage3_0_0_, student0_.smoney as smoney4_0_0_, student0_.sname as

            sname5_0_0_ from student student0_ where student0_.sid=?

            打印对象：Student{id=3, name='李小二', age=23, money=6666.8, address='安徽大别山'}

         */

        //说明：在执行到getOne方法时并不会去数据库查询数据，而只是把此方法交给了代理对象，在后面

        //      某个代码要对查询的对象student操作时才会去数据库做真正查询

        //底层调用：EntityManager里面的getReference方法

    }

    @Test   //添加

    @Transactional  //开启事务

    @Rollback(value = false)    //不自动回滚

    public void savetestA(){

        //注意这里 我调用了一个不带 ID的构咋函数

        Student student=new Student("苍井空",25,555.6,"日本东京");

        Student save = sd.save(student);

        System.out.println("添加的数据是："+save);

    }

    @Test   //更新

    public void updatetestA(){

        //更新操作的时候 首先要知道更新的ID 后通过把数据全部查询出来，然后再通过set方法修改

        Student student = sd.findOne(5);    //查询

        student.setName("小次郎");

        student.setAddress("日本");

        //更新

        Student save = sd.save(student);

        System.out.println("更新后的数据是："+save);

    }

    /**

     * 总结：更新/添加都是save

     * 区别:传入带ID的属性就是更新  不传入ID属性就是添加

     * 注意：添加必须添加事务和回滚机制

     */

//@Repository("studentDao") //这里不用加入IOC容器 Spring默认帮我们注入

public interface StudentDao extends JpaRepository<Student, Integer>, JpaSpecificationExecutor<Student> {

    //查询全部 根据年龄和地址查询

    @Query("from Student where age=?1 and address=?2")

    List<Student> findByAgeAndAddress(Integer age,String address);

    //查询全部 根据姓名模糊查询

    @Query("from Student where name like ?1")

    List<Student> findByLikeName(String name);

    //查询全部 根据地址为空的返回

    @Query("from Student where address is null ")

    List<Student> findByAddressIsNull();

    //查询全部学生零花钱大于平均零花钱的所有学生

    @Query("from Student where money > (select avg(money) from Student)")

    List<Student> findByMoneyBigAvgMoney();

    //查询零花钱在某个范围内并且降序

    @Query("from Student where money between ?1 and ?2 order by money desc")

    List<Student> findByMoneyBetweenToOrderBy(Integer begin ,Integer end);

    //查询全部个数

    @Query("select count(id) from Student")

    Long totalCount();

    /*********************删改操作************************/

    //根据姓名删除

    @Query("delete from Student where name=?1")

    @Modifying

    void deleteByName(String name);

    //更新数据

    @Query("update Student set name=?2 , address=?3 where id=?1")

    @Modifying

    void updateNameAndAddressById(Integer id,String name,String address);

}

//@Repository("studentDao") //这里不用加入IOC容器 Spring默认帮我们注入

public interface StudentDao extends JpaRepository<Student, Integer>, JpaSpecificationExecutor<Student> {

    //使用排序   地址模糊查询后 再按照id降序排列

    @Query("from Student where address like ?1 ")

    List<Student> findByLikeAddressOrderById(String address, Sort sort);

    //使用命名参数  查询年龄和地址模糊查询

    @Query("select s from Student as s where address like :address and age=:age")

    List<Student> findByLikeAddressAndAge(@Param(value="age") Integer s_age,@Param("address") String s_address);

}

/***下面测试方法****/

    @Test   //查询学生带排序

    public void test() {

        //说在前面：Sort如果不指定DESC或者ASC 如 new Sort("id");说明默认对id ASC升序排列

        //设置id为DESC降序排列

        Sort sort = new Sort(Sort.Direction.DESC, "id");

        List<Student> students = sd.findByLikeAddressOrderById("%大%", sort);

        //打印略

    }

    @Test   //使用命名参数

    public void testA() {

        List<Student> students = sd.findByLikeAddressAndAge(23, "%大别山%");

        //打印略

    }

1：查询的条件为null时

    实体介绍：Student里面包含 address为String类型 age为Integer类型

    查询方法定义：List<Student> findByAddressAndAge(String address, Integer age);

    调用时传入值：List<Student> students = sd.findByAddressAndAge(null,23);

    后台生成的SQL：where (student0_.saddress is null) and student0_.sage=?

    结论：当我们传入了一个null值时，表示会去查询数据库中address中字段为null的数据其实在编写此代码传入null值以为会跳过此判断，

         但是恰恰相反，框架会去查询为null的数据。（实现此方式的只能去动态查询，这种简单的查询不能满足要求）

2：排序功能

    List<Student> findByAddressOrderByAge(String address); //名称正序（正序时，推荐此方式，简单）

    List<Student> findByAddressOrderByAgeAsc(String address); //名称正序（效果同上）

    List<Student> findByAddressOrderByAgeDesc(String address); //名称倒序

3：限定查询结果集大小

    ①：Student findFirstByOrderByAgeAsc();

    ②：Student findTopByOrderByAgeAsc();

    说明：对表的全部数据根据age进行Asc（升序）排序后再选择第一条数据返回

    相应SQL：select .... from student student0_ order by student0_.sage asc limit ?  (这里是limit 1)

    注意但是我如果写出：List<Student> findTop3ByOrderByAgeAsc(); 则就是每次返回3条 limit 3

    ③：List<Student> findFirst2ByAddress(String address,Sort sort);

    ④：List<Student> findTop2ByAddress(String address,Sort sort);

    说明：首先进行数据查询并通过Sort进行排序后 再筛选数据列表中最前面的2行数据返回 limit 2

    ⑤：Page<Student> queryFirst2ByAddress(String address, Pageable pageable)

    ⑥：List<Student> queryFirst2ByAddress(String address, Pageable pageable)

    说明：首先进行数据查询 查询全部指定的address后进行分页，分页完后进行数据控制

    控制说明：关于带分页的控制是，假设分页过后查询的数据id为3,4,5 查询出这三条数据后进行数据控制，

              本案例是控制为2条，那返回的id数据就是3,4两条记录，id为5的就舍弃，那么如果数据控制是5条，

              那么就会打印3,4,5另外再去添加6,7补充数据长度

    总结：这里的这里的First和Top意思相同都是最前面，query也和find一样；

    关于一个小点：

        Page<Student> students = sd.queryFirst1ByAddress("安徽大别山",new PageRequest(1,2));

        这里我是返回分页后的第一条数据，可是返回了分页数据的前一个，分页后id是3,4控制数据后First1

        返回了id为2 

4：计数 返回总数

    Long countByAddress(String address);

5：删除

    void deleteByAddress(String address);

    //或 void removeByAddress(String address);

    说明：必须添加事务和回滚，这样根据条件找到几条就删几条

    @Test

    @Transactional

    @Rollback(value=false)

    public void test() {

        sd.deleteByAddress("安徽大别山");

    }

    //对应的SQL语句

    //Hibernate: select .... from student student0_ where student0_.saddress=?

    //Hibernate: delete from student where sid=?

    //Hibernate: delete from student where sid=?

    //Hibernate: delete from student where sid=?

    //Hibernate: delete from student where sid=?

    //Hibernate: delete from student where sid=?

    //Hibernate: delete from student where sid=?

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = "classpath:applicationContext.xml")

public class Client {

    //注入数据

    @Autowired

    @Qualifier(value = "studentDao")

    private StudentDao sd;

    @Test   //多条件查询

    public void testC() {

        //根据年龄和地址查询

        Specification<Student> spe=new Specification<Student>() {

            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                Path<Object> age = root.get("age");

                Path<Object> address = root.get("address");

                Predicate p1 = cb.equal(age, "23");

                Predicate p2 = cb.equal(address, "安徽大别山");

                Predicate select = cb.and(p1, p2);

                return select;

            }

        };

        List<Student> students = sd.findAll();

        //打印略

    }

    @Test   //查询多个条件

    public void testD(){

        //根据精准地址和模糊姓名查询

        List<Student> students = sd.findAll(new Specification<Student>() {

            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                Path<Object> address = root.get("address");

                Path<Object> name = root.get("name");

                Predicate p1 = cb.equal(address, "安徽大别山");

                Predicate p2 = cb.like(name.as(String.class), "李%");

                return cb.and(p1,p2);

            }

        });

        //注！！！：在使用gt、lt、ge、le、like等条件方法查询的时候需要告知传入的属性的class属性

        //如：cb.like(name.as(String.class), "李%");

        //打印略

    }

    @Test //查询全部为指定住址的学生带年龄排序

    public void testE(){

        List<Student> students = sd.findAll(new Specification<Student>() {

            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                return cb.equal(root.get("address"), "安徽大别山");

            }

        }, new Sort(Sort.Direction.DESC, "age"));

    }

    @Test //查询全部带分页

    public void testF(){

        Page<Student> students = sd.findAll(new Specification<Student>() {

            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                return null;    //return null 代表没条件查询全部

            }

        }, new PageRequest(2, 2));

    }

}

-- 一对一关系

-- 删除库

drop database demo_jpa_one_one;

-- 创建库

create database if not exists demo_jpa_one_one charset gbk collate gbk_chinese_ci;

-- 使用库

use demo_jpa_one_one;

# 家庭表创建

create table if not exists family(

    fid int(11) primary key auto_increment, -- 家庭主键

    fmember int(11) not null,               -- 成员个数

    fguardian varchar(10) not null,         -- 监护人

    ftel char(11) not null,                 -- 监护人号码

    fdad varchar(10),                       -- 爸爸姓名

    fmom varchar(10),                       -- 妈妈姓名

    faddress varchar(20)                    -- 家庭详细地址

)charset gbk;

# 创建学生表

create table if not exists student (

    sid int(11) primary key auto_increment,             -- 编号

    sname varchar(5) not null,                          -- 姓名

    ssex enum('男','女') default '男',                  -- 性别

    sage tinyint(11) unsigned not null default 20,      -- 年龄

    smoney decimal(4,1) default 0,                      -- 零花钱 最高999.9

    saddress varchar(10),                               -- 住址

    senrol date default '0000-00-00',                   -- 入学时间

    f_id int(11),                                       -- 连接家庭id

    foreign  key(f_id) references family(fid)           -- 连接家庭id主键

)charset gbk;

# 添加家庭信息

insert into family(fmember,fguardian,ftel,fdad,fmom,faddress)values

(0,"余蒙飘","","戚曦维","余蒙飘","安徽省六安市裕安区"),

(0,"孙恋烈","","梁轮亭","孙恋烈","安徽省合肥市瑶海区"),

(0,"张频时","","张频时","","安徽省安庆市宜秀区"),

(0,"王京正","","王京正","梁昼庭","安徽省六安市金安区"),

(0,"刘资纳","","王谆斌","刘资纳","安徽省滁州市全椒县"),

(0,"白飞解","","廖旺赐","白飞解","安徽省安庆市大观区"),

(0,"梁昀辉","","邬国耿","梁昀辉","安徽省蚌埠市蚌山区"),

(0,"古录钢","","","古录钢","安徽省滁州市定远县"),

(0,"姬桥毅","","姬桥毅","宁竹熊","安徽省合肥市蜀山区"),

(0,"刘始瑛","","刘始瑛","韦欢亿","安徽省淮南市大通区");

# 添加学生数据

insert into student(sid,sname,saddress,f_id)values

(1 ,"王生安","安徽六安",1 ),(2 ,"李鑫灏","安徽合肥",2 ),

(3 ,"薛佛世","安徽蚌埠",3 ),(4 ,"蔡壮保","安徽安庆",4 ),

(5 ,"钱勤堃","安徽合肥",5 ),(6 ,"潘恩依","安徽合肥",6 ),

(7 ,"陈国柏","安徽六安",7 ),(8 ,"魏皑虎","安徽六安",8 ),

(9 ,"周卓浩","安徽六安",9 ),(10,"汤辟邦","安徽六安",10);

-- 更新数据

update student set ssex=ceil(rand()*2),sage=ceil(rand()*5+20),smoney=(rand()*999),

senrol=concat(ceil(rand()*3+2017),'-' , ceil(rand()*12) , '-',ceil(rand()*20));

update family set ftel=concat(if(ceil(rand()*3)=1,if(ceil(rand()*3)=2,if(ceil(rand()*3)=1,

    if(ceil(rand()*3)=2,"155","166"),if(ceil(rand()*3)=3,"163","170")),"164"),

if(ceil(rand()*3)=3,"188","176")),floor(rand()*90000000+9999999)),fmember=ceil(rand()*3+2);

-- 查询数据

show create table student\G

show create table family\G

select * from student;

select * from family;

/**

 * 创建了一个Student的实体-表关系类

 */

@Entity

@Table(name = "student")

public class Student {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(name = "sid")

    private Integer id;

    @Column(name = "sname")

    private String name;

    @Column(name = "ssex")

    private String sex;

    @Column(name = "sage")

    private Integer age;

    @Column(name = "smoney")

    private Double money;

    @Column(name = "saddress")

    private String address;

    @Column(name = "senrol")

    private String enrol;

    //get/set/toString/无参有参构造器 大家自己创建

}

/**

 * 创建了一个Family的实体-表关系类

 */

@Entity

@Table(name = "family")

public class Family {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(name = "fid")

    private Integer id;

    @Column(name = "fmember")

    private Integer member;

    @Column(name = "fguardian")

    private String guardian;

    @Column(name = "ftel")

    private String tel;

    @Column(name = "fdad")

    private String dad;

    @Column(name = "fmom")

    private String mom;

    @Column(name = "faddress")

    private String address;

    //get/set/toString/无参有参构造器 大家自己创建

}

#### 一对多  这里我使用的关系是一个老师对应多个学生，一个学生对应一个老师

#### 老师表为主表、学生表为从表

#### 一对多

-- 删除库

drop database demo_jpa_one_many;

-- 创建库

create database if not exists demo_jpa_one_many charset gbk collate gbk_chinese_ci;

-- 使用库

use demo_jpa_one_many;

-- 创建主表 （辅导员表）

create table if not exists teacher(

    tid int primary key,               -- 编号

    tname varchar(5) not null,         -- 姓名

    tsex enum('男','女') default '男', -- 性别

    tage tinyint unsigned,             -- 年龄

    tsalary decimal(6,1) default 0,    -- 工资 最高99999.9

    taddress varchar(10)               -- 住址

)charset gbk collate gbk_chinese_ci;

-- 创建从表 （学生表）

create table if not exists student (

    sid int(11) primary key auto_increment,             -- 编号

    sname varchar(5) not null,                          -- 姓名

    ssex enum('男','女') default '男',                  -- 性别

    sage tinyint(11) unsigned not null default 20,      -- 年龄

    smoney decimal(4,1) default 0,                      -- 零花钱 最高999.9

    saddress varchar(10),                               -- 住址

    senrol date default '0000-00-00',                   -- 入学时间

    tid int ,                                           -- 连接老师id

    foreign  key(tid) references teacher(tid)  -- 连接老师主键

)charset gbk;

-- 添加老师数据

insert into teacher (tid,tname,taddress)values

(1,'张老师','江苏南京'),

(2,'李老师','江苏无锡'),

(3,'王老师','江苏常熟');

-- 添加学生数据

insert into student(sid,sname,saddress)values

(1 ,"王生安","安徽六安"),(2 ,"李鑫灏","安徽合肥"),

(3 ,"薛佛世","安徽蚌埠"),(4 ,"蔡壮保","安徽安庆"),

(5 ,"钱勤堃","安徽合肥"),(6 ,"潘恩依","安徽合肥"),

(7 ,"陈国柏","安徽六安"),(8 ,"魏皑虎",null);

-- 数据更新

update teacher set tsex=ceil(rand()*2),tage=ceil(rand()*10+25),tsalary=ceil(rand()*3000+8000);

update student set ssex=ceil(rand()*2),sage=ceil(rand()*5+20),smoney=(rand()*999),

senrol=concat(ceil(rand()*3+2017),'-' , ceil(rand()*12) , '-',ceil(rand()*20)),tid=ceil(rand()*3);

/**

 * 创建了一个Teacher的实体-表关系类

 * 在案例中 当前老师表为主表

 */

@Entity

@Table(name = "teacher")

public class Teacher {

    //创建主键字段和普通字段

    @Id

    @Column(name = "tid")

    private Integer id;

    @Column(name = "tname")

    private String name;

    @Column(name = "tsex")

    private String sex;

    @Column(name = "tage")

    private Integer age;

    @Column(name = "tsalary")

    private Double salary;

    @Column(name = "taddress")

    private String address;

    //省略 get/set/有参无参构造器

}

/**

 * 创建了一个Student的实体-表关系类

 * 在案例中 当前学生表为从表

 */

@Entity

@Table(name = "student")

public class Student {

    //创建主键字段和普通字段

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(name = "sid")

    private Integer id;

    @Column(name = "sname")

    private String name;

    @Column(name = "ssex")

    private String sex;

    @Column(name = "sage")

    private Integer age;

    @Column(name = "smoney")

    private Double money;

    @Column(name = "saddress")

    private String address;

    @Column(name = "senrol")

    private String enrol;

    //省略 get/set/有参无参构造器

}

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = "classpath:applicationContext.xml")

public class Client {

    //注入数据

    @Autowired

    @Qualifier(value = "studentDao")

    private StudentDao sd;

    @Autowired

    @Qualifier(value="teacherDao")

    private TeacherDao td;

    @Test   //多条件查询

    @Transactional

    @Rollback(value = false)

    public void testC() {

        Student student=new Student("张小三","男",25,222.3,"安徽六安","2018-8-8");

        Teacher teacher=new Teacher(10,"张老师","男",35,9999.6,"北京顺义");

        //保存

        td.save(teacher);

        sd.save(student);

    }

}

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = "classpath:applicationContext.xml")

public class Client {

    //注入数据

    @Autowired

    @Qualifier(value = "studentDao")

    private StudentDao sd;

    @Autowired

    @Qualifier(value="teacherDao")

    private TeacherDao td;

    @Test   //多条件查询

    @Transactional

    @Rollback(value = false)

    public void testC() {

        //创建2个对象

        Student student=new Student("李小二","男",25,666.3,"安徽六安","2020-6-6");

        Teacher teacher=new Teacher(100,"王老师","男",32,6666.6,"北京顺义");

        //把老师放入学生数据中  把学生数据放入老师表中

        student.setTeacher(teacher);

        teacher.getStudents().add(student);

        //保存 这里注意一下，因为我的teacher主键不是自动生成 ，

        // 所以先保存teacher才可以保存student，因为teacher主键不是自动生成，直接先保存student会无法获取teacher主键

        td.save(teacher);

        sd.save(student);

    }

}

##### 多对多

-- 删除库

drop database demo_jpa_many_many;

-- 创建库

create database if not exists demo_jpa_many_many charset gbk collate gbk_chinese_ci;

-- 使用库

use demo_jpa_many_many;

-- 创建从表 （学生表）

create table if not exists student (

    sid int(11) primary key auto_increment,             -- 编号

    sname varchar(5) not null,                          -- 姓名

    ssex enum('男','女') default '男',                  -- 性别

    sage tinyint(11) unsigned not null default 20,      -- 年龄

    smoney decimal(4,1) default 0,                      -- 零花钱 最高999.9

    saddress varchar(10),                               -- 住址

    senrol date default '0000-00-00'                    -- 入学时间

)charset gbk;

# 学生社团组织

create table if not exists organization(

    oid int(11) primary key auto_increment,  -- 社团主键id

    oname varchar(10) not null unique        -- 社团名称

)charset gbk;

# 中间表 社团和学生对应多对多关系

create table if not exists student_organization(

    soid int(11) primary key auto_increment,         -- 中间表id

    s_id int(11),                                    -- 学生id

    o_id int(11),                                    -- 社团id

    foreign key(s_id) references student(sid),       -- 连接学生id

    foreign key(o_id) references organization(oid)   -- 连接社团id

)charset gbk;

# 添加学生社团组织学习

insert into organization (oname) values

("书法协会"),("法律协会"),("武术协会"),

("魔术社团"),("网球协会"),("啦啦队团");

-- 添加学生数据

insert into student(sid,sname,saddress)values

(1 ,"王生安","安徽六安"),(2 ,"李鑫灏","安徽合肥"),

(3 ,"薛佛世","安徽蚌埠"),(4 ,"蔡壮保","安徽安庆"),

(5 ,"钱勤堃","安徽合肥"),(6 ,"潘恩依","安徽合肥"),

(7 ,"陈国柏","安徽六安"),(8 ,"魏皑虎",null);

# 添加学生和社团的中间表

insert into student_organization(s_id,o_id) values

(1,1),(1,3),(1,2),(1,6),(2,5),(2,1),(2,2),

(6,6),(6,2),(6,4),(6,3),(7,4),(7,2);

在创建映射关系的时候我们得确定哪一方会放弃外键维护权，

在多对多的时候我们通常放弃的被动的一方

@Entity

@Table(name = "student")

public class Student {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(name = "sid")

    private Integer id;

    @Column(name = "sname")

    private String name;

    @Column(name = "ssex")

    private String sex;

    @Column(name = "sage")

    private Integer age;

    @Column(name = "smoney")

    private Double money;

    @Column(name = "saddress")

    private String address;

    @Column(name = "ssenrol")

    private String senrol;

//    @ManyToMany(targetEntity = Organization.class)

//    @JoinTable(name = "student_organization",

//            joinColumns = {@JoinColumn(name = "s_id", referencedColumnName = "sid")} ,

//            inverseJoinColumns = {@JoinColumn(name = "o_id", referencedColumnName = "oid")}

//    )

    @ManyToMany(mappedBy = "students")

    private Set<Organization> organizations = new HashSet<Organization>();

}

@Entity

@Table(name = "organization")

public class Organization {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(name = "oid")

    private Integer id;

    @Column(name = "oname")

    private String name;

    @ManyToMany(targetEntity = Student.class)

    @JoinTable(name = "student_organization",

            joinColumns ={@JoinColumn(name = "o_id",referencedColumnName = "oid")} ,

            inverseJoinColumns = {@JoinColumn(name = "s_id",referencedColumnName = "sid")}

    )

    private Set<Student> students=new HashSet<Student>();

}

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = "classpath:applicationContext.xml")

public class Client {

    @Autowired

    @Qualifier(value = "studentDao")

    private StudentDao sd;

    @Autowired

    @Qualifier(value = "organizationDao")

    private OrganizationDao od;

    @Test

    @Transactional

    @Rollback(value = false)

    public void fun() {

        Student student=new Student("张三","男",45,555.6,"安徽六安","2012-8-8");

        Organization o=new Organization("魔术社");

        student.getOrganizations().add(o);

        o.getStudents().add(student);

        od.save(o);

        sd.save(student);

    }

}

<property name="jpaProperties">

            <props>

                <prop key="hibernate.hbm2ddl.auto">create</prop>

                <!--设置数据库方言-->

                <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>

            </props>

        </property>

<!--数据库方言-->

DB2                         org.hibernate.dialect.DB2Dialect

DB2 AS/400                  org.hibernate.dialect.DB2400Dialect

DB2 OS390                   org.hibernate.dialect.DB2390Dialect

PostgreSQL                  org.hibernate.dialect.PostgreSQLDialect

MySQL                       org.hibernate.dialect.MySQLDialect

MySQL with InnoDB           org.hibernate.dialect.MySQLInnoDBDialect

MySQL with MyISAM           org.hibernate.dialect.MySQLMyISAMDialect

Oracle (any version)        org.hibernate.dialect.OracleDialect

Oracle 9i/10g               org.hibernate.dialect.Oracle9Dialect

Sybase                      org.hibernate.dialect.SybaseDialect

Sybase Anywhere             org.hibernate.dialect.SybaseAnywhereDialect

Microsoft SQL Server        org.hibernate.dialect.SQLServerDialect

SAP DB                      org.hibernate.dialect.SAPDBDialect

Informix                    org.hibernate.dialect.InformixDialect

HypersonicSQL               org.hibernate.dialect.HSQLDialect

Ingres                      org.hibernate.dialect.IngresDialect

Progress                    org.hibernate.dialect.ProgressDialect

Mckoi SQL                   org.hibernate.dialect.MckoiDialect

Interbase                   org.hibernate.dialect.InterbaseDialect

Pointbase                   org.hibernate.dialect.PointbaseDialect

FrontBase                   org.hibernate.dialect.FrontbaseDialect

Firebird                    org.hibernate.dialect.FirebirdDialect