Java 将 Spring Boot JDBCTemplate 连接到 SQL Server (MSSQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25729844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connecting Spring Boot JDBCTemplate to SQL Server (MSSQL)
提问by tony
I'm very new to Spring Boot and I'm having trouble trying to set my project up so that it can communicate to SQL Server - more specifically, my JDBCTemplate instance variable is null and for some reason isn't being 'autowired' with the datasource I've specified in my application.properties file. These are the steps I've taken so far:
我是 Spring Boot 的新手，我在尝试设置我的项目以便它可以与 SQL Server 通信时遇到问题 - 更具体地说，我的 JDBCTemplate 实例变量为空，并且由于某种原因没有被“自动装配”我在 application.properties 文件中指定的数据源。这些是我迄今为止采取的步骤：
- Using STS I created a new Spring Boot project using the Spring Start Project template.
- I selected Gradle to by the 'Type' and ticked JDBC.
- I then followed the following tutorial to create an abstract interface (DAO) to SQL Server (http://www.tutorialspoint.com/spring/spring_jdbc_example.htm).
- If you scroll down the tutorial page to the MainApp.java bit, the first 4 lines of the main method I did not use - because I don't have a beans.xml file. This is where I presume Spring Boot's @Autowired annotation comes in and creates my beans for me?
- I downloaded the SQL Server jar file from Microsoft and added it to my project as an external JAR by right clicking on my project -> Build Path -> Configure Build Path -> Add External JARs..'. Doing this removed an error I had which indicated that the driverClassName I specified in my application.properties file couldn't be found.
- 使用 STS 我使用 Spring Start Project 模板创建了一个新的 Spring Boot 项目。
- 我通过“类型”选择了 Gradle 并勾选了 JDBC。
- 然后我按照以下教程为 SQL Server ( http://www.tutorialspoint.com/spring/spring_jdbc_example.htm)创建了一个抽象接口 (DAO )。
- 如果您将教程页面向下滚动到 MainApp.java 位，我没有使用 main 方法的前 4 行 - 因为我没有 beans.xml 文件。这是我假设 Spring Boot 的 @Autowired 注释进来并为我创建 bean 的地方？
- 我从 Microsoft 下载了 SQL Server jar 文件，并通过右键单击我的项目 -> 构建路径 -> 配置构建路径 -> 添加外部 JAR 将其作为外部 JAR 添加到我的项目中。这样做消除了我的错误，该错误表明无法找到我在 application.properties 文件中指定的 driverClassName。
I'll start by displaying the contents of my 'application.properties' file:
我将首先显示我的“application.properties”文件的内容：
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=testdb
spring.datasource.username=sa
spring.datasource.password=myPassword
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerConnection
spring.datasource.initialize=true
Below is my 'JDBCTemplate.java' class which contains my CRUD methods:
下面是我的 'JDBCTemplate.java' 类，其中包含我的 CRUD 方法：
package demo;
import java.util.List;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
public class BranchJDBCTemplate implements BranchDAO {
private DataSource dataSource;
@Autowired
protected JdbcTemplate jdbcTemplateObject;
@Autowired
@Override
public void setDataSource(DataSource ds) {
this.dataSource = ds;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
@Override
public void create(String name) {
String SQL = "insert into branches (name) values (?)";
jdbcTemplateObject.update(SQL, name);
System.out.println("Created Record Name = " + name);
return;
}
@Override
public Branch getBranch(Integer id) {
String SQL = "select * from branches where id = ?";
Branch student = jdbcTemplateObject.queryForObject(SQL,
new Object[]{id}, new BranchMapper());
return student;
}
@Override
public List<Branch> listBranches() {
String SQL = "select * from branches";
List <Branch> branches = jdbcTemplateObject.query(SQL, new BranchMapper());
return branches;
}
@Override
public void delete(Integer id) {
String SQL = "delete from branches where id = ?";
jdbcTemplateObject.update(SQL, id);
System.out.println("Deleted Record with ID = " + id );
return;
}
@Override
public void update(Integer id, String name) {
String SQL = "update Student set name = ? where id = ?";
jdbcTemplateObject.update(SQL, id);
System.out.println("Updated Record with ID = " + id );
return;
}
}
And finally, here is my 'CustController.java' class which contains the request mapping where I use the JDBCTemplate class to perform a database operation:
最后，这是我的“CustController.java”类，其中包含我使用 JDBCTemplate 类执行数据库操作的请求映射：
package demo;
import java.util.List;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class CustController {
@RequestMapping("/customer")
public Cust customer(@RequestParam(value="name", required=false, defaultValue="World") String name) {
BranchJDBCTemplate branchTemplate = new BranchJDBCTemplate();
List<Branch> branchesList = branchTemplate.listBranches();
for (Branch branch : branchesList) {
System.out.print("ID : " + branch.getId());
}
return new Cust(12, "Test", "Test");
}
}
The issue I'm encountering as mentioned previously is that my jdbcTemplateObject instance ...
如前所述，我遇到的问题是我的 jdbcTemplateObject 实例...
protected JdbcTemplate jdbcTemplateObject;
is null and therefore throwing an exception on the following line:
为空，因此在以下行引发异常：
List <Branch> branches = jdbcTemplateObject.query(SQL, new BranchMapper());
It isn't being initialised automatically, can anyone point out what I'm doing wrong?
它没有被自动初始化，有人能指出我做错了什么吗？
Many thanks!
非常感谢！
Tony
托尼
采纳答案by Prasad
You are right, you need to have beans.xml with datasource configured in it.
你是对的，你需要在 beans.xml 中配置数据源。
In CustController class customer() method, you are using new operator as:
在 CustController 类 customer() 方法中，您使用 new 运算符作为：
BranchJDBCTemplate branchTemplate = new BranchJDBCTemplate();
and so this branchTemplate instance is not spring manged and so datasource is not autowired resulting in null value of jdbctemplate.
所以这个 branchTemplate 实例不是 spring 管理的，所以数据源不是自动装配的，导致 jdbctemplate 为空值。
Instead use the annotatioan as:
而是使用注释作为：
@Repository("branchDao")
public class BranchJDBCTemplate implements BranchDAO {
...
}
and access branchTemplate in CustController as:
并在 CustController 中访问 branchTemplate 为：
@RestController
public class CustController {
@Autowired
@Qualifier("branchDao")
BranchJDBCTemplate branchTemplate;
...
}
回答by Prasad Khode
try using the following in your application.properties file
尝试在 application.properties 文件中使用以下内容
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=testdb;integratedSecurity=false;
