/** 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; } }
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; }
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; }
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; }
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; }
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; }