Java and SQL Server - Inserting Data
In order to insert data into an SQL Server database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.
Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-catch’ block to catch any errors that may arise.
import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class DemoSQLServer { public static void main(String[] args) { /// Connection variable. Connection connect = null; try { // Connect to database. connect = DriverManager.getConnection( "jdbc:sqlserver://;serverName=MSSQLSERVERDEMO;databaseName=Demo", "DemoUN", "DemoPW"); } catch (SQLException e) { /// Message confirming unsuccessful database connection. System.out.println("Database connection unsuccessful."); /// Stop program execution. System.exit(1); } /// Query parameters. String firstname = "Fiona"; String lastname = "Jones"; String title = "Miss"; Date dob = Date.valueOf("1985-05-19"); /// SQL to insert data into the person table. String sqlInsert = "INSERT INTO person " + " (firstname, lastname, title, dob) " + "VALUES (?, ?, ?, ?) "; try { /// Create statement and bind the parameters. PreparedStatement statement = connect.prepareStatement(sqlInsert); statement.setString(1, firstname); statement.setString(2, lastname); statement.setString(3, title); statement.setDate(4, dob); /// Execute query. statement.executeUpdate(); /// Confirm successful addition of person information. System.out.println("Person information added successfully."); /// Close the statement and database connection. statement.close(); connect.close(); } catch (SQLException e) { /// Confirm error adding person information and quit. System.out.println("Error adding person information."); System.exit(1); } } }
The contents of the ‘person’ table now looks as follows.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
2 | George | Jones | Mr | 1997-12-15 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Jones | Miss | 1985-05-19 |