SpringBoot 整合 Jpa
项目目录结构
pom文件
<?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.bodi</groupId> <artifactId>onlineretailers</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <name>onlineretailers</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.3.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> </dependency> <dependency> <groupId>org.hibernate.javax.persistence</groupId> <artifactId>hibernate-jpa-2.1-api</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>commons-net</groupId> <artifactId>commons-net</artifactId> <version>3.6</version> </dependency> <dependency> <groupId>jdom</groupId> <artifactId>jdom</artifactId> <version>1.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.46</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-jpa</artifactId> </dependency> <dependency> <groupId>org.json</groupId> <artifactId>json</artifactId> <version>20180130</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> </dependency> <dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <classifier>jdk15</classifier> </dependency> <dependency> <groupId>org.apache.httpcomponents</groupId> <artifactId>httpclient</artifactId> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> </dependency> <!--data source spring data jpa 需要用c3p0 连接池--> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> <exclusions> <exclusion> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-tomcat</artifactId> <scope>provided</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
application.properties文件配置
ms.db.driverClassName=com.mysql.jdbc.Driver ms.db.url=jdbc:mysql://47.100.96.85:3306/vending_test?prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&characterEncoding=utf-8&allowMultiQueries=true ms.db.username=vending_test ms.db.password=PedC6TfiDZ ms.db.maxActive=500 #修改监听端口号 server.port = 8081 #输出sql log logging.level.org.hibernate.SQL = DEBUG
配置类DBConfig
@Configuration public class DBConfig { @Autowired private Environment env; @Bean(name="dataSource") public ComboPooledDataSource dataSource() throws PropertyVetoException { ComboPooledDataSource dataSource = new ComboPooledDataSource(); dataSource.setDriverClass(env.getProperty("ms.db.driverClassName")); dataSource.setJdbcUrl(env.getProperty("ms.db.url")); dataSource.setUser(env.getProperty("ms.db.username")); dataSource.setPassword(env.getProperty("ms.db.password")); dataSource.setMaxPoolSize(20); dataSource.setMinPoolSize(5); dataSource.setInitialPoolSize(10); dataSource.setMaxIdleTime(300); dataSource.setAcquireIncrement(5); dataSource.setIdleConnectionTestPeriod(60); return dataSource; } }
配置类JpaConfig
@Configuration //此处是你dao文件所在的包名 @EnableJpaRepositories("com.bodi.domain") @EnableTransactionManagement public class JpaConfig { @Autowired private DataSource dataSource; @Bean public EntityManagerFactory entityManagerFactory() { HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter(); LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean(); factory.setJpaVendorAdapter(vendorAdapter); //此处com.example.*.model是你的java bean所在的包名 factory.setPackagesToScan("com.bodi.entity"); factory.setDataSource(dataSource); Map<String, Object> jpaProperties = new HashMap<String, Object>(); jpaProperties.put("hibernate.ejb.naming_strategy","org.hibernate.cfg.ImprovedNamingStrategy"); jpaProperties.put("hibernate.jdbc.batch_size",50); factory.setJpaPropertyMap(jpaProperties); factory.afterPropertiesSet(); return factory.getObject(); } @Bean public PlatformTransactionManager transactionManager() { JpaTransactionManager txManager = new JpaTransactionManager(); txManager.setEntityManagerFactory(entityManagerFactory()); return txManager; } }
主要部分 接口JpaDao extends JpaRepository<AdminUser, Long> (AdminUser是Entity类型，Long是主键类型(这个具体是什么类型我也没搞清楚 目前没因为这个报错过))
nativeQuery = true(是否使用原生sql)
public interface AdminUserJpaDao extends JpaRepository<AdminUser, Long> { /** * Find by name. * * @param name the name * @return the user */ AdminUser findByStatus(Integer status); /** * Find by name and user name. * 如果参数名为多个字母组成，请首字母大写。勿使用驼峰命名，jpa不识别驼峰 * @param name the name * @param age the age * @return the user */ AdminUser findByStatusAndMobile(String status, Integer mobile); /** * Find user. * User为@Entity 的名字 * @param name the name * @return the user */ @Query(value = "SELECT user.* FROM admin_user user WHERE user.status = ?1",nativeQuery = true) Page<AdminUser> findUser(@Param("status") Integer status,Pageable pageable); /** * @Title: findByName * @Description: 测试一下分页 * @param @param name * @param @param pageable * @param @return 参数 * @return Page<Girl> 返回类型 * @throws */ Page<AdminUser> findByStatus(Integer status,Pageable pageable); }
Entity (实体类)
@GeneratedValue注解的strategy属性提供四种值:
-AUTO主键由程序控制, 是默认选项 ,不设置就是这个
-IDENTITY 主键由数据库生成, 采用数据库自增长, Oracle不支持这种方式
-SEQUENCE 通过数据库的序列产生主键, MYSQL 不支持
-Table 提供特定的数据库产生主键, 该方式更有利于数据库的移植
如果 Save Entity 不设置strategy属性 会报错
@Entity @Table(name = "admin_user") public class AdminUser { @Id @GeneratedValue @Column(name = "user_id") private Integer userId; @Column(name = "status") private Integer status; @Column(name = "owner_id") private Integer ownerId; @Column(name = "mobile") private String mobile; @Column(name = "password_hash") private String passwordHash; @Column(name = "created_at") private Timestamp createdAt; @Column(name = "updated_at") private Timestamp updatedAt; public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public Integer getStatus() { return status; } public void setStatus(Integer status) { this.status = status; } public Integer getOwnerId() { return ownerId; } public void setOwnerId(Integer ownerId) { this.ownerId = ownerId; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getPasswordHash() { return passwordHash; } public void setPasswordHash(String passwordHash) { this.passwordHash = passwordHash; } public Timestamp getCreatedAt() { return createdAt; } public void setCreatedAt(Timestamp createdAt) { this.createdAt = createdAt; } public Timestamp getUpdatedAt() { return updatedAt; } public void setUpdatedAt(Timestamp updatedAt) { this.updatedAt = updatedAt; } }
service 和 controller 这里我不贴代码了 和正常的是一样的
启动类
@SpringBootApplication @EnableTransactionManagement @ServletComponentScan public class OnlineretailersApplication { public static void main(String[] args) { SpringApplication.run(OnlineretailersApplication.class, args); } }
创建项目 启动类旁边 多了个类 我也不知道干嘛的 我也贴出来把
public class ServletInitializer extends SpringBootServletInitializer { @Override protected SpringApplicationBuilder configure(SpringApplicationBuilder application) { return application.sources(OnlineretailersApplication.class); } }
Jpa分页
分页的代码逻辑很简单 简单看一下就明白了 有一点需要注意的
1、sorts 这个排序字段 是根据你要分页的sql定的 如果说是原生的就写表列名字 如果不是原生的 就写Entity 属性名
/** * @Title: bynamePaging * @Description: 测试一下分页 * @author yihj * @param @param status * @param @param page * @param @param size * @param @param sorts * @param @param request 参数 * @return void 返回类型 * @throws */ @SuppressWarnings("deprecation") @RequestMapping(value = "/bynamePaging", method = RequestMethod.GET) public void bynamePaging(@RequestParam(value = "status", defaultValue = "1") Integer status, @RequestParam(value = "page", defaultValue = "0") Integer page, //从第0个开始查 @RequestParam(value = "size", defaultValue = "15") Integer size, //每页展示几个 @RequestParam(value = "sorts", defaultValue = "userId") String sorts, HttpServletRequest request) { Sort sort = new Sort(Sort.Direction.DESC, sorts); //排序 Pageable pageable =new PageRequest(page,size,sort); Page<AdminUser> pages =userService.findByStatus(status, pageable); Iterator<AdminUser> it=pages.iterator(); while (it.hasNext()){ log.info("---------------------->id:" + ((AdminUser) it.next()).getUserId()); } }
重点记录一下
Jpa 自定义实体 自定义查询 这块我几乎写了5个小时(记录的可能是一个笨方法 别嫌弃。。。 有更好的方式可以互相交流学习)
Controller
@SuppressWarnings({ "deprecation", "unchecked" })
@RequestMapping(value = "/findMyOrders", method = RequestMethod.GET)
public void findMyOrders(@RequestParam(value = "openId", defaultValue = "",required=false) String openId,
@RequestParam(value = "page", defaultValue = "0",required=false) Integer page, //从第0个开始查
@RequestParam(value = "size", defaultValue = "4",required=false) Integer size, //每页展示几个
@RequestParam(value = "sorts", defaultValue = "createdAt") String sorts,
HttpServletRequest request,HttpServletResponse response) {
log.info("查询我的订单接口start");
//创建返回对象
JSONObject result = new JSONObject();
//排序
Sort sort = new Sort(Sort.Direction.DESC, sorts);
//分页
Pageable pageable =new PageRequest(page,size,sort);
log.info("参数openId："+openId);
log.info("参数page："+page);
log.info("参数size："+size);
//自定义查询
Page<List<Map<String, Object>>> pages =salesOrderService.selectMyOrders(openId, pageable);
//迭代
Iterator<List<Map<String, Object>>> it= pages.iterator();
//最终返回list
List<SalesOrderDto> dtos = new ArrayList<SalesOrderDto>();
//循环迭代
while(it.hasNext()) {
//拿到hash值
HashMap<String, Object> mapNext = (HashMap<String, Object>) it.next();
//创建临时对象
SalesOrderDto dto = new SalesOrderDto();
//设置属性
dto.setStatus(Configuration.KEYMAP.get(mapNext.get("0").toString()));
dto.setGrandTotal(new BigDecimal(mapNext.get("1").toString()));
dto.setName(mapNext.get("2").toString());
dto.setImage("http://vending_client.bb-dd.cn/media/catalog/product/"+mapNext.get("3").toString());
dto.setCreatedAtView(mapNext.get("4").toString());
//对象添加到集合
dtos.add(dto);
}
//成功返回数据
result.put("status", "1");
//数据集合
result.put("list", JSONArray.fromObject(dtos).toString());
//数据总条数
result.put("listLength", salesOrderService.findMyOrdersCount(openId));
log.info("查询我的订单接口end");
log.info("list："+JSONArray.fromObject(dtos).toString());
log.info("listLength："+result.getString("listLength"));
//返回前端
JsonUtils.outJsonString(result.toString(), response);
}
service 就正常的 调用dao方法 我没写任何逻辑 主要是这个dao
Dao
/** * @Title: findMyOrders * @Description: 我的订单查询 * @author yihj * @param @param openId * @param @param pageable * @param @return 参数 * @return Page<SalesOrder> 返回类型 * @throws */ @Query(value = "select new map(salesOrder.status,salesOrder.grandTotal,product.name, product.image, date_format(salesOrder.createdAt, '%Y-%m-%d %H:%i:%s')) from SalesOrder salesOrder " + " INNER JOIN Customer customer ON customer.customerId = salesOrder.customerId" + " INNER JOIN SalesOrderItem orderItem ON orderItem.orderId = salesOrder.orderId" + " INNER JOIN CatalogProduct product ON product.productId = orderItem.productId" + " WHERE customer.openId = ?1") Page<List<Map<String, Object>>> selectMyOrders(String openId,Pageable pageable);
