Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.Vector;
- public class Database_class {
- String driver;
- Connection conn;
- private Vector dataVector;
- /*--------------- SQL-METHODS FOR DB-CONNECTION -----------*/
- public Database_class() throws SQLException {
- driver = "org.apache.derby.jdbc.EmbeddedDriver";
- conn = DriverManager.getConnection("jdbc:derby:testdb;create=true");
- }
- void connect2db() throws SQLException {
- try {
- Class.forName(driver); // load Driver
- System.out.println(driver + " loaded. ");
- } catch (java.lang.ClassNotFoundException e) {
- System.err.print("ClassNotFoundException: ");
- System.err.println(e.getMessage());
- }
- }
- void closedb() throws SQLException {
- conn.close();
- if (driver.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
- boolean gotSQLExc = false;
- try {
- DriverManager.getConnection("jdbc:derby:;shutdown=true");
- } catch (SQLException se) {
- if (se.getSQLState().equals("XJ015")) {
- gotSQLExc = true;
- }
- }
- if (!gotSQLExc) {
- System.out.println("Database did not shut down normally");
- } else {
- System.out.println("Database shut down normally");
- }
- }
- }
- /*--------------- SQL-METHODS FOR VOCAB -------------------*/
- void queryVocab_debug() throws SQLException {
- System.out.println("Debug message: getVocab_debug()");
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("select * from Vocable");
- System.out.println("ID:\tEnglisch:\t\t\tDeutsch:\t\t\tKategorie:");
- System.out
- .println("-------------------------------------------------------------\n");
- while (result.next()) { // Prints all results
- System.out.println(result.getString(1) + "\t" + result.getString(2)
- + "\t" + result.getString(3) + "\t" + result.getString(4));
- }
- System.out.println("\n");
- }
- ResultSet queryVocab() throws SQLException {
- System.out.println("Debug message: queryVocab()");
- Statement s = conn.createStatement();
- ResultSet result = s
- .executeQuery("select Vocable.VID, Vocable.Word, Vocable.Transl, Category.Name from Vocable inner join Category on Vocable.Cat = Category.CID");
- return result;
- }
- void insertVocab(String value, String transl, int cat) throws SQLException {
- System.out.println("Debug message: insertVocab('" + value + "', '"
- + transl + "', '" + cat + "')");
- Statement s = conn.createStatement();
- s.execute("insert into Vocable(Word, Transl, Cat) values " + "('"
- + value + "','" + transl + "'," + cat + ")");
- }
- void deleteVocab(String value, boolean choice) throws SQLException {
- System.out.println("Debug message: deleteVocab(" + value + ", "
- + choice + ")");
- Statement s = conn.createStatement();
- int vID = getVocab_ID(value, choice);
- s.execute("Delete from Vocable where VID = " + vID);
- System.out.println(vID + " deleted from table Vocable");
- }
- void deleteVocab_Word(String value) throws SQLException {
- System.out.println("Debug message: deleteVocab_Word(" + value + ")");
- Statement s = conn.createStatement();
- s.execute("Delete from Vocable where Word = '" + value + "'");
- System.out.println(value + " deleted from table Vocable");
- }
- int getVocab_ID(String value, boolean choice) throws SQLException {
- System.out.println("Debug message: getVocab_ID(" + value + ", "
- + choice + ")");
- int vID = 0;
- Statement s = conn.createStatement();
- if (choice)
- s.execute("select VID from Vocable where Transl = '" + value + "'");
- else
- s.execute("select VID from Vocable where Word = '" + value + "'");
- return vID;
- }
- /*--------------- SQL-METHODS FOR CATEGORY ----------------*/
- ResultSet queryCategory() throws SQLException {
- System.out.println("Debug message: queryCategory()");
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("select * from Category");
- return result;
- }
- void insertCategory(String value) throws SQLException {
- System.out.println("Debug message: insertCategory(" + value + ")");
- Statement s = conn.createStatement();
- s.execute("insert into Category(Name) values " + "('" + value + "')");
- }
- void deleteCategory(int value) throws SQLException {
- if(alterCategoryifNotEmpty(value)){
- System.out.println("Debug message: deleteCategory(" + value + ")");
- Statement s = conn.createStatement();
- s.execute("Delete from Category where CID = " + value);
- }
- }
- int getCategoryID(String value) throws SQLException {
- int returnval = 0;
- Statement s = conn.createStatement();
- ResultSet result = s
- .executeQuery("select CID from Category where Name = '" + value
- + "'");
- while (result.next()) {
- returnval = result.getInt(1);
- }
- return returnval;
- }
- String getCategoryName(int id) throws SQLException {
- System.out.println("Debug message: getCategoryName(" + id + ")");
- String value = "";
- Statement s = conn.createStatement();
- ResultSet result = s
- .executeQuery("Select Name from Category where CID = " + id);
- while (result.next())
- value = result.getString(1);
- return value;
- }
- /**
- * Alter Category of Vocabs in Preparation to delete a category from the
- * table.
- *
- * @param id
- * @throws SQLException
- */
- boolean alterCategoryifNotEmpty(int id) throws SQLException {
- System.out.println("Debug message: checkifCategoryEmpty()");
- Statement s = conn.createStatement();
- Statement s1 = conn.createStatement();
- if (id != 1) {
- ResultSet result = s
- .executeQuery("Select VID, Cat from Vocable where Cat = "
- + id);
- while (result.next()) {
- s1.executeUpdate("update Vocable set Cat = 1 where Cat = "
- + result.getString(2));
- }
- return true;
- } else {
- System.out.println("Debug message: Go fuck yourself! I ain't gonna delete myself!");
- return false;
- }
- }
- /*--------------- SQL-METHODS FOR DBUSER ------------------*/
- /**
- * Debug-method that returns a full list of all Users to the console.
- *
- * @throws SQLEception
- */
- void queryUser_debug() throws SQLException {
- System.out.println("Debug message: queryUser_debug()");
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("select * from DBUser");
- System.out.println("UserID:\tName:");
- System.out
- .println("-------------------------------------------------------------\n");
- while (result.next()) { // prints all results
- System.out
- .println(result.getString(1) + "\t" + result.getString(2));
- }
- System.out.println("\n");
- }
- /**
- * Queries the Userdatabase and returns a full list of all Users.
- *
- * @return list of all DBUsers
- * @throws SQLException
- */
- ResultSet queryUser() throws SQLException {
- System.out.println("Debug message: queryUser()");
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("select * from DBUser");
- return result;
- }
- /**
- * Inserts a User {@value} into the database.
- *
- * @param value
- * @throws SQLException
- */
- void insertUser(String value) throws SQLException {
- System.out.println("Debug message: insertUser(" + value + ")");
- Statement s = conn.createStatement();
- s.execute("insert into DBUser(Name) values " + "('" + value + "')");
- }
- /**
- * Deletes a User {@value} from the database.
- *
- * @param value
- * @throws SQLException
- */
- void deleteUser(String value) throws SQLException {
- System.out.println("Debug message: deleteUser(" + value + ")");
- Statement s = conn.createStatement();
- s.execute("Delete from DBUser where Name = '" + value + "'");
- }
- /* -------------- END OF SQL-METHODS ---------------------- */
- ArrayList<String> getCategory() throws SQLException {
- ArrayList<String> result_list = new ArrayList(1);
- ArrayList<Integer> result_list_nr = new ArrayList(1);
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("select Name from Category");
- while (result.next()) {
- result_list.add(result.getString("Name"));
- }
- return result_list;
- }
- /* begin for User-SQL-Statements */
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement