SpringBoot数据库访问(一)--------关系型数据库访问(RDBMS)

关系型数据库访问(RDBMS)

采用JdbcTemplate、MyBatis、JPA、Hibernate等技术。

一、JdbcTemplate工具

  • 在pom.xml添加boot-starter-jdbc定义

    <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>1.4.7.RELEASE</version>
        </parent>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency><!-- jdbc -->
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            
            <dependency>
            <groupId>com.oracle</groupId><!-- oracle的版本-->
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
            </dependency>
        </dependencies>
  • 在application.properties添加链接参数定义

    spring.datasource.username=SCOTT
    spring.datasource.password=TIGER
    spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
    spring.datasource.driverClassName=oracle.jdbc.OracleDriver
  • 根据DEPT表编写Dept实体类

    public class Dept implements Serializable{
    
        private Integer deptno;
        private String dname;
        private String loc;
    
        //set和get省略
    }
  • 定义DeptDao接口

    public interface DeptDao {
    
        public List<Dept> findAll();
    
    }
  • 定义JdbcDeptDao实现类

    @Repository("deptDao")
    public class JdbcDeptDao implements DeptDao {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        public List<Dept> findAll() {
            String sql = "select * from DEPT";
            //BeanPropertyRowMapper功能同名加载。查询结果集列名和实体类属性名一致,类似写了一个Maper
            RowMapper<Dept> rowMapper = new BeanPropertyRowMapper<Dept>(Dept.class);
            List<Dept> list = jdbcTemplate.query(sql, rowMapper);
            return list;
        }
    
    }
  • 定义主启动类,开启自动配置和扫描等功能

    @SpringBootApplication
    public class MyBootApplication {
    
    }
  • 测试程序

    public static void main(String[] args) {
        ApplicationContext ac =  SpringApplication.run(MyBootApplication.class);
        DeptDao deptDao = ac.getBean("deptDao",DeptDao.class);
        List<Dept> list = deptDao.findAll();
        for(Dept dept:list){
            System.out.println(dept.getDeptno()
                +" "+dept.getDname()+" "+dept.getLoc());
        }
    }

    二、MyBatis工具

    1. 在pom.xml添加boot-starter-jdbc、mybatis-boot定义

      <parent>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-parent</artifactId>
          <version>1.4.7.RELEASE</version>
      </parent>
      
      <properties>
          <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
          <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
          <java.version>1.8</java.version>
      </properties>
      
      <dependencies>
      
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-jdbc</artifactId>
          </dependency>
      
          <dependency>
              <groupId>com.oracle</groupId>
              <artifactId>ojdbc6</artifactId>
              <version>11.2.0.3</version>
          </dependency>
      
          <!-- mybatis、mybatis-spring、autocofigurer -->
          <dependency>
              <groupId>org.mybatis.spring.boot</groupId>
              <artifactId>mybatis-spring-boot-starter</artifactId>
              <version>1.3.0</version>
          </dependency>
      
      </dependencies>
    2. 在application.properties添加数据库连接参数定义

      spring.datasource.username=SCOTT
      spring.datasource.password=TIGER
      spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
      spring.datasource.driverClassName=oracle.jdbc.OracleDriver
    3. 根据DEPT表定义Dept实体类

      同上
      
    4. 定义Mapper映射器DeptDao接口、利用注解定义SQL

      public interface DeptDao {
      
          @Select("select * from DEPT") 
          public List<Dept> selectAll();
      
          @Select("select * form DEPT where DEPTNO=#{no}")
          public Dept selectById(int id);
      
          @Insert("insert into DEPT(DEPTNO,DNAME,LOC) values (#{deptno},#{dname},#{loc})")
          public void save(Dept dept);
      
      }
    5. 定义主启动类,添加MapperScan标记

      @SpringBootApplication
      @MapperScan(basePackages={"cn.xdl.dao"}) //扫描指定包中的dao类
      public class MyBootApplication {
      
      }
    6. 测试程序

      public static void main(String[] args) {
          ApplicationContext ac =  SpringApplication.run(MyBootApplication.class, args);
          DeptDao deptDao = ac.getBean("deptDao",DeptDao.class);
          List<Dept> list = deptDao.selectAll();
          for(Dept dept:list){
              System.out.println(dept.getDeptno()+" "+dept.getDname());
          }
      }
    7. 追加pageHelper分页处理

      <dependency>
          <groupId>com.github.pagehelper</groupId>
          <artifactId>pagehelper-spring-boot-starter</artifactId>
          <version>1.2.3</version>
      </dependency> <!-- 指定添加一个分页组件pageHelp-->

      然后在调用查询方法前,执行下PageHelper.startPage()方法即可

      PageHelper.startPage(2, 3);
      List<Dept> list = deptDao.selectAll();

    Mybatis使用注意问题

    1. SQL参数使用#{}和${}的区别

      • 采用#{}格式,预编译SQL执行机制,SQL发送时采用?
      • ${}采用Statement执行机制,参数值拼到SQL中
      • SQL字段值位置使用#{},字段名或表名位置使用${}
    2. 异常:"无效列类型:1111"

      参数#{}遇到null值情况,会提示上述异常。在可能为null参数位置,使用#{}时,需要指定jdbcType属性,例如#{xx,jdbcType=XXX}

      三、JPA工具

      1. 在pom.xml追加boot-jdbc、boot-data-jpa包定义

        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.1.RELEASE</version>
        </parent>
        
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
        </properties>
        
        <dependencies>
        
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
        
            <dependency>
                <groupId>com.oracle</groupId>
                <artifactId>ojdbc6</artifactId>
                <version>11.2.0.3</version>
            </dependency>
        
            <!-- jpa -->
            <dependency>
                <groupId>org.springframework.boot</groupId><!-- 导入jpa的jar包 -->
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
        
        </dependencies>
      2. 在application.properties文件追加数据库连接参数定义

        spring.datasource.username=SCOTT
        spring.datasource.password=TIGER
        spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
        spring.datasource.driverClassName=oracle.jdbc.OracleDriver
      3. 根据DEPT表编写Dept实体类,并追加映射注解标记

        @Entity  //标识为实体类
        @Table(name="DEPT")  //标识为数据库中的表名
        public class Dept implements Serializable{
        
            @Id  //指定前的字段为主键
            @Column(name="DEPTNO") //指定目标字段与当前字段相同
            private Integer deptno;
        
            @Column(name="DNAME")
            private String dname;
        
            @Column(name="LOC")
            private String loc;
        
            //省略set和get方法       
        
        }
      4. 定义DeptDao接口,可以继承JpaRepository

        public interface DeptDao extends JpaRepository<Dept, Integer>{
        
        }

        Repository:顶级接口,无任何操作

        CrudRepository:继承了Repository,增加了增删改查操作方法

        PagingAndSortingRepository:继承CrudRepository,增加了分页和排序操作

        JpaRepository:继承了PagingAndSortingRepository,增加批处理操作

      5. 测试程序

        public static void main(String[] args) {
            ApplicationContext ac = 
                SpringApplication.run(MyBootApplication.class, args);
            DeptDao deptDao = ac.getBean("deptDao",DeptDao.class);
            List<Dept> list = deptDao.findAll();
            for(Dept dept:list){
                System.out.println(dept.getDeptno()+" "+dept.getDname());
            }
        }
        Sql语句中常用连接词




        四、Hibernate工具(后续讲解)

      6. 未完待续.........
原文地址:https://www.cnblogs.com/hx1098/p/9398630.html