springboot整合持久层(springboot+vue开发实践笔记)

mybatis

1、添加mybatis依赖,数据库连接池,数据驱动依赖

<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.9</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

2、创建类、数据库、配置application.properties的连接。
3、创建数据库访问层

@Mapper
public interface BookMapper {
    int addBook(Book1 book);
    int deleteBookById(Integer id);
    int updateBookById(Book1 book);
    Book1 getBookById(Integer id);
    List<Book1> getAllBooks();
}

4、创建映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.sang.mapper.BookMapper">
    <insert id="addBook" parameterType="org.sang.model.Book">
    INSERT INTO book(name,author) VALUES (#{name},#{author})
    </insert>
    <delete id="deleteBookById" parameterType="int">
        DELETE FROM book WHERE id=#{id}
    </delete>
    <update id="updateBookById" parameterType="org.sang.model.Book">
        UPDATE book set name=#{name},author=#{author} WHERE id=#{id}
    </update>
    <select id="getBookById" parameterType="int" resultType="org.sang.model.Book">
        SELECT * FROM book WHERE id=#{id}
    </select>
    <select id="getAllBooks" resultType="org.sang.model.Book">
        SELECT * FROM book
    </select>
</mapper>

5、创建service和controller文件

@Service
public class BookService {
    @Autowired
    BookMapper bookMapper;
    public int addBook(Book1 book) {
        return bookMapper.addBook(book);
    }
    public int updateBook(Book1 book) {
        return bookMapper.updateBookById(book);
    }
    public int deleteBookById(Integer id) {
        return bookMapper.deleteBookById(id);
    }
    public Book1 getBookById(Integer id) {
        return bookMapper.getBookById(id);
    }
    public List<Book1> getAllBooks() {
        return bookMapper.getAllBooks();
    }
}

controller.java

@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/bookOps")
    public void bookOps() {
        Book1 b1 = new Book1();
        b1.setName("西厢记");
        b1.setAuthor("王实甫");
        int i = bookService.addBook(b1);
        System.out.println("addBook>>>" + i);
        Book1 b2 = new Book1();
        b2.setId(1);
        b2.setName("朝花夕拾");
        b2.setAuthor("鲁迅");
        int updateBook = bookService.updateBook(b2);
        System.out.println("updateBook>>>"+updateBook);
        Book1 b3 = bookService.getBookById(1);
        System.out.println("getBookById>>>"+b3);
        int delete = bookService.deleteBookById(2);
        System.out.println("deleteBookById>>>"+delete);
        List<Book1> allBooks = bookService.getAllBooks();
        System.out.println("getAllBooks>>>"+allBooks);
    }
}

6、配置pom.xml(扫描Java当中的mapper.xml文件,并将文件生成到resources当中)

<build>
		<resources>
			<resource>
				<directory>src/main/java</directory>
				<includes>
					<include>**/*.xml</include>
				</includes>
			</resource>
			<resource>
				<directory>src/main/resources</directory>
			</resource>
		</resources>
	</build>

整合jpa

1、创建数据库
2、创建项目,添加mysql依赖和jpa依赖

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.9</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

3、数据库配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql:///jpa
spring.datasource.username=root
spring.datasource.password=123
spring.jpa.show-sql=true
spring.jpa.database=mysql
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect
#spring.jpa.properties.database=mysql
#spring.jpa.properties.hibernate.hbm2ddl.auto=update
#spring.jpa.properties.show-sql= true

在这里插入图片描述

4、创建实体类

@Entity(name = "t_book")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "book_name",nullable = false)
    private String name;
    private String author;
    private Float price;
    @Transient
    private String description;
   //省略getter和setter方法 
  }

5、创建bookdao

public interface BookDao extends JpaRepository<Book,Integer>{
    List<Book> getBooksByAuthorStartingWith(String author);
    List<Book> getBooksByPriceGreaterThan(Float price);
    @Query(value = "select * from t_book where id=(select max(id) from t_book)",nativeQuery = true)
    Book getMaxIdBook();
    @Query("select b from t_book b where b.id>:id and b.author=:author")
    List<Book> getBookByIdAndAuthor(@Param("author") String author, @Param("id") Integer id);
    @Query("select b from t_book b where b.id<?2 and b.name like %?1%")
    List<Book> getBooksByIdAndName(String name, Integer id);
}

jpa默认方法使用
https://www.jianshu.com/p/6476cf4ada86

jpa自定义方法命名规则
https://www.cnblogs.com/oxygenG/p/10057525.html

在这里插入图片描述
6、bookService方法

@Service
public class BookService {
    @Autowired
    BookDao bookDao;
    public void addBook(Book book) {
        bookDao.save(book);
    }
    public Page<Book> getBookByPage(Pageable pageable) {
        return bookDao.findAll(pageable);
    }
    public List<Book> getBooksByAuthorStartingWith(String author){
        return bookDao.getBooksByAuthorStartingWith(author);
    }
    public List<Book> getBooksByPriceGreaterThan(Float price){
        return bookDao.getBooksByPriceGreaterThan(price);
    }
    public Book getMaxIdBook(){
        return bookDao.getMaxIdBook();
    }
    public List<Book> getBookByIdAndAuthor(String author, Integer id){
        return bookDao.getBookByIdAndAuthor(author, id);
    }
    public List<Book> getBooksByIdAndName(String name, Integer id){
        return bookDao.getBooksByIdAndName(name, id);
    }
}

7、创建bookcontroller

@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/findAll")
    public void findAll() {
        PageRequest pageable = PageRequest.of(2, 3);
        Page<Book> page = bookService.getBookByPage(pageable);
        System.out.println("总页数:"+page.getTotalPages());
        System.out.println("总记录数:"+page.getTotalElements());
        System.out.println("查询结果:"+page.getContent());
        System.out.println("当前页数:"+(page.getNumber()+1));
        System.out.println("当前页记录数:"+page.getNumberOfElements());
        System.out.println("每页记录数:"+page.getSize());
    }
    @GetMapping("/search")
    public void search() {
        List<Book> bs1 = bookService.getBookByIdAndAuthor("鲁迅", 7);
        List<Book> bs2 = bookService.getBooksByAuthorStartingWith("吴");
        List<Book> bs3 = bookService.getBooksByIdAndName("西", 8);
        List<Book> bs4 = bookService.getBooksByPriceGreaterThan(30F);
        Book b = bookService.getMaxIdBook();
        System.out.println("bs1:"+bs1);
        System.out.println("bs2:"+bs2);
        System.out.println("bs3:"+bs3);
        System.out.println("bs4:"+bs4);
        System.out.println("b:"+b);
    }
    @GetMapping("/save")
    public void save() {
        Book book = new Book();
        book.setAuthor("鲁迅");
        book.setName("呐喊");
        book.setPrice(23F);
        bookService.addBook(book);
    }
}

多数据源

一个项目采用不同数据库中多个库,或同一个数据库实例中多个库进行数据读取。

mybatis

1、添加mybatis依赖,mysql驱动和数据库连接池

<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.10</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

2、创建mybatis配置
配置bean,主要体统sqlsessionfactory和sqlsessionTemplate

1、在@MapperScan注解中指定Mapper接口所在位置,同时制定SqlSessionFactory的实例名,该位置下的Mapper将使用SqlSessionFactory。
2、提供一个sqlsessionFactory实例,同时将DataSource的实例创建给SqlSessionFactory,这里的SqlSessionFactoryBean1实例就是sqlSessionFactoryRef指定的实例。

MyBatisConfigOne.java

@Configuration
@MapperScan(value = "org.sang.mapper1", sqlSessionFactoryRef = "sqlSessionFactoryBean1")
public class MyBatisConfigOne {
    @Autowired
    @Qualifier("dsOne")
    DataSource dsOne;

    @Bean
    SqlSessionFactory sqlSessionFactoryBean1() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsOne);
        return factoryBean.getObject();
    }
    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBean1());
    }
}

MyBatisConfigTwo.java

@Configuration
@MapperScan(value = "org.sang.mapper2", sqlSessionFactoryRef = "sqlSessionFactoryBean2")
public class MyBatisConfigTwo {
    @Autowired
    @Qualifier("dsTwo")
    DataSource dsTwo;
    @Bean
    SqlSessionFactory sqlSessionFactoryBean2() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsTwo);
        return factoryBean.getObject();
    }
    @Bean
    SqlSessionTemplate sqlSessionTemplate2() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBean2());
    }
}

创建配置文件DataSourceConfig,访问application.properties中相应的sql配置

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.one")
    DataSource dsOne() {
        return DruidDataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties("spring.datasource.two")
    DataSource dsTwo() {
        return DruidDataSourceBuilder.create().build();
    }
}

创建application.properties配置

# 数据源1
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.username=root
spring.datasource.one.password=123
spring.datasource.one.url=jdbc:mysql:///chapter05-1
# 数据源2
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.username=root
spring.datasource.two.password=123
spring.datasource.two.url=jdbc:mysql:///chapter05-2

以上所有配置已经进行完毕。

接下来进行多数据源实现测试
创建Controller和mapper文件进行测试。
bookMapper1.java

public interface BookMapper {
    List<Book> getAllBooks();
}

bookMapper1.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.sang.mapper1.BookMapper">
<select id="getAllBooks" resultType="org.sang.model.Book">
    select * from book;
</select>
</mapper>

bookMapper2.java

public interface BookMapper2 {
    List<Book> getAllBooks();
}

bookMapper2.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.sang.mapper2.BookMapper2">
<select id="getAllBooks" resultType="org.sang.model.Book">
    select * from book;
</select>
</mapper>

Controller.java

@RestController
public class BookController {
    @Autowired
    BookMapper bookMapper;
    @Autowired
    BookMapper2 bookMapper2;
    @GetMapping("/test1")
    public void test1() {
        List<Book> books1 = bookMapper.getAllBooks();
        List<Book> books2 = bookMapper2.getAllBooks();
        System.out.println("books1:"+books1);
        System.out.println("books2:"+books2);
    }
}
JPA多数据源``

1、引入jpa,mysql-connection,druid数据库连接池依赖

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.10</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

2、在数据源基础上添加jpa配置

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect
spring.jpa.properties.database=mysql
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.show-sql= true

完整的application.properties配置是

spring.datasource.one.password=123
spring.datasource.one.username=root
spring.datasource.one.url=jdbc:mysql:///chapter05-1
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.two.password=123
spring.datasource.two.username=root
spring.datasource.two.url=jdbc:mysql:///chapter05-2
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect
spring.jpa.properties.database=mysql
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.show-sql= true

3、创建Jpa配置
使用@EnableJpaRepositories注解进行配置
该注解主要包含三个属性,分别是
basePackages用来指定repository所在位置
entityManagerFactoryRef指定实体类管理工厂名称
transactionManagerRef指定事务管理器引用名称
JpaConfigOne.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "org.sang.dao1",
        entityManagerFactoryRef = "entityManagerFactoryBeanOne",
        transactionManagerRef = "platformTransactionManagerOne")
public class JpaConfigOne {
    @Resource(name = "dsOne")
    DataSource dsOne;
    @Autowired
    JpaProperties jpaProperties;
    @Bean
    @Primary
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanOne(
            EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dsOne)
                .properties(jpaProperties.getProperties())
                .packages("org.sang.model")
                .persistenceUnit("pu1")
                .build();
    }
    @Bean
    PlatformTransactionManager platformTransactionManagerOne(
            EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean factoryOne = entityManagerFactoryBeanOne(builder);
        return new JpaTransactionManager(factoryOne.getObject());
    }
}

JpaConfigTwo.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "org.sang.dao2",
entityManagerFactoryRef = "entityManagerFactoryBeanTwo",
transactionManagerRef = "platformTransactionManagerTwo")
public class JpaConfigTwo {
    @Resource(name = "dsTwo")
    DataSource dsTwo;
    @Autowired
    JpaProperties jpaProperties;
    @Bean
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanTwo(
            EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dsTwo)
                .properties(jpaProperties.getProperties())
                .packages("org.sang.model")
                .persistenceUnit("pu2")
                .build();
    }
    @Bean
    PlatformTransactionManager platformTransactionManagerTwo(
            EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean factoryTwo = entityManagerFactoryBeanTwo(builder);
        return new JpaTransactionManager(factoryTwo.getObject());
    }
}

DataSourceConfig.java
配置dsOne和dsTwo在application.properties中配置的属性。

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.one")
    @Primary
    DataSource dsOne() {
        return DruidDataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties("spring.datasource.two")
    DataSource dsTwo() {
        return DruidDataSourceBuilder.create().build();
    }
}

多数据源Jpa配置完毕

下面进行测试
1、创建实体类

@Entity(name = "t_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    private String gender;
    private Integer age;
    //省略getter/setter
    }

2、创建repository
userDao1.java

package org.sang.dao1;

import org.sang.model.User;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * Created by sang on 2018/7/16.
 */
public interface UserDao extends JpaRepository<User,Integer>{
        }

userDao2.java

package org.sang.dao2;

import org.sang.model.User;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * Created by sang on 2018/7/16.
 */
public interface UserDao2 extends JpaRepository<User,Integer>{
}

创建userController.java

@RestController
public class UserController {
    @Autowired
    UserDao userDao;
    @Autowired
    UserDao2 userDao2;
    @GetMapping("/test1")
    public void test1() {
        User u1 = new User();
        u1.setAge(55);
        u1.setName("鲁迅");
        u1.setGender("男");
        userDao.save(u1);
        User u2 = new User();
        u2.setAge(80);
        u2.setName("泰戈尔");
        u2.setGender("男");
        userDao2.save(u2);
    }
}

mybatis灵活性好用户进行sql优化,jpa方便用户,快速实现一个RestSet风格应用。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值