Connect to SQL Server using JDBC

Source: http://bbs.bc-cn.net/dispbbs.asp?boardid=12&id=140292 Finishing: Autumn recall

Contact with Java or JSP, will inevitably use to database SQL Server 2000/2005 (I use the 2005 Standard Edition [9.0.3054] test), through my own search and research, using JDBC to connect SQL Server successfully, This is the method of finishing (using Eclipse 3.2) as follows.

Preparatory work

First, the operating system is installed in SQL Server 2000/2005, if the system is equipped with version 2000 and 2005, remember to deactivate one, only one line is opened.

Then, download Microsoft SQL Server 2005 JDBC Driver 1.1 to the Microsoft Web site, or you can download it directly using this address.

Unzip the Sqljdbc_1.1.1501.101_chs.exe and copy the sqljdbc_1.1 to%programfiles% (if the system is C:\Program Files on the C drive).

Set Classpath

The JDBC driver is not included in the Java SDK. Therefore, if you want to use the driver, you must set Classpath to include the Sqljdbc.jar file. If Classpath is missing the Sqljdbc.jar entry, the application throws a common exception for the "Class not found".

The installation location of the Sqljdbc.jar file is as follows:

< installation directory >\sqljdbc_< version >\< language >\sqljdbc.jar

The following is an instance of the CLASSPATH statement for a Windows application:

CLASSPATH =.;%ProgramFiles%\sqljdbc_1.1\chs\sqljdbc.jar

The following is an instance of the CLASSPATH statement for the Unix/linux application:

CLASSPATH =.:/home/usr1/mssqlserver2005jdbc/Driver/sqljdbc_1.1/chs/sqljdbc.jar

Note:In the Window system, if the directory name is longer than 8.3 or the folder name contains spaces, it causes problems with classpath. If you suspect such a problem, you should temporarily move the Sqljdbc.jar file to a directory that has a simple name, such as C:\Temp, change classpath, and then test to see if this resolves the issue.applications that run directly at the command prompt

Configure the Classpath in the operating system. Append the Sqljdbc.jar to the classpath of the system. Alternatively, java -classpath  you can specify classpath on the Java command line that runs the application by using the option.

setting up a SQL Server server

I am using SQL Server 2005 Standard Edition SP2, which are all by default and are not normally configured. If you need to configure the port, see below.

1, start → programs → Microsoft SQL Server 2005 → configuration tools → SQL Server Configuration Manager → SQL Server 2005 network configuration → "MSSQLSERVER protocol"
2 , if "TCP/IP" is not enabled, right-click to select "Start".
3, double-click on "TCP/IP" to enter the property settings, in "IP address", you can configure "IPAll" in the "TCP port", the default is 1433.
4, restart SQL Server, or restart the computer.


CREATE database

Open SQL Server Management Studio, log on to the SQL Server server, create a new database, Named Test


test in Eclipse

1, open eclipse, file → new → project → Java project, The project is named Test
2, in Eclipse, select window → preferences ... → Java → installed JRE, select the installed JRE, click "edit" → "add external", select%programfiles% \ Sqljdbc_1.1\chs\sqljdbc.jar
3, you can see the Sqljdbc.jar in the JRE system Library of the test project, if you do not have the ability to right-click the project test→ "Build Path" → "Configure build path ..." → "Java build Path" → "Library" → "add external JAR ...", select%programfiles% \sqljdbc_1.1\chs\sqljdbc.jar
4, Write the Java code as follows:

Import java.sql.*;

public class Test {
public static void Main (string[] SRG) {
String drivername = "Com.microsoft.sqlserver.jdbc.SQLServerDriver"; Loading the JDBC Driver
String Dburl = "jdbc:sqlserver://localhost:1433;  Databasename=test "; Connect Server and Database test
String userName = "sa"; Default User Name
String userpwd = "123456"; Password
Connection Dbconn;

try {
Class.forName (drivername);
Dbconn = Drivermanager.getconnection (Dburl, UserName, userpwd);
System.out.println ("Connection successful!"); If the connection succeeds console output connection successful!
} catch (Exception e) {
E.printstacktrace ();
}
}
}


Note:

1, because SQL Express this version of the server is disabled by default and the port number is not configured, so to be reset

2. If you previously connected SQL Server 2000 with Java, you should pay attention to:

The statements that load the driver and URL paths in SQL Server 2000 are

String drivername = "Com.microsoft. jdbc. SQL Server. SQLServerDriver ";
String dburl = "jdbc:Microsoft:SQL Server://localhost:1433; Databasename=sample ";

The statements that load drivers and URLs in SQL Server 2005 are

String drivername = "Com.microsoft. SQL Server. jdbc. SQLServerDriver ";
String Dburl = "jdbc:sqlserver://localhost:1433; Databasename=sample ";

If you make a mistake, you will not find the driver.

Qiu Yi: For Microsoft SQL Server 2005 JDBC Driver 1.1, as far as I can see the official documentation does not mention the difference between 2000 and 2005, I believe it is written in accordance with the 2005 statement, I did not test 2000, if friends you test to not the same please tell me, thank you!


Other references:
Http://dev.csdn.net/author/yeno/3f06bf19b1c147198e00afa0af6bd0dc.html
Http://www.baidu.com/s?wd=JDBC+for+SQL+Server+2005&cl=3

Connect to SQL Server using JDBC