Java sql server запросы

Working with a connection

The following sections provide examples of the different ways to connect to a SQL Server database by using the SQLServerConnection class of the Microsoft JDBC Driver for SQL Server.

If you have problems connecting to SQL Server using the JDBC driver, see Troubleshooting Connectivity for suggestions on how to correct it.

Creating a connection by using the DriverManager class

The simplest approach to creating a connection to a SQL Server database is to load the JDBC driver and call the getConnection method of the DriverManager class, as in the following:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String connectionUrl = "jdbc:sqlserver://localhost;encrypt=true;database=AdventureWorks;integratedSecurity=true;" Connection con = DriverManager.getConnection(connectionUrl); 

This technique will create a database connection using the first available driver in the list of drivers that can successfully connect with the given URL.

When using the sqljdbc4.jar class library, applications do not need to explicitly register or load the driver by using the Class.forName method. When the getConnection method of the DriverManager class is called, an appropriate driver is located from the set of registered JDBC drivers. For more information, see Using the JDBC Driver.

Creating a connection by using the SQLServerDriver class

If you have to specify a particular driver in the list of drivers for DriverManager, you can create a database connection by using the connect method of the SQLServerDriver class, as in the following:

Driver d = (Driver) Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); String connectionUrl = "jdbc:sqlserver://localhost;encrypt=true;database=AdventureWorks;integratedSecurity=true;" Connection con = d.connect(connectionUrl, new Properties()); 

Creating a connection by using the SQLServerDataSource class

If you have to create a connection by using the SQLServerDataSource class, you can use various setter methods of the class before you call the getConnection method, as in the following:

SQLServerDataSource ds = new SQLServerDataSource(); ds.setUser("MyUserName"); ds.setPassword("*****"); ds.setServerName("localhost"); ds.setPortNumber(1433); ds.setDatabaseName("AdventureWorks"); Connection con = ds.getConnection(); 

Creating a connection that targets a specific data source

If you have to make a database connection that targets a specific data source, there are a number of approaches that you can take. Each approach depends on the properties that you set by using the connection URL.

To connect to the default instance on a remote server, use the following example:

String url = "jdbc:sqlserver://MyServer;encrypt=true;integratedSecurity=true;" 

To connect to a specific port on a server, use the following example:

String url = "jdbc:sqlserver://MyServer:1533;encrypt=true;integratedSecurity=true;" 

To connect to a named instance on a server, use the following example:

String url = "jdbc:sqlserver://209.196.43.19;encrypt=true;instanceName=INSTANCE1;integratedSecurity=true;" 

To connect to a specific database on a server, use the following example:

String url = "jdbc:sqlserver://172.31.255.255;encrypt=true;database=AdventureWorks;integratedSecurity=true;" 

For more connection URL examples, see Building the connection URL.

Creating a connection with a custom login timeout

If you have to adjust for server load or network traffic, you can create a connection that has a specific login timeout value described in seconds, as in the following example:

String url = "jdbc:sqlserver://MyServer;encrypt=true;loginTimeout=90;integratedSecurity=true;" 

Create a connection with application-level identity

If you have to use logging and profiling, you will have to identify your connection as originating from a specific application, as in the following example:

String url = "jdbc:sqlserver://MyServer;encrypt=true;applicationName=MYAPP.EXE;integratedSecurity=true;" 

Closing a connection

You can explicitly close a database connection by calling the close method of the SQLServerConnection class, as in the following:

This will release the database resources that the SQLServerConnection object is using, or return the connection to the connection pool in pooled scenarios.

Calling the close method will also roll back any pending transactions.

Источник

Шаг 3. Подтверждение концепции: подключение к SQL с помощью Java

Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и для него не гарантируется соблюдение рекомендаций корпорации Майкрософт.

Шаг 1. Подключение

Используйте класс подключения для подключения к базе данных SQL.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; try (Connection connection = DriverManager.getConnection(connectionUrl);) < // Code here. >// Handle any errors that may have occurred. catch (SQLException e) < e.printStackTrace(); >> > 

Шаг 2. Выполнение запроса

В этом примере следует подключиться к базе данных SQL Azure, выполнить инструкцию SELECT и вернуть выбранные строки.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) < // Create and execute a SELECT SQL statement. String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer"; resultSet = statement.executeQuery(selectSql); // Print results from select statement while (resultSet.next()) < System.out.println(resultSet.getString(2) + " " + resultSet.getString(3)); >> catch (SQLException e) < e.printStackTrace(); >> > 

Шаг 3. Вставка строки

В этом примере следует выполнить инструкцию INSERT, передать параметры и извлечь автоматически созданное значение первичного ключа.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; String insertSql = "INSERT INTO SalesLT.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES " + "('NewBike', 'BikeNew', 'Blue', 50, 120, '2016-01-01');"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); PreparedStatement prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) < prepsInsertProduct.execute(); // Retrieve the generated key from the insert. resultSet = prepsInsertProduct.getGeneratedKeys(); // Print the ID of the inserted row. while (resultSet.next()) < System.out.println("Generated: " + resultSet.getString(1)); >> // Handle any errors that may have occurred. catch (Exception e) < e.printStackTrace(); >> > 

Источник

Step 3: Proof of concept connecting to SQL using Java

This example should be considered a proof of concept only. The sample code is simplified for clarity, and doesn’t necessarily represent best practices recommended by Microsoft.

Step 1: Connect

Use the connection class to connect to SQL Database.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; try (Connection connection = DriverManager.getConnection(connectionUrl);) < // Code here. >// Handle any errors that may have occurred. catch (SQLException e) < e.printStackTrace(); >> > 

Step 2: Execute a query

In this sample, connect to Azure SQL Database, execute a SELECT statement, and return selected rows.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) < // Create and execute a SELECT SQL statement. String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer"; resultSet = statement.executeQuery(selectSql); // Print results from select statement while (resultSet.next()) < System.out.println(resultSet.getString(2) + " " + resultSet.getString(3)); >> catch (SQLException e) < e.printStackTrace(); >> > 

Step 3: Insert a row

In this example, execute an INSERT statement, pass parameters, and retrieve the auto-generated Primary Key value.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; String insertSql = "INSERT INTO SalesLT.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES " + "('NewBike', 'BikeNew', 'Blue', 50, 120, '2016-01-01');"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); PreparedStatement prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) < prepsInsertProduct.execute(); // Retrieve the generated key from the insert. resultSet = prepsInsertProduct.getGeneratedKeys(); // Print the ID of the inserted row. while (resultSet.next()) < System.out.println("Generated: " + resultSet.getString(1)); >> // Handle any errors that may have occurred. catch (Exception e) < e.printStackTrace(); >> > 

Источник

Пример получения данных результирующего набора

В этом образце приложения драйвера Microsoft JDBC Driver for SQL Server демонстрируется извлечение набора данных из базы данных SQL Server и последующее отображение этих данных.

Файл кода для этого примера с именем RetrieveResultSet.java находится в следующей папке:

\\sqljdbc_\\samples\resultsets 

Требования

Чтобы запустить этот пример приложения, необходимо включить в параметр classpath путь к файлу mssql-jdbc.jar. Вам также потребуется доступ к образцу базы данных AdventureWorks2022. См. сведения о том, как настроить параметр classpath, в руководстве по использованию JDBC Driver.

Драйвер Microsoft JDBC Driver для SQL Server включает файлы библиотек классов mssql-jdbc, которые используются в зависимости от выбранных параметров среды выполнения Java (JRE). Для получения дополнительных сведений о том, какой JAR-файл выбрать, см. статью Требования к системе для драйвера JDBC.

Пример

В следующем примере пример кода устанавливает подключение к образцу базы данных AdventureWorks2022. Затем с помощью инструкции SQL с объектом SQLServerStatement выполняется инструкция SQL, а возвращенные ею данные помещаются в объект SQLServerResultSet.

Далее пример кода вызывает настраиваемый метод displayRow для прохода по строкам данных в результирующем наборе, и некоторые из этих данных отображаются с помощью метода getString.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class RetrieveResultSet < public static void main(String[] args) < // Create a variable for the connection string. String connectionUrl = "jdbc:sqlserver://:;encrypt=true;databaseName=AdventureWorks;user=;password="; try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) < createTable(stmt); String SQL = "SELECT * FROM Production.Product;"; ResultSet rs = stmt.executeQuery(SQL); displayRow("PRODUCTS", rs); >// Handle any errors that may have occurred. catch (SQLException e) < e.printStackTrace(); >> private static void displayRow(String title, ResultSet rs) throws SQLException < System.out.println(title); while (rs.next()) < System.out.println(rs.getString("ProductNumber") + " : " + rs.getString("Name")); >> private static void createTable(Statement stmt) throws SQLException < stmt.execute("if exists (select * from sys.objects where name = 'Product_JDBC_Sample')" + "drop table Product_JDBC_Sample"); String sql = "CREATE TABLE [Product_JDBC_Sample](" + "[ProductID] [int] IDENTITY(1,1) NOT NULL," + "[Name] [varchar](30) NOT NULL," + "[ProductNumber] [nvarchar](25) NOT NULL," + "[MakeFlag] [bit] NOT NULL," + "[FinishedGoodsFlag] [bit] NOT NULL," + "[Color] [nvarchar](15) NULL," + "[SafetyStockLevel] [smallint] NOT NULL," + "[ReorderPoint] [smallint] NOT NULL," + "[StandardCost] [money] NOT NULL," + "[ListPrice] [money] NOT NULL," + "[Size] [nvarchar](5) NULL," + "[SizeUnitMeasureCode] [nchar](3) NULL," + "[WeightUnitMeasureCode] [nchar](3) NULL," + "[Weight] [decimal](8, 2) NULL," + "[DaysToManufacture] [int] NOT NULL," + "[ProductLine] [nchar](2) NULL," + "[Class] [nchar](2) NULL," + "[Style] [nchar](2) NULL," + "[ProductSubcategoryID] [int] NULL," + "[ProductModelID] [int] NULL," + "[SellStartDate] [datetime] NOT NULL," + "[SellEndDate] [datetime] NULL," + "[DiscontinuedDate] [datetime] NULL," + "[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL," + "[ModifiedDate] [datetime] NOT NULL,)"; stmt.execute(sql); sql = "INSERT Product_JDBC_Sample VALUES ('Adjustable Time','AR-5381','0','0',NULL,'1000','750','0.00','0.00',NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,'2008-04-30 00:00:00.000',NULL,NULL,'694215B7-08F7-4C0D-ACB1-D734BA44C0C8','2014-02-08 10:01:36.827') "; stmt.execute(sql); sql = "INSERT Product_JDBC_Sample VALUES ('ML Bottom Bracket','BB-8107','0','0',NULL,'1000','750','0.00','0.00',NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,'2008-04-30 00:00:00.000',NULL,NULL,'694215B7-08F7-4C0D-ACB1-D734BA44C0C8','2014-02-08 10:01:36.827') "; stmt.execute(sql); sql = "INSERT Product_JDBC_Sample VALUES ('Mountain-500 Black, 44','BK-M18B-44','0','0',NULL,'1000','750','0.00','0.00',NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,'2008-04-30 00:00:00.000',NULL,NULL,'694215B7-08F7-4C0D-ACB1-D734BA44C0C8','2014-02-08 10:01:36.827') "; stmt.execute(sql); >> 

Источник

Java — JDBC Code Examples for MSSQL

You can put any sql code in your statement, which I thought was handy. As you can see the statemet starts with an IF statement and runs a different query depending on the value of a param passed in. Also note that the connection does not specify a database, so to include that by prefixing the table name in SQL:

package database;

import java.sql.*;


public class TestJDBC  
public void test() 
String connectionUrl = "jdbc:sqlserver://localhost:1433;" + 
"databaseName=testDB;user=testuser;password=xxxxxxx";
 
Connection con = null; 
PreparedStatement pstmt = null; 
ResultSet rs = null;
 
try  
//establish connection 
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
con = DriverManager.getConnection(connectionUrl);
 
// prepare the statement 
String SQL = 
"DECLARE @test INT = ?\n" + 
"IF (@test IS NULL)\n" + 
" SELECT TOP 10 UserFirstname,UserLastName FROM [TestDB].dbo.Users" + 
" WHERE UserFirstname Like 'T%'\n" + 
"ELSE\n" + 
" SELECT TOP 10 UserFirstname,UserLastName FROM [TestDB].dbo.Users" + 
" WHERE UserFirstname Like 'J%'\n";
 
 
pstmt = con.prepareStatement(SQL); 
pstmt.setNull(1,1); // To pass in Null 
//pstmt.setInt(1,1); // To pass in a non null value
 
// run it and parse results 
rs = pstmt.executeQuery();
 
while (rs.next()) System.out.println(rs.getString(1) + ":" + rs.getString(2)); 
> 
 
>catch(Exception e) 
e.printStackTrace(); 
 
>finally  
if(rs != null) try < rs.close(); >catch(Exception e) <> 
if(pstmt != null) try < pstmt.close(); >catch(Exception e) <> 
if(con != null) try < con.close(); >catch(Exception e) <> 
 
> 
 
>

>

Источник