Java with sql server with example

Java Connect to Microsoft SQL Server Example

This JDBC tutorial helps you understand how to get JDBC driver and write code for making database connection to Microsoft SQL Server from a Java client. Suppose you have a light weight version of SQL Server installed, such as Microsoft SQL Server Express.

Table of content:

1. Download Microsoft JDBC driver for SQL server

To enable a Java program connects to Microsoft SQL Server database, we need to have a suitable JDBC driver present in the classpath. Click here to download the latest version of Microsoft JDBC Driver for SQL Server. Currently, the latest version is Microsoft JDBC driver 8.2 which supports Java 8, 11 and 13.

Extract the downloaded archive file, and put the mssql-jdbc-8.2.0.jreVERSION.jar to your project’s classpath. If you use Maven, then declare the following dependency:

 com.microsoft.sqlserver mssql-jdbc 8.2.1.jre11 

2. JDBC database URL for SQL Server

jdbc:sqlserver:// [serverName[\instanceName][:portNumber]][;property=value[;property=value]]

    • serverName : host name or IP address of the machine on which SQL server is running.
    • instanceName : name of the instance to connect to on serverName . The default instance is used if this parameter is not specified.
    • portNumber : port number of SQL server, default is 1433. If this parameter is missing, the default port is used.
    • property=value : specify one or more additional connection properties. To see the properties specific to SQL server, visit Setting the Connection Properties.
      • Windows authentication: using current Windows user account to log on SQL Server. This mode is for the case both the client and the SQL server are running on the same machine. We specify this mode by adding the property integratedSecurity=true to the URL.
      • SQL Server authentication: using a SQL Server account to authenticate. We have to specify username and password explicitly for this mode.

      — Connect to default instance of SQL server running on the same machine as the JDBC client, using Windows authentication:

      jdbc:sqlserver://localhost;integratedSecurity=true;

      — Connect to an instance named sqlexpress on the host dbServer , using SQL Server authentication:

      jdbc:sqlserver://dbHost\sqlexpress;user=sa;password=secret

      — Connect to a named database testdb on localhost using Windows authentication:

      jdbc:sqlserver://localhost:1433;databaseName=testdb;integratedSecurity=true;

      3. Register JDBC driver for SQL Server and establish connection

      The JDBC driver class of SQL Server is com.microsoft.sqlserver.jdbc.SQLServerDriver , so to register this driver, use the following statement:

      DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

      However, that is not required since JDBC 4.0 (JDK 6.0) because the driver manager can detect and load the driver class automatically as long as a suitable JDBC driver present in the classpath.

      To make a connection, call the method getConnection() of the DriverManager class. Here is a code snippet that connects the user sa with password secret to the instance sqlexpress on localhost :

      String dbURL = «jdbc:sqlserver://localhost\\sqlexpress;user=sa;password=secret»; Connection conn = DriverManager.getConnection(dbURL); if (conn != null)

      String dbURL = "jdbc:sqlserver://localhost\\sqlexpress"; String user = "sa"; String pass = "secret"; conn = DriverManager.getConnection(dbURL, user, pass);

      We can also use a java.util.Properties object to store connection properties, as in the following example:

      String dbURL = "jdbc:sqlserver://localhost\\sqlexpress"; Properties properties = new Properties(); properties.put("user", "sa"); properties.put("password", "secret"); conn = DriverManager.getConnection(dbURL, properties);

      NOTE: if you want to use Windows authentication mode ( integratedSecurity=true ), you must have the sqljdbc_auth.dll in the classpath.

      4. Java Code Example to connect to SQL Server

      To demonstrate, we create a small program that connects to an SQL Server instance on localhost and print out some database information as follows:

      package net.codejava.jdbc; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.SQLException; /** * This program demonstrates how to establish database connection to Microsoft * SQL Server. * @author www.codejava.net * */ public class JdbcSQLServerConnection < public static void main(String[] args) < Connection conn = null; try < String dbURL = "jdbc:sqlserver://localhost\\sqlexpress"; String user = "sa"; String pass = "secret"; conn = DriverManager.getConnection(dbURL, user, pass); if (conn != null) < DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData(); System.out.println("Driver name: " + dm.getDriverName()); System.out.println("Driver version: " + dm.getDriverVersion()); System.out.println("Product name: " + dm.getDatabaseProductName()); System.out.println("Product version: " + dm.getDatabaseProductVersion()); >> catch (SQLException ex) < ex.printStackTrace(); >finally < try < if (conn != null && !conn.isClosed()) < conn.close(); >> catch (SQLException ex) < ex.printStackTrace(); >> > >
      Driver name: Microsoft JDBC Driver 8.2 for SQL Server Driver version: 8.2.0.0 Product name: Microsoft SQL Server Product version: 15.00.2000

      That’s some Java code example to establish database connection to Microsoft SQL Server. For a video guide, you can watch the following video:

      JDBC API References:

      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.

      Источник

      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(); >> > 

      Источник

      Читайте также:  Php mysql удалить все строки
Оцените статью