I thought I’d provide a recipe for how to connect to SQL Server from Java. The following is a quick, relatively clean way to connect to Microsoft’s SQL Server from Java without going through too much voodoo–as is often the case when trying to connect to MS SQL Server through a non-microsoft language. Luckily, Microsoft provides a pure Java JDBC driver for connecting to it’s server! You can find it here. Don’t forget to properly include in your Java project! Also, even though it’s the MS SQL 2k3 driver, it works perfectly fine with MS SQL 2000 (so you might as well just use this driver).
/*
* Main.java
*
* Created on November 27, 2008, 3:56 PM
*
*/
package sqlserversample;
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
/**
*
* @author Timothy M. Rodriguez
*/
public class Main {
/* jdbc driver name and database url
* ms sql uses port 1433
* replace "mysqlserver" with the ip and host of your database
* if connecting from a *nix box, don't forget to use the FQDN and not just the NetBIOS host
* if not, the ip is a safe bet
*/
private static final String DATABASE_URL = "jdbc:sqlserver://mysqlserver:1433";
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.sqlserverdriver";
//used to connect to the database
private static Connection connection = null;
//used to execute queries on the database
private static Statement statement = null;
//this object is used to store your result sets
private static ResultSet resultSet = null;
//this object is used to stored data about the table
private static ResultSetMetaData metaData = null;
/** Creates a new instance of Main */
public Main() {
//default constructor
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
try {
// load the driver class
//this should work, but doesn't, not sure why, let me know in the comments if you do
// Class.forName(DRIVER);
// instead i created it this way
SQLServerDriver sqlServerDriver = new SQLServerDriver();
/*
* I manually created a SQLServerDriver instance, this is due to
* complications with the classpath establish connection to db
*/
DriverManager.registerDriver(sqlServerDriver);
connection = DriverManager.getConnection(DATABASE_URL,
"user", "password");
// create the statement for querying
statement = connection.createStatement();
//put your query string in the executeQuery method argument
resultSet = statement.executeQuery("SELECT TOP 10 * FROM Table");
metaData = resultSet.getMetaData();
//I didn't use the metadata object, but showed you how to create it
//the metaData object can be used to get column names and indices, column types, etc.
while(resultSet.next()) {
//the get object method is useful if you don't want to bother with the column type
System.out.println(resultSet.getObject(3).toString());
}
} catch (SQLException sqlException) {
//couldn't connect to the db or execute a query
sqlException.printStackTrace();
/* in a real webapp, you don't want to be printing stack traces
* instead print a nice error page that doesn't include internal information
* For example, "oops, it blew up"
*/
}
}
}
That’s it, I hope that helps!
Tags: connect, database, java, microsoft, ms sql server, recipe, sql, sql server