Example #1
0
/** Created by jose.espinoza.lopez on 11/13/2015. */
public class DetalleDAO {
  private static final MysqlDataSource ds = ConnectionFactory.getDataSource();
  private static Connection conn;
  private static PreparedStatement stmt;
  private static ResultSet rs;

  public static boolean saveDetalle(Detalle a) {
    try {
      conn = ds.getConnection();
      stmt = conn.prepareStatement("call savedetalle(?,?,?,?,?)");
      Date fech = new Date(a.getFecha().getTime());
      stmt.setInt(1, a.getFormaDePago().getId());
      stmt.setInt(2, a.getUsuario().getIdUsuario());
      stmt.setDate(3, fech);
      stmt.setDouble(4, a.getTotal());
      stmt.setTime(5, new java.sql.Time(a.getHora().toDateTimeToday().getMillis()));
      rs = stmt.executeQuery();
      while (rs.next()) {
        a.setId(rs.getInt(1));
      }
      stmt = conn.prepareStatement("call saveproductosventa(?,?)");
      for (Producto x : a.getProductos()) {
        stmt.setInt(1, a.getId());
        stmt.setInt(2, x.getIdProducto());
        stmt.execute();
      }
      rs.close();
      stmt.close();
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return false;
  }
}
Example #2
0
  public static int insertUser(Users u) {
    int status = 0;
    try {
      conn = ConnectionFactory.getCon();
      pst = conn.prepareStatement("insert into test VALUES (?,?,?,?,?)");
      pst.setInt(1, u.getId());
      pst.setString(2, u.getLogin());
      pst.setString(3, u.getPassword());
      pst.setString(4, u.getLastName());
      pst.setString(5, u.getFirstName());
      status = pst.executeUpdate();
      conn.close();
    } catch (Exception ex) {
      System.out.println(ex);
    }

    return status;
  }
Example #3
0
  public static List<Users> getUsers() throws SQLException {

    conn = ConnectionFactory.getCon();
    pst =
        conn.prepareStatement(
            "SELECT test.id, test.login,test.password,test.firstname,test.lastname FROM test");
    ResultSet resultSet = pst.executeQuery();
    ArrayList<Users> user = new ArrayList<Users>();

    while (resultSet.next()) {

      int id = resultSet.getInt(1);
      String login = resultSet.getString(2);
      String password = resultSet.getString(3);
      String firstName = resultSet.getString(4);
      String lastName = resultSet.getString(5);
      user.add(new Users(id, login, password, firstName, lastName));
    }
    conn.close();

    return user;
  }
Example #4
0
  public Map<String, List<String>> querySchema(DBConfig config) {
    Map<String, List<String>> map = new HashMap<String, List<String>>();
    List<String> schemaList = config.getSchemaList();
    Connection conn = null;
    DatabaseMetaData databaseMetaData = null;
    try {
      conn = ConnectionFactory.getInstance().getConnection(config);
      databaseMetaData = conn.getMetaData();
    } catch (Exception e) {
      e.printStackTrace();
      return map;
    }

    for (String schema : schemaList) {
      List<String> tableList = new ArrayList<String>();
      try {
        String localSchema = schema;
        if (databaseMetaData.storesLowerCaseIdentifiers()) {
          localSchema = localSchema == null ? null : localSchema.toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
          localSchema = localSchema == null ? null : localSchema.toUpperCase();
        }

        ResultSet rs = databaseMetaData.getTables(null, localSchema, null, null);
        while (rs.next()) {
          String tableName = rs.getString("TABLE_NAME");
          tableList.add(tableName);
          // System.out.println(tableName);
        }
        map.put(schema, tableList);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    return map;
  }
Example #5
0
  public static String checkUser(String login, String password) throws SQLException {
    String FIO = "";
    conn = ConnectionFactory.getCon();

    pst =
        conn.prepareStatement(
            "SELECT  test.login,test.password, test.firstname, test.lastname  FROM test WHERE test.login =? AND  test.password =?");
    pst.setString(1, login);
    pst.setString(2, password);
    ResultSet resultSet = pst.executeQuery();
    boolean myResultSetEmpty = isMyResultSetEmpty(resultSet); // если нет пользователя то true
    System.out.println(myResultSetEmpty);
    if (myResultSetEmpty == false) {

      resultSet.next();
      FIO = resultSet.getString(3) + "" + resultSet.getString(4);
      return FIO;
    }
    System.out.println(pst.toString());
    System.out.println(resultSet);
    conn.close();
    System.out.println(FIO);
    return FIO;
  }
Example #6
0
  public List<Table> queryFields(DBConfig config) {
    List<Table> tableInfoList = new ArrayList<Table>();
    Connection conn = null;
    try {
      List<TableConfiguration> tableList = (List<TableConfiguration>) config.getTableList();
      if (tableList == null || tableList.size() <= 0) return tableInfoList;

      conn = ConnectionFactory.getInstance().getConnection(config);
      DatabaseMetaData databaseMetaData = conn.getMetaData();
      for (TableConfiguration table : tableList) {
        Table tableInfo = new Table();

        String localCatalog = table.getCatalog();
        String localSchema = table.getSchema();
        String localTableName = table.getTableName();
        if (databaseMetaData.storesLowerCaseIdentifiers()) {
          localCatalog = localCatalog == null ? null : localCatalog.toLowerCase();
          localSchema = localSchema == null ? null : localSchema.toLowerCase();
          localTableName = localTableName == null ? null : localTableName.toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
          localCatalog = localCatalog == null ? null : localCatalog.toUpperCase();
          localSchema = localSchema == null ? null : localSchema.toUpperCase();
          localTableName = localTableName == null ? null : localTableName.toUpperCase();
        }

        Statement stmt = conn.createStatement();
        ResultSet tableRs = stmt.executeQuery("SHOW CREATE TABLE " + localTableName);
        if (tableRs != null && tableRs.next()) {
          String create = tableRs.getString(2);
          String comment = parse(create);
          tableInfo.setComment(comment);
        }

        ResultSet rs = databaseMetaData.getColumns(localCatalog, localSchema, localTableName, null);
        tableInfo.setSerialVersionUID(System.nanoTime() + "L");
        while (rs.next()) {
          tableInfo.setCatalog(rs.getString("TABLE_CAT"));
          tableInfo.setSchema(rs.getString("TABLE_SCHEM"));
          tableInfo.setName(rs.getString("TABLE_NAME"));
          tableInfo.setCode(rs.getString("TABLE_NAME"));

          Column introspectedColumn = new Column();
          introspectedColumn.setTableAlias(table.getTableName());
          introspectedColumn.setName(rs.getString("COLUMN_NAME"));
          introspectedColumn.setJdbcType(rs.getInt("DATA_TYPE"));
          introspectedColumn.setDataType(
              JdbcTypeNameTranslator.getJdbcTypeName(rs.getInt("DATA_TYPE"))); // $NON-NLS-1$
          introspectedColumn.setLength(rs.getInt("COLUMN_SIZE")); // $NON-NLS-1$
          introspectedColumn.setCode(rs.getString("COLUMN_NAME"));
          /*introspectedColumn.setActualColumnName(rs
          .getString("COLUMN_NAME"));*/
          //$NON-NLS-1$
          introspectedColumn.setNullable(
              rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); // $NON-NLS-1$
          introspectedColumn.setScale(rs.getInt("DECIMAL_DIGITS")); // $NON-NLS-1$
          introspectedColumn.setComment(rs.getString("REMARKS"));

          tableInfo.addColumn(introspectedColumn);

          PropertyBean pb = new PropertyBean();

          pb.setName(convertFirstUpper(getFieldName(rs.getString("COLUMN_NAME"))));
          pb.setType(JdbcType2Java.calculateJavaType(introspectedColumn));
          String importType = JdbcType2Java.importJavaType(introspectedColumn);
          if (importType != null && !importType.equals("")) {
            if (importType.indexOf("java.lang") < 0
                && !tableInfo.getImportList().contains(importType))
              tableInfo.getImportList().add(importType);
          }
          tableInfo.getPropertyBeanList().add(pb);
        }
        closeResultSet(rs);

        rs = databaseMetaData.getPrimaryKeys(localCatalog, localSchema, localTableName);
        while (rs.next()) {
          tableInfo.addPrimaryKeyColumn(rs.getString("COLUMN_NAME"));
        }
        closeResultSet(rs);
        tableInfoList.add(tableInfo);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }

    return tableInfoList;
  }