springboot+mybatis+druid学习笔记

学习网址

1. mybatis中文官网
2. 使用resultMap实现高级结果映射
3. spring boot+mybatis整合

引入依赖

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.18</version>
        </dependency>

1）、配置数据源相关属性
配置文件application.yml

spring:
  datasource:
#   数据源基本配置
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/demo?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
    type: com.alibaba.druid.pool.DruidDataSource
#   数据源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
#   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙  
    filters: stat,wall,slf4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true  
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml
  type-aliases-package: com.example.demodruid.bean  #全局的映射，不用在xml文件写实体类的全路径
  configuration:
    map-underscore-to-camel-case: true  #开启驼峰映射

druid配置类：

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return  new DruidDataSource();
    }

    //配置Druid的监控
    //1、配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();

        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默认就是允许所有访问
        //initParams.put("deny","192.168.15.21");

        bean.setInitParameters(initParams);
        return bean;
    }


    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return  bean;
    }
}

​ 2）、给数据库建表

​ 3）、创建JavaBean

4）、注解版

//指定这是一个操作数据库的mapper
@Mapper
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

5) 映射文件版

<?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="com.example.demodruid.mapper.EmployeeMapper">
   <!--    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);-->

    <resultMap id="emp_dep" type="com.example.demodruid.bean.emp_dep">
        <id property="id" column="id"></id>
        <result property="lastName" column="lastName"></result>
        <result property="gender" column="gender"></result>
        <result property="email" column="email"></result>
        <result property="dId" column="d_id"></result>
        <association property="department" column="department">
            <result property="departmentName" column="departmentName"></result>
        </association>
    </resultMap>

    <select id="getEmpAndDep" resultMap="emp_dep">
        SELECT * FROM employee,department
        WHERE employee.d_id=department.id
    </select>
    <select id="getEmpById" resultType="com.example.demodruid.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>

    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
    </insert>
    <insert id="insertSelective" parameterType="com.example.demodruid.bean.Employee" >
        insert into Employee
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="lastName != null" >
                lastName,
            </if>
            <if test="email != null" >
                email,
            </if>
            <if test="gender != null" >
                gender,
            </if>
            <if test="dId != null" >
                d_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
            <if test="lastName != null" >
                #{lastName,jdbcType=VARCHAR},
            </if>
            <if test="email != null" >
                #{email,jdbcType=VARCHAR},
            </if>
            <if test="gender != null" >
                #{gender,jdbcType=INTEGER},
            </if>
            <if test="dId != null" >
                #{d_id,jdbcType=INTEGER},
            </if>
        </trim>
    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="com.example.demodruid.bean.Employee" >
        update employee
        <set >
            <if test="lastName != null" >
                lastName = #{lastName,jdbcType=VARCHAR},
            </if>
            <if test="email != null" >
                email = #{email,jdbcType=VARCHAR},
            </if>
            <if test="gender != null" >
                gender = #{gender,jdbcType=INTEGER},
            </if>
            <if test="dId != null" >
                d_id = #{dId,jdbcType=INTEGER},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

</mapper>

//@Mapper或者@MapperScan将接口扫描装配到容器中
@Mapper
public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);

    public void insertSelective(Employee employee);

    public void updateByPrimaryKeySelective(Employee employee);

    public List<emp_dep> getEmpAndDep();
}

6） 、@MapperScan将接口扫描装配到容器中

使用MapperScan批量扫描所有的Mapper接口；
@MapperScan(value = "com.atguigu.springboot.mapper")
@SpringBootApplication
public class SpringBoot06DataMybatisApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringBoot06DataMybatisApplication.class, args);
	}
}

访问：http://localhost:8080/druid/login
截图：