Select Git revision
UserDAO.java
-
Charlie Darques authoredCharlie Darques authored
UserDAO.java 7.51 KiB
package dao;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import dto.Message;
import dto.MyThread;
import dto.User;
public class UserDAO {
private Connection con;
public UserDAO(){
this.con = new BDConnection().getConnection();
if (this.con == null) System.out.println("Connection nulle");
}
public void createUser(String username, String password){
try{
PreparedStatement stmt = this.con.prepareStatement("INSERT INTO userAccount (username, password) VALUES (?,?)");
stmt.setString(1, username);
stmt.setString(2, password);
stmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public List<User> getAllUsers(){
ArrayList<User> users = new ArrayList<>();
try{
Statement stmt = this.con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM userAccount;");
while(rs.next()){
int userId = rs.getInt(1);
String username = rs.getString(2);
String pwd = rs.getString(3);
User nUser = new User(userId, username, pwd);
users.add(nUser);
}
} catch (SQLException sqle) {
sqle.getStackTrace();
}
return users;
}
public boolean isDatabased(User user) {
boolean isIn = false;
try {
PreparedStatement stmt = this.con.prepareStatement("SELECT * FROM userAccount WHERE userid=?");
stmt.setInt(1, user.getId());
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
isIn = true;
}
} catch (SQLException sqle) {
sqle.printStackTrace();
}
return isIn;
}
public User getUserByLogs(String username, String password){
User user = new User(0,null,null);
try{
PreparedStatement stmt = this.con.prepareStatement("SELECT * FROM userAccount WHERE username=? AND password=?");
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
int userId = rs.getInt(1);
String login = rs.getString(2);
String pwd = rs.getString(3);
user = new User(userId,login,pwd);
}
} catch (SQLException sqle) {
sqle.getStackTrace();
}
return user;
}
public User getUserById(int id){
User user = new User(0,null,null);
try{
PreparedStatement stmt = this.con.prepareStatement("SELECT * FROM userAccount WHERE id=?");
stmt.setInt(1, id);
ResultSet rs = stmt.executeQuery();
if(rs.next()){
int userId = rs.getInt(1);
String username = rs.getString(2);
String pwd = rs.getString(3);
user = new User(userId,username,pwd);
}
} catch (SQLException sqle) {
sqle.getStackTrace();
}
return user;
}
public void addUser(User nUser){
try{
PreparedStatement stmt = this.con.prepareStatement("INSERT INTO userAccount (username, password) VALUES (?,?)");
stmt.setString(1, nUser.getUserName());
stmt.setString(2, nUser.getPwd());
stmt.executeUpdate();
} catch (SQLException sqle) {
sqle.getStackTrace();
}
}
public void removeUser(User nUser){
try{
PreparedStatement stmt = this.con.prepareStatement("DELETE FROM userAccount WHERE userID=?");
stmt.setInt(1, nUser.getId());
stmt.executeUpdate();
} catch (SQLException sqle) {
sqle.getStackTrace();
}
}
// S'abonner à un thread
public void followThread(User user, Thread thread) throws SQLException {
PreparedStatement ps = this.con.prepareStatement("INSERT INTO follow VALUES(?, ?, ?)");
try {
ps.setString(1, ""+user.getId());
ps.setString(2, ""+thread.getId());
ps.setString(3, LocalDate.now().toString());
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
}
// Poster un message dans un thread
public void postMessage(User user, Thread thread, String message) throws SQLException {
PreparedStatement ps = this.con.prepareStatement("INSERT INTO msg (userID_msg, threadID, msg) VALUES(?, ?, ?)");
try {
ps.setInt(1, user.getId());
ps.setInt(2, (int) thread.getId());
ps.setString(3, message);
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
}
// Réagir à un message
public void reactToMsg(User user, Message msg) throws SQLException {
PreparedStatement ps = this.con.prepareStatement("INSERT INTO reactions VALUES(?, ?, ?)");
try {
ps.setInt(1, user.getId());
ps.setInt(2, msg.getMsgId());
ps.setString(3, "true");
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
}
// Récupérer les thread auxquels un utilisateur est abonné
public List<MyThread> getThreadsByUser(User user) throws SQLException {
List<MyThread> threads = new ArrayList<>();
PreparedStatement ps = this.con.prepareStatement("SELECT threadID_follow FROM follow WHERE userID_follow = ?");
try {
ps.setInt(1, user.getId());
ResultSet rs = ps.executeQuery();
while(rs.next()){
int threadId = rs.getInt(1);
String threadname = rs.getString(2);
threads.add(new MyThread(threadId, threadname));
}
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
return threads;
}
// Récupérer les messages de chaque thread à partir du plus récent
public List<Message> getMostRecentMessages(User user) throws SQLException {
List<Message> messages = new ArrayList<>();
PreparedStatement ps = this.con.prepareStatement("""
SELECT m.msgID, m.userID_msg, m.threadID, m.msg, m.posted_at FROM msg AS m
INNER JOIN thread AS t ON m.threadid = t.threadid
WHERE m.threadID IN (
SELECT f.threadid_follow FROM follow AS f
INNER JOIN thread AS t
ON t.threadid = f.threadid_follow
INNER JOIN userAccount AS u
ON u.userid = f.userid_follow
WHERE u.userID = ?)
ORDER BY m.posted_at DESC;
""");
try {
System.out.println("user id : " + user.getId());
ps.setInt(1, user.getId());
System.out.println("dans le try");
ResultSet rs = ps.executeQuery();
while(rs.next()){
System.out.println("dans le while");
int msgID = rs.getInt(1);
int sender = rs.getInt(2);
int threadID = rs.getInt(3);
String content = rs.getString(4);
Date creationDate = rs.getDate(5);
messages.add(new Message(msgID, sender, threadID, content, creationDate.toLocalDate()));
}
}
catch (SQLException sqle) {
System.out.println("erreur");
sqle.getStackTrace();
}
System.out.println("test");
return messages;
}
}