今天来学习下SpringBoot如何连接SqlServer,废话少说,先看例子项目结构
项目结构项目采用SpringBoot+Hibernate+SqlServer,以获取工作日为例子,传入一个日期,判断是否工作日,返回1和0
Entity
先看下实体,实体为操作表的映射,不必多说
@Entity
@Table(name = "sign_workday")
public class SignWorkDayEntity {
@Id
@GeneratedValue
@Column(name = "ID")
private Long id;
@Column(name = "WORK_DATE")
private Date workDate;
@Column(name = "WORK_CODE")
private String workCode;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Date getWorkDate() {
return workDate;
}
public void setWorkDate(Date workDate) {
this.workDate = workDate;
}
public String getWorkCode() {
return workCode;
}
public void setWorkCode(String workCode) {
this.workCode = workCode;
}
}
Service
继承CrudRepository,可以拿到crud操作JPA,接口里面可以写你需要的增删改查操作的方法,需要注意的是,findBy(xxxx),这个xxxx必须是你表结构映射的字段名称,这个方法不需要实现,SpringBoot已经帮你做好了,只需要调用即可。
@Repository
public interface WorkDayRepository extends CrudRepository<SignWorkDayEntity, Long>{
SignWorkDayEntity findByWorkDate(Date workDate);
}
Controller
首先@Autowired注入Respository,再在需要的地方调用即可
@RestController
public class WorkDayController {
@Autowired
private WorkDayRepository workDayRepository;
@RequestMapping(value = "/getWorkDay.json")
@ResponseBody
public String getWorkDay(@RequestParam(value = "day") String day) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
date = sdf.parse(day);
} catch (ParseException e) {
e.printStackTrace();
}
SignWorkDayEntity entity = workDayRepository.findByWorkDate(date);
if (entity != null) {
return entity.getWorkCode();
}
return "-1";
}
}
最后也是最重要的就是配置文件了
配置参数都在这个文件配置就行,关于其他参数可以看下官方文档,这里提一下sqlserver的参数,首先pom依赖sqlserver的驱动,然后配置文件引入
url+username+password+driver
这时候已经可以连接了,但是默认指定的schema是dbo,如果想修改默认schema,这就需要最后一行spring.jpa.properties.hibernate.default_schema = schema,指定了就可以连接到某个架构下的表了
application.properties
spring.datasource.url = jdbc:sqlserver://localhost:1433;DatabaseName=DB
spring.datasource.username = username
spring.datasource.password= password
spring.datasource.driver-class-name = com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.properties.hibernate.default_schema = schema
pom配置:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
参考:
指定架构解决方案引自:https://stackoverflow.com/questions/24278659/change-database-schema-used-by-spring-boot/24278772#24278772
官方文档:https://docs.spring.io/spring-boot/docs/2.0.0.BUILD-SNAPSHOT/api/
中文文档:https://qbgbook.gitbooks.io/spring-boot-reference-guide-zh/content/
网友评论