API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 20th, 2017
71
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.13 KB | None | 0 0
raw download clone embed print report
  1. /*
  2. * To change this license header, choose License Headers in Project Properties.
  3. * To change this template file, choose Tools | Templates
  4. * and open the template in the editor.
  5. */
  6. package lapr.project.utils;
  7.  
  8. import java.sql.Connection;
  9. import java.sql.DriverManager;
  10. import java.sql.SQLException;
  11. import java.sql.Statement;
  12. import java.util.Map;
  13. import lapr.project.model.Energy;
  14. import lapr.project.model.Gear;
  15. import lapr.project.model.Project;
  16. import lapr.project.model.Regime;
  17. import lapr.project.model.Road;
  18. import lapr.project.model.RoadNetwork;
  19. import lapr.project.model.Vehicle;
  20. import lapr.project.model.VelocityLimit;
  21. import oracle.jdbc.pool.OracleDataSource;
  22. import java.sql.CallableStatement;
  23. import java.util.ArrayList;
  24. import java.util.List;
  25. import java.util.logging.Level;
  26. import java.util.logging.Logger;
  27. import lapr.project.model.FileInputOutput;
  28. import lapr.project.model.Junction;
  29. import lapr.project.model.Section;
  30.  
  31.  
  32. /**
  33. *
  34. * @author Bárbara Inês
  35. */
  36. public class DataBaseConnection {
  37.  
  38.  
  39. public static final String INSERT = "INSERT INTO %s VALUES(%s)";
  40. public static final String INSERT_2 = "SELECT * FROM %s";
  41. public static final String STRING_INSERT = "'%s'";
  42. private static final String DELETE = "DELETE FROM %s";
  43.  
  44. private static final String PROJECT = "Project ";
  45. private static final String ROAD = "Road ";
  46. private static final String ROADNETWORK = "RoadNetwork ";
  47. private static final String TOLLFARE = "Toll_Fare ";
  48. private static final String VEHICLE = "Vehicle ";
  49. private static final String VELOCITY_LIMIT = "Velocity_Limit_List ";
  50. private static final String GEAR = "Gear_List ";
  51. private static final String REGIME = "Regime ";
  52. private static final String JUNCTION = "Junction ";
  53. private static final String SECTION = "Section ";
  54.  
  55. private Connection conn;
  56. private Project project;
  57.  
  58. //fechar a conexão no main qquando o programa fechar
  59. public static void main(String[] args) throws SQLException {
  60. Project a = new Project();
  61. a.setProjectDescription("Babi");
  62. a.setProjectID("01");
  63. // Vehicle v1 = new Vehicle("ola", "01","najdfn",1,"combustion","diesel","125","sdnfi",1.5f,3.0f,5.4f,1.0f,null,null);
  64. // Vehicle v2 = new Vehicle("ole", "02","najdfn",1,"combustion","diesel","125","sdnfi",1.5f,3.0f,2.0f,2.2f,null,null);
  65. // Vehicle v3 = new Vehicle("oli", "03","najdfn",1,"combustion","diesel","125","sdnfi",1.5f,2.0f,5.01f,2.2f,null,null);
  66. // Vehicle v4 = new Vehicle("olo", "04","najdfn",1,"combustion","diesel","125","sdnfi",1.5f,5.0f,2.0f,1.2f,null,null);
  67. // Vehicle v5 = new Vehicle("olu", "05","najdfn",1,"combustion","diesel","125","sdnfi",1.5f,2.0f,1.4f,2.2f,null,null);
  68. // ArrayList<Vehicle> b = new ArrayList<>();
  69. // b.add(v1);
  70. // b.add(v2);
  71. // b.add(v3);
  72. // b.add(v4);
  73. // b.add(v5);
  74. // a.setListVehicles(b);
  75. a = FileInputOutput.importVehicleFile("TestSet02_Vehicles.xml", a);
  76. // FileInputOutput.importNetworkFile("TestSet02_Network", a);
  77. DataBaseConnection hello = new DataBaseConnection(a);
  78. if(!hello.saveProject()){
  79. System.out.println("Inserido com sucesso");
  80. }else{
  81. System.out.println("Não inserido!");
  82. };
  83. // if(!hello.saveVehicle()){
  84. // System.out.println("Inserido com sucesso");
  85. // }else{
  86. // System.out.println("Não inserido!");
  87. // };
  88. hello.closeConnection();
  89. }
  90.  
  91. public DataBaseConnection(Project project) throws SQLException{
  92. // Load Oracle JDBC Driver
  93. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  94.  
  95. //Estabelecer ligação à base de dados
  96. String url = "jdbc:oracle:thin://@vsrvbd1.dei.isep.ipp.pt:1521/pdborcl";
  97. String user = "LAPR3_G35";
  98. String pass = "navegantes";
  99. conn = DriverManager.getConnection(url, user, pass);
  100. this.project = project;
  101. }
  102.  
  103.  
  104. public Boolean saveProject() throws SQLException{
  105.  
  106. //Inserir dados na tabela Project
  107. try (CallableStatement stmt = conn.prepareCall("{call INSERTPROJECT(?,?)}")) {
  108. stmt.setString(1, project.getProjectID());
  109. stmt.setString(2, project.getProjectDescription());
  110. //stmt.executeUpdate();
  111. stmt.execute(); //false significa que guardou
  112. } catch (SQLException ex) {
  113. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  114. return true;
  115. }
  116. saveVehicle();
  117. // saveRoadNetwork();
  118. return false;
  119. }
  120.  
  121. public Boolean saveVehicle() throws SQLException{
  122. for (Vehicle v : project.getListVehicles()) {
  123. // if(!saveVelocity_Limit_List(v) && !saveEnergy(v)){
  124. try (CallableStatement stmt = conn.prepareCall("{call insertVehicle(?,?,?,?,?,?,?,?,?,?,?,?,?)}")) {
  125. stmt.setString(1, project.getProjectID());
  126. stmt.setString(2, v.getDescription());
  127. stmt.setString(3, v.getName());
  128. stmt.setString(4, v.getType());
  129. stmt.setInt(5, v.getTollClass());
  130. stmt.setString(6, v.getMotorization());
  131. stmt.setString(7, v.getFuel());
  132. stmt.setString(8, v.getMass());
  133. stmt.setString(9, v.getLoad());
  134. stmt.setFloat(10, v.getDragCoefficient());
  135. stmt.setFloat(11, v.getFrontalArea());
  136. stmt.setFloat(12, v.getRollingResistanceCoefficient());
  137. stmt.setFloat(13, v.getWheelSize());
  138. stmt.execute();
  139. saveVelocity_Limit_List(v);
  140. saveEnergy(v);
  141. //false significa que guardou
  142. } catch (SQLException ex) {
  143. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  144. return true;
  145. }
  146.  
  147. }
  148. // }
  149.  
  150. return false;
  151. }
  152.  
  153. public void deleteData(String tableName) {
  154. try (Statement st = conn.createStatement()) {
  155. String delete = String.format(DELETE, tableName);
  156. st.executeUpdate(delete);
  157. } catch (SQLException ex) {
  158. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  159. }
  160. }
  161.  
  162. public void closeConnection() throws SQLException {
  163. conn.close();
  164. }
  165.  
  166. public Boolean saveVelocity_Limit_List(Vehicle v) throws SQLException{
  167.  
  168. for (VelocityLimit vl : v.getVelocityLimitList()) {
  169. try (CallableStatement stmt = conn.prepareCall("{call insertVelocity(?,?,?,?)}")) {
  170. stmt.setString(1, v.getDescription());
  171. stmt.setString(2, v.getName());
  172. stmt.setString(3, vl.getSegmentType());
  173. stmt.setFloat(4, vl.getLimit());
  174. stmt.execute();
  175. } catch (SQLException ex) {
  176. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  177. return true;
  178. }
  179. }
  180. return false;
  181.  
  182. }
  183.  
  184. public Boolean saveEnergy(Vehicle v) throws SQLException{
  185. Energy e = v.getEnergy();
  186. // if(!&& !){
  187. try (CallableStatement stmt = conn.prepareCall("{call insertEnergy(?,?,?,?,?,?)}")) {
  188. stmt.setString(1, v.getDescription());
  189. stmt.setString(2, v.getName());
  190. stmt.setFloat(3, e.getMinRPM());
  191. stmt.setFloat(4,e.getMaxRPM() );
  192. stmt.setFloat(5,e.getFinalDriveRatio() );
  193. stmt.setFloat(6,e.getEnergyRegenerationRatio() );
  194. stmt.execute();
  195. saveGear_List(v,e);
  196. saveRegime(v,e);
  197. } catch (SQLException ex) {
  198. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  199. return true;
  200. }
  201. // }
  202. return false;
  203. }
  204.  
  205. public Boolean saveGear_List(Vehicle v, Energy e) throws SQLException{
  206. for (Gear g : e.getGearList()) {
  207. try (CallableStatement stmt = conn.prepareCall("{call insertGear_List(?,?,?,?)}")) {
  208. stmt.setString(1, v.getDescription());
  209. stmt.setString(2, v.getName());
  210. stmt.setString(3, g.getGearID());
  211. stmt.setFloat(4, g.getRatio());
  212. stmt.execute();
  213. } catch (SQLException ex) {
  214. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  215. return true;
  216. }
  217. }
  218. return false;
  219. }
  220.  
  221.  
  222. public Boolean saveRegime(Vehicle v, Energy e) throws SQLException{
  223.  
  224. for (Regime r : e.getRegimeLevels()) {
  225. try (CallableStatement stmt = conn.prepareCall("{call insertRegime(?,?,?,?,?,?,?)}")) {
  226. stmt.setString(1,v.getDescription());
  227. stmt.setString(2,v.getName());
  228. stmt.setString(3, r.getThrottleLevelString());
  229. stmt.setInt(4,r.getTorque());
  230. stmt.setInt(5,r.getRpmLow());
  231. stmt.setInt(6,r.getRpmHigh());
  232. stmt.setFloat(7,r.getSpecificFuelConsumption());
  233. stmt.execute();
  234. } catch (SQLException ex) {
  235. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  236. return true;
  237. }
  238. }
  239. return false;
  240.  
  241. }
  242.  
  243.  
  244. public Boolean saveRoadNetwork() throws SQLException {
  245. if(!saveRoad()){
  246. try (CallableStatement stmt = conn.prepareCall("{call insertRoadNetwork(?,?,?)}")) {
  247. stmt.setString(1, project.getRoadNetwork().getId());
  248. stmt.setString(2, project.getRoadNetwork().getDescription());
  249. stmt.setString(3, project.getProjectID());
  250. return stmt.execute();
  251. } catch (SQLException ex) {
  252. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  253. return true;
  254. }
  255. }return true;
  256. }
  257.  
  258. public Boolean saveRoad() throws SQLException {
  259. for (Road r : project.getRoadNetwork().getRoadList()) {
  260. try (CallableStatement stmt = conn.prepareCall("{call insertRoadNetwork(?,?,?,?,?)}")) {
  261. stmt.setString(1, project.getRoadNetwork().getId());
  262. stmt.setString(2, project.getRoadNetwork().getDescription());
  263. stmt.setString(3, r.getName());
  264. stmt.setString(4, r.getTypology());
  265. stmt.execute();
  266. } catch (SQLException ex) {
  267. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  268. return true;
  269. }
  270. }
  271. return false;
  272. }
  273.  
  274. public Boolean saveJunction() throws SQLException {
  275. for (Junction j : project.getRoadNetwork().getRoadNet().vertices()) {
  276. try (CallableStatement stmt = conn.prepareCall("{call insertNode(?,?,?)}")) {
  277. stmt.setString(1, project.getRoadNetwork().getId());
  278. stmt.setString(2, project.getRoadNetwork().getDescription());
  279. stmt.setString(3, j.getJunctionID());
  280. stmt.execute();
  281. } catch (SQLException ex) {
  282. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  283. return true;
  284. }
  285. }
  286. return false;
  287. }
  288.  
  289. public Boolean saveSection() throws SQLException {
  290. for (Edge<Junction,Section> edge : project.getRoadNetwork().getRoadNet().edges()) {
  291. try (CallableStatement stmt = conn.prepareCall("{call insertSection(?,?,?,?,?,?)}")) {
  292. stmt.setString(1, project.getRoadNetwork().getId());
  293. stmt.setString(2, project.getRoadNetwork().getDescription());
  294. stmt.setString(3, edge.getVOrig().getJunctionID());
  295. stmt.setString(4, edge.getVDest().getJunctionID());
  296. stmt.setString(5, edge.getElement().getRoadID());
  297. stmt.setString(6, edge.getElement().getDirectionString());
  298. stmt.execute();
  299. } catch (SQLException ex) {
  300. Logger.getLogger(DataBaseConnection.class.getName()).log(Level.SEVERE, null, ex);
  301. return true;
  302. }
  303. }
  304. return false;
  305. }
  306. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!