SpringBoot（6）数据库操作_整合Mybatis

最新推荐文章于 2023-03-15 20:41:52 发布

hyc9528

最新推荐文章于 2023-03-15 20:41:52 发布

阅读量169

点赞数

分类专栏： SpringBoot 文章标签： spring boot spring

本文链接：https://blog.csdn.net/hyc9528/article/details/109455975

版权

SpringBoot 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

环境准备

导入依赖
编写application.yml配置文件

spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    #    url: jdbc:mysql:///test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    url: jdbc:mysql://192.168.253.138:3306/mybatis?useUnicode=true&characterEncoding=utf8
    type: com.alibaba.druid.pool.DruidDataSource
#    schema:
#        - classpath:sql/department.sql
#        - classpath:sql/employee.sql

    #   数据源其他配置
    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,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

编写DruidConfig配置类

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

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

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

        bean.setInitParameters(inParams);
        return bean;
    }

    //2. 配置一个filter
    @Bean
    public FilterRegistrationBean WebStatFilter(){
        FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
        bean.setFilter(new WebStatFilter());

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

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

        return bean;
    }

新建数据库，添加建表sql
添加javaBean实体类

注解版Mybatis

编写mapper文件

//制定这是一个操作数据库的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);

    //添加主键自增配置并告诉springboot主键是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);
}

controller中测试mybatis

    @Autowired
    DepartmentMapper mapper;

    @GetMapping("/dept/{id}")
    public Department getDepartment(@PathVariable("id") Integer id){
        return mapper.getDeptByid(id);
    }

    @GetMapping("/dept")
    public Department insertDept(Department department){
        mapper.insertDept(department);
        return department;
    }

插入department：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hDs1Fy1Q-1604317759741)(https://note.youdao.com/yws/api/personal/file/4A587AF54D2E459D9EE5A3AB3D30FB46?method=getImage&version=1850&cstk=GsetouYl)]
查询department：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FnuwDLRO-1604317759743)(https://note.youdao.com/yws/api/personal/file/2FCF3A45B3D74F3DA4F35D225AE9B6BB?method=getImage&version=1851&cstk=GsetouYl)]

编写配置类MybatisConfig

@Configuration
public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer(){

            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                /*开启驼峰命名法*/
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

配置xml版Mybatis

新建mapper类EmployeeMapper

@Mapper
public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);

}

resource中新建文件夹mybatis，添加mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!--开启驼峰命名发-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

mybatis中新建mapper文件，添加sqlMapper.xml

<?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.hyc.springboot.mapper.EmployeeMapper">
    <!--
    public Employee getEmpById(Integer id);
    public void insertEmp(Employee employee);
    -->
    <select id="getEmpById" resultType="com.hyc.springboot.bean.Employee">
        select * from employee where id = #{id};
    </select>

    <insert id="insertEmp">
        insert into employee(lastName,email,gender,d_id) values(#{lastName},#{email},#{gender},#{d_id});
    </insert>
</mapper>

application.xml中补充Mybatis配置文件位置

mybatis:
  config-location: classpath:mybatis/mybatis-config.xml # mybatis总配置文件位置
  mapper-locations: classpath:mybatis/mapper/*.xml      # sqlMapper文件位置