public boolean addLeasedVehicle(LeasedVehicle leasedVehicle)
throws SQLException, ClassNotFoundException, FileNotFoundException {
Connection connection = DataBaseConnection.getConnection();
PreparedStatement statement =
connection.prepareStatement(
"INSERT INTO LEASEDVEHICLE(leasingno,vehiclecategoryid,modelandname,chassisno,registrationno,engineno,manufacturedyear,estimatedprice,colour,fuelused,cylindercapacity,wheelbase,licenseddate,vehiclephoto) "
+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
statement.setString(1, leasedVehicle.getLeasingNo());
statement.setString(2, leasedVehicle.getVehicleCategoryId());
statement.setString(3, leasedVehicle.getModelAndName());
statement.setString(4, leasedVehicle.getChassisNo());
statement.setString(5, leasedVehicle.getRegistrationNo());
statement.setString(6, leasedVehicle.getEngineNo());
statement.setInt(7, leasedVehicle.getManufacturedYear());
statement.setDouble(8, leasedVehicle.getEstimatedPrice());
statement.setString(9, leasedVehicle.getColour());
statement.setDouble(10, leasedVehicle.getFuelUsed());
statement.setDouble(11, leasedVehicle.getCylinderCapacity());
statement.setDouble(12, leasedVehicle.getWheelBase());
statement.setString(13, leasedVehicle.getLicensedDate());
File f = leasedVehicle.getVehiclePhoto();
FileInputStream fis = new FileInputStream(f);
statement.setBinaryStream(14, (InputStream) fis, (int) (f.length()));
int res = statement.executeUpdate();
if (res == 1) {
return true;
} else {
return false;
}
}
public int addDrug(Drug drug) {
int flag = 0;
String sql = "insert into drugvariety values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
DBConnection db = new DBConnection();
conn = db.getConn();
try {
ps = conn.prepareStatement(sql);
ps.setString(1, drug.getDID());
ps.setString(2, drug.getDrugURL());
ps.setDouble(3, drug.getPurchasing_price());
ps.setDouble(4, drug.getSelling_price());
ps.setString(5, drug.getDrugName());
ps.setInt(6, drug.getDrugType());
ps.setString(7, drug.getDescription());
ps.setString(8, drug.getProduction_date());
ps.setString(9, drug.getExpiration_date());
ps.setInt(10, drug.getShelf_life());
ps.setString(11, drug.getDetail());
ps.setString(12, drug.getManufacturer());
ps.setString(13, drug.getDirections());
ps.setInt(14, drug.getCountpurchases());
ps.setInt(15, drug.getInventory());
ps.setInt(16, drug.getDrugflag());
ps.setString(17, drug.getDrugcomment());
flag = ps.executeUpdate();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}
public int updateDrug(Drug drug) {
int flag = 0;
String sql =
"update drugvariety set drugURL=?,purchasing_price=?,selling_price=?,drugName=?,drugType=?,description=?,production_date=?,expiration_date=?,shelf_life=?,detail=?,manufacturer=?,directions=?,countpurchases=?,inventory=?,drugflag=?,drugcomment=? where DID=?";
DBConnection db = new DBConnection();
conn = db.getConn();
try {
ps = conn.prepareStatement(sql);
ps.setString(1, drug.getDrugURL());
ps.setDouble(2, drug.getPurchasing_price());
ps.setDouble(3, drug.getSelling_price());
ps.setString(4, drug.getDrugName());
ps.setInt(5, drug.getDrugType());
ps.setString(6, drug.getDescription());
ps.setString(7, drug.getProduction_date());
ps.setString(8, drug.getExpiration_date());
ps.setInt(9, drug.getShelf_life());
ps.setString(10, drug.getDetail());
ps.setString(11, drug.getManufacturer());
ps.setString(12, drug.getDirections());
ps.setInt(13, drug.getCountpurchases());
ps.setInt(14, drug.getInventory());
ps.setInt(15, drug.getDrugflag());
ps.setString(16, drug.getDrugcomment());
ps.setString(17, drug.getDID());
flag = ps.executeUpdate();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}
public void updateOrderTable(InventoryModel invProd, String PaymentType, long SmartCalCardNo)
throws SQLException {
PreparedStatement statement = null;
String query;
Connection connection = databaseFactory.getConnection();
try {
query =
"insert into orderdetails(SmartCalCardNumber,PaymentType,SKU,LineItemPrice,LineItemQuantity,TotalAmount,OrderStatus,ProductId,VendingMachineId) values(?,?,?,?,?,?,?,?,?)";
statement = connection.prepareStatement(query);
statement.setLong(1, SmartCalCardNo);
statement.setString(2, PaymentType);
statement.setLong(3, invProd.getskuId());
statement.setDouble(4, invProd.getProductPrice());
statement.setInt(5, 1);
statement.setDouble(6, invProd.getProductPrice());
statement.setString(7, "Paid");
statement.setLong(8, invProd.getProductId());
statement.setLong(9, invProd.getVendingMachineId());
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
DBUtils.closeStatement(statement);
databaseFactory.closeConnection();
}
}
public void cadastraItensVenda(List<ItemVendaModel> itensVenda) {
String SQL =
"INSERT INTO itensvenda (item, cabecalhovenda_idvenda, idproduto, quantidade, precovenda, valortotalitem) VALUES (?,?,?,?,?,?)";
Connection connection = null;
PreparedStatement pst = null;
for (ItemVendaModel ivm : itensVenda) {
try {
connection = Conexao.Conecta();
pst = connection.prepareStatement(SQL);
pst.setInt(1, ivm.getItem());
pst.setInt(2, ivm.getIdVenda());
pst.setInt(3, ivm.getIdProduto());
pst.setDouble(4, ivm.getQuantidade());
pst.setDouble(5, ivm.getPrecoVendaProduto());
pst.setDouble(6, ivm.getValorTotalItem());
pst.execute();
ProdutoModel p = new ProdutoModel();
ProdutoController pc = new ProdutoController();
p.setIdProduto(ivm.getIdProduto());
p.setQuantidade(ivm.getQuantidade());
pc.saidaestoque(p);
} catch (Exception ex) {
JOptionPane.showMessageDialog(
null, "Erro ao tentar cadastrar os itens!\n\n" + ex.getMessage());
}
}
JOptionPane.showMessageDialog(null, "Produtos adicionados com sucesso!");
try {
pst.close();
connection.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Erro!\n\n" + e.getMessage());
}
}
private void writeRouteStops(TransportRoute r, List<TransportStop> stops, boolean direction)
throws SQLException {
int i = 0;
for (TransportStop s : stops) {
if (!visitedStops.contains(s.getId())) {
transStopsStat.setLong(1, s.getId());
transStopsStat.setDouble(2, s.getLocation().getLatitude());
transStopsStat.setDouble(3, s.getLocation().getLongitude());
transStopsStat.setString(4, s.getName());
transStopsStat.setString(5, s.getEnName());
int x = (int) MapUtils.getTileNumberX(24, s.getLocation().getLongitude());
int y = (int) MapUtils.getTileNumberY(24, s.getLocation().getLatitude());
addBatch(transStopsStat);
try {
transportStopsTree.insert(new LeafElement(new Rect(x, y, x, y), s.getId()));
} catch (RTreeInsertException e) {
throw new IllegalArgumentException(e);
} catch (IllegalValueException e) {
throw new IllegalArgumentException(e);
}
visitedStops.add(s.getId());
}
transRouteStopsStat.setLong(1, r.getId());
transRouteStopsStat.setLong(2, s.getId());
transRouteStopsStat.setInt(3, direction ? 1 : 0);
transRouteStopsStat.setInt(4, i++);
addBatch(transRouteStopsStat);
}
}
/** Inserts a new row in the carrito_compras table. */
public CarritoComprasPk insert(CarritoCompras dto) throws CarritoComprasDaoException {
long t1 = System.currentTimeMillis();
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
stmt = conn.prepareStatement(SQL_INSERT);
int index = 1;
stmt.setString(index++, dto.getIdCarrito());
stmt.setDouble(index++, dto.getTotal());
stmt.setDouble(index++, dto.getSubtotal());
stmt.setDouble(index++, dto.getImpuestos());
System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
int rows = stmt.executeUpdate();
long t2 = System.currentTimeMillis();
System.out.println(rows + " rows affected (" + (t2 - t1) + " ms)");
reset(dto);
return dto.createPk();
} catch (Exception _e) {
_e.printStackTrace();
throw new CarritoComprasDaoException("Exception: " + _e.getMessage(), _e);
} finally {
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
private void setParameter(PreparedStatement stmt, List<Object> paramList) throws SQLException {
for (int i = 0; i < paramList.size(); i++) {
Object param = paramList.get(i);
if (param instanceof Arrays) {
List<Object> list = Arrays.asList(param);
for (Object obj : list) {
if (obj instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (obj instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (obj instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
if (param instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (param instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (param instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
protected void saveAccount(String name, double money) {
if (accountExists(name)) {
String sql = "UPDATE " + accounts + " SET " + pbalance + "=? WHERE " + pname + "=?";
try {
PreparedStatement prest = database.prepare(sql);
prest.setDouble(1, money);
prest.setString(2, name);
prest.executeUpdate();
prest.close();
} catch (SQLException e) {
}
} else {
String sql = "INSERT INTO " + accounts + " (" + pname + ", " + pbalance + ") VALUES (?, ?)";
try {
PreparedStatement prest = database.prepare(sql);
prest.setString(1, name);
prest.setDouble(2, money);
prest.executeUpdate();
prest.close();
} catch (SQLException e) {
}
}
}
public String alterar(TO to) throws Exception {
try {
Connection con = new Conexao().getConexao(dbf.getArquivo().getPath());
PreparedStatement ps =
con.prepareStatement(
"UPDATE GERNFEIT SET NUMR_NFE = ?, MODL_NFECE = ?, DESC_PROD = ?, "
+ "QTDE_PRODT = ?, VALR_UNITT = ?, VALR_DESCN = ?, VALR_TOTAL = ? "
+ "WHERE NUMR_NFE = ?");
ps.setString(1, to.getNum_nfe());
ps.setString(2, to.getMod_nfece());
ps.setString(3, to.getDes_prod());
ps.setInt(4, to.getQtde_prod());
ps.setDouble(5, to.getVal_unit());
ps.setDouble(6, to.getVal_desc());
ps.setDouble(7, to.getVal_total());
ps.setString(8, to.getNum_nfe());
ps.execute();
con.close();
return "Alteração OK";
} catch (Exception ex) {
System.out.println("ERRO ao alterar \n" + ex.getMessage());
}
return "";
}
protected void setUp() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
conn = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
Statement stmt = conn.createStatement();
stmt.executeUpdate(CREATE_DEPARTMENT_TABLE);
stmt.executeUpdate(CREATE_EMPLOYEE_TABLE);
PreparedStatement insertDep = conn.prepareStatement(INSERT_DEPARTMENT);
PreparedStatement insertStmt = conn.prepareStatement(INSERT_EMPLOYEE);
int k = 1;
int n = 1;
for (int i = 0; i < depNames.length; i++) {
String depName = depNames[i];
insertDep.setString(1, depName);
insertDep.setInt(2, n++);
insertDep.executeUpdate();
for (int j = 0; j < employeeNames[i].length; j++) {
insertStmt.setString(1, employeeNames[i][j]);
insertStmt.setInt(2, employeeAges[i][j]);
insertStmt.setDouble(3, employeePayments[i][j]);
insertStmt.setDouble(4, employeeBonuses[i][j]);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
insertStmt.setDate(5, new Date(sdf.parse(employeeBirthDates[i][j]).getTime()));
SimpleDateFormat tdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.SSS");
insertStmt.setTimestamp(6, new Timestamp(tdf.parse(birthTimestamps[i][j]).getTime()));
insertStmt.setInt(7, n - 1);
insertStmt.setInt(8, k++);
insertStmt.executeUpdate();
}
}
stmt.close();
insertStmt.close();
}
public void addPlayerSkills(int id, PlayerSkills skills, Date date) throws SQLException {
PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO player_skills (id_player_fk,millis,age,value,salary,form,stamina,pace,technique,passing,keeper,defender,playmaker,scorer,matches,goals,assists,cards,injurydays,day,week,experience, teamwork, discipline, weight, bmi) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)");
ps.setInt(1, id);
ps.setLong(2, date.getMillis());
ps.setInt(3, skills.getAge());
ps.setInt(4, skills.getValue().toInt());
ps.setInt(5, skills.getSalary().toInt());
ps.setInt(6, skills.getForm());
ps.setInt(7, skills.getStamina());
ps.setInt(8, skills.getPace());
ps.setInt(9, skills.getTechnique());
ps.setInt(10, skills.getPassing());
ps.setInt(11, skills.getKeeper());
ps.setInt(12, skills.getDefender());
ps.setInt(13, skills.getPlaymaker());
ps.setInt(14, skills.getScorer());
ps.setInt(15, skills.getMatches());
ps.setInt(16, skills.getGoals());
ps.setInt(17, skills.getAssists());
ps.setInt(18, skills.getCards());
ps.setDouble(19, skills.getInjurydays());
ps.setInt(20, date.getSokkerDate().getDay());
ps.setInt(21, date.getSokkerDate().getWeek());
ps.setInt(22, skills.getExperience());
ps.setInt(23, skills.getTeamwork());
ps.setInt(24, skills.getDiscipline());
ps.setDouble(25, skills.getWeight());
ps.setDouble(26, skills.getBmi());
ps.executeUpdate();
ps.close();
}
protected void upgradeRatingsStats() throws Exception {
try (LoggingTimer loggingTimer = new LoggingTimer()) {
StringBundler sb = new StringBundler(4);
sb.append("select classNameId, classPK, count(1) as totalEntries,");
sb.append(" sum(RatingsEntry.score) as totalScore, ");
sb.append("sum(RatingsEntry.score) / count(1) as averageScore ");
sb.append("from RatingsEntry group by classNameId, classPK");
String selectSQL = sb.toString();
String updateSQL =
"update RatingsStats set totalEntries = ?, totalScore = ?, "
+ "averageScore = ? where classNameId = ? and classPK = ?";
try (PreparedStatement ps1 = connection.prepareStatement(selectSQL);
ResultSet rs = ps1.executeQuery();
PreparedStatement ps2 =
AutoBatchPreparedStatementUtil.autoBatch(connection.prepareStatement(updateSQL))) {
while (rs.next()) {
ps2.setInt(1, rs.getInt("totalEntries"));
ps2.setDouble(2, rs.getDouble("totalScore"));
ps2.setDouble(3, rs.getDouble("averageScore"));
ps2.setLong(4, rs.getLong("classNameId"));
ps2.setLong(5, rs.getLong("classPK"));
ps2.addBatch();
}
ps2.executeBatch();
}
}
}
/**
* Inserts a new non-existing row into the DailyPrices table
*
* @param symbolId ID of entry in symbol table.
* @param dt Date of the price information
* @param open Opening price on the day
* @param close Closing price on the day
* @param high high price of the day
* @param low low price of the day
* @throws SQLException
*/
public void insertDailyPrice(
int symbolId, Date dt, double open, double close, double high, double low)
throws SQLException {
// ensure price doesn't exist already.
PreparedStatement psCheck =
MyConnection.prepareStatement(
"select count(*) rowcount from DailyPrices where Date=? and SymbolId=?;");
psCheck.setString(1, dt.toString());
psCheck.setInt(2, symbolId);
ResultSet rsCheck = psCheck.executeQuery();
if (rsCheck.getInt("rowcount") > 0) {
return;
}
PreparedStatement ps =
MyConnection.prepareStatement(
"insert into DailyPrices(SymbolId, Date, Open, Close, High, Low) values(?,?,?,?,?,?);");
ps.setInt(1, symbolId);
ps.setString(2, dt.toString());
ps.setDouble(3, open);
ps.setDouble(4, close);
ps.setDouble(5, high);
ps.setDouble(6, low);
ps.executeUpdate();
}
public void getData(LatLong p1, LatLong p2) throws SQLException {
con = DriverManager.getConnection(url, username, password);
BoundingBox x = new BoundingBox(20);
x.getBoundingBox(p1, p2);
LatLong min = x.getMin();
LatLong max = x.getMax();
String sql =
"select distinct Latitude, Longitude, UVA1, UVA2, UVB1, UVB2 "
+ "from uvReadings where Time > '2012-10-22' "
+ "and UVB1 > 7.289 and Latitude > ? and Longitude > ? "
+ "and Latitude < ? and Longitude < ?";
PreparedStatement p = con.prepareStatement(sql);
p.setDouble(1, min.getLatitude());
p.setDouble(2, min.getLongitude());
p.setDouble(3, max.getLatitude());
p.setDouble(4, max.getLongitude());
ResultSet rs = p.executeQuery();
while (rs.next()) {
System.out.println(
"\t"
+ rs.getString(1)
+ ","
+ rs.getString(2)
+ ","
+ rs.getString("UVA1")
+ ","
+ rs.getString("UVB1"));
}
rs.close();
con.close();
}
private static void storeNodeCentrality(
Connection connection, TableLocation nodesName, KeyedGraph graph) throws SQLException {
final PreparedStatement nodeSt =
connection.prepareStatement("INSERT INTO " + nodesName + " VALUES(?,?,?)");
try {
int count = 0;
for (VCent v : (Set<VCent>) graph.vertexSet()) {
nodeSt.setInt(1, v.getID());
nodeSt.setDouble(2, v.getBetweenness());
nodeSt.setDouble(3, v.getCloseness());
nodeSt.addBatch();
count++;
if (count >= BATCH_SIZE) {
nodeSt.executeBatch();
nodeSt.clearBatch();
count = 0;
}
}
if (count > 0) {
nodeSt.executeBatch();
nodeSt.clearBatch();
}
connection.commit();
} finally {
nodeSt.close();
}
}
public String addBillDetails(String poNumber, double totalAmount, double totalQuantity)
throws SQLException {
/**
* For retrieving the details of purchase order using poNumber corresponding to particular
* vendorNumber
*
* @param poNumber
* @return
* @throws SQLException
*/
DBConnection dbConnection = new DBConnection();
Connection connection = dbConnection.getConnection();
String billNo = "";
PreparedStatement ps = connection.prepareStatement("select 'BILL'||BILL_SEQ.nextval FROM dual");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
billNo = rs.getString(1);
System.out.println(billNo);
logger.debug("generating bill no" + billNo);
}
PreparedStatement preparedStatement =
connection.prepareStatement("INSERT INTO BILL_DETAILS VALUES(?,?,?,?)");
logger.debug("inserting values into Bill_Details" + billNo);
preparedStatement.setString(1, billNo);
preparedStatement.setString(2, poNumber);
preparedStatement.setDouble(3, totalAmount);
preparedStatement.setDouble(4, totalQuantity);
preparedStatement.execute();
return billNo;
}
public int update(ClienteDTO dto) {
String sql =
"UPDATE CLIENTE "
+ "SET CLIENTE_CPF = ?, "
+ "SET NOME = ?, "
+ "SET E_MAIL = ?, "
+ "SET DT_NASCIMENTO = ?, "
+ "SET TELEFONE = ?, "
+ "WHERE CLIENTE_CPF = ?";
PreparedStatement stmt = null;
try {
stmt = con.prepareStatement(sql);
stmt.setDouble(1, dto.getClienteCpf());
stmt.setString(2, dto.getNome());
stmt.setString(3, dto.geteMail());
// ver o date com o prof de sexta se está correto
stmt.setDate(4, new java.sql.Date(dto.getDataDeNascimento().getTime()));
stmt.setString(5, dto.getTelefone());
stmt.setDouble(6, dto.getClienteCpf());
return stmt.executeUpdate();
} catch (SQLException ex) {
ex.printStackTrace();
}
return 0;
}
/*
* Perform a deposit operation -- call this method if we already have a
* connection
*/
public static void depositToAccountWithConn(int accountId, double amt, Connection dbConn)
throws AccountDbFailure, SQLException {
double curBal;
double newBalance = 0.0;
int rowsAffected;
String updateCurBalSql = "UPDATE account SET balance = ? where id = ? AND balance = ? ;";
// Get the current balance
curBal = getAcctBalanceWithConn(accountId, dbConn);
newBalance = curBal + amt;
// Update the balance
try (PreparedStatement updateCurBalStmt = dbConn.prepareStatement(updateCurBalSql)) {
updateCurBalStmt.setDouble(1, newBalance);
updateCurBalStmt.setInt(2, accountId);
updateCurBalStmt.setDouble(3, curBal);
rowsAffected = updateCurBalStmt.executeUpdate();
if (rowsAffected != 1) {
/* Exactly one row should have been updated */
if (rowsAffected == 0) {
throw new AccountDbFailure(AccountDbFailure.RETRY);
}
/* More than one row modified? */
throw new AccountDbFailure(
AccountDbFailure.STMT_FAILED,
"Expected only one row to be affected by the deposit operation");
}
}
}
/**
* @param radius radius of the sphere.
* @param location center of the query circle.
* @param distance radius of the query circle.
* @param connection an SQL connection.
* @return places within the specified distance from location.
*/
public static java.sql.ResultSet findPlacesWithinDistance(
double radius, GeoLocation location, double distance, java.sql.Connection connection)
throws java.sql.SQLException {
GeoLocation[] boundingCoordinates = location.boundingCoordinates(distance, radius);
boolean meridian180WithinDistance =
boundingCoordinates[0].getLongitudeInRadians()
> boundingCoordinates[1].getLongitudeInRadians();
java.sql.PreparedStatement statement =
connection.prepareStatement(
"SELECT * FROM Places WHERE (Lat >= ? AND Lat <= ?) AND (Lon >= ? "
+ (meridian180WithinDistance ? "OR" : "AND")
+ " Lon <= ?) AND "
+ "acos(sin(?) * sin(Lat) + cos(?) * cos(Lat) * cos(Lon - ?)) <= ?");
statement.setDouble(1, boundingCoordinates[0].getLatitudeInRadians());
statement.setDouble(2, boundingCoordinates[1].getLatitudeInRadians());
statement.setDouble(3, boundingCoordinates[0].getLongitudeInRadians());
statement.setDouble(4, boundingCoordinates[1].getLongitudeInRadians());
statement.setDouble(5, location.getLatitudeInRadians());
statement.setDouble(6, location.getLatitudeInRadians());
statement.setDouble(7, location.getLongitudeInRadians());
statement.setDouble(8, distance / radius);
return statement.executeQuery();
}
/**
* updateRouteNum
*
* @param int id
* @param int nodeNum
* @param double maxLat
* @param double minLat
* @param double maxLon
* @param double minLon
*/
public void updateRouteNum(
int id, int nodeNum, double maxLat, double minLat, double maxLon, double minLon) {
printMsg(
"updateRouteNum "
+ " "
+ id
+ " "
+ nodeNum
+ " "
+ maxLat
+ " "
+ minLat
+ " "
+ maxLon
+ " "
+ minLon);
PreparedStatement ps = mPsUpdateNum;
try {
ps.setInt(1, nodeNum);
ps.setDouble(2, maxLat);
ps.setDouble(3, minLat);
ps.setDouble(4, maxLon);
ps.setDouble(5, minLon);
ps.setInt(6, id);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void updateNewPlace(Place place) {
String query =
"Update Places SET title=?, state=?, city=?, rating=?, rec_stay=?, n_ratings=?, n_recs=?, description=? WHERE id=?";
Connection con = getConnection();
try {
PreparedStatement stmt = con.prepareStatement(query);
stmt.setString(1, place.getTitle());
stmt.setString(2, place.getState());
stmt.setString(3, place.getCity());
stmt.setDouble(4, place.getRating());
stmt.setDouble(5, place.getRec_stay());
stmt.setInt(6, place.getN_ratings());
stmt.setInt(7, place.getN_recs());
stmt.setString(8, place.getDescription());
stmt.setInt(9, place.getId());
System.out.println(stmt.toString());
stmt.executeUpdate();
query = "DELETE FROM PlaceImages WHERE placeId=" + place.getId();
updateQuery(query);
for (String link : place.getImageLinks()) {
query =
String.format(
"INSERT INTO PlaceImages (placeId, imageLink) VALUES (%d, '%s')",
place.getId(), link);
insertQuery(query);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void addReceipt(String[] validStudentId, int feesTemplateId) {
try {
DBConnection receiptSc = (DBConnection) sc.getAttribute("dbConn");
receiptConn = receiptSc.getDBConnection();
int currentSemester = 0;
// StudentRegistrationController studentRegistrationController=new
// StudentRegistrationController();//
StudentAcademicController studentAcademicController = new StudentAcademicController();
StudentAcademic studentAcademic = new StudentAcademic();
StudentAcademic studentReceipt = new StudentAcademic();
FeesTemplateController feesTemplateController = new FeesTemplateController();
StudentFeesHistoryController studentFeesHistory = new StudentFeesHistoryController();
for (int i = 0; i < validStudentId.length; i++) {
// ArrayList<StudentRegistration> studentDetails=
// studentRegistrationController.getStudentDetails(Integer.parseInt(validStudentId[i]));
// for(StudentRegistration studentDetail:studentDetails){
// currentSemester=studentDetail.getSemester();
// }
currentSemester =
studentAcademicController.studentCurrentSemester(Integer.parseInt(validStudentId[i]));
double templateFees =
(Double)
feesTemplateController.templateDetailsFromId(feesTemplateId).get("template_fee");
receiptStmt =
receiptConn.prepareStatement(
"INSERT INTO tbl_student_fees_receipt(student_id,semester,total_fees,fees_pending,created_date) values(?,?,?,?,?)",
Statement.RETURN_GENERATED_KEYS);
receiptStmt.setInt(1, Integer.parseInt(validStudentId[i]));
receiptStmt.setInt(2, currentSemester);
receiptStmt.setDouble(3, templateFees);
receiptStmt.setDouble(4, pendingFee(templateFees, 0));
Calendar calendar = Calendar.getInstance();
java.util.Date createdDate = calendar.getTime();
Timestamp createdDateTime = new Timestamp(createdDate.getTime());
receiptStmt.setTimestamp(5, createdDateTime);
receiptStmt.execute();
receiptRs = receiptStmt.getGeneratedKeys();
if (receiptRs.next()) {
studentFeesHistory.addStudentFeesHistory(
receiptRs.getInt(1), feesTemplateId); // adding fees histoty for student
studentAcademic.setStudentId(Integer.parseInt(validStudentId[i]));
studentAcademic.setInvoiceStatus(1);
studentAcademicController.updateInvoiceStatus(studentAcademic);
studentReceipt.setStudentId(Integer.parseInt(validStudentId[i]));
studentReceipt.setReceipId(receiptRs.getInt(1));
studentAcademicController.updateReceiptId(studentReceipt);
}
}
} catch (Exception e) {
System.out.println("Exception in Add Receipt " + e);
} finally {
close();
}
}
@Override
public int RegistrarTransaccion(VentaTO t) throws Exception {
int iventa;
try {
cn = AccesoDB.getConnection();
// inicia transaccion
cn.setAutoCommit(false);
iventa = numeroVenta(); // genera nro, de venta
// registra ventas
sql =
"Insert Into Ventas(idventa,idcliente,idempleado,tipodoc,nrodoc,fecha,total)"
+ " Values(?,?,?,?,?,sysdate,?)";
ps = cn.prepareStatement(sql);
ps.setInt(1, iventa);
ps.setString(2, t.getIdcliente());
ps.setString(3, t.getIdempleado());
ps.setString(4, t.getTipodoc());
ps.setString(5, t.getNrodoc());
ps.setDouble(6, t.getTotal());
ps.executeUpdate(); // graba la factura
// graba detalle venta
sql =
"Insert Into DetalleVenta(idventa,idproducto,precio,cantidad,importe)"
+ " Values(?,?,?,?,?)";
psa = cn.prepareStatement(sql);
// actualiza stock
sql = "Update Productos set Stock = Stock - ? where idProducto=?";
psb = cn.prepareStatement(sql);
for (DetalleTO item : t.getDetalle()) {
psa.setInt(1, iventa);
psa.setString(2, item.getIdproducto());
psa.setDouble(3, item.getPrecio());
psa.setInt(4, item.getCantidad());
psa.setDouble(5, item.getTotal());
psa.executeUpdate(); // graba detalleventa
// actualizar stock de producto
psb.setInt(1, item.getCantidad());
psb.setString(2, item.getIdproducto());
psb.executeUpdate(); // actualiza cantidad de producto
}
// st.close();
ps.close();
psa.close();
psb.close();
cn.commit(); // confirma la transaccion
} catch (SQLException | ClassNotFoundException e) {
try {
cn.rollback(); // deshacer la transaccion
} catch (Exception e1) {
}
throw e;
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
return iventa;
}
// for item wastage
public void AddItemWastage(Object[] wastageinfo, int userid, int departmentid) {
/*
wastage into
obj[0] = getItemID();
obj[1] = getComboBoxItemName();
obj[2] = getItemQuantity();
obj[3] = getItemAmount();
obj[4] = getItemReason();
obj[5] = getItemWaiterId();
obj[6] = getComboBoxItemStaffName();
obj[7] = getUnitID();
obj[8] = getComboBoxItemBaseUnit();
*/
PreparedStatement stmtwastage;
PreparedStatement stmtSubtractResturantStore;
String strwastage =
"INSERT INTO wastage (id,quantity,amount,reason,staff_id,department_id,user_id,date,menu_type_flag,unit_id) VALUES(?,?,?,?,?,?,?,?,?,?)";
// String strSubtractSingleResturantStore = "UPDATE department_store_stock SET
// total_qty = department_store_stock.total_qty -(? * (select
// menu.quantity*item_unit.unit_relative_quantity from menu INNER JOIN item_unit ON menu.unit_id
// = item_unit.unit_id WHERE menu.menu_id = ?)) WHERE item_id = (select item_id from menu where
// menu_id = ?)";
String strSubtractHybridResturantStore =
"UPDATE department_store_stock SET total_qty = department_store_stock.total_qty - ?*(select unit_relative_quantity FROM item_unit WHERE unit_id = ?) WHERE department_item_id = ? ";
try {
initConnection();
// for inserting into wastage table
conn.setAutoCommit(false);
stmtwastage = conn.prepareStatement(strwastage);
stmtwastage.setInt(1, Integer.parseInt(wastageinfo[0].toString()));
stmtwastage.setDouble(2, Double.parseDouble(wastageinfo[2].toString()));
stmtwastage.setDouble(3, Double.parseDouble(wastageinfo[3].toString()));
stmtwastage.setString(4, wastageinfo[4].toString());
stmtwastage.setInt(5, Integer.parseInt(wastageinfo[5].toString()));
stmtwastage.setInt(6, departmentid);
stmtwastage.setInt(7, userid);
stmtwastage.setDate(8, new java.sql.Date(Function.returnSystemDate().getTime()));
stmtwastage.setBoolean(9, Boolean.FALSE);
stmtwastage.setInt(10, Integer.parseInt(wastageinfo[7].toString()));
stmtwastage.executeUpdate();
// for reducing the wastage quantity from the department_store_stock
stmtSubtractResturantStore = conn.prepareStatement(strSubtractHybridResturantStore);
stmtSubtractResturantStore.setDouble(1, Double.parseDouble(wastageinfo[2].toString()));
stmtSubtractResturantStore.setInt(2, Integer.parseInt(wastageinfo[7].toString()));
stmtSubtractResturantStore.setInt(3, Integer.parseInt(wastageinfo[0].toString()));
stmtSubtractResturantStore.executeUpdate();
// if everthing goes well commit
conn.commit();
JOptionPane.showMessageDialog(null, "Item Wastage Saved Succesfully");
} catch (SQLException se) {
JOptionPane.showMessageDialog(null, se + "from additemwastage" + getClass().getName());
}
}
/** 插入子单信息 */
private void insertItemSo(Connection con, JumpMQOrderItemVo soItemVo) {
PreparedStatement st = null;
String sql =
"insert into edw1_dev.REALTIME_METADATA_SO_ITEM (ID, ENDUSERID, ORDERID, PRODUCTID, MERCHANTID, ORDERITEMAMOUNT, ORDERITEMPRICE, ORDERITEMNUM, PARENTSOITEMID, ISITEMLEAF, DELIVERYFEEAMOUNT, PROMOTIONAMOUNT, COUPONAMOUNT, CREATETIME, RULE_TYPE, UPDT_TIME) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try {
st = con.prepareStatement(sql);
st.setLong(1, soItemVo.getId());
st.setLong(2, soItemVo.getEndUserId());
st.setLong(3, soItemVo.getOrderId());
st.setLong(4, soItemVo.getProductId());
st.setLong(5, soItemVo.getMerchantId());
if (soItemVo.getOrderItemAmount() != null) {
st.setDouble(6, soItemVo.getOrderItemAmount().doubleValue());
} else {
st.setDouble(6, 0.0);
}
if (soItemVo.getOrderItemPrice() != null) {
st.setDouble(7, soItemVo.getOrderItemPrice().doubleValue());
} else {
st.setDouble(7, 0.0);
}
st.setInt(8, soItemVo.getOrderItemNum());
st.setLong(9, soItemVo.getParentSoItemId());
st.setInt(10, soItemVo.getIsItemLeaf());
if (soItemVo.getDeliveryFeeAmount() != null) {
st.setDouble(11, soItemVo.getDeliveryFeeAmount().doubleValue());
} else {
st.setDouble(11, 0.0);
}
if (soItemVo.getPromotionAmount() != null) {
st.setDouble(12, soItemVo.getPromotionAmount().doubleValue());
} else {
st.setDouble(12, 0.0);
}
if (soItemVo.getCouponAmount() != null) {
st.setDouble(13, soItemVo.getCouponAmount().doubleValue());
} else {
st.setDouble(13, 0.0);
}
if (soItemVo.getCreateTime() != null) {
st.setTimestamp(14, new Timestamp(soItemVo.getCreateTime().getTime()));
} else {
st.setTimestamp(14, new Timestamp(new Date().getTime()));
}
st.setInt(15, soItemVo.getRuleType());
st.setTimestamp(16, new Timestamp(new Date().getTime()));
st.executeQuery();
} catch (Exception e) {
logger.info("error: Metadata item insert error ! ");
} finally {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
void gra() throws SQLException {
PreparedStatement ps = getPreparedStatement();
ps.setDate(0, new Date(0)); // Noncompliant - First parameter index is 1
ps.setDouble(3, 0.0); // Compliant - Query of the preparedStatement is unknown
PreparedStatement ps2 = ps;
ps2.setDate(0, new Date(0)); // Noncompliant - First parameter index is 1
ps2.setDouble(3, 0.0); // Compliant - Query of the preparedStatement is unknown
}
/**
* kmk给wang转100,然后wang给kmk转9000如果异常，回滚到最开始，如果wang的钱小于9000回滚到保存点 设置回滚点
*
* @throws SQLException
* @throws Exception
*/
public static void demo2() throws SQLException {
try {
// sql
String sql = "update account set money=money+ ? where name =?";
// 预编译
pstate = JDBCUtils.getPrepareStatement(sql);
// 开启事务，设置事务提交为false
JDBCUtils.setAutoCommit(false);
// 完成转账
pstate.setDouble(1, -100.0);
pstate.setString(2, "kmk");
pstate.executeUpdate();
// int a = 10 / 0;// 出现异常回滚
pstate.setDouble(1, 100.0);
pstate.setString(2, "wang");
pstate.executeUpdate();
// 设置保存点
Savepoint savePont = JDBCUtils.setSavepoint();
// 检查wang的账户钱是否大于0
String sql2 = "select * from account where name = ?";
// 预编译
pstate = JDBCUtils.getPrepareStatement(sql2);
// JDBCUtils.setAutoCommit(false);
pstate.setString(1, "wang");
ResultSet rs = pstate.executeQuery();
if (rs.next()) {
if (rs.getDouble("money") < 9000) {
JDBCUtils.rollBack(savePont);
System.out.println("钱不够，不能转账");
} else {
// 如果有钱可以转账
// sql
String sql3 = "update account set money=money+ ? where name =?";
// 预编译
pstate = JDBCUtils.getPrepareStatement(sql3);
// 开启事务，设置事务提交为false
JDBCUtils.setAutoCommit(false);
// 完成转账
pstate.setDouble(1, 9000.0);
pstate.setString(2, "kmk");
pstate.executeUpdate();
// int a = 10 / 0;// 出现异常回滚
pstate.setDouble(1, -9000.0);
pstate.setString(2, "wang");
pstate.executeUpdate();
}
}
JDBCUtils.commit(); // 提交
} catch (Exception e) {
JDBCUtils.rollBack(); // 回滚最开始
e.printStackTrace();
} finally {
JDBCUtils.closereSource();
}
}
/**
* Adds a racquet to the DB table racquetInfo.
*
* @param mName the model name
* @param brand the brand name
* @param mass the mass
* @param length the length
* @param swingWeight the swingweight
* @param balance point the balance point
* @param quality index the quality index
* @return the primary key for the newly added row, 0 if unsuccessful
*/
private int addRacquet(
String mName,
String brand,
double mass,
double length,
int swingWeight,
double balancePoint,
double qualityIndex)
throws ServletException, IOException {
Properties prop = new Properties();
prop.load(
Thread.currentThread().getContextClassLoader().getResourceAsStream("build.properties"));
Connection conn;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String sqlPassword = prop.getProperty("pass");
String sqlUsername = prop.getProperty("username");
String sqlURL = prop.getProperty("url"); // + "racqual";
conn = DriverManager.getConnection(sqlURL, sqlUsername, sqlPassword);
String sql =
"INSERT INTO racquetinfo(modelName, brand, mass, "
+ "length, swingweight, balancePoint, qualityIndex)"
+ "VALUES(?,?,?,?,?,?,?)";
PreparedStatement stmt = conn.prepareStatement(sql, new String[] {"racquetID"});
stmt.setString(1, mName);
stmt.setString(2, brand);
stmt.setDouble(3, mass);
stmt.setDouble(4, length);
stmt.setInt(5, swingWeight);
stmt.setDouble(6, balancePoint);
stmt.setDouble(7, qualityIndex);
int i = stmt.executeUpdate();
ResultSet rs = stmt.getGeneratedKeys();
if (i != 0 && rs != null && rs.next()) {
int id = rs.getInt(1);
stmt.close();
rs.close();
conn.close();
return id;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return 0;
}
/**
* Step 2 of the bootstrapping process.
*
* <p>Calculating the the extent for each image level (original + pyramids). This calculation is
* only done if the extent info in the master table is SQL NULL. After calculation the meta table
* is updated with the result to avoid this operation in the future.
*
* @param coverageName The coverage name in the sql meta table
* @param con JDBC connection
* @throws SQLException
* @throws IOException
*/
void calculateExtentsFromDB(String coverageName, Connection con)
throws SQLException, IOException {
PreparedStatement stmt = con.prepareStatement(config.getSqlUpdateMosaicStatement());
List<ImageLevelInfo> toBeRemoved = new ArrayList<ImageLevelInfo>();
for (ImageLevelInfo li : levelInfos) {
if (li.getCoverageName().equals(coverageName) == false) {
continue;
}
if (li.calculateExtentsNeeded() == false) {
continue;
}
Date start = new Date();
if (LOGGER.isLoggable(Level.INFO)) LOGGER.info("Calculate extent for " + li.toString());
Envelope env = getExtent(li, con);
if (env == null) {
if (LOGGER.isLoggable(Level.WARNING))
LOGGER.log(Level.WARNING, "No extent, removing this level");
toBeRemoved.add(li);
continue;
}
li.setExtentMaxX(new Double(env.getMaxX()));
li.setExtentMaxY(new Double(env.getMaxY()));
li.setExtentMinX(new Double(env.getMinX()));
li.setExtentMinY(new Double(env.getMinY()));
stmt.setDouble(1, li.getExtentMaxX().doubleValue());
stmt.setDouble(2, li.getExtentMaxY().doubleValue());
stmt.setDouble(3, li.getExtentMinX().doubleValue());
stmt.setDouble(4, li.getExtentMinY().doubleValue());
stmt.setString(5, li.getCoverageName());
stmt.setString(6, li.getTileTableName());
stmt.setString(7, li.getSpatialTableName());
stmt.execute();
long msecs = (new Date()).getTime() - start.getTime();
if (LOGGER.isLoggable(Level.INFO))
LOGGER.info("Calculate extent for " + li.toString() + " finished in " + msecs + " ms ");
}
levelInfos.removeAll(toBeRemoved);
if (stmt != null) {
stmt.close();
}
}