Table of Contents |
5.2 Opening a Database ConnectionBefore interacting with a database, an application must first establish a connection to the database server. The APIs covered in this book abstract the low-level connectivity details into a few simple object-oriented classes, making it easier to focus on the database application instead of protocols and network topology. 5.2.1 Opening an ADO.NET Database ConnectionOpening a connection with ADO.NET requires instantiating a connection object with a properly formatted connection string and then invoking the Open method on the connection object. The connection object can be an OdbcConnection, SqlConnection, or an OleDbConnection. The OdbcConnection is designed for any ODBC datasource, and the OleDbConnection type will work with any OLE DB Provider. For the highest performance data access, use connection objects specifically tuned to the specific database platform, such as SqlConnection for Microsoft SQL Server. Following is the syntax for creating a Connection object in ADO.NET: {Odbc|OleDb|Sql}Connection connection = new {Odbc|OleDb|Sql}Connection(connection_string); connection.Open( ); The format of the connection string is the same for all of the connection types. The format is a string of key/value pairs delimited by semicolons. For example: key1=value1; key2=value2; key3=value3; ... While the format is the same for every connection type, the keys and values are quite different. Tables Table 5-1 through Table 5-3 list the attributes for the three connection types listed above. Many database platforms support additional attributes that can also be set through the connection string. For a list of these attributes, please consult the appropriate database vendor documentation. Following are examples of two connection strings for an OdbcConnection: DSN=MyOracleDSN; UID=scott; PWD=tiger; DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=;DATABASE=pubs; The first string connects to a Data Source Name, or DSN, with the name MyOracleDSN using the username scott and the password tiger. The second string connects using the SQL Server driver to a database named pubs on the local server. The username is sa. The password is blank, indicating to the driver that the password is not required for the sa user. Following is an example of a connection string for an OleDbConnection, which connects to an Oracle9i data source using the MSDAORA OLE DB provider, the use name scott, and the password tiger. Provider=MSDAORA;Data Source=Oracle9;User ID=scott;Password=tiger; Finally, here is an example of a connection string for a SqlConnection, which connects to a SQL Server data source on the local server: Server=(local);UID=sa;PWD=;DATABASE=pubs;Connection Timeout=60; In Tables Table 5-1 through Table 5-3, you'll find synonyms for some keywords. For example, you can use "DSN" and "Data Source Name" interchangeably in your code.
5.2.2 Opening a JDBC Database ConnectionFollowing is the syntax for registering a driver with the JDBC Driver Manager and then opening a database connection: Class.forName(driver_name); Connection connection = DriverManager.getConnection(connect_string, username, password); The first step in establishing JDBC connectivity is to instruct the Java Virtual Machine (JVM) class loader to load the appropriate JDBC driver. The most common method of loading the driver into the class loader is to use the static forName method of the Class class. This method can provide applications with greater flexibility in changing database platforms by having the Java Virtual Machine (JVM) load the database driver at runtime: Class.forName( "driver_name" ); After the database driver has been loaded, the application can establish connectivity by invoking the static getConnection method on the JDBC DriverManager class. The getConnection method takes three arguments: a connection string, username, and password: Connection connection = DriverManager.getConnection(connect_string, username, password); The connection string follows the following JDBC URL naming scheme: jdbc:subprotocol:subname Following are examples of how to connect to the different vendors covered in this book using JDBC. 5.2.2.1 DB2Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); Connection connection = DriverManager.getConnection( "jdbc:db2:DATABASE", "user", "passwd" ); 5.2.2.2 MySQLClass.forName("org.gjt.mm.mysql.Driver"); Connection connection = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/DATABASE", "user", "passwd" ); 5.2.2.3 PostgreSQLClass.forName("org.postgresql.Driver"); Connection connection = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/DATABASE", "user", "passwd" ); 5.2.2.4 OracleClass.forName("oracle.jdbc.driver.OracleDriver"); Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@myserver", "scott","tiger" ); 5.2.2.5 SQL ServerClass.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" ); Connection connection = DriverManager.getConnection( "jdbc:microsoft:sqlserver://SERVER:1433;" + "DatabaseName=pubs;", "user", "passwd" ); |
Table of Contents |