Spring Boot(三、数据访问)

springboot整合使用jdbcTemplate

1、pom文件引入

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
		</dependency>

2、application.properties新增配置

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Controller类:

@RestController
@RequestMapping("user")
public class UserController {
    @Autowired
    private UserService userService;

    @RequestMapping("createUser")
    public void createUser() {
        System.out.println("user测试");
        userService.createUser("张三",14);
    }
}

Service实现类:

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void createUser(String name,Integer age) {
        System.out.println(name+"-----"+age+"岁");
        jdbcTemplate.update("insert into user values(1,?,?)",name,age);
    }
}

访问http://localhost:8080/user/createUser 即可在数据库中插入一条user记录。

springboot整合使用mybatis

1、pom文件引入:

<!--整合mabits-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

2、配置文件引入

#数据库连接相关信息
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=x5
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

3、Mapper代码

public interface UserMapper {
    @Select("select * from user")
    List<User> getUser();

    @Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);
}

4、service实现类调用

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private UserMapper userMapper;//idea报错是正常的，因为没声明这个bean,使用mabits的映射器时，这种情况是正常的

    @Override
    public void createUser(String name, Integer age) {
        List<User> userList = userMapper.getUser();
        for (User user :
                userList) {
            System.out.println(user);
        }
//        userMapper.insert(name,age);
//        System.out.println("插入成功");
//        jdbcTemplate.update("insert into user values(1,?,?)",name,age);
    }
}

springboot整合使用springjpa

pom文件引入

        <!-- springdata jpa依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

创建Entity实体类

/**
 * Entity类
 */
@Entity(name="userEntity")
public class UserEntity {
    @Id
    @GeneratedValue
    private int id;
    @Column(length=20)
    private String name;
    @Column
    private int age;

    //get set 方法

创建dao层继承Jpa

public interface UserDao extends JpaRepository<UserEntity,Integer> {
}

我们可以看看JpaRepository中的一些方法，如图，我们可以直接使用这些方法，包括其父类的好多方法

application.properties配置文件更新

#建表策略，这里用update，即根据实体更新表结构，如果没有该表，jpa可以直接根据entity创建表
spring.jpa.hibernate.ddl-auto = update

进行单元测试:

    @Autowired
    private UserDao userDao;

        @Test
    public void testUserDao() {
        UserEntity userEntity=userDao.findOne(1);
        System.out.println(userEntity);
    }

效果:

mysql数据库中原本没有user_entity表，当我们启动项目，jpa会直接根据entity实体类创建表，如图:

springboot整合多数据源

配置文件中新增两个数据源:

#数据库连接相关信息 数据源test1
spring.datasource.test1.url=jdbc:mysql://localhost:3306/test
spring.datasource.test1.username=root
spring.datasource.test1.password=x5
spring.datasource.test1.driver-class-name=com.mysql.jdbc.Driver
#数据库连接相关信息 数据源test2
spring.datasource.test2.url=jdbc:mysql://localhost:3306/test2
spring.datasource.test2.username=root
spring.datasource.test2.password=x5
spring.datasource.test2.driver-class-name=com.mysql.jdbc.Driver

数据源1配置类

/**
 * 配置数据源1
 */
@Configuration
@MapperScan(basePackages = "com.mytest.springbootDemo.mapper", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class MyBatisConfig1 {
    @Bean(name = "test1DataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "test1SqlSessionFactory")
    @Primary//@Primary注解标识默认使用的数据源,如果不加启动会报如下错误：意思是有两个数据源  不知需要使用哪个数据源
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //读取mybatis小配置文件
        // bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "test1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

@Primary注解标识默认使用的数据源,如果不加启动会报如下错误：意思是有两个数据源  不知需要使用哪个数据源

数据源2配置类

/**
 * 配置数据源2
 */
@Configuration
@MapperScan(basePackages = "com.mytest.springbootDemo.mapper2", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class MyBatisConfig2 {
    @Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //读取mybatis小配置文件
        // bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "test2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "test2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

分别创建针对数据源对应的mapper包，如图:

创建对应的mapper类:

usermapper:

public interface UserMapper {
    @Select("select * from user")
    List<User> getUser();

    @Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);
}

usermapper2:

public interface UserMapper2 {
    @Select("select * from user")
    List<User> getUser();

    @Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);
}

在测试类进行单元测试:

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootDemoApplicationTests {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private UserMapper2 userMapper2;

    @Test
    public void testUserMapper() {
        List<User> userList = userMapper.getUser();
        List<User> userList2 = userMapper2.getUser();
        for (User user :
                userList) {
            System.out.println(user);
        }
        for (User user :
                userList2) {
            System.out.println(user);
        }
    }
    
}

效果如图，将两个数据源的数据都读了出来。