第一个springboot+jpa+oracle+maven项目
大概花了一周的时间完成了第一个springboot项目的搭建,使用的工具是Eclipse,遇到不少问题,这里强烈推荐《Spring实战》这本书,有些问题在网上搜了半天但是都不能解决问题,后来都是在这本书中找到解决方法的。
项目结构:
pom.xml文件
<?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.0http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com</groupId>
<artifactId>ypjgnew</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>ypjgnew</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
<version>2.0.5.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.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<!-- Spring Boot log4j依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j</artifactId>
<version>1.3.4.RELEASE</version>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
</configuration>
</plugin>
</plugins>
</build>
</project>
主要配置文件:application.properties
#主数据源
spring.datasource.driver=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@
spring.datasource.username=
spring.datasource.password=
#辅数据源
spring.datasource.auth.driver=oracle.jdbc.driver.OracleDriver
spring.datasource.auth.url=jdbc:oracle:thin:@
spring.datasource.auth.username=
spring.datasource.auth.password=
#Spring Data JPA
spring.jpa.database=oracle
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# stripped before adding them to the entity manager)
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle9Dialect
#热部署
spring.devtools.restart.enabled: true
#根路径
server.servlet.context-path=/ypjg
spring.mvc.view.prefix=classpath:/resources/templates/
spring.mvc.view.suffix=.html
spring.mvc.static-path-pattern=/static/**
spring.mvc.favicon.enabled=false
#实体类的扫描路径,因为有两个数据源
packages.to.scan.auth=com.login.model
packages.to.scan=com.ypjg.model
配置类,这次用了java类的方式来创建bean,没有采用xml的方式。因为有两个数据源,所以把一些基础的配置单独写了一个类,另外两个配置对应数据库的类继承这个基础类。
基础配置类:BaseConfiguration
@Configuration
@EnableTransactionManagement
@PropertySource("classpath:application.properties")
public class BaseConfiguration {
@Autowired
protected Environment env;
/**
* 把HibernateExceptions转换成DataAccessExceptions
*/
@Bean
public HibernateExceptionTranslator hibernateExceptionTranslator() {
return new HibernateExceptionTranslator();
}
/**
* hibernate配置
* @return
*/
protected Properties hibernateProperties() {
Properties properties = new Properties();
// 方言
properties.put("spring.jpa.properties.hibernate.dialect", env.getRequiredProperty("spring.jpa.properties.hibernate.dialect"));
// 自动生成表
//properties.put("hibernate.hbm2ddl.auto", env.getRequiredProperty("spring.jpa.hibernate.ddl-auto"));
// 名字策略
properties.put("spring.jpa.hibernate.naming.physical-strategy", env.getRequiredProperty("spring.jpa.hibernate.naming-strategy"));
return properties;
}
}
配置主数据源的类:PrimaryConfig
@Configuration
@EnableJpaRepositories(basePackages = {"com.ypjg.daos"}, entityManagerFactoryRef = "entityManagerFactoryPrimary",
transactionManagerRef = "transactionManagerPrimary")
public class PrimaryConfig extends BaseConfiguration {
/**
* 1.配置主数据源
*
* @return DataSource
*/
@Bean(name = "primaryDataSource")
@Primary
@Qualifier("primaryDataSource")
public DataSource primaryDataSource() {
DruidDataSource source = new DruidDataSource();
source.setDriverClassName(env.getRequiredProperty("spring.datasource.driver"));
source.setUrl(env.getRequiredProperty("spring.datasource.url"));
source.setUsername(env.getRequiredProperty("spring.datasource.username"));
source.setPassword(env.getRequiredProperty("spring.datasource.password"));
try {
source.addFilters("log4j");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return source;
}
/**
* 2.配置EntityManagerFactory
*
* @return
*/
@Primary
@Bean(name = "entityManagerFactoryPrimary")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary() {
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
// 配置数据源
factory.setDataSource(primaryDataSource());
// VendorAdapter
factory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
// entity包扫描路径
factory.setPackagesToScan(env.getRequiredProperty("packages.to.scan"));
// jpa属性
factory.setJpaProperties(hibernateProperties());
factory.afterPropertiesSet();
return factory;
}
/**
* 3.事务管理器配置
*
* @return
*/
@Bean(name="transactionManagerPrimary")
@Primary
public PlatformTransactionManager transactionManagerPrimary() {
JpaTransactionManager manager = new JpaTransactionManager();
manager.setEntityManagerFactory(entityManagerFactoryPrimary().getObject());
return manager;
}
}
配置辅数据源的类:SecondConfig
@Configuration
@EnableJpaRepositories(basePackages = {"com.login.daos"}, entityManagerFactoryRef = "entityManagerFactorySecondary",
transactionManagerRef = "transactionManagerSecondary")
public class SecondConfig extends BaseConfiguration {
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
public DataSource secondaryDataSource() {
DruidDataSource source = new DruidDataSource();
source.setDriverClassName(env.getRequiredProperty("spring.datasource.auth.driver"));
source.setUrl(env.getRequiredProperty("spring.datasource.auth.url"));
source.setUsername(env.getRequiredProperty("spring.datasource.auth.username"));
source.setPassword(env.getRequiredProperty("spring.datasource.auth.password"));
return source;
}
@Bean(name = "entityManagerFactorySecondary")
@Qualifier("entityManagerFactorySecondary")
public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary() {
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
// 配置数据源
factory.setDataSource(secondaryDataSource());
// VendorAdapter
factory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
// entity包扫描路径
factory.setPackagesToScan(env.getRequiredProperty("packages.to.scan.auth"));
// jpa属性
factory.setJpaProperties(hibernateProperties());
factory.afterPropertiesSet();
return factory;
}
@Bean(name="transactionManagerSecondary")
@Qualifier("transactionManagerSecondary")
public PlatformTransactionManager transactionManagerSecondary() {
JpaTransactionManager manager = new JpaTransactionManager();
manager.setEntityManagerFactory(entityManagerFactorySecondary().getObject());
return manager;
}
}
实体类:从来没有配置过多对一,多对多这种映射关系,花了很多时间,也报了各种错,最后总算可以运行了,实体类比较多,这里举简单的几个例子:
1.一对多的单向,涉及到两张表,tb_auth_org_dic_type (一)和 tb_auth_org(多)表,表tb_auth_org中的orgtype对应表tb_auth_org_dic_type中的主键id。
TbAuthOrgDicType类:
@Entity
@Table(name="TB_AUTH_ORG_DIC_TYPE")
public class TbAuthDicOrgType implements java.io.Serializable{
private static final long serialVersionUID = 1L;
@Id
private String id;//主键
@Column(name = "type")
private String type;//类别
... Set和Get方法省略
}
TbAuthOrganization类:
@Entity
@Table(name="TB_AUTH_ORG")
public class TbAuthOrganization implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long orgid;
@Column(name = "ORGNAME")
private String orgname; //机构名称
@Column(name = "ORGCODE")
private String orgcode; //编码
@Column(name = "CHECKDATE")
@Type(type="timestamp")
private Date checkDate;//审核日期
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "orgtype")
private TbAuthDicOrgType tbAuthDicOrgType; //机构类型
... Set和Get方法省略
}
2.一对多双向:涉及两张表,tb_auth_unit(一)和 tb_auth_user(多)表,tb_auth_user表中的unitid对应 tb_auth_unit表中的主键 id。
TbAuthUnit类:
@Entity
@Table(name="TB_AUTH_UNIT")
public class TbAuthUnit implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long unitid;
@Column(name = "SJBMMC")
private String unitname;
...
// 一对多,一个单位对应多个用户信息
@OneToMany(fetch=FetchType.LAZY,mappedBy="tbAuthUnit")
private Set<TbAuthUser> tbAuthUsers = new HashSet<TbAuthUser>(0);
... Set和Get方法省略
}
TbAuthUser类:
@Entity
@Table(name="TB_AUTH_USER")
public class TbAuthUser implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long userid;
@Column(name = "USERNAME")
private String username;
...
//多对一,多个用户对应一个单位
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "UNITID")
private TbAuthUnit tbAuthUnit;
... Set和Get方法省略
}
3.多对多双向:涉及两张表:tb_auth_role 和 tb_auth_resource 表,会生成一张中间表 tb_auth_role_resource,roleid对应tb_auth_role中的主键roleid,resid对应tb_auth_resource 表中的主键resid。
TbAuthRole类:
@Entity
@Table(name="TB_AUTH_ROLE")
public class TbAuthRole implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long roleid;
@Column(name = "rolename")
private String rolename;
//多对多,一个角色对应多个菜单资源 (TB_AUTH_ROLE_RESOURCE中间表)
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "TB_AUTH_ROLE_RESOURCE",joinColumns = { @JoinColumn(name = "ROLEID") },inverseJoinColumns = { @JoinColumn(name = "RESID") })
private Set<TbAuthResource> tbAuthResources = new HashSet<>(0);
... Set和Get方法省略
}
TbAuthResource类:
@Entity
@Table(name="TB_AUTH_RESOURCE")
public class TbAuthResource implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long resid;
@Column(name = "resname")
private String resname;
...
//多对多 一个菜单资源对多个角色(TB_AUTH_ROLE_RESOURCE中间表)
@ManyToMany(mappedBy = "tbAuthResources",fetch=FetchType.LAZY)
private Set<TbAuthRole> tbAuthRoles = new HashSet<>(0);
... Set和Get方法省略
}
Dao层:
1.简单的单个JPA查询:使用@Query查询
UserRepository类:
@Repository
public interface UserRepository extends JpaRepository<TbAuthUser, Long> {
@Query("select t from TbAuthUser t where t.loginname = :loginname and t.psw=:psw")
public List<TbAuthUser> checkLogin(@Param("loginname")String loginName, @Param("psw")String passWord);
@Query("select t from TbAuthUser t where t.loginname = :loginname")
public TbAuthUser getUserByLoginName(@Param("loginname")String loginName);
//修改
@Modifying
@Query("update TbAuthUser t set t.psw=:psw where t.loginname=:loginname")
public int changePassword(@Param("loginname")String loginName, @Param("psw")String passWord);
@Query("select t from TbAuthUser t where t.userid=:userid")
public TbAuthUser getById(@Param("userid")Long id);
}
2.复杂查询,需要按照传统的方式来写SQL语句,就要直接使用EntityManager。
SQLDao类:
@Repository
public class SQLDao {
//如果连接的是主数据库,则不需要加UnitName
@PersistenceContext(unitName = "entityManagerFactorySecondary")
private EntityManager em;
public String selectBySql() {
String sql="select t.username from tb_auth_user t where t.loginname=?";
List<String> list = em.createNativeQuery(sql).setParameter(1, "xxx").getResultList();
return list.get(0).toString();
}
service层:如果需要加事务管理,则在方法上加注解@Transactional,如果不是主数据源,则需要加@Qualifier(“transactionManagerSecondary”)
@Service
public class BaseService {
@Autowired
private SQLDao sqlDao;
public String findByYYName() {
return this.sqlDao.selectBySql();
}
}
control层:
@Controller
public class IndexActiontest {
@Autowired
private BaseService baseService;
@RequestMapping("/test")
@Transactional
@Qualifier("transactionManagerSecondary")
public String hello(Model model){
String name = baseService.findByYYName();
model.addAttribute("name",name);
return "test";
}
}
前台用的是html5,不怎么会写,就不介绍了。
主要遇到的一些问题:
1.打印出SQL语句中的绑定参数
解决:网上有用log4jdbc-log4j2解决的,但是我怎么配都不显示绑定的参数。因为用了druid,就用这个框架配合log4j来打印SQL语句以及绑定参数了。在log4j.properties配置文件中加上log4j.logger.druid.sql.Statement= DUBUG,以及在PrimaryConfig类中加上source.addFilters(“log4j”);详细见上面的代码,如果要打印辅数据库相应的SQL语句,可以在另一个配置文件中也加上这行代码。
log4j.properties配置文件
# LOG4J配置
log4j.rootCategory=INFO, stdout, file
# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %c{1}:%L - %m%n
# root日志输出到文件
log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.file=d:/data/logs/springboot-log4j-all.log
log4j.appender.file.DatePattern='.'yyyy-MM-dd
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %c{1}:%L - %m%n
# 按不同package进行输出
# com.micai包下的日志配置
log4j.category.com.micai=DEBUG, didifile
# com.micai下的日志输出
log4j.appender.didifile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.didifile.file=d:/data/logs/springboot-log4j-my.log
log4j.appender.didifile.DatePattern='.'yyyy-MM-dd
log4j.appender.didifile.layout=org.apache.log4j.PatternLayout
log4j.appender.didifile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %c{1}:%L ---- %m%n
# ERROR级别输出到特定的日志文件中
log4j.logger.error=errorfile
# error日志输出
log4j.appender.errorfile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.errorfile.file=d:/data/logs/springboot-log4j-error.log
log4j.appender.errorfile.DatePattern='.'yyyy-MM-dd
log4j.appender.errorfile.Threshold = ERROR
log4j.appender.errorfile.layout=org.apache.log4j.PatternLayout
log4j.appender.errorfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %c{1}:%L - %m%n
#druid打印SQL语句
log4j.logger.druid.sql.Statement=DEBUG
2.实体类对应关系中懒加载的问题
解决:在查询的时候,报了 org.hibernate.LazyInitializationException: could not initialize proxy - no Session,网上查了一些资料,比较好的做法是使用OpenSessionInViewFilter,这种方法是将session交给servlet filter来管理,每当一个请求来之后就会开启一个session,只有当响应结束才会关闭。原来没有用springboot的时候我在web.xml中配置之后就会起作用,但是在springboot中怎么改都不起作用,我想既然是session关闭了,那么我在control层中加事务控制是不是一样的效果,尝试之后可以起作用。感觉这个方法有点笨,但是目前我还没有找到别的解决办法。
3.JPA复杂sql查询,需要直接使用EntityManager。因为有两个数据源,如果不是主数据源,就需要加限定。@PersistenceContext(unitName = “entityManagerFactorySecondary”)
没有解决的问题:
1.hibernate.hbm2ddl.auto=update 为什么会执行两遍生成表的sql语句,所以每次启动的时候都会有报错信息,但是能正常启动。