SpringBoot的创建与在mybatis中编写sql语句

目录

一.创建spring项目

1.1 这里类型选择maven(不然没有pom文件)别选Gradle-Groovy

 1.2导入mysql ,mybatis,与lombok依赖

1.3在resources中创建application.properties 与application-pro.properties文件 用于连接数据库

 application.properties

application-pro.properties

二用mybatis中编写sql语句

2.1创建dao包,domain包,以及resourse下的mapper包

 2.2在domain中创建实体类color User

2.3在dao中写抽象方法

2.4在UserDao.xml文件中写sql语句

三在测试类中进行测试

​编辑

3.1添加@SpringBootTest注解进行测试

---

一.创建spring项目

1.1 这里类型选择maven(不然没有pom文件)别选Gradle-Groovy

 

 1.2导入mysql ,mybatis,与lombok依赖

            <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId> <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!-- mysql-connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

1.3在resources中创建application.properties 与application-pro.properties文件 用于连接数据库

 application.properties

spring.profiles.active=pro

application-pro.properties

server.port=8082

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/user_a?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
mybatis-plus.mapper-locations=classpath:mapper/*Dao.xml

logging.level.root=info
logging.level.cn.woniu.springbootdays1.dao=debug

二用mybatis中编写sql语句

2.1创建dao包,domain包,以及resourse下的mapper包

 2.2在domain中创建实体类color User

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Color {
    private Integer id;
    private String color;
    private String type;
    private Integer userId;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer id;
    private String userName;
   private List<Color> colors;
}

2.3在dao中写抽象方法

public interface UserDao {
    /**
     * 根据id与user查询
     * @param map
     * @return
     */
    List<User> getUserById(Map map);

    /**
     * 根据id变为list集合查询
     * @param ids
     * @return
     */
    List<User>  getUserByIdList(List<Integer> ids);
    /**
     * 批量插入
     * @param users
     */
     void batchInsert(List<User> users);

    /**
     * 条件查询
     * @param user
     * @return
     */
    List<User>  queryCondition(User user);

    /**
     * 连接查询
     * @return
     */
    List<User> queqyUserColor();

}

2.4在UserDao.xml文件中写sql语句

<?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">
<mapper namespace="cn.springbootdemo02.dao.UserDao">

    <insert id="batchInsert" >
        insert into
            user(id,username)
        values
            <foreach collection="users" item="user" separator=",">
            (#{user.id},#{user.userName})
            </foreach>
    </insert>
    <!--键值对查询-->
    <select id="getUserById" resultType="cn.springbootdemo02.domain.User" parameterType="java.util.Map">
            select id,username
            from
                user
            where
                id=#{id} and username=#{userName}
    </select>
    <!--根据id集合List查询-->
    <select id="getUserByIdList" resultType="cn.springbootdemo02.domain.User">
        select id,username from user
        <where>
            <if test="ids !=null and  ids.size()!=0">
                id in
                           <foreach collection="ids" item="id" open="(" separator="," close=")">
                               #{id}
                           </foreach>
            </if>
        </where>
    </select>
    <select id="queryCondition" resultType="cn.springbootdemo02.domain.User">
        select id,username from user
<where>
        <choose>
            <when test="id ==1">
                    id=2
            </when>
            <when test="id==2">
                id=3
            </when>
    <otherwise></otherwise>
        </choose>
</where>

    </select>
    <select id="queqyUserColor" resultMap="userMap">
        select
            u.id,
            u.username,
            t.color,
            t.type,
            t.user_id
        from
            user as u
                left join t_color as t
                          on u.id=t.user_id
    </select>

    <resultMap id="userMap" type="cn.springbootdemo02.domain.User">
        <result column="id" property="id"></result>
        <result column="username" property="userName"></result>
        <collection property="colors" ofType="cn.springbootdemo02.domain.Color">
            <result column="id" property="id"></result>
            <result column="color" property="color"></result>
            <result column="type" property="type"></result>
            <result column="user_id" property="userId"></result>
        </collection>
    </resultMap>

</mapper>

三在测试类中进行测试

3.1添加@SpringBootTest注解进行测试

@SpringBootTest
class SpringbootDemo02ApplicationTests {
    @Autowired(required = false)
        private UserDao userDaoMapper;
    @Test
    void contextLoads() {
    }
    @Test
    void getUserByIda(){
        HashMap map=new HashMap();
        map.put("id","1");
        map.put("userName","aaaa");
        List<User> users=userDaoMapper.getUserById(map);
        users.forEach(e -> System.out.println(e));

    }
    @Test
    void getUserByIdList(){
       List<Integer> list= new ArrayList<>();
       list.add(1);
       list.add(2);
        List<User> userByIdList = userDaoMapper.getUserByIdList(list);
        userByIdList.forEach(e -> System.out.println(e));
    }

    @Test
    void getInsertUser(){
//        List<User> users=new ArrayList<>();
//        User user1 = new User(4, "wasd");
//        User user2 = new User(5, "wlx");
//        users.add(user1);
//        users.add(user2);
//        userDaoMapper.batchInsert(users);
    }
    @Test
    void queryCondition(){
        User user = new User();
        user.setId(1);
        List<User> users = userDaoMapper.queryCondition(user);
        users.forEach(e -> System.out.println(e));
    }
    @Test
    void queqyUserColor(){
        List<User> list= userDaoMapper.queqyUserColor();
       list.forEach(e -> System.out.println(e));
    }
}