Sqlserver Linked database

When I access Java or JSP, I will inevitably use the database SQL Server 2000/2005 (I used the 2005 Standard Edition [9.0.3054] test). After my own search and research, I successfully connected the SQL Server using JDBC, the following is an example of how to use Eclipse 3.2.

Preparations

First, Install SQL Server 2000/2005 in the operating system. If versions 2000 and 2005 are installed in the system, stop one and open only one line.

Then, download Microsoft SQL Server 2005 JDBC Driver 1.1 from the Microsoft website. You can also use this address to download it directly.

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

Set Classpath

The JDBC driver is not included in the Java SDK. Therefore, to use this driver, you must set classpath to include the sqljdbc. jar file. If the classpath lacks the sqljdbc. jar item, the application will cause a common exception of "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 example of a CLASSPATH statement for a Windows application:

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

The following is a CLASSPATH statement instance for Unix/Linux applications:

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, the classpath may be faulty. If you suspect that this type of problem exists, temporarily move the sqljdbc. jar file to a directory with a simple name, such as C: \ Temp, change classpath, and then test whether the problem has been solved.

Applications that run directly at the command prompt

Configure classpath in the operating system. append sqljdbc. jar to the classpath of the system. Alternatively, you can use the java-classpath option to specify classpath.

Set the SQL Server

I use SQL Server 2005 Standard Edition SP2, which can be set by default. If you need to configure the port, see the following.

1. "Start" → "program" → "Microsoft SQL Server 2005" → "configuration tool" → "SQL Server Configuration Manager" → "SQL Server 2005 network configuration" → "MSSQLSERVER's protocol"

2. If "TCP/IP" is not enabled, right-click and choose "start ".

3. Double-click "TCP/IP" to enter the property settings. In "IP address", you can configure "TCP port" in "IPAll". The default value is 1433.

4. Restart SQL Server or the computer.

Create a database

Open "SQL Server Management Studio", log on to the SQL Server, create a database, and name it test.

Test in Eclipse

1. Open Eclipse, "file" → "new" → "project" → "Java Project", and the project name is Test.

2. In Eclipse, select "window" → "Preferences... "→" Java "→" installed JRE ", select installed JRE, click" edit "→" add external ", and select % ProgramFiles % \ sqljdbc_1.1 \ chs \ sqljdbc. jar

3. You can see sqljdbc. jar in the "JRE system library" of the Test project. If not, 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"; // load the JDBC driver

String dbURL = "jdbc: sqlserver: // localhost: 1433; DatabaseName = test"; // connect to the 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 is Successful, the console outputs Connection Successful!

} Catch (Exception e ){

E. printStackTrace ();

}

}

}

Note:

1. Because the SQL Express server is disabled by default and the port number is not configured, You need to reset it.

2. If you have used Java to connect to SQL Server 2000, you should note the following:

The statement for loading the driver and URL path in SQL Server 2000 is:

String driverName = "com. microsoft. jdbc. sqlserver. SQLServerDriver ";

String dbURL = "jdbc: microsoft: sqlserver: // localhost: 1433; DatabaseName = sample ";

The statement for loading the driver and URL in SQL Server 2005 is

String driverName = "com. microsoft. sqlserver. jdbc. SQLServerDriver ";

String dbURL = "jdbc: sqlserver: // localhost: 1433; DatabaseName = sample ";

If the writing method is incorrect, the driver cannot be found.