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风格应用。