Skip to content

java-driver-example.md

Latest commit

History

History
299 lines (230 loc) · 12.8 KB

java-driver-example.md

File metadata and controls

299 lines (230 loc) · 12.8 KB
Raw

Create a Java app that connects to SQL Server and executes queries using Visual Studio Code

These examples may be used with Azure SQL Database

Prerequisites

  1. Java
  2. Maven
  3. Ensure that both Maven and Java are in you Environment PATH. You can check Maven with mvn -version and Java with java -version.

Step 1, Setup Java for development in Visual Studio Code

  1. Start Visual Studio Code.

  2. Select File > Open Folder (File > Open... on macOS) from the main menu.

  3. In the Open Folder dialog, create a javaexample folder in a directory of your choice and select it. Then click Select Folder (Open on macOS).

  4. In the Do you trust the authors of the files in this folder? dialog, select Yes, I trust the authors.

  5. Open the Terminal in Visual Studio Code by selecting View > Terminal from the main menu.

    The Terminal opens with the command prompt in the javaexample folder.

  6. In the Terminal, enter the following command to create a Maven starter package:

    mvn archetype:generate "-DgroupId=com.sqlsamples""-DartifactId=SqlServerSample""-DarchetypeArtifactId=maven-archetype-quickstart""-Dversion=1.0.0"

    You may need to press enter/return to have the command continue when you come to a point with the text " Y: :".

  7. In the Visual Studio Code file explorer, expand the SqlServerSample directory and click on the pom.xml file.

    What is a pom.xml?

  8. Replace the contents of the pom.xml file with the following code:

    <projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.sqlsamples</groupId>
<artifactId>SqlServerSample</artifactId>
<packaging>jar</packaging>
<version>1.0.0</version>
<name>SqlServerSample</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!-- add the JDBC Driver -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>12.2.0.jre8</version>
</dependency>
</dependencies>
<properties>
<!-- specify which version of Java to build against-->
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
</project>

  9. Save the file.

Step 2, Create a Java app that connects to SQL Server

  1. Using the Visual Studio Code file explorer again, find the App.java file located at SqlServerSample\src\main\java\com\sqlsamples\App.java

  2. Click on the App.java file to bring it up in the Visual Studio Code editor.

  3. Replace the contents of App.java by copying and pasting the code below into the file. Don't forget to replace

    StringconnectionUrl = "jdbc:sqlserver://<your_server.database.windows.net>:<your_database_port>;databaseName=master;encrypt=true;trustServerCertificate=true;user=<your_username>;password=<your_password>";

    with the values of your database.

    packagecom.sqlsamples;
importjava.sql.Connection;
importjava.sql.DriverManager;
publicclassApp {
publicstaticvoidmain(String[] args) {
StringconnectionUrl = "jdbc:sqlserver://<your_server.database.windows.net>:<your_database_port>;databaseName=master;encrypt=true;trustServerCertificate=true;user=<your_username>;password=<your_password>";
try {
// Load SQL Server JDBC driver and establish connection.System.out.print("Connecting to SQL Server ... ");
try (Connectionconnection = DriverManager.getConnection(connectionUrl)) {
System.out.println("Done.");
}
} catch (Exceptione) {
System.out.println();
e.printStackTrace();
}
}
}

  4. Save the file.

  5. Using the terminal, enter the javaexample\SqlServerSample directory. You can check in the terminal with the pwd command with the output of the command being similar to the following. The important point is to be in the SqlServerSample directory:

    PS C:\projects\javaexample\SqlServerSample> pwd
Path
----
C:\projects\javaexample\SqlServerSample

  6. While in the SqlServerSample directory, build the project and create a jar package using the following command:

    mvn package

  7. Again, in the terminal, run the application with the following command. You can remove the “-q” in the command below to show info messages from Maven.

    mvn -q exec:java "-Dexec.mainClass=com.sqlsamples.App"

    with the output of the command being:

    Connecting to SQL Server ... Done.

Step 3, Create a Java app that connects to SQL Server and executes queries using Visual Studio Code

  1. Open the App.java file in a Visual Studio Code editor page if not already opened. The next app will create a database and a table, and will insert, update, delete, and read a few rows.

  2. Replace the contents of App.java by copying and pasting the code below into the file. Don't forget to replace

    StringconnectionUrl = "jdbc:sqlserver://<your_server.database.windows.net>:<your_database_port>;databaseName=master;encrypt=true;trustServerCertificate=true;user=<your_username>;password=<your_password>";

    with the values of your database.

    packagecom.sqlsamples;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.DriverManager;
publicclassApp {
publicstaticvoidmain(String[] args) {
System.out.println("Connect to SQL Server and demo Create, Read, Update and Delete operations.");
//Update the username and password belowStringconnectionUrl = "jdbc:sqlserver://<your_server.database.windows.net>:<your_database_port>;databaseName=master;encrypt=true;trustServerCertificate=true;user=<your_username>;password=<your_password>";
try {
// Load SQL Server JDBC driver and establish connection.System.out.print("Connecting to SQL Server ... ");
try (Connectionconnection = DriverManager.getConnection(connectionUrl)) {
System.out.println("Done.");
// Create a sample databaseSystem.out.print("Dropping and creating database 'SampleDB' ... ");
Stringsql = "DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB]";
try (Statementstatement = connection.createStatement()) {
statement.executeUpdate(sql);
System.out.println("Done.");
}
// Create a Table and insert some sample dataSystem.out.print("Creating sample table with data, press ENTER to continue...");
System.in.read();
sql = newStringBuilder().append("USE SampleDB; ").append("CREATE TABLE Employees ( ")
.append(" Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ").append(" Name NVARCHAR(50), ")
.append(" Location NVARCHAR(50) ").append("); ")
.append("INSERT INTO Employees (Name, Location) VALUES ").append("(N'Jared', N'Australia'), ")
.append("(N'Nikita', N'India'), ").append("(N'Tom', N'Germany'); ").toString();
try (Statementstatement = connection.createStatement()) {
statement.executeUpdate(sql);
System.out.println("Done.");
}
// INSERT demoSystem.out.print("Inserting a new row into table, press ENTER to continue...");
System.in.read();
sql = newStringBuilder().append("INSERT Employees (Name, Location) ").append("VALUES (?, ?);")
.toString();
try (PreparedStatementstatement = connection.prepareStatement(sql)) {
statement.setString(1, "Jake");
statement.setString(2, "United States");
introwsAffected = statement.executeUpdate();
System.out.println(rowsAffected + " row(s) inserted");
}
// UPDATE demoStringuserToUpdate = "Nikita";
System.out.print("Updating 'Location' for user '" + userToUpdate + "', press ENTER to continue...");
System.in.read();
sql = "UPDATE Employees SET Location = N'United States' WHERE Name = ?";
try (PreparedStatementstatement = connection.prepareStatement(sql)) {
statement.setString(1, userToUpdate);
introwsAffected = statement.executeUpdate();
System.out.println(rowsAffected + " row(s) updated");
}
// DELETE demoStringuserToDelete = "Jared";
System.out.print("Deleting user '" + userToDelete + "', press ENTER to continue...");
System.in.read();
sql = "DELETE FROM Employees WHERE Name = ?;";
try (PreparedStatementstatement = connection.prepareStatement(sql)) {
statement.setString(1, userToDelete);
introwsAffected = statement.executeUpdate();
System.out.println(rowsAffected + " row(s) deleted");
}
// READ demoSystem.out.print("Reading data from table, press ENTER to continue...");
System.in.read();
sql = "SELECT Id, Name, Location FROM Employees;";
try (Statementstatement = connection.createStatement();
ResultSetresultSet = statement.executeQuery(sql)) {
while (resultSet.next()) {
System.out.println(
resultSet.getInt(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3));
}
}
connection.close();
System.out.println("All done.");
}
} catch (Exceptione) {
System.out.println();
e.printStackTrace();
}
}
}

  3. Save the file.

  4. Using the terminal, enter the javaexample\SqlServerSample directory if not already there from the previous example. You can check in the terminal with the pwd command with the output of the command being similar to the following. The important point is to be in the SqlServerSample directory:

    PS C:\projects\javaexample\SqlServerSample> pwd
Path
----
C:\projects\javaexample\SqlServerSample

  5. While in the SqlServerSample directory, build the project and create a jar package using the following command:

    mvn package

  6. Again, in the terminal, run the application with the following command. You can remove the “-q” in the command below to show info messages from Maven.

    mvn -q exec:java "-Dexec.mainClass=com.sqlsamples.App"

    with the output of the command being similar to the following. You will have to press enter/return to interact with the operations that the app is performing:

    Connect to SQL Server and demo Create, Read, Update and Delete operations.
Connecting to SQL Server ... Done.
Dropping and creating database 'SampleDB' ... Done.
Creating sample table with data, press ENTER to continue...
Done.
Inserting a new row into table, press ENTER to continue...1 row(s) inserted
Updating 'Location' for user 'Nikita', press ENTER to continue...
1 row(s) updated
Deleting user 'Jared', press ENTER to continue...1 row(s) deleted
Reading data from table, press ENTER to continue...
2 Nikita United States
3 Tom Germany
4 Jake United States
All done.

Congratulations! You created your first Java apps with SQL Server!