Exemplo n.º 1
0
 /**
  * 执行可变参数的SQL语句,进行保存、删除或更新操作
  *
  * @param sql 要执行的sql语句,?的赋值顺序必须与args数组的顺序相同
  * @param args 要赋值的参数列表
  * @return 操作结果,正数 成功,0 失败
  */
 public int saveOrUpdate(String sql, Object... args) {
   this.init();
   if (conn == null) {
     conn = JDBCUtil.getConn();
     if ((conn = JDBCUtil.getConn()) == null) {
       logger.error("getConn()失败!");
       return 0;
     }
   }
   try {
     ps = conn.prepareStatement(sql);
     for (int j = 0; j < args.length; j++) {
       ps.setObject(j + 1, args[j]);
     }
     logger.info("saveOrUpdate()成功!");
     return ps.executeUpdate();
   } catch (Exception e) {
     logger.error("sql执行失败" + e);
     e.printStackTrace();
   } finally {
     //			JDBCUtil.realse(conn, ps, null);
   }
   logger.error("saveOrUpdate()失败!");
   return 0;
 }
 public double deposit(int accno, double amt) {
   double nbal = 0.0;
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   try {
     con = JDBCUtil.getOracleConnection();
     ps = con.prepareStatement("select bal from accounts where accno=?");
     ps.setInt(1, accno);
     rs = ps.executeQuery();
     if (rs.next()) {
       double bal1 = rs.getDouble(1);
       nbal = bal1 + amt;
       ps = con.prepareStatement("update accounts set bal = ? where accno = ? ");
       ps.setDouble(1, nbal);
       ps.setInt(2, accno);
       ps.executeUpdate();
     } else {
       throw new InvalidAccountNumberException();
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     JDBCUtil.cleanup(con, ps, rs);
   }
   return nbal;
 }
Exemplo n.º 3
0
  public PessoaFisica findCpf(String Cpf) {
    try {
      PreparedStatement p =
          JDBCUtil.getConnection().prepareStatement("select * from tb_pessoa_fisica where cpf = ?");
      p.setString(1, Cpf);
      ResultSet res = p.executeQuery();
      if (res.next()) {
        return new PessoaFisica(
            res.getLong("id"),
            res.getString("nome"),
            res.getString("endereco"),
            res.getString("telefone"),
            res.getString("cpf"),
            res.getString("email"),
            res.getDate("data_nascimento"),
            res.getString("sexo"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }

    return null;
  }
Exemplo n.º 4
0
  @Override
  public List<PessoaFisica> findAll() {
    List<PessoaFisica> lista = new ArrayList<PessoaFisica>();
    try {
      ResultSet res =
          JDBCUtil.getConnection()
              .prepareStatement("select * from tb_pessoa_fisica")
              .executeQuery();
      while (res.next()) {
        lista.add(
            new PessoaFisica(
                res.getLong("id"),
                res.getString("nome"),
                res.getString("endereco"),
                res.getString("telefone"),
                res.getString("cpf"),
                res.getString("email"),
                res.getDate("data_nascimento"),
                res.getString("sexo")));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }

    return lista;
  }
Exemplo n.º 5
0
 /**
  * 查询符合条件的记录数
  *
  * @param sql 要执行的sql语句
  * @param args 给sql语句中的?赋值的参数列表
  * @return 符合条件的记录数
  */
 public long getCount(String sql, Object... args) {
   this.init();
   if (conn == null) {
     conn = JDBCUtil.getConn();
     if ((conn = JDBCUtil.getConn()) == null) {
       logger.error("getConn()失败!");
       return 0L;
     }
   }
   try {
     ps = conn.prepareStatement(sql);
     for (int i = 0; i < args.length; i++) {
       ps.setObject(i + 1, args[i]);
     }
     rs = ps.executeQuery();
     if (rs.next()) {
       return rs.getLong(1); // 返回结果集条数
     }
   } catch (SQLException e) {
     logger.error("sql执行失败" + e);
     e.printStackTrace();
   } finally {
     //				JDBCUtil.realse(conn, ps, rs);
   }
   return 0L;
 }
Exemplo n.º 6
0
  @Override
  public void delete(Aluno t) {
    try {

      PreparedStatement p =
          JDBCUtil.getConnection().prepareStatement("delete from tb_aluno where id = ?");
      p.setLong(1, t.getId());
      p.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 7
0
 @Override
 public void delete(PessoaFisica t) {
   // TODO Auto-generated method stub
   try {
     PreparedStatement p =
         JDBCUtil.getConnection().prepareStatement("delete from tb_pessoa_fisica where id = ?");
     p.setLong(1, t.getId());
     p.executeUpdate();
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     JDBCUtil.closeConnection();
   }
 }
Exemplo n.º 8
0
  @Override
  public void delete(CEP t) {
    try {

      String sql = "DELETE FROM tb_cep WHERE cep = ?";

      PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
      qb.setLong(1, t.getCep());

      qb.executeUpdate();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
  /**
   * Checks if the value of the param argument is a valid parameter position.
   *
   * <p>
   *
   * @param param position to check
   * @throws SQLException if the value of the param argument is not a valid parameter position
   */
  void checkRange(int param) throws SQLException {

    if (param < 1 || param > parameterCount) {
      String msg = param + " is out of range";

      throw JDBCUtil.outOfRangeArgument(msg);
    }
  }
  // #ifdef JAVA6
  @SuppressWarnings("unchecked")
  public <T> T unwrap(java.lang.Class<T> iface) throws java.sql.SQLException {

    if (isWrapperFor(iface)) {
      return (T) this;
    }

    throw JDBCUtil.invalidArgument("iface: " + iface);
  }
Exemplo n.º 11
0
  @Override
  public void insert(CEP t) {
    try {

      String sql = "INSERT INTO tb_cep" + "(cep, endereco, cidade)" + "VALUES(?, ?, ?)";

      PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
      qb.setLong(1, t.getCep());
      qb.setString(2, t.getEndereco());
      qb.setString(3, t.getCidade());

      qb.executeUpdate();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 12
0
  @Override
  public List<CEP> findAll() {
    List<CEP> listaCep = new ArrayList<CEP>();
    try {

      String sql = "select * from tb_cep";
      ResultSet res = JDBCUtil.getConnection().prepareStatement(sql).executeQuery();

      while (res.next()) {
        listaCep.add(
            new CEP(res.getLong("cep"), res.getString("endereco"), res.getString("cidade")));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
    return listaCep;
  }
Exemplo n.º 13
0
 /**
  * 以对象的形式保存或更新一个实体
  *
  * @param sql 要执行的sql语句
  * @param object 要保存或更新的实体对象
  * @param args 不需要赋值的列标组成的数组,例如sql语句 "insert into tbl_user values(seq_user.nextval,?,?,?)"应为1
  * @return 操作结果,1 成功,0 失败
  */
 public int saveEntity(String sql, Object object, int... args) {
   this.init();
   if (conn == null) {
     conn = JDBCUtil.getConn();
     if ((conn = JDBCUtil.getConn()) == null) {
       logger.error("getConn()失败!");
       return 0;
     }
   }
   try {
     ps = conn.prepareStatement(sql);
     Class<? extends Object> c = object.getClass();
     Field[] fields = object.getClass().getDeclaredFields();
     int temp = 1; // 正赋值的?的下标,最大下标为args的长度
     int colIndex = 1; // SQL语句中的当前字段下标
     int t = 0; // args数组的下标
     for (int j = 0; j < fields.length; j++) {
       Field field = fields[j]; // 得到某个声明属性
       String methodName =
           "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
       Method method = c.getMethod(methodName); // 得到了当前类中的一个method
       String rType = field.getType().getSimpleName().toString();
       if (t < args.length && colIndex == args[t]) {
         t++;
       } else if ("int".equals(rType) || "INTEGER".equals(rType)) {
         ps.setInt(temp++, (Integer) method.invoke(object));
       } else {
         ps.setObject(temp++, method.invoke(object));
       }
       colIndex++; // 更新索引下标
     }
     logger.info("saveEntity()插入成功!");
     return ps.executeUpdate();
   } catch (Exception e) {
     logger.error("sql执行失败" + e);
     e.printStackTrace();
   } finally {
     //			JDBCUtil.realse(conn, ps, null);
   }
   logger.error("saveEntity()插入失败!");
   return 0;
 }
Exemplo n.º 14
0
  @Override
  public void insert(Aluno t) {
    try {

      PreparedStatement p =
          JDBCUtil.getConnection()
              .prepareStatement(
                  "insert into tb_aluno"
                      + " (nome, cpf, matricula, data_aniversario)"
                      + "values (?,?,?,?)");
      p.setString(1, t.getNome());
      p.setString(2, t.getCpf());
      p.setString(3, t.getMatricula());
      p.setString(4, df.format(t.getAniversario()));
      p.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 15
0
  @Override
  public void update(CEP t) {
    try {

      String sql = "UPDATE tb_cep SET" + " cep = ?, endereco = ?, cidade = ? WHERE cep = ?";

      PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);

      qb.setLong(1, t.getCep());
      qb.setString(2, t.getEndereco());
      qb.setString(3, t.getCidade());
      qb.setLong(4, t.getCep());

      qb.executeUpdate();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 16
0
  @Override
  public void update(Aluno t) {
    try {

      PreparedStatement p =
          JDBCUtil.getConnection()
              .prepareStatement(
                  "update tb_aluno set nome = ?, cpf = ?,"
                      + " matricula = ?, data_aniversario = ? "
                      + "where id = ?");
      p.setString(1, t.getNome());
      p.setString(2, t.getCpf());
      p.setString(3, t.getMatricula());
      p.setString(4, df.format(t.getAniversario()));
      p.setLong(5, t.getId());
      p.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 17
0
  @Override
  public CEP find(Long id) {
    try {

      String sql = "select * from tb_cep WHERE cep = ?";

      PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
      qb.setLong(1, id);

      ResultSet res = qb.executeQuery();

      if (res.next()) {
        return new CEP(res.getLong("cep"), res.getString("endereco"), res.getString("cidade"));
      }

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
    return null;
  }
Exemplo n.º 18
0
  public List<CEP> findCep(String endereco) {
    List<CEP> listaCep = new ArrayList<CEP>();
    try {

      String sql = "select * from tb_cep WHERE endereco LIKE ?";
      PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);

      qb.setString(1, "%" + endereco + "%");

      ResultSet res = qb.executeQuery();

      while (res.next()) {
        listaCep.add(
            new CEP(res.getLong("cep"), res.getString("endereco"), res.getString("cidade")));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
    return listaCep;
  }
Exemplo n.º 19
0
  public Aluno find(String nome) {
    try {

      PreparedStatement p =
          JDBCUtil.getConnection().prepareStatement("select * from tb_aluno where nome like ?");
      p.setString(1, nome + "%");
      ResultSet res = p.executeQuery();
      if (res.next()) {
        return new Aluno(
            res.getLong("id"),
            res.getString("nome"),
            res.getString("cpf"),
            res.getString("matricula"),
            res.getDate("data_aniversario"));
      }
    } catch (Exception e) {
      // TODO: handle exception
    } finally {
      JDBCUtil.closeConnection();
    }
    return null;
  }
Exemplo n.º 20
0
  @Override
  public void insert(PessoaFisica t) {
    try {
      PreparedStatement p =
          JDBCUtil.getConnection()
              .prepareStatement(
                  "insert into tb_pessoa_fisica (nome, endereco, telefone, cpf, email, data_nascimento, sexo) "
                      + "values (?,?,?,?,?,?,?)");

      p.setString(1, t.getNome());
      p.setString(2, t.getEndereco());
      p.setString(3, t.getTelefone());
      p.setString(4, t.getCpf());
      p.setString(5, t.getEmail());
      p.setString(6, df.format(t.getDataNascimento()));
      p.setString(7, t.getSexo());
      p.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 21
0
  @Override
  public void update(PessoaFisica t) {
    try {
      PreparedStatement p =
          JDBCUtil.getConnection()
              .prepareStatement(
                  "update tb_pessoa_fisica set nome = ?, endereco = ?, telefone = ?, cpf = ?, email = ?, data_nascimento = ?, sexo = ? "
                      + "where id = ?");

      p.setString(1, t.getNome());
      p.setString(2, t.getEndereco());
      p.setString(3, t.getTelefone());
      p.setString(4, t.getCpf());
      p.setString(5, t.getEmail());
      p.setString(6, df.format(t.getDataNascimento()));
      p.setString(7, t.getSexo());
      p.setLong(8, t.getId());
      p.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
  }
Exemplo n.º 22
0
  @Override
  public List<Aluno> findAll() {
    ArrayList<Aluno> lista = new ArrayList<Aluno>();
    try {

      ResultSet res =
          JDBCUtil.getConnection().prepareStatement("select * from tb_aluno").executeQuery();
      while (res.next()) {
        lista.add(
            new Aluno(
                res.getLong("id"),
                res.getString("nome"),
                res.getString("cpf"),
                res.getString("matricula"),
                res.getDate("data_aniversario")));
      }

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection();
    }
    return lista;
  }
Exemplo n.º 23
0
 public static ResultSet executeQuery(String sql) {
   return JDBCUtil.executeQuery(sql, new Object[] {});
 }
Exemplo n.º 24
0
 private void init() {
   if (this.conn == null) {
     this.conn = JDBCUtil.getConn();
   }
 }
Exemplo n.º 25
0
 public static int executeUpdate(String sql, Object[] objs) {
   return JDBCUtil.executeUpdate(sql, objs);
 }
Exemplo n.º 26
0
  private Result newColumnResult(long position, int count) throws SQLException {

    int mArraySize = 0;
    String mArrayStruct = data.getTypeStructure();
    ArrayList<Integer> dimSize = getDimSize(mArrayStruct);
    ArrayList<Integer> iterIndex = new ArrayList<>(); // go through all the array values

    for (int i = 0; i < dimSize.size(); i++) {
      mArraySize += dimSize.get(i);
    }

    // tests if the required elements exist. The limits are determined
    // by computing the product of each dimension's size
    if (!JDBCClobClient.isInLimits(mArraySize, position, count)) {
      throw JDBCUtil.outOfRangeArgument();
    }

    Type[] types = new Type[2];

    types[0] = Type.SQL_VARCHAR;
    types[1] = elementType;

    ResultMetaData meta = ResultMetaData.newSimpleResultMetaData(types);

    meta.columnLabels = new String[] {"C1", "C2"};
    meta.colIndexes = new int[] {-1, -1};
    meta.columns = new ColumnBase[2];

    ColumnBase column = new ColumnBase("", "", "", "");

    column.setType(types[0]);

    meta.columns[0] = column;
    column = new ColumnBase("", "", "", "");

    column.setType(types[1]);

    meta.columns[1] = column;

    RowSetNavigatorClient navigator = new RowSetNavigatorClient();

    for (int i = (int) position; i < position + count; i++) {
      Object[] rowData = new Object[2];

      rowData[0] = Integer.valueOf(i + 1);
      try {
        rowData[1] = data.getCell(new RasPoint(1, 1));
      } catch (RasDimensionMismatchException | RasIndexOutOfBoundsException e) {
        System.err.println("Can't get the cell at point (1, 1)");
        e.printStackTrace();
      }

      navigator.add(rowData);
    }

    Result result = Result.newDataResult(meta);

    result.setNavigator(navigator);

    return result;
  }
Exemplo n.º 27
0
 /**
  * 释放连接资源
  *
  * @param conn 数据库连接池
  * @param ps PreparedStatement
  * @param rs ResultSet
  */
 public void release() {
   JDBCUtil.realse(conn, ps, rs);
 }
 @Test
 public void testQuery() throws Exception {
   JDBCUtil.executeQuery(conn, "SELECT * FROM G1");
 }
Exemplo n.º 29
0
  /**
   * 查询实体对象的,并封装到一个集合
   *
   * @param <T> 要查询的对象的集合
   * @param sql 要执行的sql语句
   * @param clazz 要查询的对象的类型
   * @param args 给sql语句中的?赋值的参数列表
   * @return 要查询的类的集合,无结果时返回null
   */
  @SuppressWarnings("unchecked")
  public <T> List<T> executeQuery(String sql, Class<T> clazz, Object... args) {
    this.init();
    List<Object> list = new ArrayList<Object>();
    if (conn == null) {
      conn = JDBCUtil.getConn();
      if ((conn = JDBCUtil.getConn()) == null) {
        logger.error("getConn()失败!");
        return null;
      }
    }
    try {
      ps = conn.prepareStatement(sql);
      for (int i = 0; i < args.length; i++) {
        ps.setObject(i + 1, args[i]);
      }
      rs = ps.executeQuery();
      Field[] fs = clazz.getDeclaredFields(); // 获得clazz类的所有声明的字段
      String[] colNames = new String[fs.length]; // 保存属性名
      String[] rTypes = new String[fs.length]; // 属性类型
      Method[] methods = clazz.getMethods();
      while (rs.next()) {
        for (int i = 0; i < fs.length; i++) {
          Field f = fs[i];
          String colName = f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);
          //					System.out.println(colName);
          colNames[i] = colName;
          String rType = f.getType().getSimpleName();
          rTypes[i] = rType;
        }

        Object object = (T) clazz.newInstance();
        for (int i = 0; i < colNames.length; i++) {
          String colName = colNames[i];
          String methodName = "set" + colName;
          // 查找并调用对应的setter方法赋
          for (Method m : methods) {
            if (methodName.equals((m.getName()))) {
              // 如果抛了参数不匹配异常,检查JavaBean中该属性类型,并添加else分支进行处理
              if ("int".equals(rTypes[i]) || "Integer".equals(rTypes[i])) {
                m.invoke(object, rs.getInt(colName));
              } else if ("Date".equals(rTypes[i])) {
                m.invoke(object, rs.getDate(colName));
              } else if ("Timestamp".equals(rTypes[i])) {
                m.invoke(object, rs.getTimestamp(colName));
              } else {
                m.invoke(object, rs.getObject(colName));
              }
              break;
            }
          }
        }
        list.add(object);
      }
      logger.info("executeQuery()成功!");
      return (List<T>) list;
    } catch (Exception e) {
      logger.error("sql执行失败" + e);
      e.printStackTrace();
    } finally {
      //			JDBCUtil.realse(conn, ps, rs);
    }
    logger.error("executeQuery()失败!");
    return null;
  }
Exemplo n.º 30
0
  private void checkClosed() throws SQLException {

    if (closed) {
      throw JDBCUtil.sqlException(ErrorCode.X_07501);
    }
  }