java-DAO-sample with connector

STEP 1 CREATE TABLE

DROP TABLE commodity;
CREATE TABLE commodity (
  commodityid int(11) NOT NULL auto_increment,
  commodityName varchar(30) default NULL,
  price decimal(10,2) default NULL,
  agio decimal(4,3) default NULL,
  PRIMARY KEY (commodityid)
)


STEP 2 DAO Interface

\WebRoot\CommodityDAO.java

package com.ProjectName.dao;
import.java.util,List;
import.com.ProjectName.bean.Commodity;
public interface CommodityDAO {
  public void addCommodity(Commodity commodity);
  public void updateCommodity(Commodity commodity);
  public void deleteCommodity(Commodity commodity);
  public List<Commodity> findAllCommodity();
  public Commodity findCommodityByID(int commidtyId);
}


STEP 3 DAO implement

public class CommodityDAOImpl implements CommodityDAO{

  public void addCommodity(Commodity commodity){
    Connection conn=DBConnection.getConnection();
    String addSQL="insert into commodity(commodityName,price,agio) values(?,?,?)";
//  String updateSQL="update commodity set commodityName=?prce=?,ago=? where commodityid=?";
//  String updateSQL="delete from commodity where commodityid=?";

    PreparsedStatment pstmt=null;
    try{
        pstmt=conn.prepareStatement(addSQL);
        pstmt.setString(1,commodity.getCommodityName());
        pstmt.setDouble(2,commodity.getPrice());
        pstmt.setDouble(3,commodity.getAgio());
        //pstmt.setInt(4,commodity.getCommodity());
        pstmt.executeUpdate();
  } catch (SQLException e){
        e.printStackTrace();
  } finally {
        DBConnection.close(pstmt);
        DBConnection.close(conn);
  }
}

 publicList<Commodity> findAllCommodity() {
   Connection conn=DBConnection.getConnection();
   String updateSQL="Select * from commodity";
   PreparedStatement pstmt=null;
    List<Commodity>commodityList=new ArrayList<Commodity>();
    try {
      pstmt=conn.prepareStatement(updateSQL);
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()) {
            Commodity commoidty=new Commoidty();
            commoidty.setCommodityId(rs.getInt(1));
            commoidty.setCommodityName(rs.getString(2));
            commoidty.setPrice(rs.getDouble(3));
            commoidty.setAgio(rs.getDouble(4));
            commoidtyList.add(commodity);
      }
    } finally {
            DBConnection.close(pstmt);
            DBConnection.close(conn);
    }    
     return commodityList;
 }

 public Commodity findCommodityById(int commodityId) {
   Connection conn=DBConnection.getConnection();
   String updateSQL="Select * from commodity where commodityId=?";
   PreparedStatement pstmt=null;
   Commodity commodity=new Commodity();
   try {
      pstmt=conn.prepareStatement(updateSQL);
      pstmt.setInt(1.commodityID);
      ResultSet rs=pstmt.executeQuery();
      if (rs.next()) {
            commoidty.setCommodityId(rs.getInt(1));
            commoidty.setCommodityName(rs.getString(2));
            commoidty.setPrice(rs.getDouble(3));
            commoidty.setAgio(rs.getDouble(4));
      }
    } catch (SQLException e){
           e.printStackTrace();
    } finally {
            DBConnection.close(pstmt);
            DBConnection.close(conn);
    }    
     return commodityList;
 }

}

4. DAO Factory.java

package com.ProjectName.factory; 

import com.projectName.dao.CommodityDAO;

import com.projectName.dao.CommodityDAOImpt;

public class CommodityDAOFactory {

  public static Commodity DAO getCommodityDAOInstance(){

    return new CommoidtyDAOImp();

  }

}

Connector


\WebRoot\DBConnection.java

package.com.ProjectName.util;
import.java.sql.connection;
import.java.sql.DriverManager;
import.java.sql.PreparedStatement;
import.java.sql.ResultSet;
import.java.sql.SQLException;
public class DBConnection {
  private static final String DBDRIVER="com.mysql.jdbc.Driver";
  private static final String DBURL=""jdbc:mysql://localhost:3306/DataBaseName";
  private static final String DBUSER="root";
  private static final String DBPASSWORD="admin";

  public static Connection getConnection(){
    Connection conn=null;
    try {
      Class.forName(DBDRIVER);
      conn=DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
    } catch (ClassNotFoundException e){
      e.printStackTrace();
    } catch {SQLExpection e}{
      e.printStackTrace();
    }
    return conn;
  }  
 
  public static void close(Connection conn) {
    if(conn != null) {
       try{
             conn.close();
      } catch (SQLException e) {
             e.printTrace();
      }
    }
  }

 
  public static void close(PreparedStatement pstmt) {
    if(pstmt != null) {
       try{
             pstmt.close();
      } catch (SQLException e) {
             e.printTrace();
      }
    }
  }

 
  public static void close(ResultSet rs) {
    if(rs != null) {
       try{
             rs.close();
      } catch (SQLException e) {
             e.printTrace();
      }
    }
  }