ItemDaoImpl

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
 */
package mumbay.dao.item;

//~--- non-JDK imports --------------------------------------------------------

import mumbay.data.Item;

/**
 *
 * @author HP
 */
public class ItemDaoImpl implements ItemDao {
    @Override
    public Item getItemById(int itemId) {
        assert itemId != 0;
        String itemName = ItemInfo.getItemNamebyId(itemId);
        String username = ItemInfo.getUsernameById(itemId);
        return new Item(itemId,itemName,username);
    }
}

User.java

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mumbay.data;

/**
 *
 * @author HP
 */
public class User {
    private String fname_,lname_,username_;
    private String password_;
    private String address_,webAddress_;
    private String mobile_,phone_;
    private String email_;

    public User() {
    }

    public User(String fname) {
        this.fname_ = fname;
    }

    public User(String fname, String lname) {
        this.fname_ = fname;
        this.lname_ = lname;
    }

    public User(String username, String password_,  String fname, String email_) {
        username_=username;
        this.fname_ = fname;
        this.password_ = password_;
        this.email_ = email_;
    }

    public String getUsername() {
        return username_;
    }

    public void setUsername(String username_) {
        this.username_ = username_;
    }

    public String getAddress() {
        return address_;
    }

    public void setAddress(String address_) {
        this.address_ = address_;
    }

    public String getEmail() {
        return email_;
    }

    public void setEmail(String email_) {
        this.email_ = email_;
    }

    public String getFname() {
        return fname_;
    }

    public void setFname(String fname_) {
        this.fname_ = fname_;
    }

    public String getLname() {
        return lname_;
    }

    public void setLname(String lname_) {
        this.lname_ = lname_;
    }

    public String getMobile() {
        return mobile_;
    }

    public void setMobile(String mobile_) {
        this.mobile_ = mobile_;
    }

    public String getPassword() {
        return password_;
    }

    public void setPassword(String password_) {
        this.password_ = password_;
    }

    public String getPhone() {
        return phone_;
    }

    public void setPhone(String phone_) {
        this.phone_ = phone_;
    }

    public String getWebAddress() {
        return webAddress_;
    }

    public void setWebAddress(String webAddress_) {
        this.webAddress_ = webAddress_;
    }

    @Override
    public String toString() {
        return "User{" + "fname_=" + fname_ + "lname_=" + lname_ + "password_=" + password_ + "address_=" + address_ + "webAddress_=" + webAddress_ + "mobile_=" + mobile_ + "phone_=" + phone_ + "email_=" + email_ + '}';
    }

  




}

ItemDao interface

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
 */
package mumbay.dao.item;

//~--- non-JDK imports --------------------------------------------------------

import mumbay.data.Item;

/**
 *
 * @author HP
 */
public interface ItemDao {

//  public void addItem(Item item);
    public Item  getItemById(int itemId);    // throws CustomerNotFoundException;

//  public void deleteUser(int itemId);// throws CustomerNotFoundException;
//  public void updateUser(Item item);// throws CustomerNotFoundException;
}

Item.java

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mumbay.data;

import java.util.Date;

/**
 *
 * @author HP
 */
public class Item {
    private int itemId_;
    private String itemName_,itemDescription_,itemType_;
    String userName_;
    float price_;
    Date bidDate_;
    boolean isSailable_;

    public Item() {
        isSailable_=true;
    }

    public Item(int itemId_) {
        this.itemId_ = itemId_;
    }

    public Item(int itemId,String itemName_,String username) {
        itemId_=itemId;
        this.itemName_ = itemName_;
        userName_ = username;
        isSailable_ = true;
    }

   
    public Date getBidDate() {
        return bidDate_;
    }

    public void setBidDate(Date bidDate) {
        this.bidDate_ = bidDate;
    }

    public String getItemDescription() {
        return itemDescription_;
    }

    public void setItemDescription(String itemDescription) {
        this.itemDescription_ = itemDescription;
    }

    public int getItemId() {
        return itemId_;
    }

    public void setItemId(int itemId) {
        this.itemId_ = itemId;
    }

    public String getItemName() {
        return itemName_;
    }

    public void setItemName(String itemName_) {
        this.itemName_ = itemName_;
    }

    public String getItemType() {
        return itemType_;
    }

    public void setItemType(String itemType_) {
        this.itemType_ = itemType_;
    }

    public float getPrice() {
        return price_;
    }

    public void setPrice(float price_) {
        this.price_ = price_;
    }

    public String getUserName() {
        return userName_;
    }

    public void setUserName(String userName_) {
        this.userName_ = userName_;
    }

    @Override
    public String toString() {
        return "Item{" + "itemId_=" + itemId_ + "itemName_=" + itemName_ + "itemDescription_=" + itemDescription_ + "itemType_=" + itemType_ + "userName_=" + userName_ + "price_=" + price_ + "bidDate_=" + bidDate_ + "isSailable_=" + isSailable_ + '}';
    }



}

createTable.sql

create table authorisation(
    username varchar(20) not null primary key,
    password varchar(20) not null
)


create table users(

username varchar(20) not null primary key,
fname varchar(20) not null,
lname varchar(20),
address varchar(20),
webaddress varchar(20),
email varchar(20),
phone varchar(20),
mobile varchar(20)
)

create table sailItems
(
itemId int identity primary key ,
itemName varchar(20),
username varchar(20),
price float(6),
bidDate smalldatetime, //not smalldate
isSailable boolean default true//added for history purpose
)

create table itemDescription
(
itemId int primary key,
type varchar(20),
description varchar(50)
)

create table bids
(
bidId int identity primary key,
itemId int not null,
username varchar(20) not null,
bidPrice float(10),
bidDate smalldatetime
)



//ADDING constraints
alter table users
add foreign key(username) references users(username)

alter table sailItems
add foreign key (username) references users(username);

alter table itemDescription
add foreign key (itemId) references sailItems(itemId);

alter table bids
add foreign key(itemId) references sailItems(itemId);

alter table bids
add foreign key(username) references users(username)


//Dropping tables
drop table users
drop table sailItems
drop table itemDescription


//VIEWing tables
select * from authorisation
select * from users



//POPULATIng tables

insert into users( username ,fname )
value('ballg','George') //Note the single quotes , this cause my so much time to waiste

insert into sailitems(itemname , price , biddate ,username )
values('fridge',111.12,'2010-09-21','ballg')

select * from sailitems;
ITEMID      ITEMNAME      USERNAME      PRICE      BIDDATE      ISSAILABLE
2    fridge    ballg    111.12    2010-09-21 00:00:00.0    TRUE
3    cooler    kinshukc    59.12    2010-09-20 00:00:00.0    TRUE
4    tv    richard    75.12    2010-09-22 00:00:00.0    TRUE
5    washing machine    rutuja    100.12    2010-09-23 00:00:00.0    TRUE
6    ipod    ballg    34.12    2010-09-24 00:00:00.0    TRUE
7    fridge    ballg    112.12    2010-09-24 00:00:00.0    TRUE
(6 rows, 4 ms)

insert into bids(itemId , bidPrice , biddate ,username )
values(2,112.12,'2010-09-24','kinshukc')

insert into bids(itemId , bidPrice , biddate ,username )
values(3,67.12,'2010-09-24','kinshukc')

insert into bids(itemId , bidPrice , biddate ,username )
values(4,76.12,'2010-09-25','rutuja')

insert into bids(itemId , bidPrice , biddate ,username )
values(6,33.12,'2010-09-25','rutuja')

insert into bids(itemId , bidPrice , biddate ,username )
values(6,35.12,'2010-09-25','rutuja')

insert into bids(itemId , bidPrice , biddate ,username )
values(6,34.13,'2010-09-25','ballg')