package com.wrox.webservices;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  
  public class ProductService
  {
     public static Product getProduct(int productID)
     {
        Product obj = new Product();
        try
        {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           String connectionUrl =
         "jdbc:sqlserver://localhost;database=AdventureWorks;user=sa;password=thiru";
           Connection con = DriverManager.getConnection(connectionUrl);
           con.setAutoCommit(false);
           PreparedStatement pstmt = con.prepareStatement
              ("SELECT ProductID, Name, ProductNumber, Color FROM " +
              " Production.Product WHERE ProductID = ?");
           pstmt.setInt(1, productID);
           ResultSet rs = pstmt.executeQuery();
           while (rs.next())
           { 
              obj.setProductID(rs.getInt("ProductID"));
              obj.setName(rs.getString("Name"));
              obj.setProductNumber(rs.getString("ProductNumber"));
              obj.setColor(rs.getString("Color"));
           }
           rs.close();
           pstmt.close();
           con.close();
        }
        catch (Exception e)
        {
           e.printStackTrace();
        }
        return obj;
     }        
  }