第五次课后练习

最新推荐文章于 2024-10-20 21:16:19 发布

m0_75191259

最新推荐文章于 2024-10-20 21:16:19 发布

阅读量296

点赞数 3

文章标签：数据库

本文链接：https://blog.csdn.net/m0_75191259/article/details/138229447

版权

1、在idea中配置好数据源

建mybatis数据库和user数据表和实体类User

create table user(
                     id int unsigned primary key auto_increment comment 'ID',
                     name varchar(100) comment '姓名',
                     age tinyint unsigned comment '年龄',
                     gender tinyint unsigned comment '性别, 1:男, 2:女',
                     phone varchar(11) comment '手机号'
) comment '用户表';

insert into user(id, name, age, gender, phone) VALUES (null,'白眉鹰王',55,'1','18800000000');
insert into user(id, name, age, gender, phone) VALUES (null,'金毛狮王',45,'1','18800000001');
insert into user(id, name, age, gender, phone) VALUES (null,'青翼蝠王',38,'1','18800000002');
insert into user(id, name, age, gender, phone) VALUES (null,'紫衫龙王',42,'2','18800000003');
insert into user(id, name, age, gender, phone) VALUES (null,'光明左使',37,'1','18800000004');
insert into user(id, name, age, gender, phone) VALUES (null,'光明右使',48,'1','18800000005');

引入mybatis的相关依赖，配置mybatis

resources-application.properties中：

#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
#连接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=1234

2、视频案例中只给出了查询所有结果的示例，请自己完成添加、删除、修改操作的代码。以下供参考。

test

package com.ithema;

import com.ithema.mapper.UserMapper;
import com.ithema.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest//springboot整合单元测试的注解
class SpringbootMybatisQuickstartApplicationTests {

    @Autowired//依赖注入
    private UserMapper userMapper;

    //查询所有用户信息
    @Test//方法一
    public void listAll(){
        List<User> userList1 = userMapper.list1();
        for (User user : userList1) {
            System.out.print(user.getId()+",");
            System.out.print(user.getName()+",");
            System.out.print(user.getAge()+",");
            System.out.print(user.getGender()+",");
            System.out.println(user.getPhone());
        }
    }
    @Test//方法二
    public void testListUser(){
        List<User> userList2=userMapper.list2();
        userList2.stream().forEach(user -> {
            System.out.println(user);
        });
    }

    //添加用户信息
    @Test
    public void insert() {
// User c  =new User(20,"qqq",(short)55,(short)2,"123334");

        User c = new User();
        c.setName("王维");
        c.setGender((short) 1);
        c.setAge((short) 1);
        c.setPhone("12343234543");
        userMapper.insert(c);
        listAll();
    }

    //更改用户信息
    @Test
    public void update() {
        User c= userMapper.get(7);
        c.setName("杜甫");
        userMapper.update(c);
        listAll();
    }

    //查询其中一条用户信息
    @Test
    public void get() {
        User c= userMapper.get(6);
        System.out.print(c.getId()+",");
        System.out.print(c.getName()+",");
        System.out.print(c.getAge()+",");
        System.out.print(c.getGender()+",");
        System.out.println(c.getPhone());
    }

    //删除一条用户信息
    @Test
    public void delete() {
        userMapper.delete(7);
        listAll();
    }
}

UserMapper

package com.ithema.mapper;

import com.ithema.pojo.User;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

@Mapper//在运行时，会自动生成该接口的实现类对象，并且将该对象交给IOC容器管理（相当于Dao）
public interface UserMapper {

    //查询所有用户数据
    //方法一
    @Select("select id, name, age, gender, phone from user")
    public List<User> list1();
    //方法二(用到了User类中的toString方法）
    @Select("select * from user")
    public List<User> list2();

    //插入一条用户信息
    @Insert("insert into user(name, age, gender, phone) values (#{name}, #{age}, #{gender}, #{phone})")
    public void insert(User user);

    //删除一条用户信息
    @Delete(" delete from user where id= #{id} ")
    public void delete(int id);

    //用id查询一条用户信息
    @Select("select * from user where id= #{id} ")
    public User get(int id);

    //更改一个用户的姓名信息
    @Update("update user set name=#{name} where id=#{id} ")
    public int update(User user);
}

3、idea的sql 控制台，可以测试sql语句是否正确。所以mapper接口中的sql语句可以先测试。对于有参数的情况，用？代替。

4、lombok库的使用

package com.ithema.pojo;

import lombok.*;

/*@Getter
@Setter
@ToString
@EqualsAndHashCode*/
/*以上四个相当于*/@Data
@NoArgsConstructor //无参构造
@AllArgsConstructor //全参构造
public class User {
    private Integer id;   //id（主键）
    private String name;  //姓名
    private Short age;    //年龄
    private Short gender; //性别
    private String phone; //手机号

   /* public User(Integer id, String name, Short age, Short gender, String phone) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.phone = phone;
    }

    public User() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Short getAge() {
        return age;
    }

    public void setAge(Short age) {
        this.age = age;
    }

    public Short getGender() {
        return gender;
    }

    public void setGender(Short gender) {
        this.gender = gender;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString(){
        return "User{"+
                "id="+id+
                ",name='"+name+'\''+
                ",age="+age+
                ",gender="+gender+
                ",phone='"+phone+'\''+
                '}';
    }*/
}

5、学习idea的调试技巧，并尝试使用。

debug步骤

1)添加断点（行断点、方法断点、字段断点、条件断点）

2）启动调试

3）单步执行

4）观察变量和执行流程，并找到解决问题

6、对以下案例使用mybatis进行添加、删除、修改、更新的操作。（不需要实现页面，test中控制台能输出结果就行。）

准备数据库表

create table poet (
                     编号 int unsigned primary key auto_increment comment '编号',
                     姓名 varchar(20) not null unique comment '姓名',
                     性别 varchar(20) not null comment '性别',
                     朝代 varchar(50) not null comment '朝代',
                     头衔 varchar(50) not null comment '头衔',
                     风格 varchar(50) not null comment '风格'

) comment '诗人信息列表';

INSERT INTO poet
(编号,姓名, 性别, 朝代, 头衔, 风格) VALUES
(1,'陶渊明','男','东晋末至南朝宋初期','诗人和辞赋家','古今隐逸诗人之宗'),
(2,'王维','男','唐代','诗佛','空灵、寂静'),
(3,'李白','男','唐代','诗仙','豪放飘逸的诗风和丰富的想象力'),
(4,'李商隐','男','唐代','诗坛鬼才','无'),
(5,'李清照','女','宋代','女词人','婉约风格'),
(6,'杜甫','男','唐代','诗圣','反映社会现实和人民疾苦'),
(7,'苏轼','男','北宋','文学家、书画家，诗神','清新豪健的诗风和独特的艺术表现力');

删除、新增、更新、查询

poetMapper

package com.itheima.mapper;

import com.itheima.pojo.Poet;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PoetMapper {

    //根据id删除数据
    @Delete("delete from poet where id=#{id}")
    public void delete(Integer id);
    //public int delete(Integer id);//返回删除操作的次数

    //新增
    @Options(useGeneratedKeys = true,keyProperty = "id")//返回id
    @Insert("insert into poet(id,author,gender,dynasty,title,style)"+
            "values (#{id},#{author},#{gender},#{dynasty},#{title},#{style})")
    public void insert(Poet poet);

    //更新
    @Update("update poet set author=#{author},gender=#{gender},dynasty=#{dynasty},title=#{title},style=#{style} where id=#{id};")
    public void update(Poet poet);

    //根据id查询
    @Select("select * from poet where id=#{id}")
    public Poet getById(Integer id);

    //条件查询
    @Select("select * from poet where/* author like '%${author}%' and */gender=#{gender}")
    public List<Poet> list(/*String author,*/String gender);
}

test

package com.itheima;

import com.itheima.mapper.PoetMapper;
import com.itheima.pojo.Poet;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class SpringbootMybatisCrudApplicationTests {

    @Autowired
    private PoetMapper poetMapper;

    //删除
    @Test
    public void testDelete(){
        poetMapper.delete(10);
        /*int delete = poetMapper.delete(7);
        System.out.println(delete);*///返回操作的次数
    }

    //新增
    @Test
    public void testInsert(){
        Poet poet=new Poet();
        poet.setId(9);
        poet.setAuthor("sushi3");
        poet.setGender("男");
        poet.setDynasty("北宋");
        poet.setTitle("文学家、书画家，诗神");
        poet.setStyle("清新豪健的诗风和独特的艺术表现力");

        poetMapper.insert(poet);
        System.out.println(poet.getId());
    }

    //更新
    @Test
    public void testUpdate(){
        Poet poet=new Poet();
        poet.setId(9);
        poet.setAuthor("ssss3");
        poet.setGender("男");
        poet.setDynasty("北宋");
        poet.setTitle("文学家、书画家，诗神");
        poet.setStyle("清新豪健的诗风和独特的艺术表现力");

        poetMapper.update(poet);
    }

    //根据id查询
    @Test
    public void testGetById(){
        Poet poet=poetMapper.getById(7);
        System.out.println(poet);
    }

    //条件查询
    @Test
    public void testList(){
        List<Poet> poetList =poetMapper.list(/*"李",*/"男");
        System.out.println(poetList);
    }
}