How to connect to SQL Server 2008 database using JDBC?

Question

I have MSSQL 2008 installed on my local PC, and my Java application needs to connect to the MSSQL database. I am new to MSSQL and I would like to get some help creating a user input for my Java application and connecting via JDBC. So far I have tried to create a login for my application and used the following connection string, but I am not working at all. Any help and hint would be appreciated.

jdbc:jtds:sqlserver://127.0.0.1:1433/dotcms username="shuxer" password="itarator" 

Answer 1

There are two ways to use JDBC - using Windows authentication and SQL authentication. SQL authentication is probably the easiest. What you can do is something like:

 String userName = "username"; String password = "password"; String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB"; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager.getConnection(url, userName, password); 

after adding sqljdbc4.jar to the build path.

For Windows authentication, you can do something like:

 String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB;integratedSecurity=true"; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager.getConnection(url); 

and then add the path to sqljdbc_auth.dll as an argument to the virtual machine (still need sqljdbc4.jar in the build path).

Please look here for a quick walkthrough showing how to connect to SQL Server with Java using jTDS and JDBC so you need more details. Hope this helps!

Answer 2

You can use this :

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ConnectMSSQLServer { public void dbConnect(String db_connect_string, String db_userid, String db_password) { try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password); System.out.println("connected"); Statement statement = conn.createStatement(); String queryString = "select * from sysobjects where type='u'"; ResultSet rs = statement.executeQuery(queryString); while (rs.next()) { System.out.println(rs.getString(1)); } } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { ConnectMSSQLServer connServer = new ConnectMSSQLServer(); connServer.dbConnect("jdbc:sqlserver://<hostname>", "<user>", "<password>"); } } 

Answer 3

I also use mssql server 2008 and jtds.In my case I use the following connection string and it works.

 Class.forName( "net.sourceforge.jtds.jdbc.Driver" ); Connection con = DriverManager.getConnection( "jdbc:jtds:sqlserver://<your server ip address>:1433/zacmpf", userName, password ); Statement stmt = con.createStatement(); 

Answer 4

If you have any connection problems, most likely the problem is that you have not yet enabled the TCP / IP listener on port 1433. The quick command "netstat -an" will tell you if it is listening. By default, SQL Server does not enable this after installation.

In addition, you need to set the password for the sa account, as well as the ENABLE account for sa, if you plan to use this account to connect.

Obviously, this also means that you need to enable "mixed mode authentication" on your MSSQL node.

Answer 5

Try using it like this: jdbc: jtds: sqlserver: //127.0.0.1/dotcms; Instance = InstanceName

I do not know which version of mssql you are using, if it is an express version, the default instance is sqlexpress

Be sure to check if the SQL Server Viewer service is running.

Answer 6

You can try to configure the SQL server:

• Step 1: Open SQL Server 20xx Configuration Manager
• Step 2: Click “Protocols for SQL” in the SQL server configuration. Then right click TCP / IP, select "Properties"
• Step 3: Click the IP Address tab, edit all TCP. Port - 1433

NOTE. ALL TCP port - 1433 Finally, restart the server.

Answer 7

A simple Java program that connects to SQL Server.

NOTE. You need to add sqljdbc.jar to the build path

// localhost: the local computer acts as a server

// 1433: default SQL port number

// username: sa

// password: use the password that is used during installation of SQL Server Management Studio, in my case it is "root"

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class Conn { public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException { Connection conn=null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=company", "sa", "root"); if(conn!=null) System.out.println("Database Successfully connected"); } catch (SQLException e) { e.printStackTrace(); } } } 

Answer 8

Try this.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class SQLUtil {

public void dbConnect (String db_connect_string, String db_userid, String db_password) {

try {

  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password); System.out.println("connected"); Statement statement = conn.createStatement(); String queryString = "select * from cpl"; ResultSet rs = statement.executeQuery(queryString); while (rs.next()) { System.out.println(rs.getString(1)); } } catch (Exception e) { e.printStackTrace(); } } 

public static void main (String [] args) {

SQLUtil connServer = new SQLUtil ();

connServer.dbConnect ("jdbc: sqlserver: //192.168.10.97: 1433; databaseName = myDB", "sa", "0123");

}

}

Answer 9

This sample Java program connects to a MySQL database using JDBC, executes a query, and retrieves and prints the value of a database field.

The same sample code can be used to connect to any type of database, all you have to do is change the connection URL (dbUrl in the sample). For this code to work correctly, you need to download the mysql driver for JDBC, in other words, Java Connectors from mysql.com.

If it still doesn’t work after loading the URL, this may be due to the classpath. You will need to add the jar driver file to the classpath.

 import java.sql.*; import javax.sql.*; public class jdbcdemo{ public static void main(String args[]){ String dbtime; String dbUrl = "jdbc:mysql://your.database.domain/yourDBname"; String dbClass = "com.mysql.jdbc.Driver"; String query = "Select * FROM users"; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection (dbUrl); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { dbtime = rs.getString(1); System.out.println(dbtime); } //end while con.close(); } //end try catch(ClassNotFoundException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); } } //end main } //end class