Files
Datenbanken/db/DBQueries.java
2025-05-22 09:01:21 +02:00

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);
}
}
}
}