Connecting SQL Server using JDBC
Setting up SQL Server Server
I am using SQL Server 2005 standard version SP2, all of which are just defaulted and generally do not require configuration. If you need to configure the port, please 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 and select "Start".
3. Double-click "TCP/IP" to enter the property settings. In "IP Address", you can configure the "TCP Port" in "IPAll", which is default to 1433.
4. Restart SQL Server or restart the computer.
Create a database
Open "SQL Server Management Studio", log in and connect to the SQL Server server, create a new database, and name it test
Testing in Eclipse
1. Open Eclipse, "File" → "New" → "Project" → "Java Project", the project name is Test
2. In Eclipse, select "Window" → "Preferences..." → "Java" → "Installed JRE", select the 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 there is no, you can 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 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 the server and database test String userName = "sa"; //Default username String userPwd = "123456"; //Password Connection dbConn; try { Cl ass.forName(driverName ); dbConn = DriverManager.getConnection(dbURL, userName, userPwd); System.out.println("Connection Successful!"); //If the connection is successful, the console outputs Connection Succes sful! } catch (Exception e) { e.printStackTrace( ); }}}}
Note:
1. Because the server of SQL Express version is disabled by default and the port number is not configured, it needs to be reset.
2. If you used to connect to SQL Server 2000 in Java, you should pay attention:
The statement that loads 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 statements that load drivers and URLs in SQL Server 2005 are
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=sample";
If the write method is wrong, the driver will not be found.
jtds connection sqlserver
JTDS is an open source 100% pure Java driver for JDBC 3.0 drivers for Microsoft SQL Server and Sybase (versions 10, 11, 12, 15). JTDS is based on freetds and is currently the fastest production ready JDBC drivers for SQL Server and Sybase.
JTDS is fully compatible with JDBC 3.0, supports parallel (fully independent) statements in forward-only, and scrollable/updable result sets (ResultSets), and can implement all databasemetadata and resultetmetadata methods.
jTDS - SQL Server and Sybase JDBC driver
package sqlserver_jtds; import java.sql.*; public class SQLServer { String dbURL = "jdbc:jtds:sqlserver://127.0.0.1:1433;;DatabaseName=test "; String user = "sa"; String password = "123456 "; Connection conn; public SQLServer(){ this.connect(); } public void connect(){ try{ try{ Class.forName("net.sourceforge.jtds.jdbc.Driver "); }catch(Exception e) { e.printStackTrace(); } //DriverManager.registerDriver(new net.sourceforge.jtds.jdbc.Driver()); conn = DriverManager.getConnection(dbURL,user, password); DatabaseMetaData metaData = conn.getMetaData(); System.out.print(metaData.getDatabaseProductVersion()); }catch(Exception e){ e.printStackTrace(); } } public static void main(String[] args) { new SQLServer(); } }