Connecting to SQL Server2000 database using Java (practical)

Author：Eve Cole Update Time：2009-11-30 17:15:02

There are many ways to connect to the SQL Server2000 database using Java. The following are the two most commonly used methods (connecting to the database through the JDBC driver).

1. Connect through Microsoft's JDBC driver. This JDBC driver has three files, namely mssqlserver.jar, msutil.jar and msbase.jar, which can be downloaded from Microsoft's website (://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502 -461A-B138-2AA54BFDC03A&displaylang=en), if you downloaded setup.exe, you also need to install it. After installation, the above three jar files will be generated. This JDBC driver implements JDBC 2.0.
Driver name: com.microsoft.jdbc.sqlserver.SQLServerDriver (i.e. classforname below)
Database connection URL: jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname (i.e. the following url)

2. Connect to the SQL Server database through the JTDS JDBC Driver. The file name of this driver is jtds-1.2.jar, and the 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 implementing JDBC3.0, are free.
Driver name: net.sourceforge.jtds.jdbc.Driver (i.e. classforname below)
Database connection URL: jdbc:jtds:sqlserver://localhost:1433/dbname (i.e. the following url)

There are a lot of Bean codes for JDBC to connect to SQL Server databases on the Internet. Here is an excerpt from part of them: (Please change localhost and 1433 to the SQL Server server address and port number in your actual application, and 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(){}

/**
* Obtain database connection through Microsoft JDBC driver
* @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)

catch (SQLException ex)

return conn;
}

}

Of course, before doing the above work, you must first check whether there is any problem with your SQL Server settings. The steps are as follows:

First open the "command line window", which is the MS-Dos window, and enter
telnet localhost 1433 (Of course, replace localhost with the server address where SQL Server is located, and change the port to the actual port of SQL Server. The default is 1433)

If it succeeds, it means that your SQL Server can be connected. If it fails (usually for Win2003 or WinXP SP2), please enter the control panel, open "Services" in "Administrative Tools", and start the "SQLSERVERAGENT" service ( Of course, you can also apply the SP3 patch package of SQL Server) and continue the above operation, which should be successful.

Secondly, check whether your username and password can log in to the SQL Server server. Of course, the most direct way is to open the "Query Analyzer" of SQL Server, enter the username and password, and click OK

If it succeeds, it means that your SQL Server login settings are OK. If it fails, please open the "Enterprise Manager" of SQL Server and go to the SQL Server server you registered (that is, the one under "SQL Server Group" on the left Dongdong) That is, right-click, select "Properties", select "Security" in the "SQL Server (Properties) Configuration" dialog box, set the authentication to "SQL Server and Windows(S)", and then use Query Analysis Test the server once. If you still can't connect, check whether your username and password are correct. Repeat the test until successful.

If you use connections in JSP, of course, in addition to directly using JDBC, everyone is most interested in the connection pool (Pool). Here we will focus on several uses of the connection pool.

For convenience, first set the JSP container to Tomcat, because everyone uses it more

1. Global configuration (any web application in Tomcat can use the configured connection pool): Configure the connection pool in server.xml. The server.xml file is located in the $TOMCAT_HOME$/conf/ directory. Open it and find </ GlobalNamingResources> and insert the following code before this line:

Note: name is the full global JNDI name of the connection pool, username is the database connection username and password, driverClassName is the database driver name, and url is the database connection string. Please modify it according to your actual configuration.
The configuration is not complete yet. Next, we need to set the name of global access in context.xml. The settings are as follows:

Find </Context> and insert the following code before this line:
<ResourceLink global="jdbc/poolName" name="jdbc/poolName" type="javax.sql.DataSource"/>

2. Partial configuration: Create a new xml file in the $TOMCAT_HOME$/conf/Catalina/localhost/ directory. The xml file must have the same name as the web application directory you publish. If it is webappname.xml, add the following content (configuration of Tomcat The external virtual directory is also handled here, ha!

For the first two methods, the code for calling the connection pool is as follows: (The Debug.log() method of com.yeno.util.Debug is mainly used to print debugging information and can be replaced by System.out.println())

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.*;
import com.yeno.util.Debug;
/**
* Database operation management class only implements database connection, shutdown and transaction processing
* @Aurhor Yeno.hhr
* Create Date 2005-12-9
*/
public class DataPool {

public DataPool(){}

/**
* Obtain database connection through Tomcat connection pool
* @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 files of the JDBC driver (mssqlserver.jar, msutil.jar and msbase.jar for Microsoft, jtds-1.2.jar for JTDS) have been correctly configured. You can copy the relevant JAR files to $TOMCAT_HOME $/common/lib/ directory, or you can copy it to $WEB_ROOT$/WEB-INF/lib/ directory

You can also use injection to call the connection pool, that is, call it in the Hibernate configuration file hibernate.cfg.xml. After configuring the connection pool in the JSP container, call the system connection pool settings in the Hibernate configuration file. Key code An excerpt follows:

<session-factory>

<property name="connection.datasource">java:comp/env/jdbc/poolName</property>
<property name="show_sql">false</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>

</session-factory>

For specific usage of Hibernate, please check the relevant details.