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