utilsserviceimpl.java

一个简单的Ext例子

package com.utils.service.Impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import com.utils.model.Customer;
import com.utils.model.Department;
import com.utils.model.Employeer;
import com.utils.model.Goods;
import com.utils.model.Order;
import com.utils.model.Other;
import com.utils.model.Salary;
import com.utils.model.Subsidies;
import com.utils.service.UtilsService;
import com.utils.servlet.*;

public class UtilsServiceImpl implements UtilsService {
	private Connection conn = null;
	
	public UtilsServiceImpl(){
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
			conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=LEDOA","sa","123");
		} catch (InstantiationException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e){
			e.printStackTrace();
		}
	}

	public List checkCustomer(String str) {
		// 查询客户
		System.out.println(str);
		ArrayList<Customer> it = new ArrayList<Customer>();
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
			//conn.commit();
			while(rs.next()){
				Customer em = new Customer();
				em.setId(rs.getString("id"));
				em.setCustomerAchor(rs.getString("customerAchor"));
				em.setCustomerAddress(rs.getString("customerAddress"));
				em.setCustomerBusiness(rs.getString("customerBusiness"));
				em.setCustomerEmail(rs.getString("customerEmail"));
				em.setCustomerName(rs.getString("customerName"));
				em.setCustomerNet(rs.getString("customerNet"));
				em.setCustomerTel(rs.getString("customerTel"));
				it.add(em);
			}
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return it;
	}
	public void addCustomer(Customer customer) {
		// 新增客户资料
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			String id = "customer"+getRandomString(10);
			String str = "insert into Customer values (";
			str += "'"+id+"',";
			str += "'"+customer.getCustomerName()+"',";
			str += "'"+customer.getCustomerAchor()+"',";
			str += "'"+customer.getCustomerTel()+"',";
			str += "'"+customer.getCustomerAddress()+"',";
			str += "'"+customer.getCustomerEmail()+"',";
			str += "'"+customer.getCustomerNet()+"',";
			str += "'"+customer.getCustomerBusiness()+"')";
			System.out.println(str);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	public void delCustomer(Customer customer) {
		//删除客户资料
		try {
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String str = "delete from Customer ";
			str += "where id='"+customer.getId()+"'";
			System.out.println(str);
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	public void updateCustomer(Customer customer) {
		// 编辑客户资料
		try {
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String str = "update Customer set ";
			str += "personName='"+customer.getCustomerName()+"',";
			str += "personDept='"+customer.getCustomerAchor()+"',";
			str += "personPost='"+customer.getCustomerTel()+"',";
			str += "personNational='"+customer.getCustomerAddress()+"',";
			str += "personNarriage='"+customer.getCustomerEmail()+"',";
			str += "personSex='"+customer.getCustomerNet()+"',";
			str += "personBirthday='"+customer.getCustomerBusiness()+"' ";
			str += "where id='"+customer.getId()+"'";
			System.out.println(str);
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	
	public List checkDepartment(String str) {
		// 查询部门
		System.out.println(str);
		ArrayList<Department> it = new ArrayList<Department>();
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
			//conn.commit();
			while(rs.next()){
				Department em = new Department();
				em.setId(rs.getString("id"));
				em.setDeptAchor(rs.getString("deptAchor"));
				em.setDeptName(rs.getString("deptName"));
				em.setDeptNote(rs.getString("deptNote"));
				em.setDeptTel(rs.getString("deptTel"));
				it.add(em);
			}
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return it;
	}
	public void addDepartment(Department department) {
		// 新增部门资料
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			String id = "department"+getRandomString(10);
			String str = "insert into Department values (";
			str += "'"+id+"',";
			str += "'"+department.getDeptName()+"',";
			str += "'"+department.getDeptAchor()+"',";
			str += "'"+department.getDeptTel()+"',";
			str += "'"+department.getDeptNote()+"')";
			System.out.println(str);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	public void delDepartment(Department department) {
		// 删除部门资料
		try {
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String str = "delete from Department ";
			str += "where id='"+department.getId()+"'";
			System.out.println(str);
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	public void updateDepartment(Department department) {
		// 编辑部门资料
		try {
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String str = "update Department set ";
			str += "personName='"+department.getDeptName()+"',";
			str += "personDept='"+department.getDeptAchor()+"',";
			str += "personPost='"+department.getDeptTel()+"',";
			str += "personNational='"+department.getDeptNote()+"' ";
			str += "where id='"+department.getId()+"'";
			System.out.println(str);
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public List checkEmployeer(String str) {
		System.out.println(str);
		//查询员工
		ArrayList<Employeer> it = new ArrayList<Employeer>();
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
			//conn.commit();
			while(rs.next()){
				Employeer em = new Employeer();
				em.setId(rs.getString("id"));
				em.setPersonAddress(rs.getString("personAddress"));
				em.setPersonAge(rs.getInt("personAge"));
				em.setPersonBirthday(rs.getString("personBirthday"));
				em.setPersonCulture(rs.getString("personCulture"));
				em.setPersonDept(rs.getString("personDept"));
				em.setPersonIDCard(rs.getString("personIDCard"));
				em.setPersonLanguage(rs.getString("personLanguage"));
				em.setPersonName(rs.getString("personName"));
				em.setPersonNarriage(rs.getString("personNarriage"));
				em.setPersonNational(rs.getString("personNational"));
				em.setPersonPost(rs.getString("personPost"));
				em.setPersonProfessional(rs.getString("personProfessional"));
				em.setPersonSalary(rs.getString("personSalary"));
				em.setPersonSex(rs.getString("personSex"));
				em.setPersonSocialID(rs.getString("personSocialID"));
				em.setPersonTel(rs.getString("personTel"));
				em.setPersonYear(rs.getString("personYear"));
				it.add(em);
			}
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return it;
	}
	public void addEmployeer(Employeer employeer) {
		//新增员工
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			String id = "employeer"+getRandomString(10);
			String str = "insert into Employeer values (";
			str += "'"+id+"',";
			str += "'"+employeer.getPersonName()+"',";
			str += "'"+employeer.getPersonDept()+"',";
			str += "'"+employeer.getPersonPost()+"',";
			str += "'"+employeer.getPersonNational()+"',";
			str += "'"+employeer.getPersonNarriage()+"',";
			str += "'"+employeer.getPersonSex()+"',";
			str += "'"+employeer.getPersonBirthday()+"',";
			str += "'"+employeer.getPersonCulture()+"',";
			str += "'"+employeer.getPersonProfessional()+"',";
			str += "'"+employeer.getPersonYear()+"',";
			str += "'"+employeer.getPersonLanguage()+"',";
			str += "'"+employeer.getPersonSalary()+"',";
			str += "'',";
			str += "'"+employeer.getPersonIDCard()+"',";
			str += "'"+employeer.getPersonSocialID()+"',"+employeer.getPersonAge()+",";
			str += "'"+employeer.getPersonTel()+"','','','','','','','','')";
			System.out.println(str);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	public void delEmployeer(Employeer employeer) {
		//删除员工
		try {
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String str = "delete from Employeer ";
			str += "where id='"+employeer.getId()+"'";
			System.out.println(str);
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	public void updateEmployeer(Employeer employeer) {
		//编辑员工
		try {
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String str = "update Employeer set ";
			str += "personName='"+employeer.getPersonName()+"',";
			str += "personDept='"+employeer.getPersonDept()+"',";
			str += "personPost='"+employeer.getPersonPost()+"',";
			str += "personNational='"+employeer.getPersonNational()+"',";
			str += "personNarriage='"+employeer.getPersonNarriage()+"',";
			str += "personSex='"+employeer.getPersonSex()+"',";
			str += "personBirthday='"+employeer.getPersonBirthday()+"',";
			str += "personCulture='"+employeer.getPersonCulture()+"',";
			str += "personProfessional='"+employeer.getPersonProfessional()+"',";
			str += "personYear='"+employeer.getPersonYear()+"',";
			str += "personLanguage='"+employeer.getPersonLanguage()+"',";
			str += "personSalary='"+employeer.getPersonSalary()+"',";
			str += "personIDCard='"+employeer.getPersonIDCard()+"',";
			str += "personSocialID='"+employeer.getPersonSocialID()+"',";
			str += "personAge='"+employeer.getPersonAge()+"',";
			str += "personTel='"+employeer.getPersonTel()+"' ";
			str += "where id='"+employeer.getId()+"'";
			System.out.println(str);
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public List checkGoods(String str) {
		// 查询商品
		System.out.println(str);
		ArrayList<Goods> it = new ArrayList<Goods>();
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
			//conn.commit();
			while(rs.next()){
				Goods gd = new Goods();
				gd.setId(rs.getString("id"));
				gd.setGoodsName(rs.getString("goodsName"));
				gd.setPrices(rs.getInt("prices"));
				gd.setGoodsNote(rs.getString("goodsNote"));
				it.add(gd);
			}
			conn.close();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return it;
	}
	public void addGoods(Goods goods) {
		// TODO 自动生成方法存根
		try {
			if(conn == null)System.out.println("conn is null");
			//conn.setAutoCommit(false);
			String id = "goods"+getRandomString(10);
			String str = "insert into Goods values (";
			str += "'"+id+"',";
			str += "'"+goods.getGoodsName()+"',";
			str += goods.getPrices()+",";
			str += "'"+goods.getGoodsNote()+")";
			System.out.println(str);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(str);
			//conn.commit();
			conn.close();