Basic data types sample
This Microsoft JDBC Driver for SQL Server sample application demonstrates how to use result set getter methods to retrieve basic SQL Server data type values, and how to use result set update methods to update those values.
The code file for this sample is named BasicDataTypes.java, and it can be found in the following location:
\<installation directory>\sqljdbc_<version>\<language>\samples\datatypes
Requirements
To run this sample application, you must set the classpath to include the mssql-jdbc jar file. You'll also need access to the AdventureWorks2022 sample database. For more information about how to set the classpath, see Using the JDBC Driver.
The sample will create the required table and insert sample data in the AdventureWorks2022 sample database:
Note
The Microsoft JDBC Driver for SQL Server provides mssql-jdbc class library files to be used depending on your preferred Java Runtime Environment (JRE) settings. For more information about which JAR file to choose, see System Requirements for the JDBC Driver.
Example
In the following example, the sample code makes a connection to the AdventureWorks2022 database, and then retrieves a single row of data from the DataTypesTable test table. The custom displayRow method is then called to display all the data in the result set using various get<Type> methods of the SQLServerResultSet class.
Next, the sample uses various update<Type> methods of the SQLServerResultSet class to update the data in the result set, and then calls the updateRow method to persist that data back to the database.
Finally, the sample refreshes the data in the result set and then calls the custom displayRow method again to display the data in the result set.
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import com.microsoft.sqlserver.jdbc.SQLServerResultSet;
import microsoft.sql.DateTimeOffset;
public class DatatypesTest {
private static final String tableName = "DataTypesTable";
public static void main(String[] args) {
// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<database>;user=<user>;password=<password>";
try (Connection con = DriverManager.getConnection(connectionUrl);
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);) {
dropAndCreateTable(stmt);
insertOriginalData(con);
String SQL = "SELECT * FROM " + tableName;
ResultSet rs = stmt.executeQuery(SQL);
rs.next();
displayRow("ORIGINAL DATA", rs);
// Update the data in the result set.
rs.updateString(2, "B");
rs.updateString(3, "Some updated text.");
rs.updateBoolean(4, true);
rs.updateDouble(5, 77.89);
rs.updateDouble(6, 1000.01);
long timeInMillis = System.currentTimeMillis();
Timestamp ts = new Timestamp(timeInMillis);
rs.updateTimestamp(7, ts);
rs.updateDate(8, new Date(timeInMillis));
rs.updateTime(9, new Time(timeInMillis));
rs.updateTimestamp(10, ts);
rs.updateTimestamp(11, ts);
rs.updateObject(12, 987654321L, microsoft.sql.Types.SQL_VARIANT);
// -480 indicates GMT - 8:00 hrs
((SQLServerResultSet) rs).updateDateTimeOffset(11, DateTimeOffset.valueOf(ts, -480));
rs.updateRow();
// Get the updated data from the database and display it.
rs = stmt.executeQuery(SQL);
rs.next();
displayRow("UPDATED DATA", 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);
System.out.println(rs.getInt(1) + " , " + // SQL integer type
rs.getString(2) + " , " + // SQL char type
rs.getString(3) + " , " + // SQL varchar type
rs.getBoolean(4) + " , " + // SQL bit type
rs.getDouble(5) + " , " + // SQL decimal type
rs.getDouble(6) + " , " + // SQL money type
rs.getTimestamp(7) + " , " + // SQL datetime type
rs.getDate(8) + " , " + // SQL date type
rs.getTime(9) + " , " + // SQL time type
rs.getTimestamp(10) + " , " + // SQL datetime2 type
((SQLServerResultSet) rs).getDateTimeOffset(11) + " , " + // SQL datetimeoffset type
rs.getObject(12)); // SQL sqlvariant type
System.out.println();
}
private static void dropAndCreateTable(Statement stmt) throws SQLException {
stmt.executeUpdate("if object_id('" + tableName + "','U') is not null" + " drop table " + tableName);
String sql = "create table " + tableName + " (" + "c1 int, " + "c2 char(20), " + "c3 varchar(20), " + "c4 bit, "
+ "c5 decimal(10,5), " + "c6 money, " + "c7 datetime, " + "c8 date, " + "c9 time(7), "
+ "c10 datetime2(7), " + "c11 datetimeoffset(7), " + "c12 sql_variant" + ");";
stmt.execute(sql);
}
private static void insertOriginalData(Connection con) throws SQLException {
String sql = "insert into " + tableName + " values( " + "?,?,?,?,?,?,?,?,?,?,?,?" + ")";
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setObject(1, 100);
pstmt.setObject(2, "original text");
pstmt.setObject(3, "original text");
pstmt.setObject(4, false);
pstmt.setObject(5, 12.34);
pstmt.setObject(6, 56.78);
pstmt.setObject(7, new java.util.Date(1453500034839L));
pstmt.setObject(8, new java.util.Date(1453500034839L));
pstmt.setObject(9, new java.util.Date(1453500034839L));
pstmt.setObject(10, new java.util.Date(1453500034839L));
pstmt.setObject(11, new java.util.Date(1453500034839L));
pstmt.setObject(12, 123456789L, microsoft.sql.Types.SQL_VARIANT);
pstmt.execute();
}
}
}