Spring JDBC的原理与实现

柔情只为你懂 2024-04-18 11:28 54阅读 0赞

# 1、简介 #

### 1.1 为什么使用Spring框架中的JDBC？ ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70 1]

### 1.2 使用Spring JDBC需要使用的模板类 ###

![在这里插入图片描述][20190906083257981.png]  
![在这里插入图片描述][20190906083304264.png]

# 2、实现 #

### 2.1 准备依赖 ###

![在这里插入图片描述][20190906083443602.png]

<?xml version="1.0" encoding="UTF-8"?>
    
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
    
      <groupId>com.project</groupId>
      <artifactId>Spring04</artifactId>
      <version>1.0.0</version>
    
      <name>Spring04</name>
      
      <url>http://www.example.com</url>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
        
        <project.spring.version>5.0.0.RELEASE</project.spring.version>
      </properties>
    
      <dependencies>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.12</version>
        </dependency>
    
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-core</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-beans</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-test</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-expression</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-context</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-aop</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
    
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-tx</artifactId>
          <version>${project.spring.version}</version>
        </dependency>
    
        
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.1.10</version>
        </dependency>
        
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.38</version>
        </dependency>
    
    
        <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
          <version>1.16.20</version>
          <scope>provided</scope>
        </dependency>
    
    
    
        <dependency>
          <groupId>org.aspectj</groupId>
          <artifactId>aspectjweaver</artifactId>
          <version>1.9.2</version>
        </dependency>
    
    
    
      </dependencies>
    
    
    
      <build>
    
        
        <resources>
          <resource>
            <directory>src/main/java</directory>
            <includes>
              <include>**/*.xml</include>
              <include>**/*.properties</include>
            </includes>
          </resource>
    
          <resource>
            <directory>src/main/resources</directory>
            <includes>
              <include>**/*.xml</include>
              <include>**/*.properties</include>
            </includes>
          </resource>
        </resources>
    
    
      </build>
    </project>

### 2.2 创建数据表及实体类 ###

![在这里插入图片描述][20190906083810937.png]

@Setter@Getter@ToString
    @AllArgsConstructor@NoArgsConstructor
    public class User {
        private Integer id;
        private String name;
        private String passwd;
    
    }

### 2.3 编写Dao层接口及实现类 ###

实现类完成对数据库的增删改查方法：

public class UserDaoImpl implements IUserDao {
    
        /*通过JdbcTemplate类，获得jdbc的connection对象*/
        private JdbcTemplate jdbcTemplate;
        
        /*通过datasource的get方法，将xml里面的数据源注入*/
        public void setDataSource1(DataSource dataSource){
             this.jdbcTemplate = new JdbcTemplate(dataSource);
        }
    
        /*增删改都是使用update*/
        public void insert(User user) {
            this.jdbcTemplate.update("insert into users values (null,?,?)",
                    user.getName(),user.getPasswd());
        }
    
        @Override
        public void update(User user) {
            jdbcTemplate.update("update users set name=? where id=?",
                    user.getName(),user.getId());
        }
    
        @Override
        public void delete(Integer id) {
            jdbcTemplate.update("delete from users where id=?",
                    id);
        }
    
        @Override
        public User selectone(Integer id) {
            List<User> list = this.jdbcTemplate.query("select * from users where id=?",
                    new Object[]{id},
                    new RowMapper<User>() {
                        @Override
                        public User mapRow(ResultSet rs, int i) throws SQLException {
                            User user = new User();
                            user.setId(rs.getInt("id"));
                            user.setName(rs.getString("name"));
                            user.setPasswd(rs.getString("passwd"));
                            return user;
                        }
                        //方法的返回值,就会直接封装到集合集合中
    
                    });
            return list.size()>0? list.get(0):null;
        }
    
        @Override
        public List<User> select() {
            List<User> query = this.jdbcTemplate.query("select * from users",
                    new RowMapper<User>() {
    
                        @Override
                        public User mapRow(ResultSet rs, int i) throws SQLException {
                            User user = new User();
                            user.setId(rs.getInt("id"));
                            user.setName(rs.getString("name"));
                            user.setPasswd(rs.getString("passwd"));
                            return user;
                        }
                    });
            return query;
        }
    }

### 2.4 配置Spring配置文件 ###

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:context="http://www.springframework.org/schema/context"
           xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
        
        
        <context:property-placeholder location="classpath:db.properties" system-properties-mode="NEVER"/>
        
        <bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="driverClassName" value="${driver}"/>
            <property name="url" value="${url}"/>
            <property name="username" value="${username}"/>
            <property name="password" value="${password}"/>
        </bean>
    
        <bean id="service" class="com.project.SpringJDBC.service.impl.UserServiceImpl">
            <property name="dao" ref="dao"/>
        </bean>
    
        <bean id="dao" class="com.project.SpringJDBC.dao.impl.UserDaoImpl">
            <property name="dataSource1" ref="datasource"/>
        </bean>
    
    </beans>

在resources目录下，新建db.properties文件

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=UTF-8
    username=root
    password=admin

### 2.5 测试增删改查 ###

@RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration("classpath:applicationContext.xml")
    public class TestRun {
    
        @Autowired
        private IUserService service;
        @Test
        public void save(){
            User user = new User(null,"张三三","10086");
            service.insert(user);
        }
    
        @Test
        public void update(){
            User user = new User(15,"张123","10086");
            service.update(user);
        }
    
        @Test
        public void delete(){
    
            service.delete(16);
        }
    
        @Test
        public void selectone(){
    
            User selectone = service.selectone(190);
            System.out.println(selectone);
        }
    
        @Test
        public void select(){
            List<User> select = service.select();
            System.out.println(select);
        }
    }

# 3、小结 #

1.查询单个对象时,可以使用JDBCTemplate的queryForObject方法,方法的第一个参数为SQL语句,第二参数为SQL中需要的参数,第三个参数为实现RowMapper接口的对象,用于处理结果集,该方法要求返回单一的对象。  
![在这里插入图片描述][20190906084832252.png]  
此方法在没有查询到结果集时会抛出异常。  
2.查询集合对象,可以使用JDBCTemplate的query方法,参数与queryForObject相同,也可以部传入SQL需要的参数.  
![在这里插入图片描述][20190906084847384.png]  
3.推荐使用Mybatis！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/a1f58b22a4f644a29dd54e344c10c4bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/5a10fc6d367742058f093d4f28403b2e.png
[20190906083257981.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/03deaed711634f588a3a41d56d7932d8.png
[20190906083304264.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/b248e162d5bf4de9b70181bcf9b5f65d.png
[20190906083443602.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/2b73b8060e334f1b9260342dbe738742.png
[20190906083810937.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/520c9395f4d945bc853c73f1b6f3d1ad.png
[20190906084832252.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/42650de7864c4ed2aa8f67c1ab4a53c6.png
[20190906084847384.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/eaefc146976a43819dab05690b5c4edc.png