Connecting to SQL Server 2000 with JDBC: A Practical Example

Connecting Through JDBC Driver for Microsoft SQL Server 2000

Exercise 2

import java.sql.*;

Class Home

public class Jdbc_sqls3 {

private Connection con = null;

Constants for PLS USE chain link

private final String url = "jdbc:microsoft:sqlserver://";

private final String server = "localhost";

private final String port = "1433";

private final String db = "pubsSQL";

private final String user = "sa";

private final String pass = "spark";

The password is easily found; it is recommended to request the user for the final application.

Make the driver use cursor on the server side, allowing more than one active SQL statement per connection.

private final String method = "cursor";

Main Method of Entry Program

public static void main(String[] args) throws Exception {

Jdbc_sqls2 pruebaJdbc = new Jdbc_sqls2();

pruebaJdbc.mostrarDatos();

}

Constructor

public Jdbc_sqls3() {

try {

Connection String

String Url = "" + url + server + ":" + port + ";databaseName=" + db + ";selectMethod=" + method + ";";

System.out.println("\nJDBC SQL Server");

System.out.println("=============== \n");

Register the JDBC driver using the Class.forName classloader.

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

con = DriverManager.getConnection(url, user, pass);

If no active connection:

if (con != null) System.out.println("Connection with " + db + " created successfully.");

} catch (Exception chicken) {

System.out.println("Error: Failed to create the connection with " + db + ". \n");

}

Displays Information About the Driver, SQL Server, and Catalog of BD

public void showdata() {

DatabaseMetaData dm = null;

The ResultSet interface represents a set of data resulting from a SQL query. For access to records, it uses a cursor that initially points before the first record. To move through the records, the resultSet.next() method is used.

ResultSet rs = null;

try {

If no active connection:

if (con != null) {

Get the Various Objects Information

dm = con.getMetaData();

System.out.println("\nInformation on Driver: \n");

System.out.println("\tDriver Name:" + dm.getDriverName());

System.out.println("\tDriver Version:" + dm.getDriverVersion());

System.out.println("\nInformation on SQL Server: \n");

System.out.println("\tDatabase Name:" + dm.getDatabaseProductName());

System.out.println("\tDatabase Version:" + dm.getDatabaseProductVersion());

System.out.println("Catalog database available: \n");

rs = dm.getCatalogs();

To show the whole catalog of BD:

int n = 1;

while (rs.next()) {

System.out.println("\t" + n + "-" + rs.getString(1));

n += 1;

}

Free resources:

rs.close();

rs = null;

dm = null;

System.out.println("");

Execute a SQL SELECT Statement

Statement select = con.createStatement();

ResultSet names = select.executeQuery("SELECT TOP 10 * FROM authors");

System.out.println("Show the first 10 rows from the table:");

System.out.println("");

int col = names.findColumn("au_lname");

boolean continue = names.next();

while (continue) {

System.out.println("\t" + names.getString(col));

continue = names.next();

}

System.out.println("");

Free resources:

names.close();

select.close();

cerrarCon();

}

If no active connection:

else System.out.println("Error: No active connection " + db + ". \n");

} catch (Exception chicken) {

System.out.println("Error: Can not display data. \n");

}

Close the Connection

private void cerrarCon() {

try {

if (con != null)

con.close();

con = null;

} catch (Exception chicken) {

System.out.println("\nError to close the connection with " + db + ". \n");

}

}

}