package com.itjiandan.daoimpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.itjiandan.dao.IUser_DAO;
import com.itjiandan.model.User_Entity;
import com.itjiandan.util.DBManager;
import com.itjiandan.util.LogUtil;
import com.itjiandan.util.Md5Util;
public class User_DAOImpl implements IUser_DAO {
    @Override
    public User_Entity getUser(String name, String pass) {
        Connection conn = DBManager.getConnection();
        User_Entity user = null;
        PreparedStatement pre = null;
        ResultSet set = null;
                                 
        String sql = "select * from itjiandan_user where name=? and password=?";
                                 
        String password = Md5Util.md5s(pass);
        LogUtil.i("getUser---->name="+name+",password="+password);
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, name);
            pre.setString(2, password);
            set = pre.executeQuery();
            if(set.next()){
                user = new User_Entity();
                user.setId(set.getInt("id"));
                user.setName(set.getString("name"));
                user.setPass(set.getString("password"));
                LogUtil.i("getUser---->name="+user.getName());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
                                 
        return user;
    }
    @Override
    public List<User_Entity> getPageUser(int page, int size) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        List<User_Entity> users = null;
        ResultSet set = null;
                                 
        int start = ( page - 1 ) * size;
                                 
        String sql = "select u.id,u.name,u.age,u.sex,u.job,u.mail,g.name," +
                "u.createdate,u.reworkdate,u.state from itjiandan_user as u left " +
                "join itjiandan_user_group as ug on u.id=ug.uid left join" +
                " itjiandan_group as g on g.id=ug.gid Limit ?,?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, start);
            pre.setInt(2, size);
            set = pre.executeQuery();
            users = new ArrayList<User_Entity>();
            while(set.next()){
                User_Entity user = new User_Entity();
                user.setId(set.getInt("u.id"));
                user.setName(set.getString("u.name"));
                user.setAge(set.getInt("u.age"));
                user.setSex(set.getString("u.sex"));
                user.setJob(set.getString("u.job"));
                user.setMail(set.getString("u.mail"));
                user.setGname(set.getString("g.name"));
                                         
                user.setCreatedate(set.getTimestamp("u.createdate"));
                user.setReworkdate(set.getTimestamp("u.reworkdate"));
                user.setState(set.getInt("u.state"));
                users.add(user);
                                         
            }
                                     
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
                                 
                                 
        return users;
    }
    @Override
    public boolean addUser(User_Entity user) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "insert into itjiandan_user(name,password,age,sex,job,mail,createdate," +
                "state)values(?,?,?,?,?,?,?,?)";
        String password = Md5Util.md5s(user.getPass());
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, user.getName());
            pre.setString(2, password);
            pre.setInt(3, user.getAge());
            pre.setString(4, user.getSex());
            pre.setString(5, user.getJob());
            pre.setString(6, user.getMail());
            pre.setTimestamp(7, user.getCreatedate());
            pre.setInt(8, user.getState());
            int change = pre.executeUpdate();
            LogUtil.i("addUser----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean upUser(User_Entity user) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "update itjiandan_user set name=?,age=?,sex=?,job=?,mail=?," +
                "reworkdate=?,state=? where id=?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, user.getName());
            pre.setInt(2, user.getAge());
            pre.setString(3, user.getSex());
            pre.setString(4, user.getJob());
            pre.setString(5, user.getMail());
            pre.setTimestamp(6, user.getReworkdate());
            pre.setInt(7, user.getState());
            pre.setInt(8, user.getId());
            int change = pre.executeUpdate();
            LogUtil.i("addUser----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public User_Entity upUserById(int id) {
        Connection conn = DBManager.getConnection();
        User_Entity user = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select u.id,u.name,u.age,u.sex,u.job,u.mail,g.name,u.state" +
                " from itjiandan_user as u join itjiandan_user_group as ug " +
                "on u.id=? and ug.uid=? join itjiandan_group as g on g.id=ug.gid";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1,id);
            pre.setInt(2,id);
            set = pre.executeQuery();
            if(set.next()){
                LogUtil.i("client to upUserById succeed! g.name="+set.getString("g.name"));
                user = new User_Entity();
                user.setId(set.getInt("u.id"));
                user.setName(set.getString("u.name"));
                user.setAge(set.getInt("u.age"));
                user.setSex(set.getString("u.sex"));
                user.setJob(set.getString("u.job"));
                user.setMail(set.getString("u.mail"));
                user.setGname(set.getString("g.name"));
                user.setState(set.getInt("u.state"));
                                         
            }else{
                sql = "select u.id,u.name,u.age,u.sex,u.job,u.mail,u.state from" +
                        " itjiandan_user as u where id=?";
                pre = conn.prepareStatement(sql);
                pre.setInt(1, id);
                set = pre.executeQuery();
                if(set.next()){
                    user = new User_Entity();
                    user.setId(set.getInt("u.id"));
                    user.setName(set.getString("u.name"));
                    user.setAge(set.getInt("u.age"));
                    user.setSex(set.getString("u.sex"));
                    user.setJob(set.getString("u.job"));
                    user.setMail(set.getString("u.mail"));
                    user.setState(set.getInt("u.state"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
                                 
        return user;
    }
    @Override
    public boolean delUser(int[] id) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_user where id=?";
                                 
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<id.length; i++){
                pre.setInt(1, id[i]);
                pre.addBatch();
            }
                                     
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
                                 
        return false;
    }
    @Override
    public int getCountUser() {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        int count = 0;
        String sql = "select count(id) as c from itjiandan_user";
        try {
            pre = conn.prepareStatement(sql);
            set = pre.executeQuery();
            if(set.next()){
                count = set.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
        return count;
    }
    @Override
    public boolean addUserGroup(String name, int gid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select id from itjiandan_user where name=?";
                                 
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1,name);
            set = pre.executeQuery();
            if(set.next()){
                sql = "insert into itjiandan_user_group(uid,gid)values(?,?)";
                pre = conn.prepareStatement(sql);
                pre.setInt(1, set.getInt("id"));
                pre.setInt(2, gid);
                int change = pre.executeUpdate();
                if(change >= 1){
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
                                 
                                 
        return false;
    }
    @Override
    public boolean delUserGroup(int uid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_user_group where uid=?";
                                 
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, uid);
            int change = pre.executeUpdate();
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
                                 
        return false;
    }
                             
}




package com.itjiandan.daoimpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.itjiandan.model.Authority_Entity;
import com.itjiandan.model.Group_Entity;
import com.itjiandan.util.DBManager;
import com.itjiandan.util.LogUtil;
import com.itjiandan.dao.IGroup_DAO;
public class Group_DAOImpl implements IGroup_DAO {
    @Override
    public List<Group_Entity> getPageGroup(int page, int size) {
        Connection conn = DBManager.getConnection();
        List<Group_Entity> groups = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        int start = (page - 1) * size;
        String sql = "select * from itjiandan_group Limit ?,?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, start);
            pre.setInt(2, size);
            LogUtil.i("client to getPageGroup start=" + start + ",size=" + size);
            groups = new ArrayList<Group_Entity>();
            set = pre.executeQuery();
            while (set.next()) {
                Group_Entity group = new Group_Entity();
                group.setId(set.getInt("id"));
                group.setName(set.getString("name"));
                group.setCreatedate(set.getTimestamp("createdate"));
                group.setReworkdate(set.getTimestamp("reworkdate"));
                group.setState(set.getInt("state"));
                groups.add(group);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.close(conn, pre, set);
        }
                        
        return groups;
    }
    @Override
    public int getCountGroup() {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        int count = 0;
        String sql = "select count(id) as g from itjiandan_group";
        try {
            pre = conn.prepareStatement(sql);
            set = pre.executeQuery();
            if(set.next()){
                count = set.getInt("g");
            }
                                
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
                            
        return count;
    }
    @Override
    public boolean addGroup(Group_Entity group) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "insert into itjiandan_group(name,createdate,state)values(?,?,?)";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, group.getName());
            pre.setTimestamp(2, group.getCreatedate());
            pre.setInt(3, group.getState());
            int change = pre.executeUpdate();
            LogUtil.i("addgroup----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean delGroup(int[] id) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_group where id=?";
                            
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<id.length; i++){
                pre.setInt(1, id[i]);
                pre.addBatch();
            }
                                
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean upGroup(Group_Entity group) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "update itjiandan_group set name=?,reworkdate=?,state=? where id=?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, group.getName());
            pre.setTimestamp(2, group.getReworkdate());
            pre.setInt(3, group.getState());
            pre.setInt(4, group.getId());
            int change = pre.executeUpdate();
            LogUtil.i("addgroup----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public Group_Entity upGroupById(int id) {
        Connection conn = DBManager.getConnection();
        Group_Entity group = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select g.id,g.name,g.state from itjiandan_group as g where id=?";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, id);
                set = pre.executeQuery();
                if(set.next()){
                    group = new Group_Entity();
                    group.setId(set.getInt("g.id"));
                    group.setName(set.getString("g.name"));
                    group.setState(set.getInt("g.state"));
                }
                            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return group;
    }
    @Override
    public boolean addGroupAuthority(String name, int[] aid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select id from itjiandan_group where name=?";
                            
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1,name);
            set = pre.executeQuery();
            if(set.next()){
                sql = "insert into itjiandan_group_authority(gid,aid)values(?,?)";
                pre = conn.prepareStatement(sql);
                for(int i=0; i<aid.length; i++){
                    pre.setInt(1, set.getInt("id"));
                    pre.setInt(2, aid[i]);
                    pre.addBatch();
                }
                                    
                int[] change = pre.executeBatch();
                if(change.length >= 1){
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
                            
                            
        return false;
    }
    @Override
    public boolean delGroupAuthority(int[] gid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_group_authority where gid=?";
                            
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<gid.length; i++){
                pre.setInt(1, gid[i]);
                pre.addBatch();
            }
                                
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public List<Authority_Entity> getGroupAuthorityById(int gid) {
        Connection conn = DBManager.getConnection();
        List<Authority_Entity> authoritys = null;
        Authority_Entity authority = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select a.id,a.name,a.description,a.myform,a.method,a.path,a.state from " +
                "itjiandan_group_authority as ga join itjiandan_group" +
                " as g on ga.gid=? and g.id=? left join itjiandan_authority " +
                "as a on ga.aid=a.id";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, gid);
                pre.setInt(2, gid);
                set = pre.executeQuery();
                authoritys = new ArrayList<Authority_Entity>();
                while(set.next()){
                    authority = new Authority_Entity();
                    authority.setId(set.getInt("a.id"));
                    authority.setName(set.getString("a.name"));
                    authority.setDescription(set.getString("a.description"));
                    authority.setMyform(set.getString("a.myform"));
                    authority.setMethod(set.getString("a.method"));
                    authority.setPath(set.getString("a.path"));
                    authority.setState(set.getInt("a.state"));
                    authoritys.add(authority);
                }
                            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return authoritys;
    }
    @Override
    public Group_Entity getGroupByUserId(int uid) {
        Connection conn = DBManager.getConnection();
        Group_Entity group = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select g.id,g.name from itjiandan_user_group as ug join" +
                " itjiandan_user as u on u.id=? and ug.uid=? left join itjiandan_group" +
                " as g on ug.gid=g.id";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, uid);
                pre.setInt(2, uid);
                set = pre.executeQuery();
                group = new Group_Entity();
                if(set.next()){
                    group.setId(set.getInt("g.id"));
                    group.setName(set.getString("g.name"));
                }
                            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return group;
    }
                        
}






package com.itjiandan.daoimpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.itjiandan.dao.IAuthority_DAO;
import com.itjiandan.model.Authority_Entity;
import com.itjiandan.util.DBManager;
import com.itjiandan.util.LogUtil;
public class Authority_DAOImpl implements IAuthority_DAO {
    @Override
    public List<Authority_Entity> getPageAuthority(int pid,int page,int size) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        List<Authority_Entity> authoritys = null;
        ResultSet set = null;
                     
        int start = ( page - 1 ) * size;
                     
        String sql = "select * from itjiandan_authority where pid=? limit ?,?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, pid);
            pre.setInt(2, start);
            pre.setInt(3, size);
            set = pre.executeQuery();
            authoritys = new ArrayList<Authority_Entity>();
            while(set.next()){
                Authority_Entity authority = new Authority_Entity();
                authority.setId(set.getInt("id"));
                authority.setName(set.getString("name"));
                authority.setDescription(set.getString("description"));
                authority.setMyform(set.getString("myform"));
                authority.setMethod(set.getString("method"));
                authority.setPath(set.getString("path"));
                authority.setCreatedate(set.getTimestamp("createdate"));
                authority.setReworkdate(set.getTimestamp("reworkdate"));
                authority.setState(set.getInt("state"));
                authority.setPid(set.getInt("pid"));
                authoritys.add(authority);
                             
            }
                         
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
        return authoritys;
    }
    @Override
    public int getCountAuthority() {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        int count = 0;
        String sql = "select count(id) as a from itjiandan_authority";
        try {
            pre = conn.prepareStatement(sql);
            set = pre.executeQuery();
            if(set.next()){
                count = set.getInt("a");
            }
                         
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
        return count;
    }
    @Override
    public boolean addAuthority(Authority_Entity authority) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "insert into itjiandan_authority(name,description,myform,method,path,createdate,state,pid)values(?,?,?,?,?,?,?,?)";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, authority.getName());
            pre.setString(2, authority.getDescription());
            pre.setString(3, authority.getMyform());
            pre.setString(4, authority.getMethod());
            pre.setString(5, authority.getPath());
            pre.setTimestamp(6, authority.getCreatedate());
            pre.setInt(7, authority.getState());
            pre.setInt(8, authority.getPid());
            int change = pre.executeUpdate();
            LogUtil.i("addauthority----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean delAuthority(int[] id) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_authority where id=?";
                     
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<id.length; i++){
                pre.setInt(1, id[i]);
                pre.addBatch();
            }
                         
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean upAuthority(Authority_Entity authority) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "update itjiandan_authority set name=?,description=?,myform=?,method=?,path=?,reworkdate=?,state=?,pid=? where id=?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, authority.getName());
            pre.setString (2, authority.getDescription());
            pre.setString(3, authority.getMyform());
            pre.setString(4, authority.getMethod());
            pre.setString(5, authority.getPath());
            pre.setTimestamp(6, authority.getReworkdate());
            pre.setInt(7, authority.getState());
            pre.setInt(8, authority.getPid());
            pre.setInt(9, authority.getId());
            int change = pre.executeUpdate();
                         
            LogUtil.i("addauthority----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public Authority_Entity upAuthorityById(int id) {
        Connection conn = DBManager.getConnection();
        Authority_Entity authority = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select a.id,a.name,a.description,a.myform,a.method,a.path,a.state,a.pid from itjiandan_authority as a where id=?";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, id);
                set = pre.executeQuery();
                if(set.next()){
                    authority = new Authority_Entity();
                    authority.setId(set.getInt("a.id"));
                    authority.setName(set.getString("a.name"));
                    authority.setDescription(set.getString("a.description"));
                    authority.setMyform(set.getString("a.myform"));
                    authority.setMethod(set.getString("a.method"));
                    authority.setPath(set.getString("a.path"));
                    authority.setState(set.getInt("a.state"));
                    authority.setPid(set.getInt("a.pid"));
                }
                     
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return authority;
    }
}





package com.itjiandan.daoimpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.itjiandan.dao.ICommodityClass_DAO;
import com.itjiandan.model.CommodityClass_Entity;
import com.itjiandan.util.DBManager;
import com.itjiandan.util.LogUtil;
public class CommodityClass_DAOImpl implements ICommodityClass_DAO {
    @Override
    public List<CommodityClass_Entity> getPageCommodityClass(int pid, int page,
            int size) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        List<CommodityClass_Entity> commodityclasss = null;
        ResultSet set = null;
               
        int start = ( page - 1 ) * size;
               
        String sql = "select * from itjiandan_commodityclass where pid=? limit ?,?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, pid);
            pre.setInt(2, start);
            pre.setInt(3, size);
            set = pre.executeQuery();
            commodityclasss = new ArrayList<CommodityClass_Entity>();
            while(set.next()){
                CommodityClass_Entity commodityclass = new CommodityClass_Entity();
                commodityclass.setId(set.getInt("id"));
                commodityclass.setName(set.getString("name"));
                commodityclass.setPath(set.getString("path"));
                commodityclass.setCreatedate(set.getTimestamp("createdate"));
                commodityclass.setReworkdate(set.getTimestamp("reworkdate"));
                commodityclass.setState(set.getInt("state"));
                commodityclass.setPid(set.getInt("pid"));
                commodityclasss.add(commodityclass);
                       
            }
                   
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
        return commodityclasss;
    }
    @Override
    public int getCountCommodityClass() {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        int count = 0;
        String sql = "select count(id) as cc from itjiandan_commodityclass";
        try {
            pre = conn.prepareStatement(sql);
            set = pre.executeQuery();
            if(set.next()){
                count = set.getInt("cc");
            }
                   
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
        return count;
    }
    @Override
    public boolean addCommodityClass(CommodityClass_Entity commodityclass) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "insert into itjiandan_commodityclass(name,path,createdate,state,pid)values(?,?,?,?,?)";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, commodityclass.getName());
            pre.setString(2, commodityclass.getPath());
            pre.setTimestamp(3, commodityclass.getCreatedate());
            pre.setInt(4, commodityclass.getState());
            pre.setInt(5, commodityclass.getPid());
            int change = pre.executeUpdate();
            LogUtil.i("addcommodityclass----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean delCommodityClass(int[] id) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_commodityclass where id=?";
               
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<id.length; i++){
                pre.setInt(1, id[i]);
                pre.addBatch();
            }
                   
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean upCommodityClass(CommodityClass_Entity commodityclass) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "update itjiandan_commodityclass set name=?,path=?,reworkdate=?,state=?,pid=? where id=?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1, commodityclass.getName());
            pre.setString(2, commodityclass.getPath());
            pre.setTimestamp(3, commodityclass.getReworkdate());
            pre.setInt(4, commodityclass.getState());
            pre.setInt(5, commodityclass.getPid());
            pre.setInt(6, commodityclass.getId());
            int change = pre.executeUpdate();
                   
            LogUtil.i("upcommodityclass----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public CommodityClass_Entity upCommodityClassById(int id) {
        Connection conn = DBManager.getConnection();
        CommodityClass_Entity commodityclass = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select cc.id,cc.name,cc.path,cc.state,cc.pid from itjiandan_commodityclass as cc where id=?";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, id);
                set = pre.executeQuery();
                if(set.next()){
                    commodityclass = new CommodityClass_Entity();
                    commodityclass.setId(set.getInt("cc.id"));
                    commodityclass.setName(set.getString("cc.name"));
                    commodityclass.setPath(set.getString("cc.path"));
                    commodityclass.setState(set.getInt("cc.state"));
                    commodityclass.setPid(set.getInt("cc.pid"));
                }
               
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return commodityclass;
    }
    @Override
    public boolean delCommodityCommodityClassById(int[] ccid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_commodity_commodityclass where ccid=?";
               
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<ccid.length; i++){
                pre.setInt(1, ccid[i]);
                pre.addBatch();
            }
                   
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public String[] getCommodityClassById(int[] ccid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        String[] paths = new String[ccid.length];
        String sql = "select cc.path from itjiandan_commodityclass as cc where id=?";
        try {
                pre = conn.prepareStatement(sql);
                for(int i=0; i<ccid.length; i++){
                    pre.setInt(1, ccid[i]);
                    set = pre.executeQuery();
                    if(set.next()){
                        paths[i] = set.getString("cc.path");
                    }
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return paths;
    }
}




package com.itjiandan.daoimpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.itjiandan.dao.ICommodity_DAO;
import com.itjiandan.model.CommodityClass_Entity;
import com.itjiandan.model.Commodity_Entity;
import com.itjiandan.util.DBManager;
import com.itjiandan.util.LogUtil;
public class Commodity_DAOImpl implements ICommodity_DAO {
    @Override
    public List<Commodity_Entity> getPageCommodity(int page, int size) {
        Connection conn = DBManager.getConnection();
        List<Commodity_Entity> commoditys = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        int start = (page - 1) * size;
        String sql = "select * from itjiandan_commodity Limit ?,?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, start);
            pre.setInt(2, size);
            LogUtil.i("client to getPagecommodity start=" + start + ",size=" + size);
            commoditys = new ArrayList<Commodity_Entity>();
            set = pre.executeQuery();
            while (set.next()) {
                Commodity_Entity commodity = new Commodity_Entity();
                commodity.setId(set.getInt("id"));
                commodity.setNumber(set.getInt("number"));
                commodity.setName(set.getString("name"));
                commodity.setPrice(set.getFloat("price"));
                commodity.setPath(set.getString("path"));
                commodity.setLink(set.getString("link"));
                commodity.setCreatedate(set.getTimestamp("createdate"));
                commodity.setReworkdate(set.getTimestamp("reworkdate"));
                commodity.setState(set.getInt("state"));
                commoditys.add(commodity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.close(conn, pre, set);
        }
        return commoditys;
    }
    @Override
    public int getCountCommodity() {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        int count = 0;
        String sql = "select count(id) as c from itjiandan_commodity";
        try {
            pre = conn.prepareStatement(sql);
            set = pre.executeQuery();
            if(set.next()){
                count = set.getInt("c");
            }
              
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
        return count;
    }
    @Override
    public boolean addCommodity(Commodity_Entity commodity) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "insert into itjiandan_commodity(number,name,price,path,link,createdate,state)values(?,?,?,?,?,?,?)";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, commodity.getNumber());
            pre.setString(2, commodity.getName());
            pre.setFloat(3, commodity.getPrice());
            pre.setObject(4, commodity.getPath());
            pre.setString(5,commodity.getLink());
            pre.setTimestamp(6, commodity.getCreatedate());
            pre.setInt(7, commodity.getState());
            int change = pre.executeUpdate();
            LogUtil.i("addcommodity----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean delCommodity(int[] id) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_commodity where id=?";
          
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<id.length; i++){
                pre.setInt(1, id[i]);
                pre.addBatch();
            }
              
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public boolean upCommodity(Commodity_Entity commodity) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "update itjiandan_commodity set number=?,name=?,price=?,path=?,link=?,reworkdate=?,state=? where id=?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, commodity.getNumber());
            pre.setString(2, commodity.getName());
            pre.setFloat(3, commodity.getPrice());
            pre.setObject(4, commodity.getPath());
            pre.setString(5, commodity.getLink());
            pre.setTimestamp(6, commodity.getReworkdate());
            pre.setInt(7, commodity.getState());
            pre.setInt(8, commodity.getId());
            int change = pre.executeUpdate();
            LogUtil.i("addcommodity----->change="+change);
            if(change >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public Commodity_Entity upCommodityById(int id) {
        Connection conn = DBManager.getConnection();
        Commodity_Entity commodity = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select c.id,c.number,c.name,c.price,c.path,c.link,c.state from itjiandan_commodity as c where id=?";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, id);
                set = pre.executeQuery();
                if(set.next()){
                    commodity = new Commodity_Entity();
                    commodity.setId(set.getInt("c.id"));
                    commodity.setNumber(set.getInt("c.number"));
                    commodity.setName(set.getString("c.name"));
                    commodity.setPrice(set.getFloat("c.price"));
                    commodity.setPath(set.getString("c.path"));
                    commodity.setLink(set.getString("c.link"));
                    commodity.setState(set.getInt("c.state"));
                }
          
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return commodity;
    }
    @Override
    public boolean addCommodityCommodityClass(int num, int ccid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select id from itjiandan_commodity where number=?";
          
        try {
            pre = conn.prepareStatement(sql);
            pre.setInt(1, num);
            set = pre.executeQuery();
            if(set.next()){
                sql = "insert into itjiandan_commodity_commodityclass(cid,ccid)values(?,?)";
                pre = conn.prepareStatement(sql);
                    pre.setInt(1, set.getInt("id"));
                    pre.setInt(2, ccid);
                int change = pre.executeUpdate();
                if(change >= 1){
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, set);
        }
          
        return false;
    }
    @Override
    public boolean delCommodityCommodityClass(int[] cid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        String sql = "delete from itjiandan_commodity_commodityclass where cid=?";
          
        try {
            pre = conn.prepareStatement(sql);
            for(int i=0; i<cid.length; i++){
                pre.setInt(1, cid[i]);
                pre.addBatch();
            }
              
            int[] change = pre.executeBatch();
            if(change.length >= 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn, pre, null);
        }
        return false;
    }
    @Override
    public CommodityClass_Entity getCommodityCommodityClassById(int cid) {
        Connection conn = DBManager.getConnection();
        CommodityClass_Entity commodity = null;
        PreparedStatement pre = null;
        ResultSet set = null;
        String sql = "select cc.id,cc.name,cc.path,cc.state,cc.pid from " +
                "itjiandan_commodity_commodityclass as ccc join itjiandan_commodity" +
                " as c on ccc.cid=? and c.id=? left join itjiandan_commodityclass" +
                " as cc on ccc.ccid=cc.id";
        try {
                pre = conn.prepareStatement(sql);
                pre.setInt(1, cid);
                pre.setInt(2, cid);
                set = pre.executeQuery();
                if(set.next()){
                    commodity = new CommodityClass_Entity();
                    commodity.setId(set.getInt("cc.id"));
                    commodity.setName(set.getString("cc.name"));
                    commodity.setPath(set.getString("cc.path"));
                    commodity.setState(set.getInt("cc.state"));
                    commodity.setPid(set.getInt("cc.pid"));
                }
          
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return commodity;
    }
    @Override
    public String[] getCommodityById(int[] cid) {
        Connection conn = DBManager.getConnection();
        PreparedStatement pre = null;
        ResultSet set = null;
        String[] paths = new String[cid.length];
        String sql = "select c.path from itjiandan_commodity as c where id=?";
        try {
                pre = conn.prepareStatement(sql);
                for(int i=0; i<cid.length; i++){
                    pre.setInt(1, cid[i]);
                    set = pre.executeQuery();
                    if(set.next()){
                        paths[i] = set.getString("c.path");
                    }
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.close(conn,pre,set);
        }
        return paths;
    }
}