【spring学习】spring访问数据库

一、xml方式访问数据库

---

1.1 Spring的JdbcTemplate

Spring的JdbcTemplate（了解会用）
​ 在Spring中提供了一个可以操作数据库的对象org.springframework.jdbc.core.JdbcTemplate，对象封装了jdbc技术，JDBC的模板对象与DBUtils中的QueryRunner非常相似.

测试：
在pom.xml中导入依赖

   <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>

编写测试类：

JdbcTemplate jt = new JdbcTemplate();
jt.setDataSource(dataSource);

List<User> list = jt.query("select * from user where id =?",new BeanPropertyRowMapper<User>(User.class),1);

System.out.println(list.get(0));

//jt.update("delete from  user where id =?",4);

1.2 创建user表并添加数据

CREATE TABLE USER(

id INT,
username VARCHAR(20),
PASSWORD VARCHAR(20)

)

1.3 pom.xml导入相关依赖

<dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

1.3 编写pojo

package com.qf.pojo;

import lombok.Data;

@Data
public class User {

    private Integer id;
    private String name;
    private String password;

}

1.4 编写dao

package com.qf.dao;

import com.qf.pojo.User;

import java.util.List;

public interface UserDao {

    List<User> findAll();
}

---

package com.qf.dao.impl;

import com.qf.dao.UserDao;
import com.qf.pojo.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    private JdbcTemplate jdbcTemplate;

    public List<User> findAll() {

        return  jdbcTemplate.query("select * from user",new BeanPropertyRowMapper<User>(User.class));

    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}

1.5 编写service

package com.qf.service;

import com.qf.pojo.User;

import java.util.List;

public interface UserService {

    List<User> findAll();
}

---

package com.qf.service.impl;

import com.qf.dao.UserDao;
import com.qf.pojo.User;
import com.qf.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

public class UserServiceImpl implements UserService {

    private UserDao userDao;

    public List<User> findAll() {
        return userDao.findAll();
    }

    public void setUserDao(UserDao userDao) {
        this.userDao = userDao;
    }
}

1.6 编写controller

package com.qf.cotroller;

import com.qf.pojo.User;
import com.qf.service.UserService;

import java.util.List;

public class UserController {

    private UserService userService;

    public List<User> findAll(){

        return userService.findAll();
    }

    public void setUserService(UserService userService) {
        this.userService = userService;
    }
}

1.7 jdbc.properties配置文件

jdbc.username = root
jdbc.password= root
jdbc.url = jdbc:mysql:///java2001?serverTimezone=Asia/Shanghai
jdbc.driverClassName = com.mysql.cj.jdbc.Driver

1.8 applicationContext.xml配置文件

<?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 https://www.springframework.org/schema/context/spring-context.xsd">

        <!-- 引入jdbc.properties配置文件 -->
        <context:property-placeholder location="classpath:jdbc.properties"/>

        <!-- 配置druid数据库连接池 -->
        <bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="driverClassName" value="${jdbc.driverClassName}"></property>

        <!-- 配置spring的jdbcTemplate-->
        <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
                <property name="dataSource" ref="dataSource"></property>
        </bean>

        <!-- dao -->
        <bean name="userDao" class="com.qf.dao.impl.UserDaoImpl">
                <property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>

        <!-- service -->
        <bean name="userService" class="com.qf.service.impl.UserServiceImpl">
                <property name="userDao" ref="userDao"></property>
        </bean>

        <!-- controller -->
        <bean name="userController" class="com.qf.cotroller.UserController">
                <property name="userService" ref="userService"></property>
        </bean>

</beans>

1.9 测试

package com.qf.test;

import com.alibaba.druid.pool.vendor.SybaseExceptionSorter;
import com.qf.cotroller.UserController;
import com.qf.pojo.User;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class TestUserController {

    @Test
    public void test_findAll(){

        ClassPathXmlApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationContext.xml");

        UserController userController =
                (UserController)applicationContext.getBean("userController");

        List<User> list = userController.findAll();

        System.out.println(list);
    }
}

二、注解方式访问数据库

---

2.1 Spring中的注解

@Configuration
​ 作用：指定当前类是一个配置类
​ 细节：当配置类作为AnnotationConfigApplicationContext对象创建的参数时，该注解可以不写。

@ComponentScan
作用：用于通过注解指定spring在创建容器时要扫描的包
属性：value：它和basePackages的作用是一样的，都是用于指定创建容器时要扫描的包。
等同于xml中: <context:component-scan base-package=“com.qf”/>

@PropertySource
作用：用于指定properties文件的位置
属性：value：指定文件的名称和路径。
关键字：classpath，表示类路径下

等同于xml中: <context:property-placeholder location=“classpath:jdbc.properties”/>

@Bean
​ 作用：用于把当前方法的返回值作为bean对象存入spring的ioc容器中
​ 属性:name:用于指定bean的id。当不写时，默认值是当前方法的名称
​ 细节：当我们使用注解配置方法时，如果方法有参数，在参数前加：@Qualifier("@Bean注解中name的值")，spring框架会去容器中查找有没有可用的bean对象查找的方式和Autowired注解的作用是一样的。

    @Bean("dataSource1")
    public DataSource getDataSource1(){
     try{
         Properties pro = new Properties();
         pro.setProperty("url",url);
         pro.setProperty("username",username);
         pro.setProperty("password",password);
         pro.setProperty("driverClassName",driver);

         DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
         return dataSource;
         
     } catch (Exception e){
     }
        return null;
    }

    @Bean("dataSource2")
    public DataSource getDataSource2(){
        try{
            Properties pro = new Properties();
            pro.setProperty("url",url);
            pro.setProperty("username",username);
            pro.setProperty("password",password);
            pro.setProperty("driverClassName",driver);

            DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
            return dataSource;

        } catch (Exception e){
        }
        return null;
    }

    @Bean("dataSource")
    public DataSource getDataSource(@Qualifier("dataSource1") DataSource ds){
        return ds;
    }

@Import
​ 作用：用于导入其他的配置类
​ 属性：value：用于指定其他配置类的字节码。
​ 当我们使用Import的注解之后，有Import注解的类就父配置类，而导入的都是子配置类

​ 等同于xml中:

2.2 创建pojo，dao，service，controller

使用@Repository，@Service，@Controller以及@Autowired 配置所需代码

2.3 创建jdbc.properties配置文件

jdbc.username = root
jdbc.password= root
jdbc.url = jdbc:mysql:///java2001?serverTimezone=Asia/Shanghai
jdbc.driverClassName = com.mysql.cj.jdbc.Driver

2.5 创建SpringConfiguration.java作为注解配置类，它的作用和bean.xml是一样的

package com.qf.config;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.Properties;


@PropertySource(value = "classpath:jdbc.properties")
@ComponentScan("com.qf")
@Configuration
public class SpringConfiguration {

    @Value("${jdbc.driverClassName}")
    private String driverClassName;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;

    @Bean("jdbcTemplate")
    public JdbcTemplate getJdbcTemplate(){

        try{
            Properties pro = new Properties();
            pro.setProperty("url",url);
            pro.setProperty("username",username);
            pro.setProperty("password",password);
            pro.setProperty("driverClassName",driverClassName);

            DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            return jdbcTemplate;

        } catch (Exception e){

        }
        return null;
    }
}

2.6 使用Spring整合junit测试

Spring整合junit的配置
​ 1. 导入spring整合junit坐标
​
​ ​ org.springframework
​ ​ spring-test
​ ​ 5.0.2.RELEASE
​
​ 2. 使用Junit提供的一个注解把原有的main方法替换了，替换成spring提供的
​ @RunWith(SpringJUnit4ClassRunner.class)

3. 告知spring的运行器，spring和ioc创建是基于xml还是注解的，并且说明位置
   @ContextConfiguration(locations = “classpath:applicationContext.xml”)
   locations：指定xml文件的位置，classpath关键字表示在类路径下

   @ContextConfiguration(classes = SpringConfiguration.class)
   classes：指定注解配置类（需要手动编写配置类）

注意：当我们使用spring 5.x版本的时候，要求junit的jar必须是4.12及以上，spring版本必须保持一致

2.7 编写测试类进行测试

package com.qf.test;

import com.qf.config.SpringConfiguration;
import com.qf.cotroller.UserController;
import com.qf.pojo.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = SpringConfiguration.class)
public class TestUserController {

    @Autowired
    private UserController userController;

    @Test
    public void test_findAll() throws Exception{

        List<User> list = userController.findAll();

        System.out.println(list);

    }

}