database connectivity?

Question

How to establish SQL 2005 database connectivity with Java application. Actually I don't know how to set path for JDBC. Any help would be great.

Answer 1

This is a HOWTO from Microsoft on where to get the driver and how to configure it.

Briefly, download the .jar from Microsoft, reference it in your classpath and use:

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
   "databaseName=AdventureWorks;user=MyUserName;password=*****;";
Connection con = DriverManager.getConnection(connectionUrl);

substituting the relevant info.

Answer 2

This link will help you with this

jdbc connection mssql

Code Snippet

 private java.sql.Connection getConnection(){
          try{
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); 
               con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
               if(con!=null) System.out.println("Connection Successful!");
          }catch(Exception e){
               e.printStackTrace();
               System.out.println("Error Trace in getConnection() : " + e.getMessage());
         }
          return con;
      }

private String getConnectionUrl(){
          return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
     }

Answer 3

1. Download the jdbc driver from http://www.microsoft.com/sqlserver/2005/en/us/java-database-connectivity.aspx.
2. You can either download the Windows or Unix version. It does not really matter which one you use.
3. If you chosed the Windows version, run the downloaded exe file; this will create a directory called Microsoft SQL Server 2005 JDBC Driver in the directory you downloaded the file to.
4. Add the file Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.2\enu\sqljdbc.jar to your classpath (either using the -cp option of java or, if you are using an application server, by putting it in the appropriate directory).
5. Set your connection. Basically, the information required for this are:
   • the driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver
   • the connection url: jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

Check Connecting to SQL Server with the JDBC Driver for more details and/or the resources available in the help directory (sample code available in help/samples/connections/ConnectURL.java).

Answer 4

Your actual problem is thus that you don't know what to do with the phenomenon "classpath".

Actually, the classpath is kind of a collection of disk file system paths which points to the whole .jar file(s) and/or to some root folder with .class file(s) where the Java Virtual Machine should lookup for classes to be imported and loaded.

You can specify the classpath during compile and runtime using the -classpath or -cp argument of javac.exe and java.exe. The -cp is just a shorthand, it does nothing different. Then you have the mysterious %CLASSPATH% environment variable which you should just entirely forget. It is a poor thing which was intented to make starters easy to manage the classpath, but at end it just confused them more.

As you're using Class#forName() to load the driver, you only need to have it in the classpath during runtime, not during compiletime. So here's a basic example how to execute it:

java -cp .;c:/path/to/mssql-jdbc-driver.jar com.example.YourClass

You see, the classpath exist of two parts, the . which represents the current working directory and the c:/path/to/mssql-jdbc-driver.jar which should be the absolute path to the JAR file. The ; is just a path separator (in Windows; in Unix and clones it should be a colon :). Note: if a path contains spaces, e.g. c:/spacy path to/file.jar, then you need to wrap the individual path with doublequotes.

If you're using an IDE such as Eclipse, then normal practice is that you create a folder in the project where in you can drop all of those 3rd party JAR files which are required by the project. Create a project folder called lib, drop the JDBC driver in there and rightclick project > Properties > Java Build Path > Libraries > Add JARs > Select the JAR file which you dropped in project's lib > OK.

That should be it. Hope this helps.