List of usage examples for java.sql PreparedStatement toString
public String toString()
From source file:otn.mobile.bl.TrackServicesHandler.java
private ArrayList<OtnServiceGetTracksListResponse> getSpatialResults(List<OtnServiceGeometryPoints> fromPoints, List<OtnServiceGeometryPoints> toPoints, double radius, OtnServiceGetTracksRequest request, Apps appId, User userId) throws ClassNotFoundException { java.sql.Connection conpg = null; PreparedStatement stmt = null; // Track track; ArrayList<OtnServiceGetTracksListResponse> trackList; TypedQuery query;// w w w . j a va 2 s . c o m List<Track> tracksparamsList; StringBuilder sb; int maxResults = 100; try { /* * Load the JDBC driver and establish a connection. */ Class.forName("org.postgresql.Driver"); String url = "jdbc:postgresql://localhost:5432/otn_web_app"; conpg = DriverManager.getConnection(url, username, password); // conpg = DriverManager.getConnection(url, "postgres", "admin"); // conpg = DriverManager.getConnection(url, "mobile_service", "otn_ck@n"); //*********************Read data***************************** // Point fromPoint = new Point(fromPoints.get(0).getLongitude(), fromPoints.get(0).getLatitude()); // // Point toPoint = new Point(toPoints.get(0).getLongitude(), toPoints.get(0).getLatitude()); // PreparedStatement stmt = conpg.prepareStatement("SELECT id,geom, start_point, end_point,track_id FROM otn_web_app.track_spatial WHERE ST_Distance_Sphere(start_point,ST_GeomFromText(?)) < ? AND ST_Distance_Sphere(end_point, ST_GeomFromText(?)) < ?"); // PreparedStatement stmt = conpg.prepareStatement("SELECT id, end_point,ST_DistanceSphere(end_point, ?) FROM otn_web_app.track_spatial WHERE id > 20 "); // stmt.setObject(1, new org.postgis.PGgeometry(fromPoint)); // stmt.setDouble(2, radius); // stmt.setObject(3, new org.postgis.PGgeometry(toPoint)); // stmt.setDouble(4, radius); if (fromPoints.get(0).getLatitude() != 0 && toPoints.get(0).getLatitude() != 0) { stmt = conpg.prepareStatement( "SELECT id,geom, start_point, end_point,track_id FROM otn_web_app.track_spatial WHERE ST_Distance_Sphere(start_point,ST_GeomFromText('POINT(" + fromPoints.get(0).getLongitude() + " " + fromPoints.get(0).getLatitude() + ")',4326)) < " + radius + " AND ST_Distance_Sphere(end_point, ST_GeomFromText('POINT(" + toPoints.get(0).getLongitude() + " " + toPoints.get(0).getLatitude() + ")',4326)) <" + radius + " "); } else if (fromPoints.get(0).getLatitude() != 0 && toPoints.get(0).getLatitude() == 0) { stmt = conpg.prepareStatement( "SELECT id,geom, start_point, end_point,track_id FROM otn_web_app.track_spatial WHERE ST_Distance_Sphere(start_point,ST_GeomFromText('POINT(" + fromPoints.get(0).getLongitude() + " " + fromPoints.get(0).getLatitude() + ")',4326)) < " + radius + " "); } if (fromPoints.get(0).getLatitude() == 0 && toPoints.get(0).getLatitude() != 0) { stmt = conpg.prepareStatement( "SELECT id,geom, start_point, end_point,track_id FROM otn_web_app.track_spatial WHERE ST_Distance_Sphere(end_point, ST_GeomFromText('POINT(" + toPoints.get(0).getLongitude() + " " + toPoints.get(0).getLatitude() + ")',4326)) <" + radius + " "); } System.out.println(stmt.toString()); String geomPoints; ResultSet r = stmt.executeQuery(); // Statement stmt = conpg.createStatement(); // ResultSet r = stmt.executeQuery("SELECT id, geom FROM otn_web_app.track_spatial "); trackList = new ArrayList<OtnServiceGetTracksListResponse>(); while (r.next()) { sb = new StringBuilder(); /* * Retrieve the geometry as an object then cast it to the geometry type. * Print things out. */ int id = r.getInt(1); int track_id = r.getInt(5); PGgeometry geom = (PGgeometry) r.getObject(2); PGgeometry start_point = (PGgeometry) r.getObject(3); PGgeometry end_point = (PGgeometry) r.getObject(4); System.out.println("Row " + id + ":"); System.out.println(geom.toString()); System.out.println(start_point); System.out.println(end_point); geomPoints = geom.toString(); // track = em.find(Track.class, track_id); String queryString = "SELECT t FROM Track t WHERE t.trackId = :trackId and t.appId = :appId"; sb.append(queryString); if (request.isIsPublic() == null && request.isIsMine() == null) { sb.append(" and t.isPublic = true"); } else if (!(request.isIsPublic() == null) && request.isIsPublic() && request.isIsMine() == null) { sb.append(" and t.isPublic = true"); } else if (request.isIsPublic() == null && !(request.isIsMine() == null) && request.isIsMine()) { System.out.println("is mine"); sb.append(" and t.userId = :userId"); } else if (request.isIsPublic() && !request.isIsMine()) { sb.append(" and t.isPublic = true and t.userId <> :userId"); } else if (request.isIsPublic() && request.isIsMine()) { sb.append(" and t.isPublic = true"); } else if (request.isIsMine() && !request.isIsPublic()) { sb.append(" and t.userId = :userId and t.isPublic = false"); } sb.append(" order by t.creationDate desc "); query = em.createQuery(sb.toString(), Track.class); query.setParameter("trackId", track_id); query.setParameter("appId", appId); if (request.isIsMine() != null) { if (!(!(request.isIsPublic() == null) && request.isIsPublic() && request.isIsMine())) { query.setParameter("userId", userId); } } if (request.isIsPublic() == null && request.isIsMine() == null) { query.setMaxResults(maxResults); } else if (!(request.isIsPublic() == null) && request.isIsPublic() && request.isIsMine() == null) { query.setMaxResults(maxResults); } else if (request.isIsPublic() && !request.isIsMine()) { query.setMaxResults(maxResults); } else if (request.isIsPublic() && request.isIsMine()) { query.setMaxResults(maxResults); } // query = (TypedQuery) em.createQuery("SELECT t FROM Track t WHERE t.trackId = :trackId and t.userId <> :userId and t.appId = :appId order by t.creationDate desc"); // query.setParameter("trackId", track_id); //we use list to avoid "not found" exception tracksparamsList = query.getResultList(); //if we found no results, no public tracks available //so return error message if (!tracksparamsList.isEmpty()) { for (Track track : tracksparamsList) { trackList.add(new OtnServiceGetTracksListResponse(track.getName(), track.getTrackId(), track.getUserId().getUserId(), track.getDescription(), track.getPicture(), track.getDistance(), track.getDuration(), track.getSpeed(), track.getElevation(), track.getTransportTypeId().getName(), track.getStartAddress(), track.getEndAddress(), track.getIsPublic(), geomPoints)); } } } r.close(); // conpg.commit(); // conpg.close(); stmt.close(); return trackList; } catch (SQLException ex) { //Logger.getLogger(InsertPointPostGis.class.getName()).log(Level.SEVERE, null, ex); return null; } finally { //finally block used to close resources try { if (stmt != null) { conpg.close(); } } catch (SQLException se) { } // do nothing try { if (conpg != null) { conpg.close(); } } catch (SQLException se) { se.printStackTrace(); } //end finally try } }
From source file:com.cmart.DB.CassandraDBQuery.java
public User getPublicUser(long userID) { if (userID < 1) return null; User user = null;//from w w w .ja v a 2 s . c o m int attemptsRemaining = SQL_RETRIES; do { Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; try { statement = conn.prepareStatement("SELECT username,rating FROM users WHERE KEY=" + userID); ResultSet rs = statement.executeQuery(); // Read the user's details if (rs.next()) { try { user = new User(userID, rs.getString("username"), new Long(rs.getLong("rating")).toString()); } catch (Exception e) { } } rs.close(); attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("CassandraQuery (getPublicUser): Could not get the user"); System.out.println(statement.toString()); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return user; }
From source file:org.osmdroid.reader.Reader.java
/** * imports the osm bz2 file into the database * * @param path//from w w w . jav a 2 s. c om * @param connection * @throws Exception if the file wasn't found, can't write the output file, or there's some kind of IO exception while reading */ public void read(String path, Connection connection) throws Exception { if (path == null) throw new IllegalArgumentException("path"); if (!new File(path).exists()) throw new FileNotFoundException("File Not Found"); PreparedStatement p; try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"nodes\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"lat\" DOUBLE NOT NULL , \"lon\" DOUBLE NOT NULL , \"version\" INTEGER, \"timestamp\" DATETIME, \"uid\" INTEGER, \"user\" TEXT, \"changeset\" INTEGER)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"relation_members\" (\"type\" TEXT NOT NULL , \"ref\" INTEGER NOT NULL , \"role\" TEXT, \"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL )"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"relations\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"user\" TEXT, \"uid\" INTEGER, \"version\" INTEGER, \"changeset\" INTEGER, \"timestamp\" DATETIME)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"tag\" (\"id\" INTEGER NOT NULL , \"k\" TEXT NOT NULL , \"v\" TEXT NOT NULL , \"reftype\" INTEGER NOT NULL DEFAULT -1, PRIMARY KEY( \"reftype\",\"k\" ,\"id\" ) )"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"way_no\" (\"way_id\" INTEGER NOT NULL , \"node_id\" INTEGER NOT NULL, PRIMARY KEY (\"way_id\", \"node_id\") ) "); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"ways\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"changeset\" INTEGER, \"version\" INTEGER, \"user\" TEXT, \"uid\" INTEGER, \"timestamp\" DATETIME)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } BufferedReader xmlInputStream = getBufferedReaderForBZ2File(path); XmlPullParserFactory factory = XmlPullParserFactory.newInstance(); factory.setNamespaceAware(false); XmlPullParser xmlStreamReader = factory.newPullParser(); xmlStreamReader.setInput(xmlInputStream); Stack<String> xpath = new Stack<String>(); long recordCount = 0; long batchCount = 0; long lastId = -1; long start = System.currentTimeMillis(); OsmType lastType = OsmType.NODE; long id = -1; long changset = -1; double lat = 0.0; double lon = 0.0; long version = -1; String user = ""; long uid = -1; long inserts = 0; String key = ""; String val = ""; //int eventType = -1; Date timestamp = new Date(System.currentTimeMillis()); //connection.setAutoCommit(false); int eventType = xmlStreamReader.getEventType(); while (eventType != XmlPullParser.END_DOCUMENT) { String tagname = xmlStreamReader.getName(); recordCount++; key = ""; val = ""; id = -1; changset = -1; version = -1; user = ""; uid = -1; //timestamp = new Date(System.currentTimeMillis()); //System.out.println(recordCount); //System.out.println ("XMLEvent " + eventType + " " + tagname); //long btime = System.currentTimeMillis(); switch (eventType) { case XmlPullParser.START_TAG: if (xmlStreamReader.getName().toString().equalsIgnoreCase("osm")) { } if (xmlStreamReader.getName().toString().equalsIgnoreCase("bounds")) { } if (xmlStreamReader.getName().toString().equalsIgnoreCase("node")) { xpath.push(xmlStreamReader.getName().toString()); p = connection .prepareStatement("INSERT INTO nodes (id,changeset,version,user,uid,timestamp,lat,lon) " + "VALUES (?,?,?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("lat")) { lat = Double.parseDouble(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("lon")) { lon = Double.parseDouble(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); p.setDouble(7, lat); p.setDouble(8, lon); try { p.executeUpdate(); inserts++; //batchCount++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.NODE; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("relation")) { xpath.push(xmlStreamReader.getName().toString()); p = connection .prepareStatement("INSERT INTO relations (id,changeset,version,user,uid,timestamp) " + "VALUES (?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } else { System.err.println("relation attrib unhandled " + xmlStreamReader.getAttributeName(i)); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); try { p.executeUpdate(); // batchCount++; inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.RELATION; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("way")) { xpath.push(xmlStreamReader.getName().toString()); p = connection.prepareStatement("INSERT INTO ways (id,changeset,version,user,uid,timestamp) " + "VALUES (?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { //System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); try { p.executeUpdate(); inserts++; // batchCount++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.WAY; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("tag")) { if (!xpath.isEmpty() && ((xpath.peek().equalsIgnoreCase("way") || xpath.peek().equalsIgnoreCase("node") || xpath.peek().equalsIgnoreCase("relation")) && lastId != -1)) { for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { //System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("k")) { key = xmlStreamReader.getAttributeValue(i); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("v")) { val = xmlStreamReader.getAttributeValue(i); } else { //uncaptured attribute System.out.println(xmlStreamReader.getAttributeName(i) + "=" + xmlStreamReader.getAttributeValue(i)); } } if (lastId != -1) { p = connection .prepareStatement("INSERT INTO tag (id,k,v,reftype) " + "VALUES (?,?,?,?); "); p.setLong(1, lastId); p.setString(2, key); p.setString(3, val); p.setInt(4, lastType.ordinal()); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } // batchCount++; p.close(); p = null; } else { System.err.println("ERR0003"); } } else { System.err.println("ERR0002"); } } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("nd")) { if (xpath.peek().equalsIgnoreCase("way") && lastId != -1) { id = -1; for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("ref")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } } if (id != -1) { p = connection .prepareStatement("INSERT INTO way_no (way_id,node_id) " + "VALUES (?,?); "); p.setLong(1, lastId); p.setLong(2, id); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; //batchCount++; } } else { System.err.println("ERR0001"); } } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("member")) { if ((xpath.peek().equalsIgnoreCase("relation")) && lastId != -1) { //String type = ""; id = -1; //String role = ""; for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("type")) { key = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("ref")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("role")) { val = xmlStreamReader.getAttributeValue(i); } } if (lastId != -1) { p = connection.prepareStatement( "INSERT INTO relation_members (id,type,ref,role) " + "VALUES (?,?,?,?); "); p.setLong(1, lastId); p.setString(2, key); p.setLong(3, id); p.setString(4, val); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; // batchCount++; } else { System.err.println("ERR0006"); } } else { System.err.println("ERR0005"); } } else { System.err.println("unhandled node! " + xmlStreamReader.getName().toString()); } break; case XmlPullParser.TEXT: //System.out.print("text!" + xmlStreamReader.getText()); break; case XmlPullParser.END_TAG: //System.out.println("</" + xmlStreamReader.getName().toString() + ">"); if (xmlStreamReader.getName().toString().equalsIgnoreCase("node")) { if (!xpath.isEmpty()) { xpath.pop(); } } if (xmlStreamReader.getName().toString().equalsIgnoreCase("way")) { if (!xpath.isEmpty()) { xpath.pop(); } } if (xmlStreamReader.getName().toString().equalsIgnoreCase("relation")) { if (!xpath.isEmpty()) { xpath.pop(); } } break; default: //do nothing break; } //if (batchCount == 100) { //connection.commit(); // long txPerSecond = (System.currentTimeMillis() - btime); System.out.println((start - System.currentTimeMillis()) + " total elements processed " + recordCount + " inserts " + inserts + " stack " + xpath.size()); //batchCount = 0; //System.gc(); //System.out.println(); // } eventType = xmlStreamReader.next(); } System.out.println(System.currentTimeMillis() - start + "ms"); }
From source file:com.ws.WS_TCS201.java
@Path("/Act") @JSONP(queryParam = "callback") @POST/*from w w w . java 2 s . co m*/ @Produces({ "application/x-javascript" }) public String Act(@QueryParam("callback") String callback, @FormParam("state") String state, @FormParam("com") String com, @FormParam("emp") String emp, @FormParam("tcd") String tcd, @FormParam("year") String year, @FormParam("strdate") String strdate, @FormParam("enddate") String enddate, @FormParam("days") String days, @FormParam("comment") String comment, @FormParam("oldstrdate") String oldstrdate, @Context HttpServletResponse servletResponse) { JSONObject obj1 = new JSONObject(); LinkedList l1 = new LinkedList(); String cSQL = ""; int yeartkb = 0, yeartkt = 0, cnttkb = 0, cnttkt = 0; float cntdays = 0, allowdays = 0, tmplst = 0; String tmptck = "-"; String msg = ""; String yearStartDate = ""; PreparedStatement prepStmt = null; try { if (!state.equals("D") && (msg.equals(""))) { // if (state.equals("I")) { oldstrdate = "0"; } //? cSQL = " SELECT tceemp, tceapd, tceall, tcetkb, tcetkt FROM TCSTCE " + " WHERE tcecom= ? AND tceemp= ? " + " ORDER BY tceapd DESC"; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); ResultSet result = prepStmt.executeQuery(); if (result.next()) { // Object obj = result.getObject(3); allowdays = Integer.parseInt(obj.toString()); obj = result.getObject(4); yeartkb = Integer.parseInt(obj.toString()); obj = result.getObject(5); yeartkt = Integer.parseInt(obj.toString()); //*************************************************************** if (msg.equals("")) { obj = result.getObject(2); yearStartDate = obj.toString(); if (Integer.parseInt(yearStartDate) <= 20100913) { //2010.9.13?? 01/01 if ((Integer.parseInt(strdate.substring(4)) < Integer.parseInt("0101")) && (Integer.parseInt(enddate.substring(4)) >= Integer.parseInt("0101"))) { msg = "C"; } } else { //2010.9.13? ?? yearStartDate = year + yearStartDate.substring(4); if ((Integer.parseInt(strdate) < Integer.parseInt(yearStartDate)) && (Integer.parseInt(enddate) >= Integer.parseInt(yearStartDate))) { msg = "C"; } } } } else { msg = "H"; } // if ((Integer.parseInt(strdate) > Integer.parseInt(enddate)) && (msg.equals(""))) { msg = "A"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorA.html"); //return "N"; } //??***************************************************** if ((state.equals("I") || (state.equals("U") && !strdate.equals(oldstrdate))) && (msg.equals(""))) { cSQL = "SELECT * FROM TCSTCH " + "WHERE TCHCOM=? AND TCHEMP=? AND " + " ?<=TCHDED AND ?>=TCHDST "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); //prepStmt.setString(3, tcd); prepStmt.setString(3, strdate); prepStmt.setString(4, enddate); result = prepStmt.executeQuery(); if (result.next()) { msg = "B"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorB.html"); //return "N"; } } //************************************************************* /* if ((Integer.parseInt(strdate.substring(0,4)) != Integer.parseInt(enddate.substring(0,4))) && (msg.equals(""))) { msg = "C:??"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorC.html"); //return "N"; }*/ //<=- SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); Calendar c1 = Calendar.getInstance(); Calendar c2 = Calendar.getInstance(); c1.setTime(sdf.parse(strdate)); c2.setTime(sdf.parse(enddate)); cntdays = c2.get(Calendar.DATE) - c1.get(Calendar.DATE) + 1; if ((Integer.parseInt(days) > cntdays) && (msg.equals(""))) { msg = "E"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorE.html"); //return "N"; } //?? if (tcd.equals("B") || tcd.equals("T")) { cSQL = " SELECT SUM(CASE WHEN tchtcd = \"B\" THEN 1 ELSE 0 END ) AS sumtkb," + " SUM(CASE WHEN tchtcd = \"T\" AND tchtck = \"A\" THEN 1 WHEN tchtcd = \"T\" AND tchtck = \"B\" THEN 2 ELSE 0 END ) AS sumtkt " + " FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? AND tchyer = ? AND tchdst <> ? AND tchtcd IN (\"B\",\"T\") AND tchtck NOT IN (\"-\",\"0\") "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, strdate); result = prepStmt.executeQuery(); if (result.next()) { Object obj = result.getObject(1); if (obj != null) { cnttkb = Integer.parseInt(obj.toString()); } obj = result.getObject(2); if (obj != null) { cnttkt = Integer.parseInt(obj.toString()); } } if (cnttkb > yeartkb || cnttkt > yeartkt) { tmptck = "0"; } } else if (tcd.equals("M") && (msg.equals(""))) { cSQL = " SELECT * FROM TCSTCH " + " WHERE tchcom = ? AND tchemp= ? AND tchtcd = ? AND tchtck NOT IN (\"-\",\"X\") "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, tcd); result = prepStmt.executeQuery(); if (result.next()) { msg = "F"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorF.html"); //return "N"; } } else if (tcd.equals("F")) { cSQL = " SELECT COUNT(*) AS times FROM TCSTCH " + " WHERE tchcom = ? AND tchemp= ? AND tchyer = ? AND tchtcd = ? AND tchtck NOT IN (\"-\",\"X\") "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, tcd); result = prepStmt.executeQuery(); if (result.next()) { Object obj = result.getObject(1); if (obj != null && Integer.parseInt(obj.toString()) == 1) { tmptck = "X"; } } } //?******************************************************** if (tcd.equals("C") && (msg.equals(""))) { cSQL = " SELECT tchtcd, sum(tchday) AS tchday FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? " + " AND tchyer = ? AND tchdst <> ? " + " AND tchtcd = \"C\" " + " GROUP BY tchemp "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, oldstrdate); result = prepStmt.executeQuery(); if (!result.wasNull()) { Object obj = result.getObject(2); cntdays = Integer.parseInt(obj.toString()) + Integer.parseInt(days); if (cntdays > 5) { msg = "G"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorG.html"); //return "N"; } } } if (msg.equals("")) { //<=? cSQL = " SELECT * FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? " + " AND tchyer = ? AND tchdst <> ? " + " ORDER BY tchdst DESC "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, oldstrdate); result = prepStmt.executeQuery(); if (result.next()) { Object obj = result.getObject(9); tmplst = Float.parseFloat(obj.toString()); if (state.equals("U")) { obj = result.getObject(8); tmplst = tmplst + Integer.parseInt(obj.toString()); } tmplst = tmplst - Integer.parseInt(days); } else { tmplst = allowdays - Integer.parseInt(days); } if (tmplst < 0) { msg = "D"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorD.html"); //return "N"; } } } if (!msg.equals("")) { obj1.put("Msg", msg); return obj1.toString(); } // DateFormat day = new SimpleDateFormat("yyyyMMdd"); String upddate = day.format(new java.util.Date()); DateFormat time = new SimpleDateFormat("HHmmss"); String updtime = time.format(new java.util.Date()); if (state.equals("I")) { cSQL = " INSERT INTO TCSTCH VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setInt(2, Integer.parseInt(year)); prepStmt.setString(3, emp); prepStmt.setString(4, tcd); prepStmt.setFloat(5, Float.parseFloat(String.valueOf(allowdays))); prepStmt.setInt(6, Integer.parseInt(strdate)); prepStmt.setInt(7, Integer.parseInt(enddate)); prepStmt.setFloat(8, Float.parseFloat(days)); prepStmt.setFloat(9, tmplst); prepStmt.setString(10, tmptck); prepStmt.setString(11, ""); prepStmt.setString(12, comment); prepStmt.setString(13, "WS_TCS201"); prepStmt.setString(14, "TEST"); prepStmt.setInt(15, Integer.parseInt(upddate)); prepStmt.setInt(16, Integer.parseInt(updtime)); if (prepStmt.executeUpdate() == 0) { obj1.put("Msg", "SI"); return obj1.toString(); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorI.html"); //return "N"; } } else if (state.equals("U")) { cSQL = " UPDATE TCSTCH " + " SET tchdst = ?, " + " tchded = ?, " + " tchday = ?, " + " tchlst = ?, " + " tchtxt = ?, " + " tchpg = ?, " + " tchus = ?, " + " tchdt = ?, " + " tchtm = ? " + " WHERE tchcom = ? AND tchemp = ? AND tchtcd = ? AND tchdst = ? "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setInt(1, Integer.parseInt(strdate)); prepStmt.setInt(2, Integer.parseInt(enddate)); prepStmt.setFloat(3, Float.parseFloat(days)); prepStmt.setFloat(4, tmplst); prepStmt.setString(5, comment); prepStmt.setString(6, "WS_TCS201"); prepStmt.setString(7, "TEST"); prepStmt.setInt(8, Integer.parseInt(upddate)); prepStmt.setInt(9, Integer.parseInt(updtime)); prepStmt.setString(10, com); prepStmt.setString(11, emp); prepStmt.setString(12, tcd); prepStmt.setString(13, oldstrdate); String tmptest = prepStmt.toString(); if (prepStmt.executeUpdate() == 0) { obj1.put("Msg", "SU"); return obj1.toString(); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorU.html"); //return "N"; } } else if (state.equals("D")) { cSQL = " DELETE FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? AND tchtcd = ? AND tchdst = ? "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, tcd); prepStmt.setString(4, oldstrdate); if (prepStmt.executeUpdate() == 0) { obj1.put("Msg", "SD"); return obj1.toString(); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorD.html"); //return "N"; } } obj1.put("Msg", "SY"); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_Success.html"); } catch (SQLException e) { prepStmt = null; e.printStackTrace(); } catch (Exception e) { prepStmt = null; e.printStackTrace(); } return obj1.toString(); //return "Y"; }