/*
* Created on 2007-7-16
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package db;
/**
* @author Administrator
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class Opinion
{
private int opinionId=-1;
private String name;
private String department;
private String receiveTime;
private String dealTime;
private String content="";
private int carInfoId;
private int activityId=-1;
/**
* @return Returns the activityId.
*/
public int getActivityId()
{
return activityId;
}
/**
* @param activityId The activityId to set.
*/
public void setActivityId(int activityId)
{
this.activityId = activityId;
}
/**
* @return Returns the carInfoId.
*/
public int getCarInfoId()
{
return carInfoId;
}
/**
* @param carInfoId The carInfoId to set.
*/
public void setCarInfoId(int carInfoId)
{
this.carInfoId = carInfoId;
}
/**
* @return Returns the content.
*/
public String getContent()
{
return content;
}
/**
* @param content The content to set.
*/
public void setContent(String content)
{
this.content = content;
}
/**
* @return Returns the dealTime.
*/
public String getDealTime()
{
return dealTime;
}
/**
* @param dealTime The dealTime to set.
*/
public void setDealTime(String dealTime)
{
this.dealTime = dealTime;
}
/**
* @return Returns the department.
*/
public String getDepartment()
{
return department;
}
/**
* @param department The department to set.
*/
public void setDepartment(String department)
{
this.department = department;
}
/**
* @return Returns the name.
*/
public String getName()
{
return name;
}
/**
* @param name The name to set.
*/
public void setName(String name)
{
this.name = name;
}
/**
* @return Returns the opinionId.
*/
public int getOpinionId()
{
return opinionId;
}
/**
* @param opinionId The opinionId to set.
*/
public void setOpinionId(int opinionId)
{
this.opinionId = opinionId;
}
/**
* @return Returns the receiveTime.
*/
public String getReceiveTime()
{
return receiveTime;
}
/**
* @param receiveTime The receiveTime to set.
*/
public void setReceiveTime(String receiveTime)
{
this.receiveTime = receiveTime;
}
}
/*
* Created on 2007-7-16
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* @author Administrator
*
* TODO To change the template for this generated type comment go to Window -
* Preferences - Java - Code Style - Code Templates
*/
public class OpinionDAO
{
public int addOpinion(Opinion opinion)
{
Connection con = null;
Statement st = null; //創建Statement對象
ResultSet rs=null;
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:jtds:sqlserver://localhost:1433/ActiveBPEL", "sa",
"peace");
st = con.createStatement();
String name = opinion.getName();
String department = opinion.getDepartment();
String receiveTime = opinion.getReceiveTime();
String dealTime = opinion.getDealTime();
String content = opinion.getContent();
int carInfoId = opinion.getCarInfoId();
int activityId=opinion.getActivityId();
st
.executeUpdate("insert opinion(name,department,receiveTime,dealTime,content,carInfoId,activityId) values("
+ "'"
+ name
+ "','"
+ department
+ "','"
+ receiveTime
+ "','"
+ dealTime
+ "','"
+ content
+ "'," + carInfoId + ","+activityId+")");
rs=st.executeQuery("select max(opinionId) from opinion");
int opinionId=-1;
while(rs.next())
{
opinionId=rs.getInt(1);
}
return opinionId;
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} finally
{
if (rs != null)
{
try
{
rs.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
rs = null;
}
if (st != null)
{
try
{
st.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
st = null;
}
if (con != null)
{
try
{
con.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
con = null;
}
}
return -1;
}
public void updateOpinion(Opinion opinion)
{
Connection con = null;
Statement st = null; //創建Statement對象
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:jtds:sqlserver://localhost:1433/ActiveBPEL", "sa",
"peace");
st = con.createStatement();
int opinionId = opinion.getOpinionId();
String name = opinion.getName();
String department = opinion.getDepartment();
String receiveTime = opinion.getReceiveTime();
String dealTime = opinion.getDealTime();
String content = opinion.getContent();
int carInfoId = opinion.getCarInfoId();
int activityId=opinion.getActivityId();
st
.executeUpdate("update opinion set name="
+ "'"
+ name
+ "',department='"
+ department
+ "',receiveTime='"
+ receiveTime
+ "',dealTime='"
+ dealTime
+ "',content='"
+ content
+ "',carInfoId=" + carInfoId+" ,activityId="+activityId+" where opinionId="+opinionId);
//輸出被查詢的情況
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} finally
{
if (st != null)
{
try
{
st.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
st = null;
}
if (con != null)
{
try
{
con.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
con = null;
}
}
}
public List getOpinions(int carInfoId)
{
Connection con = null;
Statement st = null; //創建Statement對象
ResultSet rs = null;
List opinions = new ArrayList();
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:jtds:sqlserver://localhost:1433/ActiveBPEL", "sa",
"peace");
st = con.createStatement();
rs = st.executeQuery("select * from opinion where carInfoId="
+ carInfoId);
while (rs.next())
{
Opinion opinion = new Opinion();
opinion.setOpinionId(rs.getInt("opinionId"));
opinion.setName(rs.getString("name"));
opinion.setDepartment(rs.getString("department"));
Timestamp timee = rs.getTimestamp("receiveTime");
SimpleDateFormat myFormat = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
String mystrdate = "";
if (timee != null)
{
mystrdate = myFormat.format(timee);
}
opinion.setReceiveTime(mystrdate);
timee= rs.getTimestamp("dealTime");
if (timee!= null)
{
mystrdate = myFormat.format(timee);
}
opinion.setDealTime(mystrdate);
opinion.setContent(rs.getString("content"));
opinion.setCarInfoId(rs.getInt("carInfoId"));
opinion.setActivityId(rs.getInt("activityId"));
opinions.add(opinion);
}
return opinions;
//輸出被查詢的情況
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} finally
{
if (rs != null)
{
try
{
rs.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
rs = null;
}
if (st != null)
{
try
{
st.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
st = null;
}
if (con != null)
{
try
{
con.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
con = null;
}
}
return null;
}
public Opinion getOpinion(int carInfoId,int activityId)
{
Connection con = null;
Statement st = null; //創建Statement對象
ResultSet rs = null;
Opinion opinion=new Opinion();
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:jtds:sqlserver://localhost:1433/ActiveBPEL", "sa",
"peace");
st = con.createStatement();
rs = st.executeQuery("select opinionId,content from opinion where carInfoId="
+ carInfoId+"and activityId="+activityId);
while (rs.next())
{
opinion.setOpinionId(rs.getInt("opinionId"));
opinion.setContent(rs.getString("content"));
}
return opinion;
//輸出被查詢的情況
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} finally
{
if (rs != null)
{
try
{
rs.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
rs = null;
}
if (st != null)
{
try
{
st.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
st = null;
}
if (con != null)
{
try
{
con.close();
} catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
con = null;
}
}
return opinion;
}
}