| title | Basic Data Types Sample | Microsoft Docs |
|---|---|
| ms.custom | |
| ms.date | 07/31/2018 |
| ms.prod | sql |
| ms.prod_service | connectivity |
| ms.reviewer | |
| ms.technology | connectivity |
| ms.topic | conceptual |
| ms.assetid | 59ac80cf-fc66-4493-933d-38e479c5f54d |
| author | MightyPen |
| ms.author | genemi |
| manager | craigg |
[!INCLUDEDriver_JDBC_Download]
This [!INCLUDEjdbcNoVersion] sample application demonstrates how to use result set getter methods to retrieve basic [!INCLUDEssNoVersion] data type values, and how to use result set update methods to update those values.
The code file for this sample is named BasicDT.java, and it can be found in the following location:
\<installation directory>\sqljdbc_<version>\<language>\samples\datatypesTo run this sample application, you must set the classpath to include the mssql-jdbc jar file. You'll also need access to the [!INCLUDEssSampleDBnormal] 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 [!INCLUDEssSampleDBnormal] sample database:
Note
The [!INCLUDEjdbcNoVersion] 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.
In the following example, the sample code makes a connection to the [!INCLUDEssSampleDBnormal] 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 BasicDataTypes {
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>;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);
// -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.
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), " + ");";
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.execute();
}
}
}