· 8 years ago · Apr 11, 2018, 03:44 PM
1package de.tum.wzw.genomeannotation.database;
2
3import java.io.BufferedReader;
4import java.io.FileReader;
5import java.io.IOException;
6import java.sql.DriverManager;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.Properties;
10
11import com.mysql.jdbc.Connection;
12
13import de.tum.wzw.genomeannotation.file.Util;
14
15public class DatabaseOperations {
16
17 public static Connection getConnection(Properties credentials) {
18 Connection conn = null;
19
20 try {
21 Class.forName("com.mysql.jdbc.Driver").newInstance();
22
23 conn = (Connection) DriverManager.getConnection(credentials.getProperty("url"), credentials.getProperty("user"), credentials.getProperty("password"));
24
25 System.out.println("Connection to database: "+conn.getHost());
26 } catch (InstantiationException e) {
27 e.printStackTrace();
28 } catch (IllegalAccessException e) {
29 e.printStackTrace();
30 } catch (ClassNotFoundException e) {
31 e.printStackTrace();
32 } catch (SQLException e) {
33 e.printStackTrace();
34 }
35
36 return conn;
37 }
38
39 public static void createTable(String fileName) {
40 Connection conn = null;
41 Statement stmt = null;
42
43 BufferedReader br = null;
44
45 try {
46 conn = getConnection(Util.DB_CREDENTIALS);
47
48 br = new BufferedReader(new FileReader(Util.CONFIG_ABSOLUTE+fileName));
49
50 StringBuilder create = new StringBuilder();
51 create.append("CREATE TABLE IF NOT EXISTS ");
52
53 // skip first line
54 br.readLine();
55 // name
56 create.append(br.readLine());
57
58 create.append("(");
59
60 // fields
61 String fileLine = "";
62 while ((fileLine = br.readLine()) != null) {
63 if (!fileLine.startsWith("#")) {
64 create.append(fileLine);
65 }
66 }
67
68 create.append(")");
69
70 stmt = conn.createStatement();
71 stmt.executeUpdate(create.toString());
72 } catch (SQLException e) {
73 e.printStackTrace();
74 } catch (IOException e) {
75 e.printStackTrace();
76 } finally {
77 try { if (br != null) br.close(); } catch (IOException e) { e.printStackTrace(); };
78 // close statement and connection
79 try { if (stmt != null) stmt.close(); } catch (SQLException se) {};
80 try { if (conn != null) conn.close(); } catch (SQLException se) {};
81 }
82 }
83
84 public static void alterTable(String tableName, String name, String type, String position) {
85 Connection conn = null;
86 Statement stmt = null;
87
88 try {
89 conn = getConnection(Util.DB_CREDENTIALS);
90
91 String alter = "";
92
93 if (position.equals("")) {
94 alter = "ALTER TABLE "+tableName+" ADD "+name+" "+type;
95 } else {
96 alter = "ALTER TABLE "+tableName+" ADD "+name+" "+type+" AFTER "+position;
97 }
98
99 stmt = conn.createStatement();
100 stmt.execute(alter);
101 } catch (SQLException e) {
102 e.printStackTrace();
103 } finally {
104 // close statement and connection
105 try { if (stmt != null) stmt.close(); } catch (SQLException se) {};
106 try { if (conn != null) conn.close(); } catch (SQLException se) {};
107 }
108 }
109
110 public static boolean getBatchStatus(int[] batch) {
111 boolean status = true;
112
113 for (int command : batch) {
114 if (command >= 1) {
115 status = true;
116 }
117
118 if (command == Statement.SUCCESS_NO_INFO) {
119 status = false;
120 break;
121 }
122
123 if (command == Statement.EXECUTE_FAILED) {
124 status = false;
125 break;
126 }
127 }
128
129 return status;
130 }
131
132 public static void insertDataFromFile(String fileName, String tableName) {
133 Connection conn = null;
134 Statement stmt = null;
135
136 try {
137 conn = getConnection(Util.DB_CREDENTIALS);
138
139 String query = "LOAD DATA LOCAL INFILE '"+fileName+"' INTO TABLE "+tableName;
140 stmt = conn.createStatement();
141 stmt.executeUpdate(query);
142 } catch (SQLException e) {
143 e.printStackTrace();
144 } finally {
145 // close statement and connection
146 try { if (stmt != null) stmt.close(); } catch (SQLException se) {};
147 try { if (conn != null) conn.close(); } catch (SQLException se) {};
148 }
149 }
150}