javaweb01

底层DBManager的数据封装

 1. 创建db.properties文件

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=scott
userpass=tiger
 2. 创建带有数据库连接池的数据库配置文件
主要负责找到数据库动（驱动串），连接数据库（连接字符串），数据库连接池（List集合里放了一批数据库连接对象）
package com.ruide.db;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBHelper {
    public static ComboPooledDataSource ds;
    static{
        ds=new ComboPooledDataSource();
        InputStream in=DBHelper.class.getClassLoader().getResourceAsStream("db.properties");
        Properties pro=new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }

        String driver=pro.getProperty("driver");
        String url=pro.getProperty("url");
        String username=pro.getProperty("username");
        String userpass=pro.getProperty("userpass");

        try {
            ds.setDriverClass(driver);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }

        ds.setJdbcUrl(url);
        ds.setUser(username);
        ds.setPassword(userpass);
    }
}
2．DBManager数据库管理类，主要负责获取连接对象，封装结果集，基本按照5大步骤做
（1）建连接（2）建预处理通道（3）sql语句绑定数据（4）得到结果集rs（5）封装结果集
package com.ruide.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ruide.mapper.IMapper;

public class DBManager{
    Connection conn=null;
    PreparedStatement pstm=null;
    ResultSet rs=null;
    public Connection getconn()throws SQLException{
        //返回连接池对象
        return DBHelper.ds.getConnection();
    }
    //1增删改函数
    public int executeUpdate(String sql,Object[]params)throws SQLException{
        //建连接
        try {
            conn=this.getconn();
            //建预处理通道
            pstm=conn.prepareStatement(sql);
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    pstm.setObject(i+1, params[i]);
                }
            }
            //执行sql
            int result=pstm.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
            try {
                pstm.close();
                conn.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    //2对象查询函数
    public List executeQueryObjectList(String sql,Object[]params,IMapper map)throws SQLException{
        try {
            //建连接
            conn=this.getconn();
            //建通道
            pstm=conn.prepareStatement(sql);
            //sql语句绑定数据
            if(params!=null){
                for(int i=0;i<params.length;i++){
                    pstm.setObject(i+1, params[i]);
                }
            }
            //得到结果集
            rs=pstm.executeQuery();
            //封装结果集
            List list=map.mapper(rs);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //3总条数查询
    public int executeTotal(String sql,Object[]params)throws SQLException{
        try {
            //建连接
            conn=this.getconn();
            //建预处理通道
            pstm=conn.prepareStatement(sql);
            //sql语句绑定数据
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    pstm.setObject(i+1, params[i]);
                }
            }
            //得到结果集
            rs=pstm.executeQuery();
            //处理结果集
            rs.next();
            int total=rs.getInt(1);
            return total;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //4.List集合的 数组方式封装结果集
    public List<Object[]> executeQueryArrayList(String sql,Object[] params)throws SQLException{
        try {
            conn=this.getconn();
            pstm=conn.prepareStatement(sql);
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    pstm.setObject(i+1, params[i]);
                }
            }
            rs=pstm.executeQuery();
            ResultSetMetaData rsmd=rs.getMetaData();
            int count=rsmd.getColumnCount();
            List<Object[]> list=new ArrayList<Object[]>();
            while(rs.next()){
                Object[] obj=new Object[count];
                for (int i = 0; i < obj.length; i++) {
                    obj[i]=rs.getObject(i+1);
                }
                list.add(obj);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //5.List集合的  HashMap方式封装结果集
    public List<Map<String,Object>> executeQueryMapList(String sql,Object []params)throws SQLException{
        try {
            conn=this.getconn();
            pstm=conn.prepareStatement(sql);
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    pstm.setObject(i+1, params[i]);
                }
            }
            rs=pstm.executeQuery();
            ResultSetMetaData rsmd=rs.getMetaData();
            int count=rsmd.getColumnCount();
            List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
            while(rs.next()){
                Map<String,Object> map=new HashMap<String,Object>();
                for (int i = 0; i < count; i++) {
                    String name=rsmd.getColumnName(i+1);
                    Object values=rs.getObject(name);
                    map.put(name,values);
                }

            list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //6.二维数组方式封装数据
    public Object[][] executeQueryArray(String sql,Object []params)throws SQLException{
        try {
            List<Object[]> list=this.executeQueryArrayList(sql, params);
            Object[][] obj=new Object[list.size()][];
            for(int i=0;i<obj.length;i++){
                obj[i] =list.get(i);
            }
            return obj;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw e;
        }finally{
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }   
}
2.  VO包中主要是提取出的对象属性，里面有getXXX方法和setXXX方法
package com.ruide.vo;

public class Dept {
    private int deptno;
    private String dname;
    private String loc;
    public int getDeptno() {
        return deptno;
    }
    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
    public String getDname() {
        return dname;
    }
    public void setDname(String dname) {
        this.dname = dname;
    }
    public String getLoc() {
        return loc;
    }
    public void setLoc(String loc) {
        this.loc = loc;
    }   
}

package com.ruide.vo;

public class Emp {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private String hiredate;
    private double sal;
    private double comm;
    private int deptno;
    public int getEmpno() {
        return empno;
    }
    public void setEmpno(int empno) {
        this.empno = empno;
    }
    public String getEname() {
        return ename;
    }
    public void setEname(String ename) {
        this.ename = ename;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
    public int getMgr() {
        return mgr;
    }
    public void setMgr(int mgr) {
        this.mgr = mgr;
    }
    public String getHiredate() {
        return hiredate;
    }
    public void setHiredate(String hiredate) {
        this.hiredate = hiredate;
    }
    public double getSal() {
        return sal;
    }
    public void setSal(double sal) {
        this.sal = sal;
    }
    public double getComm() {
        return comm;
    }
    public void setComm(double comm) {
        this.comm = comm;
    }
    public int getDeptno() {
        return deptno;
    }
    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
}

3.  mapper包中主要负责封装结果集，供List集合的对象方式调用
package com.ruide.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public interface IMapper {
    public List mapper(ResultSet rs)throws SQLException;
}

package com.ruide.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ruide.vo.Dept;
public class DeptMapper implements IMapper{
    public List mapper(ResultSet rs)throws SQLException{
        List<Dept> list=new ArrayList<Dept>();
        while(rs.next()){
            int deptno=rs.getInt(1);
            String dname=rs.getString(2);
            String loc=rs.getString(3);
            Dept d=new Dept();
            d.setDeptno(deptno);
            d.setDname(dname);
            d.setLoc(loc);
            list.add(d);
        }
        return list;
    }
}

package com.ruide.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ruide.vo.Emp;

public class EmpMapper implements IMapper{
    public List mapper(ResultSet rs)throws SQLException{
        List<Emp> list=new ArrayList();
        while(rs.next()){
            int empno=rs.getInt(1);
            String ename=rs.getString(2);
            String job=rs.getString(3);
            int mgr=rs.getInt(4);
            //String hiredate=rs.getString(5);
            java.sql.Date d=rs.getDate(5);
            //java.sql.Date 是 java.util.Date子类
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            String hiredate=sdf.format(d);

            double sal=rs.getDouble(6);
            double comm=rs.getDouble(7);
            int deptno=rs.getInt(8);
            Emp e=new Emp();
            e.setEmpno(empno);
            e.setEname(ename);
            e.setJob(job);
            e.setMgr(mgr);
            e.setHiredate(hiredate);
            e.setSal(sal);
            e.setComm(comm);
            e.setDeptno(deptno);
            list.add(e);
        }
        return list;
    }
}

4. DAO模式的包主要负责具体的SQL语句，和参数变量，通过调用DBManager中的方法，来得到封装好的结果集，供用户调用。
该包中共有9个方法3个增（save）删（delete）改（merge）和6个查询方法
书写步骤：（1）写SQL语句（2）初始化数组（3）创建DBManager对象（4）调用方法

package com.ruide.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.ruide.db.DBManager;
import com.ruide.mapper.DeptMapper;
import com.ruide.mapper.IMapper;
import com.ruide.vo.Dept;

public class DeptDAO {
    //增加
    public int save(Dept d)throws SQLException{
        String sql=" insert into dept values(?,?,?) ";
        Object []params={d.getDeptno(),d.getDname(),d.getLoc()};
        DBManager db=new DBManager();
        int hang=db.executeUpdate(sql, params);
        return hang;
    }
    //修改
    public int merge(Dept d)throws SQLException{
        String sql="update dept set dname=?,loc=? where deptno=?";
        Object []params={d.getDname(),d.getLoc(),d.getDeptno()};
        DBManager db=new DBManager();
        return db.executeUpdate(sql, params);
    }
    //删除
    public int delete(int deptno)throws SQLException{
        String sql=" delete from dept where deptno=? ";
        Object []params={deptno};
        DBManager db=new DBManager();
        return db.executeUpdate(sql, params);
    }
    //对象查询语句
    public List queryObjectList(int pagesize,int pagenow)throws SQLException{
        String sql="";
        sql+=" select A.deptno,A.dname,A.loc from "; 
        sql+=" (select d.*,rownum rn from dept d) A ";
        sql+=" where rn>? and rownum<=? ";
        Object[] params={(pagenow-1)*pagesize,pagesize};
        IMapper mapper=new DeptMapper();
        DBManager db=new DBManager();
        List list=db.executeQueryObjectList(sql,params, mapper);
        return list;
    }
    //总条数查询
    public int querytotal()throws SQLException{
        String sql="select count(*) from dept ";
        DBManager db=new DBManager();
        int total=db.executeTotal(sql, null);
        return total;
    }
    //list 数组查询
    public List<Object[]> queryObjectArrayList(int pagesize,int pagenow)throws SQLException{
        String sql="";
        sql+=" select A.* from "; 
        sql+=" (select d.*,rownum rn from dept d) A ";
        sql+=" where rn>? and rownum<=? ";
        Object []params={(pagenow-1)*pagesize,pagesize};
        DBManager db=new DBManager();
        return db.executeQueryArrayList(sql, params);
    }
    //list HashMap查询
    public List<Map<String,Object>> queryMapList(int pagesize,int pagenow)throws SQLException{
        String sql="";
        sql+="select A.* from "; 
        sql+=" (select d.*,rownum rn from dept d) A ";
        sql+=" where rn>? and rownum<=? ";
        Object[] params={(pagenow-1)*pagesize,pagesize};
        DBManager db=new DBManager();
        return db.executeQueryMapList(sql, params);
    }
    //二维数组查询
    public Object[][] queryObjectArray(int pagesize,int pagenow)throws SQLException{
        String sql="";
        sql+="select A.* from "; 
        sql+=" (select d.*,rownum rn from dept d) A ";
        sql+=" where rn>? and rownum<=? ";
        Object[]params={(pagenow-1)*pagesize,pagesize};
        DBManager db=new DBManager();
        return db.executeQueryArray(sql, params);
    }
    //特定对象查询（根据主键）
    public Dept queryById(int deptno)throws SQLException{
        String sql="select * from dept where deptno=? ";
        Object[] params={deptno};
        IMapper mapper=new DeptMapper();
        DBManager db=new DBManager();
        List<Dept> list=db.executeQueryObjectList(sql, params, mapper);
        Dept d=list.get(0);
        return d;
    }
    public List queryAll()throws SQLException{
        String sql="select *from dept";
        IMapper mapper=new DeptMapper();
        DBManager db=new DBManager();
        List<Dept> list=db.executeQueryObjectList(sql, null, mapper);
        return list;
    }
}


package com.ruide.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.ruide.db.DBManager;
import com.ruide.mapper.EmpMapper;
import com.ruide.mapper.IMapper;
import com.ruide.vo.Emp;

public class EmpDAO {
    //1.增加
    public int save(Emp e)throws SQLException{
        String sql=" insert into emp values(?,?,?,?,to_date(?,'yyyy-MM-dd'),?,?,?) ";
        Object[]params={e.getEmpno(),e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno()}; 
        DBManager db=new DBManager();
        return db.executeUpdate(sql, params);
    }
    //2.修改
    public int merge(Emp e)throws SQLException{
        String sql="update emp set ename=?,job=?,mgr=?,hiredate=to_date(?,'yyyy-MM-dd'),sal=?,comm=?,deptno=? where empno=?";
        Object[]params={e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno(),e.getEmpno()};
        DBManager db=new DBManager();
        return db.executeUpdate(sql, params);
    }
    //3.删除
    public int delete(int empno)throws SQLException{
        String sql="delete from emp where empno=?";
        Object[]params={empno};
        DBManager db=new DBManager();
        return db.executeUpdate(sql, params);
    }
    //4.总条数查询
    public int queryTotal()throws SQLException{
        String sql=" select count(*) from emp ";
        DBManager db=new DBManager();
        int total=db.executeTotal(sql, null);
        return total;   
    }
    //5.根据主键查询
    public Emp queryById(int empno)throws SQLException{
        String sql=" select *from emp where empno=? ";
        Object []params={empno};
        IMapper mapper=new EmpMapper();
        DBManager db=new DBManager();
        List<Emp> list=db.executeQueryObjectList(sql, params, mapper);
        Emp e=list.get(0);
        return e;
    }

    //6.List 集合对象查询
    public List queryObjectList(int pagesize,int pagenow)throws SQLException{
        String sql="";  
        sql+=" select A.empno,A.ename,A.job,A.mgr,A.hiredate,A.sal,A.comm,A.deptno from ";
        sql+=" (select rownum rn,e.* from emp e ) A ";
        sql+=" where rn>? and rownum<=? ";
        Object[] params={(pagenow-1)*pagesize,pagesize};
        IMapper mapper=new EmpMapper();
        DBManager db=new DBManager();
        List<Emp> list=db.executeQueryObjectList(sql,params, mapper);
        return list;
    }
    //7.List 集合数组的查询
    public List<Object[]> queryObjectArrayList(int pagesize,int pagenow)throws SQLException{
        String sql="";

        sql+=" select A.* from ";
        sql+=" (select e.*,rownum rn from emp e ) A ";
        sql+=" where rn>? and rownum<=?";
        Object []params={(pagenow-1)*pagesize,pagesize};
        DBManager db=new DBManager();
        return db.executeQueryArrayList(sql, params);
    }
    //8.List 集合Map方式查询
    public List<Map<String,Object>> queryMapList(int pagesize,int pagenow)throws SQLException{
        String sql="";
        sql+=" select A.* from ";
        sql+=" (select e.*,rownum rn from emp e ) A ";
        sql+=" where rn>? and rownum<=? ";
        Object[]params={(pagenow-1)*pagesize,pagesize};
        DBManager db=new DBManager();
        return db.executeQueryMapList(sql, params);     
    }
    //二维数组方式查询
    public Object[][] queryObjectArray(int pagesize,int pagenow)throws SQLException{
        String sql="";
        sql+=" select A.* from ";
        sql+=" (select e.*,rownum rn from emp e ) A ";
        sql+=" where rn>? and rownum<=? ";
        Object[]params={(pagenow-1)*pagesize,pagesize};
        DBManager db=new DBManager();
        Object[][] obj=db.executeQueryArray(sql, params);
        return obj;
    }
    public List queryAll()throws SQLException{
        String sql="select *from emp ";
        IMapper mapper=new EmpMapper();
        DBManager db=new DBManager();
        List<Emp> list=db.executeQueryObjectList(sql, null, mapper);
        return list;
    }
}


5.test包中主要是用户输入的数据，从来测试以上的方法
package com.ruide.test;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import com.ruide.dao.DeptDAO;
import com.ruide.dao.EmpDAO;
import com.ruide.vo.Dept;
import com.ruide.vo.Emp;

public class UserShuju {
    //dept增加
    public void dinsert(){
        System.out.println("对dept表的增加数据操作");
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要增加的部门编号");
        int deptno=sca.nextInt();
        System.out.println("请输入您要增加的部门名称");
        String dname=sca.next();
        System.out.println("请输入您要增加的部门地址");
        String loc=sca.next();
        Dept d=new Dept();
        d.setDeptno(deptno);
        d.setDname(dname);
        d.setLoc(loc);
        DeptDAO dao=new DeptDAO();
        try {
            int hang=dao.save(d);
            System.out.println("成功插入了"+hang+"条数据");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //dept 修改
    public void dupdate() {
        System.out.println("对dept表的修改操作");
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要修改的部门编号");
        int deptno=sca.nextInt();
        System.out.println("请输入您要修改的部门名称");
        String dname=sca.next();
        System.out.println("请输入您要修改的部门地址");
        String loc=sca.next();
        Dept d=new Dept();
        d.setDeptno(deptno);
        d.setDname(dname);
        d.setLoc(loc);
        DeptDAO dao=new DeptDAO();
        try {
            int hang=dao.merge(d);
            System.out.println("成功修改了"+hang+"条数据");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //dept 删除
    public void ddelete(){
        System.out.println("对dept表的删除操作");
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要删除的部门编号");
        int deptno=sca.nextInt();
        Dept d=new Dept();
        d.setDeptno(deptno);
        DeptDAO dao=new DeptDAO();
        try {
            int hang =dao.delete(d);
            System.out.println("成功删除了"+hang+"条数据");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //1.dept list Object 查询
    public void dObjectList(){
        System.out.println("使用List的对象方式查询");
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要查询的当前页数");
        int pagenow=sca.nextInt();
        System.out.println("请您输入每页大小");
        int pagesize=sca.nextInt();
        try {
            DeptDAO dao=new DeptDAO();
            int total=dao.querytotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页码"+pagenow);
            List<Dept> list=dao.queryObjectList(pagesize, pagenow);
            for(Dept d:list){
                System.out.println(d.getDeptno()+" "+d.getDname()+" "+d.getLoc());
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //2.dept 主键方式查询
    public void dByIdSelect(){
        System.out.println("使用主键查询特定对象");
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要查询的部门编号");
        int deptno=sca.nextInt();
        Dept d=new Dept();
        d.setDeptno(deptno);
        DeptDAO dao=new DeptDAO();
        try {
            d=dao.queryById(deptno);
            System.out.println(d.getDeptno()+" "+d.getDname()+" "+d.getLoc());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //3.List集合 ObjectArray方法查询
    public void dObjectArrayList(){
        System.out.println("使用List的数组方式查询");
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要查询的当前页码");
        int pagenow=sca.nextInt();
        System.out.println("请您输入每页大小");
        int pagesize=sca.nextInt();
        DeptDAO dao=new DeptDAO();
        try {
            int total=dao.querytotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页码"+pagenow);
            List<Object[]> list=dao.queryObjectArrayList(pagesize, pagenow);
            for(int i=0;i<list.size();i++){
                Object[] obj=list.get(i);
                for (int j = 0; j < obj.length; j++) {
                    System.out.print(obj[j]+" ");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //4.List集合 Map方式查询数据
    public  void dMapList(){
        System.out.println("使用List的Map方式查询");
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入每页大小");
        int pagesize=sca.nextInt();
        System.out.println("请您输入要查询的当前页码");
        int pagenow=sca.nextInt();
        try {
            DeptDAO dao=new DeptDAO();
            int total=dao.querytotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页"+pagenow);
            List<Map<String,Object>> list=dao.queryMapList(pagesize, pagenow);
            for (int i = 0; i < list.size(); i++) {
                Map<String,Object> map=list.get(i);
                Object deptno=map.get("DEPTNO");
                Object dname=map.get("DNAME");
                Object loc=map.get("LOC");
                System.out.println(deptno+" "+dname+" "+loc);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
    //5.二维数组方式查询
    public void dObjectArray() {
        System.out.println("使用二维数组方式查询");
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要查询的当前页码");
        int pagenow=sca.nextInt();
        System.out.println("请输入每页大小");
        int pagesize=sca.nextInt();
        DeptDAO dao=new DeptDAO();
        try {
            int total=dao.querytotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("数据总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页"+pagenow);
            Object[][]obj=dao.queryObjectArray(pagesize, pagenow);
            for (int i = 0; i < obj.length; i++) {
                Object[]arr=obj[i];
                for(int j=0;j<arr.length;j++){
                    System.out.print(arr[j]+" ");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    ///////////对Emp表的操作//////////////
    //1.增加
    public void empsave(){
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入要增加的员工编号");
        int empno=sca.nextInt();
        System.out.println("请输入要增加的员工姓名");
        String ename=sca.next();
        System.out.println("请输入要增加的员工工作");
        String job=sca.next();
        System.out.println("请输入要增加的经理编号");
        int mgr=sca.nextInt();
        System.out.println("请输入要增加的入职日期");
        String hiredate=sca.next();
        System.out.println("请输入要增加的工资");
        double sal=sca.nextDouble();
        System.out.println("请输入要增加的奖金");
        double comm=sca.nextDouble();
        System.out.println("请输入员工所属的部门编号");
        int deptno=sca.nextInt();
        Emp e=new Emp();
        e.setEmpno(empno);
        e.setEname(ename);
        e.setJob(job);
        e.setMgr(mgr);
        e.setHiredate(hiredate);
        e.setSal(sal);
        e.setComm(comm);
        e.setDeptno(deptno);
        EmpDAO edao=new EmpDAO();
        try {
            int hang=edao.save(e);
            System.out.println("成功增加了"+hang+"条数据");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }

    //2.修改
    public  void empmerge() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要修改的员工编号");
        int empno=sca.nextInt();
        System.out.println("请输入您要修改的员工名称");
        String ename=sca.next();
        System.out.println("请输入您要修改的员工工作");
        String job=sca.next();
        System.out.println("请输入您要修改的经理编号");
        int mgr=sca.nextInt();
        System.out.println("请输入您要修改的日期");
        String hiredate=sca.next();
        System.out.println("请输入您要修改的员工薪资");
        double sal=sca.nextDouble();
        System.out.println("请输入您要修改的员工奖金");
        double comm=sca.nextDouble();
        System.out.println("请输入您要修改的员工所属部门编号");
        int deptno=sca.nextInt();
        Emp e=new Emp();
        e.setEmpno(empno);
        e.setEname(ename);
        e.setJob(job);
        e.setMgr(mgr);
        e.setHiredate(hiredate);
        e.setSal(sal);
        e.setComm(comm);
        e.setDeptno(deptno);    
        EmpDAO edao=new EmpDAO();
        try {
            int hang=edao.merge(e);
            System.out.println("成功修改了"+hang+"条数据");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }

    //删除
    public  void empdelete() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要删除的员工编号");
        int empno=sca.nextInt();
        Emp e=new Emp();
        e.setEmpno(empno);
        EmpDAO edao=new EmpDAO();
        try {
            int hang=edao.delete(e);
            System.out.println("成功删除了"+hang+"条数据");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }
    //4.根据主键查询
    public  void empById() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要查询的员工编号");
        int empno=sca.nextInt();
        Emp e=new Emp();
        e.setEmpno(empno);
        EmpDAO edao=new EmpDAO();
        try {
            e=edao.queryById(empno);
            System.out.println(e.getEmpno()+" "+e.getEname()+" "+e.getJob()+" "+e.getMgr()+" "+e.getHiredate()+" "+e.getSal()+" "+e.getComm()+" "+e.getDeptno());
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }
    //5.对象查询
    public void empObjectList() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要查询的页码");
        int pagenow=sca.nextInt();
        System.out.println("请您输入每页大小");
        int pagesize=sca.nextInt();
        EmpDAO edao=new EmpDAO();
        try {
            int total=edao.queryTotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("数据总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页"+pagenow);
            List<Emp> list=edao.queryObjectList(pagesize, pagenow);
            for(int i=0;i<list.size();i++){
                Emp e=list.get(i);
                int empno=e.getEmpno();
                String ename=e.getEname();
                String job=e.getJob();
                int mgr=e.getMgr();
                String hiredate=e.getHiredate();
                double sal=e.getSal();
                double comm=e.getComm();
                int deptno=e.getDeptno();
                System.out.println(empno+" "+ename+" "+job+" "+mgr+" "+hiredate+" "+sal+" "+comm+" "+deptno);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //6.List集合的数组方式查询
    public  void empArrayList() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要查询的页码");
        int pagenow=sca.nextInt();
        System.out.println("请您输入每页的大小");
        int pagesize=sca.nextInt();
        try {
            EmpDAO edao=new EmpDAO();
            int total=edao.queryTotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页码"+pagenow);
            List<Object[]> list=edao.queryObjectArrayList(pagesize, pagenow);
            for (int i = 0; i < list.size(); i++) {
                Object[]obj=list.get(i);
                for (int j = 0; j < obj.length; j++) {
                    System.out.print(obj[j]+" ");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //7.List集合的Map方式查询
    public  void empMapList() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请您输入要查询的页码");
        int pagenow=sca.nextInt();
        System.out.println("请您输入每页的大小");
        int pagesize=sca.nextInt();
        try {
            EmpDAO edao=new EmpDAO();
            int total=edao.queryTotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页"+pagenow);
            List<Map<String,Object>> list=edao.queryMapList(pagesize, pagenow);
            for (int i = 0; i < list.size(); i++) {
                Map<String,Object> map=list.get(i);
                Object empno=map.get("EMPNO");
                Object ename=map.get("ENAME");
                Object job=map.get("JOB");
                Object mgr=map.get("MGR");
                Object hiredate=map.get("HIREDATE");
                Object sal=map.get("SAL");
                Object comm=map.get("COMM");
                Object deptno=map.get("DEPTNO");
                System.out.println(empno+" "+ename+" "+job+" "+mgr+" "+hiredate+" "+sal+" "+comm+" "+deptno);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }   
    }

    //8. 二维数组方式查询
    public  void empArray() {
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要查询的页码");
        int pagenow=sca.nextInt();
        System.out.println("请您输入每页的大小");
        int pagesize=sca.nextInt();
        try {
            EmpDAO edao=new EmpDAO();
            int total=edao.queryTotal();
            int pagetotal=(total-1)/pagesize+1;
            System.out.println("总页数"+pagetotal);
            System.out.println("数据总条数"+total);
            System.out.println("每页大小"+pagesize);
            System.out.println("当前页码"+pagenow);
            Object [][] obj=edao.queryObjectArray(pagesize, pagenow);
            for (int i = 0; i < obj.length; i++) {
                Object[]arr=obj[i];
                for (int j = 0; j < arr.length; j++) {
                    System.out.print(arr[j]+"  ");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
package com.ruide.test;

import java.util.Scanner;

public class UserXuanze {
    public void usercaidan(){
        Scanner sca=new Scanner(System.in);
        System.out.println("请输入您要操作的对象 1.部门表 2.员工表 3.退出");
        int b=sca.nextInt();
        UserXuanze ux=new UserXuanze();
        if(b==1){
            ux.deptxuanze();    
        }else if(b==2){
            ux.empxuanze();
        }else{
            System.exit(1);
        }
    }
    public void deptxuanze(){
        Scanner sca=new Scanner(System.in);
        System.out.println("请您选择针对Dept表的操作：1.保存数据 2.修改数据 3.删除数据 4.List的对象查询 5.根据主键的查询 ");
        System.out.println("6.List的数组方式查询  7.List的Map方式查询 8.二维数组方式查询 9.返回上一个菜单 10.退出操作");
        int d=sca.nextInt();
        UserShuju us=new UserShuju();
        if(d==1){
            us.dinsert();
            deptxuanze();
        }else if(d==2){
            us.dupdate();
            deptxuanze();
        }else if(d==3){
            us.ddelete();
            deptxuanze();
        }else if(d==4){
            us.dObjectList();
            deptxuanze();
        }else if(d==5){
            us.dByIdSelect();
            deptxuanze();
        }else if(d==6){
            us.dObjectArrayList();
            deptxuanze();
        }else if(d==7){
            us.dMapList();
            deptxuanze();
        }else if(d==8){
            us.dObjectArray();
            deptxuanze();
        }else if(d==9){
            usercaidan();
        }else{
            System.exit(1);
        }
    }   
    public void empxuanze(){
        Scanner sca=new Scanner(System.in);
        System.out.println("请您选择针对Emp表的操作：1.保存数据 2.修改数据 3.删除数据 4.List的对象查询 5.根据主键的查询 ");
        System.out.println("6.List的数组方式查询  7.List的Map方式查询 8.二维数组方式查询9.返回上一个菜单 10.退出操作");
        int e=sca.nextInt();
        UserShuju sj=new UserShuju();
        if(e==1){
            sj.empsave();
            empxuanze();
        }else if(e==2){
            sj.empmerge();
            empxuanze();
        }else if(e==3){
            sj.empdelete();
            empxuanze();
        }else if(e==4){
            sj.empObjectList();
            empxuanze();
        }else if(e==5){
            sj.empById();
            empxuanze();
        }else if(e==6){
            sj.empArrayList();
            empxuanze();
        }else if(e==7){
            sj.empMapList();
            empxuanze();
        }else if(e==8){
            sj.empArray();
            empxuanze();
        }else if(e==9){
            usercaidan();
        }else{
            System.exit(1);
        }
    }       
}

package com.ruide.test;

import java.util.Scanner;


public class Testmain {
    public static void main(String[] args) {
        UserXuanze us=new UserXuanze();
        us.usercaidan();
    }
}