List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:org.ojbc.adapters.rapbackdatastore.dao.RapbackDAOImpl.java
@Override public Integer saveSubject(final Subject subject) { log.debug("Inserting row into IDENTIFICATION_SUBJECT table : " + subject); final String SUBJECT_INSERT = "INSERT into IDENTIFICATION_SUBJECT " + "(UCN, CRIMINAL_SID, CIVIL_SID, FIRST_NAME, LAST_NAME, MIDDLE_INITIAL, DOB, SEX_CODE) " + "values (?, ?, ?, ?, ?, ?, ?, ?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(SUBJECT_INSERT, new String[] { "UCN", "CRIMINAL_SID", "CIVIL_SID", "FIRST_NAME", "LAST_NAME", "MIDDLE_INITIAL", "DOB" }); ps.setString(1, subject.getUcn()); ps.setString(2, subject.getCriminalSid()); ps.setString(3, subject.getCivilSid()); ps.setString(4, subject.getFirstName()); ps.setString(5, subject.getLastName()); ps.setString(6, subject.getMiddleInitial()); ps.setDate(7, toSqlDate(subject.getDob())); ps.setString(8, subject.getSexCode()); return ps; }/* w w w . j a va 2s.c o m*/ }, keyHolder); return keyHolder.getKey().intValue(); }
From source file:org.apache.phoenix.query.BaseTest.java
private static void initEntityHistoryTableValues(String tenantId, byte[][] splits, Date date, Long ts, String url) throws Exception { if (ts == null) { ensureTableCreated(url, ENTITY_HISTORY_TABLE_NAME, splits); } else {/*from ww w . java 2 s. c om*/ ensureTableCreated(url, ENTITY_HISTORY_TABLE_NAME, splits, ts - 2); } Properties props = new Properties(); if (ts != null) { props.setProperty(CURRENT_SCN_ATTRIB, ts.toString()); } Connection conn = DriverManager.getConnection(url, props); try { // Insert all rows at ts PreparedStatement stmt = conn.prepareStatement("upsert into " + ENTITY_HISTORY_TABLE_NAME + "(" + " ORGANIZATION_ID, " + " PARENT_ID, " + " CREATED_DATE, " + " ENTITY_HISTORY_ID, " + " OLD_VALUE, " + " NEW_VALUE) " + "VALUES (?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId); stmt.setString(2, PARENTID1); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID1); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID2); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID2); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID3); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID3); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID4); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID4); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID5); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID5); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID6); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID6); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID7); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID7); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID8); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID8); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID9); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID9); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); conn.commit(); } finally { conn.close(); } }
From source file:org.dspace.storage.rdbms.DatabaseManager.java
/** * Iterate over the given parameters and add them to the given prepared statement. * Only a select number of datatypes are supported by the JDBC driver. * * @param statement/*from ww w . ja va 2 s . com*/ * The unparameterized statement. * @param parameters * The parameters to be set on the statement. */ protected static void loadParameters(PreparedStatement statement, Object[] parameters) throws SQLException { statement.clearParameters(); int idx = 1; for (Object parameter : parameters) { if (parameter instanceof String) { statement.setString(idx, (String) parameter); } else if (parameter instanceof Long) { statement.setLong(idx, ((Long) parameter).longValue()); } else if (parameter instanceof Integer) { statement.setInt(idx, ((Integer) parameter).intValue()); } else if (parameter instanceof Short) { statement.setShort(idx, ((Short) parameter).shortValue()); } else if (parameter instanceof Date) { statement.setDate(idx, (Date) parameter); } else if (parameter instanceof Time) { statement.setTime(idx, (Time) parameter); } else if (parameter instanceof Timestamp) { statement.setTimestamp(idx, (Timestamp) parameter); } else if (parameter instanceof Double) { statement.setDouble(idx, ((Double) parameter).doubleValue()); } else if (parameter instanceof Float) { statement.setFloat(idx, ((Float) parameter).floatValue()); } else if (parameter == null) { throw new SQLException("Attempting to insert null value into SQL query."); } else { throw new SQLException("Attempting to insert unknown datatype (" + parameter.getClass().getName() + ") into SQL statement."); } idx++; } }
From source file:oscar.dms.EDocUtil.java
public ArrayList<EDoc> getUnmatchedDocuments(String creator, String responsible, Date startDate, Date endDate, boolean unmatchedDemographics) { ArrayList<EDoc> list = new ArrayList<EDoc>(); // boolean matchedDemographics = true; String sql = "SELECT DISTINCT c.module, c.module_id, d.doccreator, d.source, d.sourceFacility, d.responsible, d.program_id, d.status, d.docdesc, d.docfilename, d.doctype, d.document_no, d.updatedatetime, d.contenttype, d.observationdate, d.appointment_no FROM document d, ctl_document c WHERE c.document_no=d.document_no AND c.module='demographic' and doccreator = ? and responsible = ? and updatedatetime >= ? and updatedatetime <= ?"; if (unmatchedDemographics) { sql += " and c.module_id = -1 "; }//from w ww . j av a 2 s . c o m /* * else if (matchedDemographics){ sql += " and c.module_id != -1 "; } */ try { java.sql.Date sDate = new java.sql.Date(startDate.getTime()); java.sql.Date eDate = new java.sql.Date(endDate.getTime()); logger.debug("Creator " + creator + " start " + sDate + " end " + eDate); Connection c = DbConnectionFilter.getThreadLocalDbConnection(); PreparedStatement ps = c.prepareStatement(sql); ps.setString(1, creator); ps.setString(2, responsible); ps.setDate(3, new java.sql.Date(startDate.getTime())); ps.setDate(4, new java.sql.Date(endDate.getTime())); ResultSet rs = ps.executeQuery(); while (rs.next()) { logger.debug("DOCFILENAME " + rs.getString("docfilename")); EDoc currentdoc = new EDoc(); currentdoc.setModule(rsGetString(rs, "module")); currentdoc.setModuleId(rsGetString(rs, "module_id")); currentdoc.setDocId(rsGetString(rs, "document_no")); currentdoc.setDescription(rsGetString(rs, "docdesc")); currentdoc.setType(rsGetString(rs, "doctype")); currentdoc.setCreatorId(rsGetString(rs, "doccreator")); currentdoc.setSource(rsGetString(rs, "source")); currentdoc.setSourceFacility(rsGetString(rs, "sourceFacility")); currentdoc.setResponsibleId(rsGetString(rs, "responsible")); String temp = rsGetString(rs, "program_id"); if (temp != null && temp.length() > 0) currentdoc.setProgramId(Integer.valueOf(temp)); temp = rsGetString(rs, "appointment_no"); if (temp != null && temp.length() > 0) currentdoc.setAppointmentNo(Integer.valueOf(temp)); currentdoc.setDateTimeStamp(rsGetString(rs, "updatedatetime")); currentdoc.setFileName(rsGetString(rs, "docfilename")); currentdoc.setStatus(rsGetString(rs, "status").charAt(0)); currentdoc.setContentType(rsGetString(rs, "contenttype")); currentdoc.setObservationDate(rsGetString(rs, "observationdate")); list.add(currentdoc); } rs.close(); } catch (Exception e) { logger.error("Error", e); } // mysql> SELECT DISTINCT c.module, c.module_id, d.doccreator, d.source, d.program_id, d.status, d.docdesc, d.docfilename, d.doctype, d.document_no, d.updatedatetime, d.contenttype, d.observationdate FROM document d, ctl_document c WHERE // c.document_no=d.document_no AND c.module='demographic' and module_id = -1 return list; }
From source file:com.intelligentz.appointmentz.controllers.addSession.java
@SuppressWarnings("Since15") @Override/*from ww w . java 2 s . c o m*/ public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { try { String room_id = null; String doctor_id = null; String start_time = null; String date_picked = null; ArrayList<SessonCustomer> sessonCustomers = new ArrayList<>(); DiskFileItemFactory factory = new DiskFileItemFactory(); // maximum size that will be stored in memory factory.setSizeThreshold(maxMemSize); // Location to save data that is larger than maxMemSize. factory.setRepository(new File(filePath + "temp")); // Create a new file upload handler ServletFileUpload upload = new ServletFileUpload(factory); // maximum file size to be uploaded. upload.setSizeMax(maxFileSize); // Parse the request to get file items. List fileItems = upload.parseRequest(req); // Process the uploaded file items Iterator i = fileItems.iterator(); while (i.hasNext()) { FileItem fi = (FileItem) i.next(); if (!fi.isFormField()) { // Get the uploaded file parameters String fieldName = fi.getFieldName(); String fileName = fi.getName(); String contentType = fi.getContentType(); boolean isInMemory = fi.isInMemory(); long sizeInBytes = fi.getSize(); // Write the file if (fileName.lastIndexOf("\\") >= 0) { filePath = filePath + fileName.substring(fileName.lastIndexOf("\\")); file = new File(filePath); } else { filePath = filePath + fileName.substring(fileName.lastIndexOf("\\") + 1); file = new File(filePath); } fi.write(file); Files.lines(Paths.get(filePath)).forEach((line) -> { String[] cust = line.split(","); sessonCustomers.add(new SessonCustomer(cust[0].trim(), Integer.parseInt(cust[1].trim()))); }); } else { if (fi.getFieldName().equals("room_id")) room_id = fi.getString(); else if (fi.getFieldName().equals("doctor_id")) doctor_id = fi.getString(); else if (fi.getFieldName().equals("start_time")) start_time = fi.getString(); else if (fi.getFieldName().equals("date_picked")) date_picked = fi.getString(); } } con = new connectToDB(); if (con.connect()) { Connection connection = con.getConnection(); Class.forName("com.mysql.jdbc.Driver"); Statement stmt = connection.createStatement(); String SQL, SQL1, SQL2; SQL1 = "insert into db_bro.session ( doctor_id, room_id, date, start_time) VALUES (?,?,?,?)"; PreparedStatement preparedStmt = connection.prepareStatement(SQL1); preparedStmt.setString(1, doctor_id); preparedStmt.setString(2, room_id); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd-HH:mm"); try { java.util.Date d = formatter.parse(date_picked + "-" + start_time); Date d_sql = new Date(d.getTime()); java.util.Date N = new java.util.Date(); if (N.compareTo(d) > 0) { res.sendRedirect("./error.jsp?error=Invalid Date!"); } //String [] T = start_time.split(":"); //Time t = Time.valueOf(start_time); //Time t = new Time(Integer.parseInt(T[0]),Integer.parseInt(T[1]),0); //java.sql.Time t_sql = new java.sql.Date(d.getTime()); preparedStmt.setString(4, start_time + ":00"); preparedStmt.setDate(3, d_sql); } catch (ParseException e) { displayMessage(res, "Invalid Date!" + e.getLocalizedMessage()); } // execute the preparedstatement preparedStmt.execute(); SQL = "select * from db_bro.session ORDER BY session_id DESC limit 1"; ResultSet rs = stmt.executeQuery(SQL); boolean check = false; while (rs.next()) { String db_doctor_id = rs.getString("doctor_id"); String db_date_picked = rs.getString("date"); String db_start_time = rs.getString("start_time"); String db_room_id = rs.getString("room_id"); if ((doctor_id == null ? db_doctor_id == null : doctor_id.equals(db_doctor_id)) && (start_time == null ? db_start_time == null : (start_time + ":00").equals(db_start_time)) && (room_id == null ? db_room_id == null : room_id.equals(db_room_id)) && (date_picked == null ? db_date_picked == null : date_picked.equals(db_date_picked))) { check = true; //displayMessage(res,"Authentication Success!"); SQL2 = "insert into db_bro.session_customers ( session_id, mobile, appointment_num) VALUES (?,?,?)"; for (SessonCustomer sessonCustomer : sessonCustomers) { preparedStmt = connection.prepareStatement(SQL2); preparedStmt.setString(1, rs.getString("session_id")); preparedStmt.setString(2, sessonCustomer.getMobile()); preparedStmt.setInt(3, sessonCustomer.getAppointment_num()); preparedStmt.execute(); } try { connection.close(); } catch (SQLException e) { displayMessage(res, "SQLException"); } res.sendRedirect("./home"); } } if (!check) { try { connection.close(); } catch (SQLException e) { displayMessage(res, "SQLException"); } displayMessage(res, "SQL query Failed!"); } } else { con.showErrormessage(res); } /*res.setContentType("text/html");//setting the content type PrintWriter pw=res.getWriter();//get the stream to write the data //writing html in the stream pw.println("<html><body>"); pw.println("Welcome to servlet: "+username); pw.println("</body></html>"); pw.close();//closing the stream */ } catch (Exception ex) { Logger.getLogger(authenticate.class.getName()).log(Level.SEVERE, null, ex); displayMessage(res, "Error!" + ex.getLocalizedMessage()); } }
From source file:net.bhira.sample.api.dao.EmployeeDaoImpl.java
/** * @see net.bhira.sample.api.dao.EmployeeDao#save(net.bhira.sample.model.Employee) *///from w w w . j av a2 s .c o m @Override public void save(Employee employee) throws ObjectNotFoundException, InvalidObjectException, InvalidReferenceException { try { if (employee == null) { throw new InvalidObjectException("Employee object is null."); } employee.initForSave(); employee.validate(); boolean isNew = employee.isNew(); int count = 0; if (isNew) { // for new employee, construct SQL insert statement KeyHolder keyHolder = new GeneratedKeyHolder(); count = jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement pstmt = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS); pstmt.setLong(1, employee.getCompanyId()); if (employee.getDepartmentId() == 0) { pstmt.setNull(2, java.sql.Types.BIGINT); } else { pstmt.setLong(2, employee.getDepartmentId()); } pstmt.setString(3, employee.getName()); if (employee.getManagerId() == 0) { pstmt.setNull(4, java.sql.Types.BIGINT); } else { pstmt.setLong(4, employee.getManagerId()); } pstmt.setString(5, employee.getSalutation()); pstmt.setString(6, employee.getSex() == null ? null : employee.getSex().toString()); pstmt.setDate(7, employee.getDOB() == null ? null : new Date(employee.getDOB().getTime())); pstmt.setString(8, employee.getTitle()); pstmt.setString(9, employee.getAddress()); pstmt.setTimestamp(10, new Timestamp(employee.getCreated().getTime())); pstmt.setTimestamp(11, new Timestamp(employee.getModified().getTime())); pstmt.setString(12, employee.getCreatedBy()); pstmt.setString(13, employee.getModifiedBy()); return pstmt; } }, keyHolder); // fetch the newly created auto-increment ID employee.setId(keyHolder.getKey().longValue()); LOG.debug("inserted employee, count = {}, id = {}", count, employee.getId()); } else { // for existing employee, construct SQL update statement Long deptId = employee.getDepartmentId() == 0 ? null : employee.getDepartmentId(); Long mgrId = employee.getManagerId() == 0 ? null : employee.getManagerId(); String sex = employee.getSex() == null ? null : employee.getSex().toString(); Date dob = employee.getDOB() == null ? null : new Date(employee.getDOB().getTime()); Object[] args = new Object[] { employee.getCompanyId(), deptId, employee.getName(), mgrId, employee.getSalutation(), sex, dob, employee.getTitle(), employee.getAddress(), employee.getModified(), employee.getModifiedBy(), employee.getId() }; count = jdbcTemplate.update(SQL_UPDATE, args); LOG.debug("updated employee, count = {}, id = {}", count, employee.getId()); } // if insert/update has 0 count value, then rollback if (count <= 0) { throw new ObjectNotFoundException("Employee with ID " + employee.getId() + " was not found."); } // update dependent entries, as needed if (isNew) { // for new model if there is contact info, save it to contact info table and then // add entry in relationship table if (employee.getContactInfo() != null) { contactInfoDao.save(employee.getContactInfo()); Object[] args = new Object[] { employee.getId(), employee.getContactInfo().getId() }; jdbcTemplate.update(SQL_CINFO_REL_INSERT, args); } } else { // for existing model, fetch contact info ID from relationship table List<Long> cinfoIds = jdbcTemplate.queryForList(SQL_CINFO_REL_LOAD, Long.class, new Object[] { employee.getId() }); Long cinfoId = (cinfoIds != null && !cinfoIds.isEmpty()) ? cinfoIds.get(0) : null; if (employee.getContactInfo() == null) { // clean up old contact info entry, if needed if (cinfoId != null) { jdbcTemplate.update(SQL_CINFO_REL_DELETE, new Object[] { employee.getId() }); contactInfoDao.delete(cinfoId); } } else { // insert/update contact info entry if (cinfoId != null) { employee.getContactInfo().setId(cinfoId); contactInfoDao.save(employee.getContactInfo()); } else { contactInfoDao.save(employee.getContactInfo()); Object[] args = new Object[] { employee.getId(), employee.getContactInfo().getId() }; jdbcTemplate.update(SQL_CINFO_REL_INSERT, args); } } } } catch (DataIntegrityViolationException dive) { String msg = dive.getMessage(); if (msg != null) { if (msg.contains("fk_employee_compy")) { throw new InvalidReferenceException("Invalid reference for attribute 'companyId'", dive); } else if (msg.contains("fk_employee_dept")) { throw new InvalidReferenceException("Invalid reference for attribute 'departmentId'", dive); } else if (msg.contains("fk_employee_mgr")) { throw new InvalidReferenceException("Invalid reference for attribute 'managerId'", dive); } } throw dive; } }
From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java
public void setParams(PreparedStatement st, Object[] params) throws SQLException { if (params != null) { for (int i = 0; i < params.length; i++) { if (params[i] instanceof java.util.Date) { st.setDate(i + 1, new java.sql.Date(((java.util.Date) params[i]).getTime())); } else { st.setObject(i + 1, (Object) params[i]); }/*from w ww.ja va 2 s . co m*/ } } }
From source file:org.apache.phoenix.query.BaseTest.java
protected static void initSaltedEntityHistoryTableValues(String tenantId, byte[][] splits, Date date, Long ts, String url) throws Exception { if (ts == null) { ensureTableCreated(url, ENTITY_HISTORY_SALTED_TABLE_NAME, splits); } else {/* w w w.j ava 2 s . c o m*/ ensureTableCreated(url, ENTITY_HISTORY_SALTED_TABLE_NAME, splits, ts - 2); } Properties props = new Properties(); if (ts != null) { props.setProperty(CURRENT_SCN_ATTRIB, ts.toString()); } Connection conn = DriverManager.getConnection(url, props); try { // Insert all rows at ts PreparedStatement stmt = conn.prepareStatement("upsert into " + ENTITY_HISTORY_SALTED_TABLE_NAME + "(" + " ORGANIZATION_ID, " + " PARENT_ID, " + " CREATED_DATE, " + " ENTITY_HISTORY_ID, " + " OLD_VALUE, " + " NEW_VALUE) " + "VALUES (?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId); stmt.setString(2, PARENTID1); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID1); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID2); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID2); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID3); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID3); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID4); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID4); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID5); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID5); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID6); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID6); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID7); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID7); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID8); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID8); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, PARENTID9); stmt.setDate(3, date); stmt.setString(4, ENTITYHISTID9); stmt.setString(5, A_VALUE); stmt.setString(6, B_VALUE); stmt.execute(); conn.commit(); } finally { conn.close(); } }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
private static void initDateTableValues(String tablename, Connection conn, String tenantId, Date startDate) throws Exception { double dateIncrement = 2.0; PreparedStatement stmt = conn.prepareStatement("upsert into " + tablename + "(" + " ORGANIZATION_ID, " + " \"DATE\", " + " FEATURE, " + " UNIQUE_USERS, " + " TRANSACTIONS, " + " CPU_UTILIZATION, " + " DB_UTILIZATION, " + " REGION, " + " IO_TIME)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId);/*from ww w .j a v a 2 s. c om*/ stmt.setDate(2, startDate); stmt.setString(3, "A"); stmt.setInt(4, 10); stmt.setLong(5, 100L); stmt.setBigDecimal(6, BigDecimal.valueOf(0.5)); stmt.setBigDecimal(7, BigDecimal.valueOf(0.2)); stmt.setString(8, R2); stmt.setNull(9, Types.BIGINT); stmt.execute(); startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement)); stmt.setString(1, tenantId); stmt.setDate(2, startDate); stmt.setString(3, "B"); stmt.setInt(4, 20); stmt.setLong(5, 200); stmt.setBigDecimal(6, BigDecimal.valueOf(1.0)); stmt.setBigDecimal(7, BigDecimal.valueOf(0.4)); stmt.setString(8, null); stmt.setLong(9, 2000); stmt.execute(); startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement)); stmt.setString(1, tenantId); stmt.setDate(2, startDate); stmt.setString(3, "C"); stmt.setInt(4, 30); stmt.setLong(5, 300); stmt.setBigDecimal(6, BigDecimal.valueOf(2.5)); stmt.setBigDecimal(7, BigDecimal.valueOf(0.6)); stmt.setString(8, R1); stmt.setNull(9, Types.BIGINT); stmt.execute(); startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement)); stmt.setString(1, tenantId); stmt.setDate(2, startDate); stmt.setString(3, "D"); stmt.setInt(4, 40); stmt.setLong(5, 400); stmt.setBigDecimal(6, BigDecimal.valueOf(3.0)); stmt.setBigDecimal(7, BigDecimal.valueOf(0.8)); stmt.setString(8, R1); stmt.setLong(9, 4000); stmt.execute(); startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement)); stmt.setString(1, tenantId); stmt.setDate(2, startDate); stmt.setString(3, "E"); stmt.setInt(4, 50); stmt.setLong(5, 500); stmt.setBigDecimal(6, BigDecimal.valueOf(3.5)); stmt.setBigDecimal(7, BigDecimal.valueOf(1.2)); stmt.setString(8, R2); stmt.setLong(9, 5000); stmt.execute(); startDate = new Date(startDate.getTime() + (long) (QueryConstants.MILLIS_IN_DAY * dateIncrement)); stmt.setString(1, tenantId); stmt.setDate(2, startDate); stmt.setString(3, "F"); stmt.setInt(4, 60); stmt.setLong(5, 600); stmt.setBigDecimal(6, BigDecimal.valueOf(4.0)); stmt.setBigDecimal(7, BigDecimal.valueOf(1.4)); stmt.setString(8, null); stmt.setNull(9, Types.BIGINT); stmt.execute(); }
From source file:com.spvp.dal.MySqlDatabase.java
@Override public Boolean ucitajPrognozeUBazu(ArrayList<Prognoza> prognoze) throws SQLException { Connection conn = null;/*from ww w .j a va 2 s. co m*/ Boolean status = false; try { conn = getConnection(); conn.setAutoCommit(false); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT AUTO_INCREMENT " + "FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_SCHEMA = 'weather_forecasting' " + "AND TABLE_NAME = 'historija_prognoze';"); int zadnjiId = -1; rs.next(); zadnjiId = rs.getInt("AUTO_INCREMENT"); int idGrada = -1; PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO historija_prognoze (id, vrijeme, temp, pritisak, brzina_vjetra, vlaznost_zraka, datum) " + "VALUES(?, ?,?,?,?,?,?)"); PreparedStatement pstmt3 = conn .prepareStatement("INSERT INTO gradovi_prognoze (prognoza_id, grad_id) " + "VALUES(?,?)"); for (Prognoza x : prognoze) { pstmt.clearParameters(); pstmt.setInt(1, zadnjiId); pstmt.setString(2, x.getVrijeme()); pstmt.setString(3, x.getTemperatura()); pstmt.setString(4, x.getPritisakZraka()); pstmt.setString(5, x.getBrzinaVjetra()); pstmt.setString(6, x.getVlaznostZraka()); pstmt.setDate(7, new java.sql.Date(x.getDatum().getTime())); pstmt.addBatch(); idGrada = dajIdGradaPoImenu(x.getZaGrad().getImeGrada()); pstmt3.clearParameters(); pstmt3.setInt(1, zadnjiId); pstmt3.setInt(2, idGrada); pstmt3.addBatch(); zadnjiId++; } pstmt.executeBatch(); pstmt3.executeBatch(); conn.commit(); status = true; } catch (SQLException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); if (conn != null) conn.rollback(); } finally { if (conn != null) conn.close(); } return status; }