Select Git revision
UserDAO.java
-
Charlie Darques authoredCharlie Darques authored
UserDAO.java 9.30 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{
if (getUserByLogs(username, password) == null) {
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 = 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, MyThread thread) throws SQLException {
PreparedStatement ps = this.con.prepareStatement("INSERT INTO follow VALUES(?, ?, ?)");
try {
ps.setInt(1, user.getId());
ps.setInt(2, thread.getId());
ps.setString(3, "TO_DATE(" +LocalDate.now().toString() + ", 'YYYY/MM/DD')");
ps.executeUpdate();
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
}
// Poster un message dans un thread
public void postMessage(User user, MyThread thread, String message) throws SQLException {
PreparedStatement ps = this.con.prepareStatement("INSERT INTO msg (userID_msg, threadID, msg, posted_at) VALUES(?, ?, ?, ?)");
try {
System.out.println("dans le try de post message");
ps.setString(1, ""+user.getId());
// PROBLEME ICI
ps.setString(2, ""+thread.getId());
ps.setString(3, message);
ps.setString(4, "TO_DATE(" + LocalDate.now().toString() + ", 'YYYY/MM/DD')");
System.out.println(ps);
ps.executeUpdate();
}
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> getThreadsFollowedByUser(User user) throws SQLException {
List<MyThread> threads = new ArrayList<>();
ThreadDAO threadDAO = new ThreadDAO();
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 = threadDAO.getThreadById(threadId).getThreadName();
threads.add(new MyThread(threadId, threadname));
}
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
return threads;
}
public List<MyThread> getThreadsCreatedByUser(User user) throws SQLException {
List<MyThread> threads = new ArrayList<>();
PreparedStatement ps = this.con.prepareStatement("""
SELECT threadID, threadname FROM thread AS t
INNER JOIN userAccount AS u
ON t.userid_thread = u.userid
WHERE u.userid = ?
""");
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 {
ps.setInt(1, user.getId());
ResultSet rs = ps.executeQuery();
while(rs.next()){
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) {
sqle.getStackTrace();
}
return messages;
}
public LocalDate getFollowDateOfThread(User user, MyThread thread)
throws SQLException {
LocalDate followDate = null;
PreparedStatement ps = this.con.prepareStatement("""
SELECT followDate FROM follow
WHERE userID_follow = ?
AND threadID_follow = ?
""");
try {
ps.setInt(1, user.getId());
ps.setInt(2, thread.getId());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
followDate = rs.getDate(1).toLocalDate(); System.out.println(followDate);
}
}
catch (SQLException sqle) {
sqle.getStackTrace();
}
return followDate;
}
}