Java Servlet connecting to SQL Server tutorial?

Question

Can anyone recommend a tutorial on how to write a Java Servlet which connects to MS SQL server and manages data?

I'm trying to write something which pulls products out of one database and inserts them into a stored procedure located on another database.

Thanks in advance.

Answer 1

You will need MSSQL JDBC driver. From that point on, you can treat MSSQL as any other database server. You can use JDBC API, or even better JPA/Hibernate to communicate with the database.

Answer 2

Take a look at this:

http://www.roseindia.net/jsp/Accessingdatabase-fromJSP.shtml

You just have to change the driver to use MSSQL.

Basically this:

String driver = "org.gjt.mm.mysql.Driver";

to:

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

and

String url="jdbc:mysql://localhost/books?user=<user>&password=<password>";

to:

String url = "jdbc:microsoft:sqlserver://<server_name>:<1433>"

And finally:

con=DriverManager.getConnection(url);

with

con=DriverManager.getConnection(url, usr, pwd );

Although this is not the best way to connect a web app with a database, it will be a good start for you to understand the basics of JDBC.

A better second approach would be to access the database from a java object, and call invoke methods on that class.

You can later load the connection from a connection pool, use a persistence framework and many other improvement. But I think this would be a good start.

I hope it helps.

Answer 3

Offical JDBC Docs from Sun

In a nut shell...

    try{
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection conn = DriverManager.getConnection("jdbc:sqlserver://ipaddress:port;databaseName=schema_name","user","pass");

    PreparedStatement pps = conn.prepareStatement("SELECT * FROM table");
    ResultSet rs = pps.executeQuery();
    while(rs.next()){
    //do something
    System.out.println(rs.getString("some col"));
    }
//close connection
conn.close();
    }catch(Exception ex){
      // do something
    }

You can also try hibernate which is a Object-Relational mapping tool which makes your life easier (of course it is an overkill, if you just want simple connection and do some simple stuff)

You can download the official jdbc jar here You may also wanna read this... M$ knowledge base article

Answer 4

Check this article HOW TO: SQL & JAVA for details on how to connect to SQL Server database from Java database applications. It also describes how to pass embedded SQL queries (SELECT, INSERT, UPDATE, DELETE), calling stored procedures, pass parameters etc.