API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 27th, 2016
60
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.66 KB | None | 0 0
raw download clone embed print report
  1. package dataacesslayer;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.ResultSet;
  5. import java.sql.ResultSetMetaData;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.util.ArrayList;
  9.  
  10. import connection.Connector;
  11.  
  12. public class DataAccessLayer {
  13.  
  14. private static String sql;
  15.  
  16. // Uppg 2 hämta valfri Employee Tabell
  17. public ArrayList<String> getTabelData(String from) throws SQLException {
  18. ArrayList<String> list = new ArrayList<String>();
  19. Connection con = Connector.goConnector();
  20. try {
  21. sql = "select * from [" + from + "];";
  22.  
  23. Statement stmt = con.createStatement();
  24. ResultSet rs = stmt.executeQuery(sql);
  25. int k = 5;
  26.  
  27. while (rs.next()) {
  28. for (int i = 1; i <= k; i++) {
  29. list.add(rs.getString(i));
  30. }
  31. }
  32. return list;
  33.  
  34. } catch (SQLException e) {
  35. return null;
  36. }
  37. }
  38.  
  39. // Uppg 2 få in Tabellens label
  40. public ArrayList<String> getTabelLabel(String from) throws SQLException {
  41. ArrayList<String> list = new ArrayList<String>();
  42. Connection con = Connector.goConnector();
  43. try {
  44. sql = "select * from [" + from + "];";
  45.  
  46. Statement stmt = con.createStatement();
  47. ResultSet rs = stmt.executeQuery(sql);
  48. ResultSetMetaData rsmd = rs.getMetaData();
  49.  
  50. int k = 5;
  51.  
  52. for (int i = 1; i <= k; i++) {
  53. list.add(rsmd.getColumnLabel(i));
  54. }
  55. return list;
  56. } catch (SQLException e) {
  57. return null;
  58. }
  59. }
  60. // Uppg 2 Nycklar*
  61. public ArrayList<String> getKeys() throws SQLException {
  62. ArrayList<String> list = new ArrayList<String>();
  63.  
  64. Connection con = Connector.goConnector();
  65. try {
  66. sql = "SELECT TABLE_NAME, COLUMN_NAME " + "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE";
  67.  
  68. Statement stmt = con.createStatement();
  69. ResultSet rs = stmt.executeQuery(sql);
  70. // ResultSetMetaData rsmd = rs.getMetaData();
  71. int j = 2;
  72. while (rs.next()) {
  73. for (int i = 1; i <= j; i++) {
  74. list.add(rs.getString(i));
  75. }
  76. }
  77. return list;
  78.  
  79. } catch (SQLException e) {
  80. return null;
  81. }
  82. }
  83.  
  84. // Uppg 2 få Nycklarnas Label
  85. public ArrayList<String> getKeysLabel() throws SQLException {
  86. ArrayList<String> list = new ArrayList<String>();
  87. Connection con = Connector.goConnector();
  88.  
  89. try {
  90. sql = "SELECT TABLE_NAME, COLUMN_NAME " + "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE";
  91.  
  92. Statement stmt = con.createStatement();
  93. ResultSet rs = stmt.executeQuery(sql);
  94. ResultSetMetaData rsmd = rs.getMetaData();
  95. int j = rsmd.getColumnCount();
  96. while (rs.next()) {
  97. for (int i = 1; i <= j; i++) {
  98. list.add(rsmd.getColumnLabel(i));
  99. }
  100. }
  101. return list;
  102.  
  103. } catch (SQLException e) {
  104. return null;
  105. }
  106. }
  107.  
  108. // Uppg 2 få Indexs Label *
  109. public ArrayList<String> getIndexLabel() throws SQLException {
  110. ArrayList<String> list = new ArrayList<String>();
  111.  
  112. Connection con = Connector.goConnector();
  113. try {
  114. sql = "SELECT * FROM sys.indexes";
  115.  
  116. Statement stmt = con.createStatement();
  117. ResultSet rs = stmt.executeQuery(sql);
  118. ResultSetMetaData rsmd = rs.getMetaData();
  119. int j = rsmd.getColumnCount();
  120. for (int i = 1; i <= j; i++) {
  121. list.add(rsmd.getColumnLabel(i));
  122. }
  123. return list;
  124.  
  125. } catch (SQLException e) {
  126. return null;
  127. }
  128. }
  129. // Uppg 2 få Index
  130. public ArrayList<String> getIndex() throws SQLException {
  131. ArrayList<String> list = new ArrayList<String>();
  132.  
  133. Connection con = Connector.goConnector();
  134. try {
  135. sql = "SELECT * FROM sys.indexes";
  136.  
  137. Statement stmt = con.createStatement();
  138. ResultSet rs = stmt.executeQuery(sql);
  139. ResultSetMetaData rsmd = rs.getMetaData();
  140. int j = rsmd.getColumnCount();
  141. while (rs.next()) {
  142. for (int i = 1; i <= j; i++) {
  143. list.add(rs.getString(i));
  144. }
  145. }
  146. return list;
  147.  
  148. } catch (SQLException e) {
  149. return null;
  150. }
  151. }
  152.  
  153. // Uppg 2 Constraints Label
  154. public ArrayList<String> getConstraintsLabel() throws SQLException {
  155. ArrayList<String> list = new ArrayList<String>();
  156. try {
  157. Connection con = Connector.goConnector();
  158.  
  159. sql = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS";
  160.  
  161. Statement stmt = con.createStatement();
  162. ResultSet rs = stmt.executeQuery(sql);
  163. ResultSetMetaData rsmd = rs.getMetaData();
  164. int j = rsmd.getColumnCount();
  165. for (int i = 1; i <= j; i++) {
  166. list.add(rsmd.getColumnLabel(i));
  167. }
  168. return list;
  169.  
  170. } catch (SQLException e) {
  171. return null;
  172. }
  173. }
  174.  
  175. // Uppg 2 få Constraints
  176. public ArrayList<String> getConstraints() throws SQLException {
  177. ArrayList<String> list = new ArrayList<String>();
  178. try {
  179. Connection con = Connector.goConnector();
  180.  
  181. sql = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS";
  182.  
  183. Statement stmt = con.createStatement();
  184. ResultSet rs = stmt.executeQuery(sql);
  185. ResultSetMetaData rsmd = rs.getMetaData();
  186. int j = rsmd.getColumnCount();
  187. while (rs.next()) {
  188. for (int i = 1; i <= j; i++) {
  189. list.add(rs.getString(1));
  190. }
  191. }
  192. return list;
  193.  
  194. } catch (SQLException e) {
  195. return null;
  196. }
  197. }
  198.  
  199. // Uppg 2 få alla Columns Lösning 1
  200. public ArrayList<String> getColumnsFirst() throws SQLException {
  201. ArrayList<String> list = new ArrayList<String>();
  202. Connection con = Connector.goConnector();
  203. try {
  204. sql = "select COLUMN_NAME" + " from INFORMATION_SCHEMA.COLUMNS "
  205. + "where TABLE_NAME = 'CRONUS Sverige AB$Employee'";
  206.  
  207. Statement stmt = con.createStatement();
  208. ResultSet rs = stmt.executeQuery(sql);
  209. ResultSetMetaData rsmd = rs.getMetaData();
  210. while (rs.next()) {
  211. for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  212. list.add(rs.getString(i));
  213. }
  214. }
  215. return list;
  216.  
  217. } catch (SQLException e) {
  218. return null;
  219. }
  220. }
  221. // Uppg2 få alla Columns Lösning 2
  222. public ArrayList<String> getColumnsSecond() throws SQLException {
  223. ArrayList<String> list = new ArrayList<String>();
  224. Connection con = Connector.goConnector();
  225. try {
  226. sql = "select c.name " + "from sys.columns c join sys.tables t " + "on c.object_id=t.object_id "
  227. + "where t.name='CRONUS Sverige AB$Employee'" + " order by t.name";
  228.  
  229. Statement stmt = con.createStatement();
  230. ResultSet rs = stmt.executeQuery(sql);
  231. ResultSetMetaData rsmd = rs.getMetaData();
  232. while (rs.next()) {
  233. for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  234. list.add(rs.getString(i));
  235. }
  236. }
  237. return list;
  238.  
  239. } catch (SQLException e) {
  240. return null;
  241. }
  242. }
  243.  
  244. // Uppg 2 hämta alla Tables nammn
  245. public ArrayList<String> getTablesOne() throws SQLException {
  246. ArrayList<String> list = new ArrayList<String>();
  247.  
  248. Connection con = Connector.goConnector();
  249. try {
  250. sql = "SELECT * FROM sys.tables";
  251.  
  252. Statement stmt = con.createStatement();
  253. ResultSet rs = stmt.executeQuery(sql);
  254. while (rs.next()) {
  255. list.add(rs.getString(1));
  256. }
  257. return list;
  258. } catch (SQLException e) {
  259. return null;
  260. }
  261. }
  262. // Uppg2 hämta alla Tables names Labels
  263. public ArrayList<String> getTablesOneLabel() throws SQLException {
  264. ArrayList<String> list = new ArrayList<String>();
  265.  
  266. Connection con = Connector.goConnector();
  267. try {
  268. sql = "SELECT * FROM sys.tables";
  269.  
  270. Statement stmt = con.createStatement();
  271. ResultSet rs = stmt.executeQuery(sql);
  272. ResultSetMetaData rsmd = rs.getMetaData();
  273.  
  274. list.add(rsmd.getColumnLabel(1));
  275. return list;
  276.  
  277. } catch (SQLException e) {
  278. return null;
  279. }
  280. }
  281. // Uppg 2 hämta alla namn på Tables lösning2
  282. public ArrayList<String> getTablesTwo() throws SQLException {
  283. ArrayList<String> list = new ArrayList<String>();
  284.  
  285. Connection con = Connector.goConnector();
  286. try {
  287. sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
  288.  
  289. Statement stmt = con.createStatement();
  290. ResultSet rs = stmt.executeQuery(sql);
  291. ResultSetMetaData rsmd = rs.getMetaData();
  292.  
  293. while (rs.next()) {
  294. list.add(rs.getString(3));
  295. }
  296. return list;
  297. } catch (SQLException e) {
  298. return null;
  299. }
  300. }
  301. // Uppg2 hämmta alla Tables name lösning 2 Labels
  302. public ArrayList<String> getTablesTwoLabel() throws SQLException {
  303. ArrayList<String> list = new ArrayList<String>();
  304.  
  305. Connection con = Connector.goConnector();
  306. try {
  307. sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
  308.  
  309. Statement stmt = con.createStatement();
  310. ResultSet rs = stmt.executeQuery(sql);
  311. ResultSetMetaData rsmd = rs.getMetaData();
  312.  
  313. list.add(rsmd.getColumnLabel(3));
  314. return list;
  315.  
  316. } catch (SQLException e) {
  317. return null;
  318. }
  319. }
  320.  
  321. // Uppg 2 Den rad som har MaxRader(flest rader) *
  322. public ArrayList<String> getMaxRows() throws SQLException {
  323. ArrayList<String> list = new ArrayList<String>();
  324.  
  325. Connection con = Connector.goConnector();
  326.  
  327. sql = "SELECT TOP 1 t.name, p.rows " + "FROM sys.tables t JOIN sys.partitions p "
  328. + "ON t.object_id = p.object_id " + "ORDER BY rows DESC";
  329.  
  330. Statement stmt = con.createStatement();
  331. ResultSet rs = stmt.executeQuery(sql);
  332. ResultSetMetaData rsmd = rs.getMetaData();
  333.  
  334. int j = rsmd.getColumnCount();
  335. while (rs.next()) {
  336. for (int i = 1; i <= j; i++) {
  337. list.add(rs.getString(i));
  338. }
  339. }
  340. return list;
  341. } // Uppg 2, Label till MaxRow
  342. public ArrayList<String> getMaxRowsLabel() throws SQLException {
  343. ArrayList<String> list = new ArrayList<String>();
  344.  
  345. Connection con = Connector.goConnector();
  346.  
  347. sql = "SELECT TOP 1 t.name, p.rows " + "FROM sys.tables t JOIN sys.partitions p "
  348. + "ON t.object_id = p.object_id " + "ORDER BY rows DESC";
  349.  
  350. Statement stmt = con.createStatement();
  351. ResultSet rs = stmt.executeQuery(sql);
  352. ResultSetMetaData rsmd = rs.getMetaData();
  353.  
  354. int j = rsmd.getColumnCount();
  355. while (rs.next()) {
  356. for (int i = 1; i <= j; i++) {
  357. list.add(rsmd.getColumnLabel(i));
  358. }
  359. }
  360. return list;
  361. }
  362. }
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!