《web应用技术》第5次课后作业

1、在idea中配置好数据源

• 准备工作（创建springboot工程、数据库表user、实体类User）
• 引入相关依赖，配置数据库连接信息
• 编写SQL语句（注解/XML）

创建数据表user

创建实体类 User

package com.caz.pojo;

public class User {
    private Integer id;
    private String name;
    private short age;
    private short gender;
    private String phone;

    public User() {
    }

    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 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 + '\'' +
                '}';
    }
}

配置数据库连接信息

编写SQL语句

package com.caz;

import com.caz.mapper.UserMapper;
import com.caz.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 MybatisApplicationTests {
    @Autowired//依赖注入
    private UserMapper userMapper;

    @Test
    public void testListUser(){
        List<User> userList = userMapper.list();//调用接口中的list方法来查询
        userList.stream().forEach(user ->{
            System.out.println(user);
        });
    }
}

测试程序运行结果

2、视频案例中只给出了查询所有结果的示例，在这个基础上完成添加、删除、修改操作的代码。

插入信息

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

 @Test
    public void testInsert(){
        //构造对象
        User user=new User();
        user.setName("陈安志");
        user.setAge((short) 20);
        user.setGender((short)1);
        user.setPhone("15671629190");
        userMapper.insert(user);
    }

 

删除信息

//删除指定id的信息
    @Delete("delete from user where id=#{id}")
    public void delete(Integer id);

@Test
    public void testDelete() {
        userMapper.delete(3);
        testListUser();
    }

修改信息 

@Update("update user set name=#{name} where id=#{id} ")
    public void update(User user);

@Test
    public void testUpdate(){
        List<User> userList = userMapper.list();
        User user=userList.get(2);
        user.setName("222");
        userMapper.update(user);
        testListUser();
    }

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

4、lombok库的使用。尝试将实体类采用注解的方式来实现。 （通过添加注解，使其自动为实体类中的属性生成相应的方法）

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

首先创建表writer,并插入信息

create table writer(
                       id int unsigned primary key auto_increment comment 'ID',
                       name varchar(5) comment '姓名',
                       gender char comment '性别',
                       dynasty varchar(10) comment '朝代',
                       title varchar(15) comment '头衔',
                       style varchar(20) comment '风格'
) comment '诗人信息';
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'陶渊明','男','东晋末至南朝宋初期','诗人和辞赋家','古今隐逸诗人之宗');
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'王维','男','唐代','诗佛','空灵、寂静');
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'李白','男','唐代','诗仙','豪放飘逸的诗风和丰富的想象力');
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'李商隐','女','唐代','诗坛鬼才','无');
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'李清照','女','宋代','女词人','婉约风格');
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'杜甫','男','唐代','诗圣','反映社会现实和人民疾苦');
insert into writer(id, name, gender, dynasty,title,style) VALUES (null,'苏轼','男','北宋','文学家、书画家，诗神','清新豪健的诗风和独特的艺术表现力');

创建实体类Writer 

package com.caz.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Writer {
    private Integer id;
    private String name;
    private char gender;
    private String dynasty;
    private String title;
    private String style;
}

查找

@Select("select * from writer")//查询注解,表明执行查询操作
    public List<Writer> list();

 @Test
    public void testListWriter(){
        List<Writer> writerList = writerMapper.list();//调用接口中的list方法来查询
        writerList.stream().forEach(writer ->{
            System.out.println(writer);
        });
    }

 

删除

//删除指定id的信息
    @Delete("delete from writer where id=#{id}")
    public void delete(Integer id);

@Test
    public void writerDelete() {
        writerMapper.delete(3);
        testListWriter();//列出结果
    }

 

插入

@Test
    public void writerInsert(){
        //构造对象
        Writer writer=new Writer();
        writer.setName("王安石");
        writer.setGender('男');
        writer.setDynasty("唐代");
        writer.setTitle("五柳先生");
        writer.setStyle("含蓄深婉");
        writerMapper.insert(writer);
        testListWriter();//列出结果
    }

//插入信息
    @Insert("insert into writer(name, gender, dynasty, title,style) values (#{name}, #{gender}, #{dynasty}, #{title},#{style})")
    public void insert(Writer writer);

 

更新 

//更新信息
    @Update("update writer set title=#{title} where id=#{id} ")
    public void update(Writer writer);

@Test
    public void writerUpdate(){
        Writer writer=new Writer();
        writer.setId(9);
        writer.setTitle("临川先生");
        writerMapper.update(writer);
        testListWriter();
    }