JDBC操作数据库基本（二）

注：保证自增长的字段必须是主键且字段类型必须是数字类型

    public static boolean insertEmp() throws Exception{
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        //定义语句
        String sql = "INSERT INTO emp(ename,job,sal) VALUES('jack','salesman',3500)";
        //取得发送sql语句的对象,并且获取自动增长主键
        PreparedStatement pst=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        //执行语句
        int row = pst.executeUpdate();
        //获取主键
        ResultSet rst = pst.getGeneratedKeys();
        while (rst.next()){
            System.out.println(rst.getInt(1));
        }
        //关闭资源
        ConnectionUtil.close(conn);
        return row>0;
    }

    public static boolean updateDeposit(double num) throws Exception{
        //是否成功的标记
        boolean flag = true;
        //取得数据库连接
        Connection conn = ConnectionUtil.getConnection();
        //SQL语句
        String sql1 = "UPDATE emp set deposit = deposit-"+num+"where ename = 'jack'";
        String sql2 = "UPDATE emp set deposit = deposit+"+num+"where ename = 'mike'";
        //转换数据库指令
        PreparedStatement pst1=conn.prepareStatement(sql1);
        PreparedStatement pst2=conn.prepareStatement(sql2);
        //取消自动提交。如果不设置，默认自动提交
        conn.setAutoCommit(false);
        //执行费语句
        try {
            pst1.executeUpdate();
            pst2.executeUpdate();
            //如果没异常，则手动提交
            conn.commit();
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
            //如果出现异常，回滚
            conn.rollback();
            // TODO: handle exception
        }finally{
            //关闭资源
            ConnectionUtil.close(conn);
        }

        return flag;
    }

public static boolean deletemp(Set ids)throws Exception{
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        //准备将要执行的SQL语句
        StringBuffer sql = new StringBuffer("DELETE FROM emp where empno in(");
        Iterator iterator = ids.iterator();
        while (iterator.hasNext()){
            Integer id = iterator.next();
            sql.append(id+",");
        }
        //最后一个empno后有一个逗号，且在语句后面加一个）
        sql.delete(sql.length()-1,sql.length());//删除最后一个逗号
        sql.append(")");
        //取得预编译的对象
        PreparedStatement pst=conn.prepareStatement(sql.toString());
        //执行语句
        int row = pst.executeUpdate();
        //关闭连接
        ConnectionUtil.close(conn);
        return row>0;
    }

    
    public  static  void getEmpByid(Integer id) throws SQLException {
        //取得连接对象
        Connection conn = ConnectionUtil.getConnection();
        try {
            //准备sql语句
            String sql = "SELECT empno,ename,job,hiredate,sal FROM emp WHERe empno ="+id;
            //转换指令
            PreparedStatement pst = conn.prepareStatement(sql);
            //执行语句
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                System.out.println("雇员编号："+rst.getObject("empno")+",雇员姓名"+rst.getObject("ename")+",入职日期"+
                        rst.getObject("hiredate")+",薪资"+rst.getObject("sal"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //关闭连接
            ConnectionUtil.close(conn);
        }

    }

 
    public static  void getEmpData(String kw,Integer cp,Integer ls) throws SQLException {
        if (kw==null){
            kw = "";
        }
        kw="'%"+kw+"%'";
        //处理页数和数据量
        //获取当前页第一行数据的索引。数据的索引从0开始
        //获取连接对象
        Connection conn = ConnectionUtil.getConnection();
        try {
            //准备sql语句
            String sql = "SELECt * FROM emp WHERe ename LIKE "+kw+" limit "+(cp-1)*ls+","+ls;//注意关键字之间的空格
            //指令处理
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                System.out.println("雇员编号："+rst.getObject("empno")+",雇员姓名"+rst.getObject("ename")+",入职日期"+
                        rst.getObject("hiredate")+",薪资"+rst.getObject("sal"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectionUtil.close(conn);
        }
    }