Connecting to a Server
Getting a basic Connection object from the database is the first operation to get a handle on. The code snippet below gets a connection to a SQL Server database. Note that the Class.forName line is unnecessary if you are using a JDBC 4.0 driver with Java SE 6 or above.
String url = “jdbc:datadirect:sqlserver://nc-cqserver:1433;databaseName=testDB;user=test;password=test”;
try {
Class.forName(“com.ddtek.jdbc.sqlserver.SQLServerDriver”);
Connection con = DriverManager.getConnection(url);
}
catch (Exception except) {
SQLException ex = new SQLException(
“Error Establishing Connection: “ +
except.getMessage());
throw ex;
}
It is good to get metaData from the Connection object to see what driver and server version you are using. This comes in handy when its time to debug. Printing to system out or logging to a file is preferable:
DatabaseMetaData dbmd = con.getMetaData();
System.out.println( “\nConnected with “ +
dbmd.getDriverName() + “ “ + dbmd.getDriverVersion()
+ “{ “ + dbmd.getDriverMajorVersion() + “,” +
dbmd.getDriverMinorVersion() +” }” + “ to “ +
dbmd.getDatabaseProductName() + “ “ +
dbmd.getDatabaseProductVersion() + “\n”);
Retrieving Data
A straightforward approach to retrieving data from a database is to simply select the data using a Statement object and iterate through the ResultSet object:
Statement stmt = con.createStatement();
ResultSet results = stmt.executeQuery(“Select * from foo”);
String product;
int days = 0;
while (results.next()){
product = results.getString(1);
days = results.getInt(2);
System.out.println(product + “\t” + days);
}
The JDBC specification allows for fetching all data types using getString or getObject; however, it is a best practice to use the correct getXXX method as demonstrated in the code sample above to avoid unnecessary data conversions.
Executing a PreparedStatement
Use a PreparedStatement any time you have optional parameters to specify to the SQL Statement, or values that do not convert easily to strings, for example BLOBs. It also helps prevent SQL injection attacks when working with string values.
PreparedStatement pstmt = con.prepareStatement(“INSERT into table2 (ID, lastName, firstName) VALUES (?,?,?)”);
pstmt.setInt(1, 87);
pstmt.setString(2, “Picard”);
pstmt.setString(3, “Jean-Luc”);
rowsInserted += pstmt.executeUpdate();
Calling a Stored Procedure via CallableStatement
Use a CallableStatement any time you wish to execute a stored procedure on the server:
CallableStatement cstmt = con.prepareCall(“{CALL STPROC1 (?)}”);
cstmt.setString(1, “foo”);
ResultSet rs = cstmt.executeQuery();
rs.next();
int value = rs.getInt(1);
CallableStatements can return resultSets, even when inserting data on the server. If the application doesn’t know if results should be returned, check for results by issuing a call to getMoreResults() after execution.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}