Spring Boot+mybatis集成数据库访问

1、整合druid数据源

1.1 单数据源配置

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.1.10</version>
</dependency>
?
@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;
  }
}
spring.datasource.druid.initial-size=10
spring.datasource.druid.min-idle=10
spring.datasource.druid.max-active=15
spring.datasource.druid.filters=stat,wall,log4j2
spring.datasource.druid.filter.config.enabled=true
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
spring.datasource.druid.filter.wall.config.delete-allow=true
spring.datasource.druid.filter.wall.config.drop-table-allow=false
spring.datasource.druid.filter.log4j2.enabled=true
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin@zt3000
spring.datasource.druid.stat-view-servlet.use-global-data-source-stat=true
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.aop-patterns=com.xxxxxxxxxxx.bmp.dao.*
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver

 

1.2 多数据源配置

spring:
# 数据源配置
datasource:
  ds1: #数据源1
    driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
    url: jdbc:mysql://ip:3306/db1 #数据源地址
    username: root # 用户名
    password: root # 密码
  ds2: # 数据源2
    driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
    url: jdbc:mysql://ip:3307/db2#数据源地址
    username: root # 用户名
    password: root # 密码
/**
* 多数据源配置
*/
@Configuration
public class DataSourceConfig {
?
  //主数据源配置 ds1数据源
  @Primary
  @Bean(name = "ds1DataSourceProperties")
  @ConfigurationProperties(prefix = "spring.datasource.ds1")
  public DataSourceProperties ds1DataSourceProperties() {
      return new DataSourceProperties();
   }
?
  //主数据源 ds1数据源
  @Primary
  @Bean(name = "ds1DataSource")
  public DataSource ds1DataSource(@Qualifier("ds1DataSourceProperties") DataSourceProperties dataSourceProperties) {
      return dataSourceProperties.initializeDataSourceBuilder().build();
   }
?
  //第二个ds2数据源配置
  @Bean(name = "ds2DataSourceProperties")
  @ConfigurationProperties(prefix = "spring.datasource.ds2")
  public DataSourceProperties ds2DataSourceProperties() {
      return new DataSourceProperties();
   }
?
  //第二个ds2数据源
  @Bean("ds2DataSource")
  public DataSource ds2DataSource(@Qualifier("ds2DataSourceProperties") DataSourceProperties dataSourceProperties) {
      return dataSourceProperties.initializeDataSourceBuilder().build();
   }
?
}

2、整合Mybatis

2.1 简单整合

2.1.1 添加依赖
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
   <groupId>org.mybatis.spring.boot</groupId>
   <artifactId>mybatis-spring-boot-starter</artifactId>
   <version>2.0.0</version>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.10</version>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.28</version>
   <scope>runtime</scope>
</dependency>
2.1.2 配置数据源
spring.datasource.url=jdbc:mysql:///testdb?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
2.1.3 创建Mapper (基于注解)
public interface UserMapper {
   @Select("select * from user")
   List<User> getAllUsers();
?
   @Results({
           @Result(property = "id", column = "id"),
           @Result(property = "username", column = "u"),
           @Result(property = "address", column = "a")
  })
   @Select("select username as u,address as a,id as id from user where id=#{id}")
   User getUserById(Long id);
?
   @Select("select * from user where username like concat(‘%‘,#{name},‘%‘)")
   List<User> getUsersByName(String name);
?
   @Insert({"insert into user(username,address) values(#{username},#{address})"})
   @SelectKey(statement = "select last_insert_id()", keyProperty = "id", before = false, resultType = Integer.class)
   Integer addUser(User user);
?
   @Update("update user set username=#{username},address=#{address} where id=#{id}")
   Integer updateUserById(User user);
?
   @Delete("delete from user where id=#{id}")
   Integer deleteUserById(Integer id);
}
2.1.4 创建Mapper (基于XML文件)
public interface UserMapper {
   List<User> getAllUser();
?
   Integer addUser(User user);
?
   Integer updateUserById(User user);
?
   Integer deleteUserById(Integer id);
}
<?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.boot.mybatis.mapper.UserMapper">
   <select id="getAllUser" resultType="org.sang.mybatis.model.User">
      select * from t_user;
   </select>
   <insert id="addUser" parameterType="org.sang.mybatis.model.User">
      insert into user (username,address) values (#{username},#{address});
   </insert>
   <update id="updateUserById" parameterType="org.sang.mybatis.model.User">
      update user set username=#{username},address=#{address} where id=#{id}
   </update>
   <delete id="deleteUserById">
      delete from user where id=#{id}
   </delete>
</mapper>
2.1.5 Mapper配置文件位置
2.1.5.1 放到UserMapper类同级

必须配置资源插件,因为boot默认只会加载resources的目录文件

<build>
   <resources>
       <resource>
           <directory>src/main/java</directory>
           <includes>
               <include>**/*.xml</include>
           </includes>
       </resource>
       <resource>
           <directory>src/main/resources</directory>
       </resource>
   </resources>
</build>
2.1.5.1 放到resources文件夹中指定的目录

在application.properties中告诉mybatis去哪里扫描mapper:

mybatis.mapper-locations=classpath:mapper/*.xml

2.2 逆向工程

2.2.1 添加逆向工程插件
<plugin>
       <groupId>org.mybatis.generator</groupId>
       <artifactId>mybatis-generator-maven-plugin</artifactId>
       <version>1.4.0</version>
       <configuration>
           <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
           <verbose>true</verbose>
           <overwrite>true</overwrite>
       </configuration>
   </plugin>
2.2.2 指定逆向工程配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
       PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
       "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
?
<generatorConfiguration>
   <classPathEntry
           location="D:\installed\devtools\maven\repository\mysql\mysql-connector-java\5.1.38\mysql-connector-java-5.1.38.jar"/>
   <context id="mySQL" targetRuntime="MyBatis3">
       <commentGenerator>
           <property name="suppressDate" value="true"/>
           <property name="suppressAllComments" value="true"/>
       </commentGenerator>
?
       <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                       connectionURL="jdbc:mysql://localhost:3306/2pc" userId="root"
                       password="123">
       </jdbcConnection>
?
       <javaTypeResolver>
           <property name="forceBigDecimals" value="false"/>
       </javaTypeResolver>
?
       <!--实体生成的配置-->
       <javaModelGenerator targetPackage="com.mmren.edu.domain"
                           targetProject=".\src\main\java">
           <property name="enableSubPackages" value="true"/>
           <property name="trimStrings" value="true"/>
       </javaModelGenerator>
?
       <!--mapper接口生成配置-->
       <sqlMapGenerator targetPackage="com.mmren.edu.mapper"
                        targetProject=".\src\main\java">
           <property name="enableSubPackages" value="true"/>
       </sqlMapGenerator>
?
       <!--mapper对应的映射文件生成配置-->
       <javaClientGenerator type="XMLMAPPER"
                            targetPackage="com.mmren.edu.mapper" targetProject=".\src\main\java">
           <property name="enableSubPackages" value="true"/>
       </javaClientGenerator>
?
       <!--表对应实体名称的配置-->
       <table tableName="t_order" domainObjectName="OrderInfo"
              enableCountByExample="false"
              enableUpdateByExample="false"
              enableDeleteByExample="false"
              enableSelectByExample="false"
              selectByExampleQueryId="false"/>
   </context>
</generatorConfiguration>

Spring Boot+mybatis集成数据库访问

生成实体,mapper,mapper配置文件

2.3 通用Mapper

通用Mapper就是为了解决单表增删改查,基于Mybatis的插件。开发人员不需要编写SQL,不需要在DAO中增加方法,只要写好实体类,就能支持相应的增删改查方法。

2.3.1 添加依赖
<dependency>
   <groupId>tk.mybatis</groupId>
   <artifactId>mapper-spring-boot-starter</artifactId>
   <version>2.1.5</version>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.38</version>
</dependency>
2.3.2 添加实体
@Table(name="t_user") // 配置实体映射的表名
public class User implements Serializable {
   private static final long serialVersionUID = -37900582537861695L;
?
   @Id // 必须配置不配置,如果使用通过ID查询的时候会把所有字段当作条件进行查询
   private Integer userId;
   
   private String userName;
   
   private Integer userAge;
   
   private Date userBirth;
   
   private Date createTime;
   
   private Date updateTime;
?
?
   public Integer getUserId() {
       return userId;
  }
?
   public void setUserId(Integer userId) {
       this.userId = userId;
  }
?
   public String getUserName() {
       return userName;
  }
?
   public void setUserName(String userName) {
       this.userName = userName;
  }
?
   public Integer getUserAge() {
       return userAge;
  }
?
   public void setUserAge(Integer userAge) {
       this.userAge = userAge;
  }
?
   public Date getUserBirth() {
       return userBirth;
  }
?
   public void setUserBirth(Date userBirth) {
       this.userBirth = userBirth;
  }
?
   public Date getCreateTime() {
       return createTime;
  }
?
   public void setCreateTime(Date createTime) {
       this.createTime = createTime;
  }
?
   public Date getUpdateTime() {
       return updateTime;
  }
?
   public void setUpdateTime(Date updateTime) {
       this.updateTime = updateTime;
  }
?
}
2.3.3 配置Mapper
@Component
@org.apache.ibatis.annotations.Mapper
public interface UserMapper extends Mapper<User> {
?
}
2.3.4 配置数据源
spring:
datasource:
  driver-class-name: com.mysql.jdbc.Driver
  url: jdbc:mysql:///test1?useSSL=false
  username: root
  password: 123
mybatis:
configuration:
  log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2.3.5 编写测试用例
/**
* 欢迎来到牧码人教育,做Java我们是专业的
*
* @创建人: 牧码人教育-Gerry
* @创建时间: 2020-4-21
* @功能描述:
*/
public class MapperTests extends DemoApplicationTests {
   @Autowired
   private UserMapper userMapper;
?
   ///////////////////////// 查询 测试 /////////////////////
   // 测试根据ID查询,必须在注解字段标记@Id
   @Test
   public void testSelectById() throws Exception {
       User user = userMapper.selectByPrimaryKey(30001);
       convertToJson(user);
  }
?
   // 测试根据指定的列进行查询
   @Test
   public void testSelectByColumns() throws Exception {
       // 创建查询条件对象
       Example example = new Example(User.class);
       example.and().andEqualTo("userName","牧码人")
              .andEqualTo("updateTime","2020-04-21 23:42:13.0");
      /* Example.Criteria criteria = example.createCriteria();
       criteria.andEqualTo("userName","牧码人")
               .andEqualTo("updateTime","2020-04-21 23:42:13.0");*/
       User user = userMapper.selectOneByExample(example);
       convertToJson(user);
  }
?
   // 测试模糊查询后排序
   @Test
   public void testSelectByColumnsForLike() throws Exception {
       // 创建查询条件对象
       Example example = new Example(User.class);
       example.and().andLike("userName","%人%")
              .andBetween("userAge",1,100);
       example.orderBy("userId").desc();
      /* Example.Criteria criteria = example.createCriteria();
       criteria.andEqualTo("userName","牧码人")
               .andEqualTo("updateTime","2020-04-21 23:42:13.0");*/
       List<User> users = userMapper.selectByExample(example);
       convertToJson(users);
  }
?
   // 自定义SQL语句查询
   @Test
   public void testSelectByCustomSql() throws Exception {
       // 创建查询条件对象
       Example example = new Example(User.class);
       example.and().andLike("userName","%人%")
              .andBetween("userAge",1,100)
               // 自定义拼接的SQL
              .andCondition("create_time=","2020-04-22 00:13:58")
               // 自定义SQL语句
              .andCondition("update_time=‘2020-04-22 00:13:58‘");
       example.orderBy("userId").desc();
       List<User> users = userMapper.selectByExample(example);
       convertToJson(users);
  }
?
   // 分页查询(全部)
   @Test
   public void testSelectByPage() throws Exception {
       RowBounds rowBounds = new RowBounds(((1-1)*2),2);
       List<User
上一篇:mogodb and pymongo


下一篇:数据库隔离级别