391 lines
12 KiB
Java
391 lines
12 KiB
Java
package edu.hsog.db;
|
|
|
|
import javax.swing.*;
|
|
import java.sql.*;
|
|
import java.util.ArrayList;
|
|
|
|
public class DBQueries {
|
|
|
|
// Count ohne try-with-resource und mit statement
|
|
public static int count() {
|
|
|
|
Connection con = Globals.getPoolConnection();
|
|
Statement st = null;
|
|
ResultSet rs = null;
|
|
try {
|
|
st = con.createStatement();
|
|
String q = "select count(*)\n" +
|
|
"from gadgets";
|
|
rs = st.executeQuery(q);
|
|
rs.next();
|
|
return rs.getInt(1);
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
} finally {
|
|
try {
|
|
if (rs != null) rs.close();
|
|
if (st != null) st.close();
|
|
if (con != null) con.close();
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
}
|
|
}
|
|
}
|
|
|
|
// Count mit try-with-resource und mit statement
|
|
/*
|
|
public static int count() {
|
|
String query = "SELECT COUNT(*) FROM gadgets";
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
Statement st = con.createStatement();
|
|
ResultSet rs = st.executeQuery(query)) {
|
|
|
|
rs.next(); // Kein if, da COUNT(*) immer ein Ergebnis liefert
|
|
return rs.getInt(1);
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error while counting gadgets", e);
|
|
}
|
|
*/
|
|
|
|
// Count mit try-with-resource und mit prepared-statement
|
|
/*
|
|
public static int count() {
|
|
String query = "SELECT COUNT(*) FROM gadgets";
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query);
|
|
ResultSet rs = pst.executeQuery()) {
|
|
|
|
rs.next(); // Kein if, da COUNT(*) immer ein Ergebnis liefert
|
|
return rs.getInt(1);
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error while counting gadgets", e);
|
|
}
|
|
}
|
|
*/
|
|
|
|
// Login mit try-with-resource und mit statement
|
|
/*
|
|
public static boolean login(String username, String password) {
|
|
|
|
Connection con = Globals.getPoolConnection();
|
|
Statement st = null;
|
|
ResultSet rs = null;
|
|
try {
|
|
st = con.createStatement();
|
|
|
|
String q = "select count(*)\n" +
|
|
"from users\n" +
|
|
"where email = '" + username + "' and passwd = '" + password + "'";
|
|
rs = st.executeQuery(q);
|
|
rs.next();
|
|
int c = rs.getInt(1);
|
|
return (c == 1);
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
} finally {
|
|
try {
|
|
if (rs != null) rs.close();
|
|
if (st != null) st.close();
|
|
if (con != null) con.close();
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
}
|
|
}
|
|
}
|
|
*/
|
|
|
|
// Login mit try-with-resource und mit prepared-statement
|
|
public static boolean login(String username, String password) {
|
|
String query = "SELECT COUNT(*) FROM users WHERE email = ? AND passwd = ?";
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
// Parameter setzen
|
|
pst.setString(1, username);
|
|
pst.setString(2, password);
|
|
|
|
try (ResultSet rs = pst.executeQuery()) {
|
|
rs.next(); // Kein if notwendig, COUNT(*) liefert immer eine Zeile
|
|
int count = rs.getInt(1);
|
|
return count == 1;
|
|
}
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error during login attempt", e);
|
|
}
|
|
}
|
|
|
|
public static ArrayList showAll() {
|
|
String query = "SELECT * FROM gadgets";
|
|
ArrayList icons = new ArrayList<Icon>();
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
// Parameter setzen
|
|
// pst.setString(1, username);
|
|
try (ResultSet rs = pst.executeQuery()) {
|
|
while (rs.next()) {
|
|
Icon i = Converter.blob2Icon(rs.getBlob("cover"));
|
|
icons.add(i);
|
|
}
|
|
return icons;
|
|
}
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error during login attempt", e);
|
|
}
|
|
}
|
|
|
|
public static ImageIcon getImage(int z) {
|
|
String query = "SELECT * from gadgets order by url asc";
|
|
|
|
ImageIcon imIcon;
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)){
|
|
ResultSet rs = pst.executeQuery();
|
|
|
|
for(int i = 0; i<z;i++) {
|
|
rs.next();
|
|
}
|
|
Blob b = rs.getBlob("COVER");
|
|
imIcon = Converter.blob2ImageIcon(b);
|
|
|
|
}catch (SQLException e) {
|
|
throw new RuntimeException("Error while counting gadgets", e);
|
|
}
|
|
return imIcon;
|
|
}
|
|
|
|
public static ImageIcon getImageByUrl(String partialUrl) {
|
|
String query = "SELECT * FROM gadgets WHERE url LIKE ?";
|
|
|
|
ImageIcon imIcon = null;
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
pst.setString(1, "%" + partialUrl + "%"); // Teilstring-Suche
|
|
ResultSet rs = pst.executeQuery();
|
|
|
|
if (rs.next()) {
|
|
Blob b = rs.getBlob("COVER");
|
|
imIcon = Converter.blob2ImageIcon(b);
|
|
} else {
|
|
System.out.println("Keine passende URL gefunden für: " + partialUrl);
|
|
}
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Fehler beim Abrufen des Bildes für URL-Teil: " + partialUrl, e);
|
|
}
|
|
|
|
return imIcon;
|
|
}
|
|
public static ImageIcon getImageByDesc(String desc) {
|
|
String query = "SELECT * FROM gadgets WHERE DESCRIPTION = ?";
|
|
|
|
ImageIcon imIcon = null;
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
pst.setString(1, desc);
|
|
ResultSet rs = pst.executeQuery();
|
|
|
|
if (rs.next()) {
|
|
Blob b = rs.getBlob("COVER");
|
|
imIcon = Converter.blob2ImageIcon(b);
|
|
}
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Fehler beim Abrufen des Bildes für URL: " + desc, e);
|
|
}
|
|
|
|
return imIcon;
|
|
}
|
|
|
|
public static void insertImageUrlEmail(String url, String email) {
|
|
String query = "insert into gadgets(url,email,cover)values(?,?,?)";
|
|
Icon icon = Converter.generatePatternIcon();
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
Blob coverBlob = Converter.icon2Blob(icon,con);
|
|
// Parameter setzen
|
|
pst.setString(1, url);
|
|
pst.setString(2, email);
|
|
pst.setBlob(3,coverBlob);
|
|
|
|
pst.executeQuery();
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error during login attempt", e);
|
|
}
|
|
}
|
|
|
|
public static void updateItemLikeKeywords(String eingabe) {
|
|
String query = "update gadgets g set g.cover = ? where keywords like ?";
|
|
Icon icon = Converter.generatePatternIcon();
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
// Parameter setzen
|
|
pst.setBlob(1,Converter.icon2Blob(icon,con));
|
|
pst.setString(2, eingabe);
|
|
|
|
pst.executeUpdate();
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error during login attempt", e);
|
|
}
|
|
}
|
|
|
|
public static ArrayList<Icon> iconListEmail(String username) {
|
|
String query = "SELECT cover from gadgets where email = ?";
|
|
Icon icon = Converter.generatePatternIcon();
|
|
ArrayList<Icon>myList = new ArrayList<>();
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
pst.setString(1,username);
|
|
ResultSet rs = pst.executeQuery();
|
|
|
|
while (rs.next()){
|
|
|
|
icon = Converter.blob2Icon(rs.getBlob("cover"));
|
|
myList.add(icon);
|
|
|
|
}
|
|
return myList;
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error during login attempt", e);
|
|
}
|
|
}
|
|
|
|
|
|
public static int searchGadgetsUrlIndex(String url) {
|
|
|
|
Connection con = Globals.getPoolConnection();
|
|
Statement st = null;
|
|
ResultSet rs = null;
|
|
try {
|
|
st = con.createStatement();
|
|
String q = "SELECT * from gadgets";
|
|
rs = st.executeQuery(q);
|
|
int lineCounter = 1;
|
|
String foundURL = null;
|
|
|
|
while (rs.next()){
|
|
if(rs.getString(1).contains(url)){
|
|
foundURL = url;
|
|
break;
|
|
}
|
|
lineCounter ++;
|
|
}
|
|
if(foundURL != null){
|
|
return lineCounter;
|
|
}
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
} finally {
|
|
try {
|
|
if (rs != null) rs.close();
|
|
if (st != null) st.close();
|
|
if (con != null) con.close();
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
}
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
public static String zeilenSpalten(int z, int col) {
|
|
String query = "select * from bewertung order by url asc";
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
|
|
ResultSet rs = st.executeQuery(query )) {
|
|
|
|
rs.next();
|
|
// Parameter setzen
|
|
rs.absolute(z);
|
|
|
|
if(col== 1){
|
|
return rs.getString("email");
|
|
}
|
|
if(col== 2){
|
|
return rs.getString("url");
|
|
}
|
|
if(col== 3){
|
|
return rs.getString("bewertung");
|
|
}
|
|
if(col== 4){
|
|
return rs.getString("kommentars");
|
|
}
|
|
} catch (SQLException ex) {
|
|
throw new RuntimeException(ex);
|
|
}
|
|
|
|
|
|
return "..";
|
|
}
|
|
|
|
|
|
public static DTO emailPassword(String teil) {
|
|
String query = "SELECT EMAIL,PASSWD FROM users WHERE email like ? ";
|
|
|
|
try (Connection con = Globals.getPoolConnection();
|
|
PreparedStatement pst = con.prepareStatement(query)) {
|
|
|
|
// Parameter setzen
|
|
pst.setString(1,"%"+ teil+"%");
|
|
|
|
|
|
ResultSet rs = pst.executeQuery();
|
|
rs.next(); // Kein if notwendig, COUNT(*) liefert immer eine Zeile
|
|
|
|
String email = rs.getString("EMAIL");
|
|
String passwd = rs.getString("PASSWD");
|
|
|
|
return new DTO(email,passwd);
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException("Error during login attempt", e);
|
|
}
|
|
}
|
|
|
|
public static int sumGefallenUrl(String eingabe) {
|
|
|
|
Connection con = Globals.getPoolConnection();
|
|
Statement st = null;
|
|
ResultSet rs = null;
|
|
try {
|
|
st = con.createStatement();
|
|
String q = "select sum(gefallen) from bewertung where url like '%" + eingabe +"%' ";
|
|
rs = st.executeQuery(q);
|
|
|
|
if(rs.next()){
|
|
return rs.getInt(1);
|
|
}else {
|
|
return 0;
|
|
}
|
|
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
} finally {
|
|
try {
|
|
if (rs != null) rs.close();
|
|
if (st != null) st.close();
|
|
if (con != null) con.close();
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
}
|
|
}
|
|
}
|
|
}
|