Connect to database using JDBC

[Former Member]

Hi Experts,

I would like to connect to MS SQL Server Database using JDBC from my application. I would like to know what all should i check up in visual administrator. And how to use JDBC in Webdynpro Applications.

Regards

Abdullah

[Former Member · Accepted solution]

Hello there are several ways to connect via JDBC.

1.) You can write a Database-Class like this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;

import javax.faces.context.FacesContext;

/**
 * Diese Klasse stellt eine statische Methode zum Verbindungsaufbau mit der
 * Datenbank bereit.
 * 
 * @author Björn Karpenstein
 */
public final class Database {
	
	private String dbString = null;
		
	private static Database instance = null;
	
	// nicht instanziierbar
	public Database() 
	{
	    // get a reference to properties file to retrieve messages

        String user = "USER";
        String password = "PASSWORD";
        String database  = "DATABASENAME";
        String host = "HOSTNAME";

		this.dbString = "jdbc:microsoft:sqlserver://"+host+":1433;User="+user+";Password="+password+";databaseName="+database+";";
	}
	
	public static Database getInstance() {
		
		if (instance == null) {
			instance = new Database();
		}
		
		return instance;
	}
	
	public Connection getConnection() throws ClassNotFoundException, SQLException 
	{
		Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		
		return DriverManager.getConnection(this.dbString);
		
	}
}

The class is managing the connection for you as singleton. Then you can use the getInstance-Method from your Object and do some statements like that:

		Connection conn = null;
		PreparedStatement stmt = null;
		String sql;

		try {
			conn = Database.getInstance().getConnection();
			sql = "INSERT INTO containersize(containersize) VALUES (?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, size);
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace(System.out);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

Here i have used the Microsoft SQL Driver, you have to include the following jar-Files in your class-path of the application:

msbase.jar

mssqlserver.jar

msutil.jar

In your web dynpro application there are some Collections with represents the data of the object (for example tables), there you can simply add some Lines.

Another way is to use sap proprietary open sql, i think for that you will find some tutorials in here.

With best regards