Java spring ms sql server

Подключаемся к MS SQL Server в Spring Boot

Подключение к MS SQL Server в Spring Boot выполняется следующим образом.

Вначале добавим в pom.xml зависимости для драйвера СУБД для JDBC и библиотеки, при помощи которой мы будем с этой СУБД взаимодействовать.

Зависимость для драйвера СУБД:

Если для работы с базой данных планируется использовать JPA:

Если для ваших целей достаточно просто «обычного» JDBC:

Далее необходимо прописать параметры подключения в конфигурационном файле приложения.

spring . datasource . url = jdbc : sqlserver : //Hostname\\SQLServerInstanceName;databaseName=MyDatabaseName

Если вы используете Spring Data JPA, который работает на основе Hibernate, нужно дополнительно указать диалект Transact-SQL.

После этого можно работать с MS SQL Server из вашего Spring Boot приложения. Только не забудьте предварительно включить у нужного экземпляра SQL Server поддержку TCP протокола и открыть для него соответствующие порты (по умолчанию 1433 и 1434).

Добавить комментарий Отменить ответ

Рубрики

• 1С
• Access
• Arduino
• Asp.net
• C#
• C++
• Delphi
• Frontend Разработка (Javascript, Html, Css)
• Inifilenet
• Java
• Java Android
• Java SE/EE
• Kotlin
• Linux
• MongoDB
• MS SQL Server
• MySQL
• NamedJDBCParams (open source)
• Node.js
• Php
• Python
• Qt
• RSS молния
• RSSLight
• Streletz Корпоративный портал
• Streletz-Note
• StreletzBuilder
• StreletzNoticeBoard
• TAdvancedLinkLabel (open source)
• Task tracker
• TLogger (open source)
• Total-Old-Revision-Cleaner
• Windebextraction (Open Source)
• Windows
• WordPress
• Архив программ
• Базы данных
• Безопасность
• Блокировщик Спящего Режима
• Генератор Ic_Launcher (Open Source)
• Другие библиотеки и компиляторы C++
• Карманный Фонарик
• Новости сайта
• Общие вопросы программирования
• Общие вопросы системного администрирования
• Паттерны проектирования
• Подбор цвета (open source)
• Приложения для Android
• Прогноз Погоды (Open Source)
• Разное
• Система тестирования SC
• Системное администрирование
• Статьи
• Экранные Часы

Свежие записи

Источник

Spring Boot Connect to Microsoft SQL Server Examples

In this Spring Boot article, you will learn how to connect to Microsoft SQL Server from a Spring Boot application in the following two common scenarios:

• A Spring Boot console application with Spring JDBC and JdbcTemplate
• A Spring Boot web application with Spring Data JPA and Hibernate framework

• Declare a dependency for SQL Server JDBC driver that allows Java application to connect to Microsoft SQL Server.
• Declare a dependency for Spring JDBC or Spring Data JPA
• Specify data source properties for the database connection information
• For simple cases, you can use Spring JDBC with JdbcTemplate for executing plain SQL statements against the database
• For more advanced usage, you can use Spring Data JPA with an entity class and a repository interface.

1. Declare dependency for SQL Server JDBC Driver

 com.microsoft.sqlserver mssql-jdbc runtime 

Note that the scope is runtime, which means the driver JAR file is only needed at runtime – not development (compile time). And you don’t need to specify the version as Spring Boot uses the default version specified in the parent POM.

2. Specify Data Source Properties

Open the Spring Boot application configuration file ( application.properties ) and specify the following properties:

spring.datasource.url=jdbc:sqlserver://sqlsrv\\sqlexpress;databaseName=customer spring.datasource.username=username spring.datasource.password=password

Here, the JDBC URL points to a named instance of a remote SQL server and SQL authentication mode is used (recommended).

3. Connect to SQL Server with Spring JDBC

Use Spring JDBC if you just want to connect and execute simple SQL statements. Add the following dependency to your project:

 org.springframework.boot spring-boot-starter-jdbc 

For example, below is code of a Spring Boot console program that uses Spring JDBC with JdbcTemplate API:

package net.codejava; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; @SpringBootApplication public class SpringBootJdbcTemplateSqlServerApplication implements CommandLineRunner < @Autowired private JdbcTemplate jdbcTemplate; public static void main(String[] args) < SpringApplication.run(SpringBootJdbcTemplateSqlServerApplication.class, args); >@Override public void run(String. args) throws Exception < String sql = "SELECT * FROM customers"; Listcustomers = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Customer.class)); customers.forEach(System.out :: println); > >

As you can see, this program connects to SQL Server and execute a SQL Select statement for retrieving rows from the customers table. You don’t need to explicitly make a connection or disconnection as the JdbcTemplate does it behind the scene.

Follow this this article to learn more about using Spring JdbcTemplate.

4. Connect to SQL Server with Spring Data JPA

 org.springframework.boot spring-boot-starter-data-jpa 

Note that by default, Spring Data JPA uses Hibernate as implementation of Java Persistence API (JPA).

Besides the required data source properties (JDBC URL, username and password), you can also specify some additional properties as follows:

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver spring.jpa.hibernate.ddl-auto=update spring.jpa.show-sql=true spring.jpa.properties.hibernate.format_sql=true spring.jpa.properties.hibernate.dialect=org.hibernate.dialect. org.hibernate.dialect.SQLServer2008Dialect

package net.codejava; import javax.persistence.*; @Entity @Table(name = "customers") public class Customer < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String name; private String email; private int age; // getters and setters. >

This entity class maps to the corresponding customers table in SQL server database. And then you need to create the corresponding repository interface as below:

package net.codejava; import org.springframework.data.jpa.repository.JpaRepository; public interface CustomerRepository extends JpaRepository

@Controller public class CustomerController < @Autowired private CustomerRepository customerRepo; @GetMapping("/customers") public String listAll(Model model) < ListlistCustomers = customerRepo.findAll(); model.addAttribute("listCustomers", listCustomers); return "customers"; > >

Those are some code examples about connecting to Microsoft SQL Server in a Spring Boot application. You can use Spring JDBC for simple cases and Spring Data JPA for more advanced usage.

To learn more about Spring Data JPA, I recommend you to follow this article: Understand Spring Data JPA with Simple Example .

To see the coding in action, I recommend you to watch the following video:

Related Articles:

Other Spring Boot Tutorials:

• How to create a Spring Boot Web Application (Spring MVC with JSP/ThymeLeaf)
• Spring Boot CRUD Example with Spring MVC – Spring Data JPA – ThymeLeaf — Hibernate — MySQL
• Spring Boot Hello World RESTful Web Services Tutorial
• Spring Boot Thymeleaf Form Handling Tutorial
• Spring Data JPA Paging and Sorting Examples
• Spring Boot Error Handling Guide
• Spring Boot Logging Basics
• Spring Security Role-based Authorization Tutorial
• Spring Security Customize Login and Logout
• How to Get Logged-in User’s Details with Spring Security
• Spring Security: Prevent User from Going Back to Login Page if Already logged in

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Add comment

Comments

Tôi đã mapping với database nhưng việc sử dụng hàm findAll() của tôi không thể thực hiện được việc lấy dữ liệu trong database

Источник