Connect to the sqlserver2000 database using jtds

Use Java to connect to the SQL Server2000 Database ============================================== ================================== Author: gavindai (http://gavindai.itpub.net)
Posted on: 2007.05.08
Category: Database
Source: http://gavindai.itpub.net/post/16343/286373
---------------------------------------------------------------

There are multiple methods to connect to the SQL Server2000 Database Using java. The following describes the two most commonly used methods (connecting to the database using a JDBC driver ). And connection pool.

There are multiple methods to connect to the SQL Server2000 Database Using java. The following describes the two most commonly used methods (connecting to the database using a JDBC driver ).

1. connect through Microsoft's JDBC driver. The JDBC driver has three files, MSSQLServer. jar, msutil. jar and msbase. jar, which can be downloaded from Microsoft website (http://www.microsoft.com/downloads/details.aspx? You also need to install it. After installation, the above three jar files will be generated. The JDBC driver implements the JDBC 2.0.

Driver name: COM. Microsoft. JDBC. sqlserver. sqlserverdriver (that is, the following classforname)

Database Connection URL: JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = dbname (that is, the following URL)

2. Connect to the SQL Server database through jtds JDBC driver, the driver file name is jtds-1.2.jar, download path is (http://sourceforge.net/project/showfiles.php? Group_id = 33291). This driver supports Microsoft SQL Server (6.5, 7.0, 2000 and 2005) and Sybase, and implements jdbc3.0, which is free of charge.

Driver name: net. SourceForge. jtds. JDBC. Driver (that is, the following classforname)

Database Connection URL: JDBC: jtds: sqlserver: // localhost: 1433/dbname (that is, the following URL)

There are a lot of bean code on the Internet for connecting JDBC to the SQL Server database. The following is an excerpt: (Change localhost and 1433 to the address and port number of the SQL Server server in your actual application, change dbname to your actual database name)

Import java. SQL .*;

Public class databaseconn {

Private connection conn;

Private statement stmt;

Private string url = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = dbname ";

Private string classforname = "com. Microsoft. JDBC. sqlserver. sqlserverdriver ";

Private string uid = "sa ";

Private string Pwd = "password ";

 

Public databaseconn (){}

 

/**

* <P> obtain the database connection through the Microsoft JDBC driver </P>

* @ Return connection

* @ Exception classnotfoundexception, sqlexception

*/

Public connection getconnection ()

{

Try

{

Class. forname (classforname );

If (conn = NULL | conn. isclosed ())

Conn = drivermanager. getconnection (URL, uid, PWD );

}

Catch (classnotfoundexception ex)

{

Ex. printstacktrace ();

}

Catch (sqlexception ex)

{

Ex. printstacktrace ();

}

Return conn;

}

}

Before you do the preceding operations, check whether your SQL server settings are correct:

Open the "command line window", that is, the MS-DOS window, and enter

Telnet localhost 1433 (of course, replace localhost with the address of the server where SQL server is located, and change the port to the actual port of SQL Server. The default value is 1433)

If the connection succeeds, it indicates that your SQL Server can be connected. If the connection fails (generally for win2003 or WINXP SP2), go to the control panel, open the "service" in "Administrative Tools", start the "SQLServerAgent" Service (of course, you can also add the SQL Server SP3 patch package), and continue the above operation, it should be successful.

Secondly, check whether your user name and password can be used to log on to the SQL Server server. Of course, the most direct way is to open the SQL Server "query analyzer", enter the user name and password, and click OK.

If it succeeds, it indicates that your SQL Server login settings are correct. If it fails, open "Enterprise Manager" of SQL Server ", on the SQL Server server you registered (that is, the stuff under the "SQL Server group" on the left), right click, select "attribute", and go to "SQL Server (attribute) in the "Configure" dialog box, select "security", set the authentication to "SQL Server and windows (s)", and test it with the query analyzer. If the connection fails, check whether your username and password are correct. Repeat the test until the test is successful.

If the connection is applied in JSP, of course, in addition to using JDBC directly, everyone is most keen on the connection pool. The following describes several usage of the connection pool.

For convenience, set the JSP Container to Tomcat first, because many

1. global configuration (any web application in Tomcat can use the configured connection pool. configure the connection pool, server. the XML file is located in the $ tomcat_home $/CONF/directory, open it, find </globalnamingresources>, and insert the following code before this line:

<Resource

Name = "JDBC/poolname"

Auth = "Container"

Type = "javax. SQL. datasource"

Maxactive = "100"

Maxidle = "30"

Maxwait = "10000"

Username = "sa"

Password = "password"

Driverclassname = "com. Microsoft. JDBC. sqlserver. sqlserverdriver"

Url = "JDBC: Microsoft: sqlserver: // 127.0.0.1: 1433; databasename = dbname"/>

Note: name indicates the full name of the global JNDI of the Connection Pool, username indicates the connection username and password of the database, driverclassname indicates the name of the database driver, and URL indicates the connection string of the database. Please modify according to your actual configuration.

The configuration is not completed yet. In the context. xml file, set the global access name as follows:

Find </context> and insert the following code before this line:

<Resourcelink global = "JDBC/poolname" type = "javax. SQL. datasource"/>

2. local configuration: Create an XML file in the $ tomcat_home $/CONF/Catalina/localhost/directory. The XML file must be the same as the Web application directory name you published. For example, webappname. XML, add the following content (the external virtual directory for Tomcat configuration is also done here, haha !)

<Context Path = "/webappname" docbase = "D:/webappname" DEBUG = "0" reloadable = "true" crosscontext = "true">

<Resource

Name = "JDBC/poolname"

Auth = "Container"

Type = "javax. SQL. datasource"

Maxactive = "100"

Maxidle = "30"

Maxwait = "10000"

Username = "sa"

Password = "password"

Driverclassname = "com. Microsoft. JDBC. sqlserver. sqlserverdriver"

Url = "JDBC: Microsoft: sqlserver: // 127.0.0.1: 1433; databasename = dbname"/>

<Resourcelink global = "JDBC/poolname" type = "javax. SQL. datasource"/>

</Context>

For the preceding two methods, the code for calling the connection pool is as follows: (COM. yeno. util. debug. the log () method is mainly used to print debugging information. You can use system. out. println () instead)

Import java. SQL .*;

Import javax. SQL. datasource;

Import javax. Naming .*;

Import com. yeno. util. debug;

/**

* <P> database operation management class: Only database connections, closures, and transactions are implemented. </P>

* @ Aurhor yeno. hhr

* Create date 2005-12-9

*/

Public class datapool {

Public datapool (){}

 

/**

* <P> connect to the database through the Tomcat connection pool </P>

* @ Param No

* @ Return connection database connection

* @ Exception namingexception, sqlexception, exception

*/

Public connection getconnect ()

{

Connection conn = NULL;

Try

{

Context intitctx = new initialcontext ();

Context envctx = (context) intitctx. Lookup ("Java: COMP/ENV ");

Datasource DS = (datasource) envctx. Lookup ("JDBC/poolname ");

Conn = Ds. getconnection ();

}

Catch (namingexception NEX)

{

Debug. Log (this, "getconnect ()", "no correct environment! ");

}

Catch (sqlexception sqlex)

{

Debug. Log (this, "getconnect ()", "can't get connection! ");

}

Return conn;

}

}

Before using the above Code, you must ensure that the relevant JAR file (Microsoft is MSSQLServer. jar, msutil. jar and msbase. jar, jtds is jtds-1.2.jar) is configured correctly, you can copy the relevant JAR file to the $ atat_home $/common/lib/directory, you can also copy it to the $ web_root $/WEB-INF/lib/directory.

You can also use the injection method to call the connection pool, that is, in the hibernate configuration file hibernate. cfg. call in XML. After the connection pool is configured in the JSP Container, call the system connection pool settings in the hibernate configuration file. The key code is extracted as follows:

<Session-factory>

<! --

<Property> </property>

<Property> </property>

-->

<Property> JAVA: COMP/ENV/jdbc/poolname </property>

<Property> false </property>

<Property> org. hibernate. dialect. sqlserverdialect </property>

</Session-factory>

For more information about how to use hibernate, see.