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

你的名字 2023-07-06 11:33 135阅读 0赞

### 关系型数据库访问（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>
                    <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>
            </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>
            
                
                <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> 
        
         
        
        然后在调用查询方法前，执行下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\}![1056093-20180731214029539-1858545122.png][]
        
        #### 三、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>
                
                    
                    <dependency>
                        <groupId>org.springframework.boot</groupId>
                        <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.      未完待续.........

[1056093-20180731214029539-1858545122.png]: https://images2018.cnblogs.com/blog/1056093/201807/1056093-20180731214029539-1858545122.png