/*
* This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/).
*/
package ch08.database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import javax.servlet.http.HttpSession;
import ch08.*;
import ch08.object.unit.*;
/**
* 针对客户信息的数据处理类
* @author ShenYK
* @version 1.0
*/
public class DCustomer extends DCommon
{
//获得所有客户信息
public Vector getAllCustomerInfo()
throws Exception
{
//获得数据库连接
Connection conn = this.getDBConnection();
if ( conn == null )
{
throw new Exception("数据库连接获得失败!");
}
Statement stmt = null;
ResultSet rs = null;
try
{
//放入一个大的Vector中
Vector vCustomers = new Vector();
stmt = conn.createStatement();
//执行SQL语句
String sQuery = "select * from customer where del_flg<>'1' order by customer_id desc";
rs = stmt.executeQuery( sQuery );
while ( rs.next() )
{
Customer oCustomer = new Customer(rs);
vCustomers.add( oCustomer );
}
return vCustomers;
}
catch(Exception e)
{
e.printStackTrace();
throw e;
}
finally
{
try
{
rs.close();
stmt.close();
conn.close();
}catch(Exception ex)
{
}
}
}
//获得对应分类信息
public Customer getCustomerById( String sCustomerId )
throws Exception
{
//获得数据库连接
Connection conn = this.getDBConnection();
if ( conn == null )
{
throw new Exception("数据库连接获得失败!");
}
Statement stmt = null;
ResultSet rs = null;
try
{
//返回的分类对象
Customer oCustomer = null;
stmt = conn.createStatement();
//执行SQL语句
String sQuery = "select * from customer where customer_id='" + sCustomerId + "' and del_flg<>'1'";
rs = stmt.executeQuery( sQuery );
if( rs.next() )
{
oCustomer = new Customer(rs);
}
return oCustomer;
}
catch(Exception e)
{
e.printStackTrace();
throw e;
}
finally
{
try
{
rs.close();
stmt.close();
conn.close();
}catch(Exception ex)
{
}
}
}
//删除指定的分类
public void deleteCustomerById( String sCustomerId )
throws Exception
{
//获得数据库连接
Connection conn = this.getDBConnection();
if ( conn == null )
{
throw new Exception("数据库连接获得失败!");
}
Statement stmt = null;
try
{
stmt = conn.createStatement();
//执行SQL语句,设置删除标志
String sUpdateQuery = "update customer set del_flg='1' where customer_id='" + sCustomerId + "'";
stmt.executeUpdate( sUpdateQuery );
return;
}
catch(Exception e)
{
e.printStackTrace();
throw e;
}
finally
{
try
{
stmt.close();
conn.close();
}catch(Exception ex)
{
}
}
}
//修改指定的分类
public void modifyCustomer ( Customer customerObj )
throws Exception
{
//获得数据库连接
Connection conn = this.getDBConnection();
if ( conn == null )
{
throw new Exception("数据库连接获得失败!");
}
Statement stmt = null;
try
{
stmt = conn.createStatement();
//执行SQL语句,修改题目
String sUpdateQuery = "update customer set "
+ "realname = '" + customerObj.getRealname() + "', "
+ "sex = '" + customerObj.getSex() + "', "
+ "birthday = '" + customerObj.getBirthday() + "', "
+ "phone = '" + customerObj.getPhone() + "', "
+ "cellphone = '" + customerObj.getCellphone() + "', "
+ "address = '" + customerObj.getAddress() + "', "
+ "start_date = '" + customerObj.getStartDate() + "', "
+ "memo = '" + customerObj.getMemo() + "' "
+ "where customer_id = '" + customerObj.getCustomerId() + "'";
stmt.executeUpdate( sUpdateQuery );
return;
}
catch(Exception e)
{
e.printStackTrace();
throw e;
}
finally
{
try
{
stmt.close();
conn.close();
}catch(Exception ex)
{
}
}
}
//添加一个新分类
public void addCustomer ( Customer customerObj )
throws Exception
{
//获得数据库连接
Connection conn = this.getDBConnection();
if ( conn == null )
{
throw new Exception("数据库连接获得失败!");
}
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = conn.createStatement();
//执行SQL语句生成最大的categoryId
String sCustomerId = "";
String sQuery = "select max(customer_id) from customer";
rs = stmt.executeQuery( sQuery );
rs.next();
String sCurrentMaxId = rs.getString(1);
//当前是第一次登录
if ( sCurrentMaxId == null )
{
sCustomerId = "0000000001";
}
else
{
int iMaxCd = Integer.parseInt(sCurrentMaxId);
sCustomerId = String.valueOf(iMaxCd+1);
int iLength = sCustomerId.length();
for(int i=10; i>iLength; i--)
{
sCustomerId = "0" + sCustomerId;
}
}
//执行SQL语句,添加客户信息
String sInsertQuery = "insert into customer set "
+ "customer_id = '" + sCustomerId + "', "
+ "realname = '" + customerObj.getRealname() + "', "
+ "sex = '" + customerObj.getSex() + "', "
+ "birthday = '" + customerObj.getBirthday() + "', "
+ "phone = '" + customerObj.getPhone() + "', "
+ "cellphone = '" + customerObj.getCellphone() + "', "
+ "address = '" + customerObj.getAddress() + "', "
+ "start_date = '" + customerObj.getStartDate() + "', "
+ "memo = '" + customerObj.getMemo() + "', "
+ "del_flg = '0'";
stmt.executeUpdate( sInsertQuery );
return;
}
catch(Exception e)
{
e.printStackTrace();
throw e;
}
finally
{
try
{
rs.close();
stmt.close();
conn.close();
- 1
- 2
前往页