xcCx1NNn

1import java.sql.Connection;
2import java.sql.Statement;
3import java.sql.PreparedStatement;
4import java.sql.ResultSet;
5import java.sql.DriverManager;
6
7public class CRUD {
8
9    public static void main(String[] args) {
10
11        System.out.println("Connect to SQL Server and demo Create, Read, Update and Delete operations.");
12        String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=master;user=sa;password=your_password";
13
14        try {
15            // Load SQL Server JDBC driver and establish connection.
16            System.out.print("Connecting to SQL Server ... ");
17            try (Connection connection = DriverManager.getConnection(connectionUrl)) {
18                System.out.println("done.");
19
20                // Create a sample database
21                System.out.print("Dropping and creating database 'SampleDB' ... ");
22                //   //SS: Blank comment. Remove it 
23                String sql = "DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB]";
24                try (Statement statement = connection.createStatement()) {
25                    statement.executeUpdate(sql);
26                    System.out.println("done.");
27                }
28
29                // Create a Table and insert some sample data
30                System.out.print("Creating sample table with data, press ENTER to continue...");
31                System.in.read();
32                sql = new StringBuilder()
33                        .append("USE SampleDB; ")
34                        .append("CREATE TABLE Employees ( ")
35                        .append(" Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ")
36                        .append(" Name NVARCHAR(50), ")
37                        .append(" Location NVARCHAR(50) ")
38                        .append("); ")
39                        .append("INSERT INTO Employees (Name, Location) VALUES ")
40                        .append("(N'Jared', N'Australia'), ")
41                        .append("(N'Nikita', N'India'), ")
42                        .append("(N'Tom', N'Germany'); ")
43                        .toString();
44                try (Statement statement = connection.createStatement()) {
45                    statement.executeUpdate(sql);
46                    System.out.println("done.");
47                }
48
49                // INSERT demo
50                System.out.print("Inserting a new row into table, press ENTER to continue...");
51                System.in.read();
52                sql = new StringBuilder()
53                        .append("INSERT Employees (Name, Location) ")
54                        .append("VALUES (?, ?);")
55                        .toString();
56                try (PreparedStatement statement = connection.prepareStatement(sql)) {
57                    statement.setString(1, "Jake");
58                    statement.setString(2, "United States");
59                    int rowsAffected = statement.executeUpdate();
60                    System.out.println(rowsAffected + " row(s) inserted");
61                }
62
63                // UPDATE demo
64                String userToUpdate = "Nikita";
65                System.out.print("Updating 'Location' for user '" + userToUpdate + "', press ENTER to continue...");
66                System.in.read();
67                sql = "UPDATE Employees SET Location = N'United States' WHERE Name = ?";
68                try (PreparedStatement statement = connection.prepareStatement(sql)) {
69                    statement.setString(1, userToUpdate);
70                    int rowsAffected = statement.executeUpdate();
71                    System.out.println( rowsAffected + " row(s) updated");
72                }
73
74                // DELETE demo
75                String userToDelete = "Jared";
76                System.out.print("Deleting user '" + userToDelete + "', press ENTER to continue...");
77                System.in.read();
78                sql = "DELETE FROM Employees WHERE Name = ?;";
79                try (PreparedStatement statement = connection.prepareStatement(sql)) {
80                    statement.setString(1, userToDelete);
81                    int rowsAffected = statement.executeUpdate();
82                    System.out.println( rowsAffected + " row(s) deleted");
83                }
84
85                // READ demo
86                System.out.print("Reading data from table, press ENTER to continue...");
87                System.in.read();
88                sql = "SELECT Id, Name, Location FROM Employees;";
89                try (Statement statement = connection.createStatement();
90                     ResultSet resultSet = statement.executeQuery(sql)) {
91                    while (resultSet.next()) {
92                        System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3));
93                    }
94                }
95
96                System.out.println("All done.");
97            }
98        } catch (Exception e) {  // Do you want to restrict your exception to SqlException instead of catching all Exceptions? 
99            System.out.println(""); // SS: If you want to print a new line you dont have to use "" you can simply do System.out.println();
100            e.printStackTrace();
101        }
102    }
103}