package com.chen.dao.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import com.chen.factory.ConnectionFactory;
import com.chen.pojo.Droit;
import com.chen.pojo.User;
public class UserDao {
private Connection con;
private PreparedStatement ps;
public UserDao() {
con = ConnectionFactory.getConnection();
}
public void deleteUser(Integer id) throws SQLException {
String sql1 = \"delete from user where id = ?\";
String sql2 = \"delete from u_d where user_id = ?\";
ps = con.prepareStatement(sql1);
ps.setInt(1, id);
ps.executeUpdate();
ps = con.prepareStatement(sql2);
ps.setInt(1, id);
ps.executeUpdate();
}
public User findUserById(Integer id) throws SQLException {
String sql = \"select user.id,user.name,user.password,user.actor,droit.name \"
+ \"from user,u_d,droit \"
+ \"where user.id=u_d.user_id and droit.id=u_d.droit_id and user.id = ?\";
ps = con.prepareStatement(sql);
ps.setInt(1, id);
ResultSet result = ps.executeQuery();
User user = new User();
HashSet<Droit> set = new HashSet<Droit>();
while (result.next()) {
user.setId(Integer.parseInt(result.getString(1)));
user.setName(result.getString(2));
user.setPassword(result.getString(3));
user.setActor(result.getString(4));
Droit droit = new Droit();
droit.setName(result.getString(5));
set.add(droit);
}
user.setDroits(set);
return user;
}
public User findByName(String name, String psw) throws SQLException {
String sql = \"select user.id,user.name,user.password,user.actor,droit.name \"
+ \"from user,u_d,droit where user.id=u_d.user_id and \"
+ \"droit.id=u_d.droit_id and user.name = ? and user.password = ?\";
ps = con.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, psw);
ResultSet result = ps.executeQuery();
User user = new User();
HashSet<Droit> set = new HashSet<Droit>();
while (result.next()) {
user.setId(Integer.parseInt(result.getString(1)));
user.setName(result.getString(2));
user.setPassword(result.getString(3));
user.setActor(result.getString(4));
Droit droit = new Droit();
droit.setName(result.getString(5));
set.add(droit);
}
user.setDroits(set);
return user;
}
public HashSet findUserByAll() throws SQLException {
String sql1 = \"select user.id,user.name,user.password,user.actor from user \";
String sql2 = \"select droit.id,droit.name from u_d,user,droit \"
+ \" where u_d.droit_id=droit.id and u_d.user_id = ?\";
HashSet<User> users = new HashSet<User>();
ps = con.prepareStatement(sql1);
ResultSet rs1 = ps.executeQuery();
while (rs1.next()) {
User user = new User();
user.setId(Integer.parseInt(rs1.getString(1)));
user.setName(rs1.getString(2));
user.setPassword(rs1.getString(3));
user.setActor(rs1.getString(4));
ps = con.prepareStatement(sql2);
ps.setLong(1, user.getId());
ResultSet rs2 = ps.executeQuery();
HashSet droits = new HashSet();
while (rs2.next()) {
Droit droit = new Droit();
droit.setId(rs2.getInt(1));
droit.setName(rs2.getString(2));
droits.add(droit);
}
user.setDroits(droits);
users.add(user);
}
return users;
}
public void addUser(User user) throws SQLException {
String sql1 = \"insert into user values(?,?,?,?)\";
ps = con.prepareStatement(sql1);
user.setId(sum(\"user\")+1);
System.out.println(user.getId());
ps.setInt(1, user.getId());
ps.setString(2, user.getPassword());
ps.setString(3, user.getName());
ps.setString(4, user.getActor());
ps.executeUpdate();
String sql2 = \"insert into droit values(?,?)\";
PreparedStatement ps1 = con.prepareStatement(sql2);
HashSet<Droit> ls = user.getDroits();
Integer j = sum(\"droit\");
for (Iterator<Droit> it = ls.iterator(); it.hasNext();) {
Droit droit = it.next();
droit.setId(++j);
ps1.setInt(1, droit.getId());
ps1.setString(2, droit.getName());
ps1.executeUpdate();
}
String sql3 = \"insert into u_d values(?,?)\";
PreparedStatement ps2 = con.prepareStatement(sql3);
ps2.setInt(1, user.getId());
HashSet<Droit> dt = user.getDroits();
for (Iterator<Droit> tt = dt.iterator(); tt.hasNext();) {
Droit droit = tt.next();
ps2.setInt(2, droit.getId());
ps2.executeUpdate();
}
}
public void modifyUser(User user) throws SQLException {
/*
* String sql1 = \"update user set user_name=?,password = ? where id=?\";
* String sql2 = \"update user_actor_droit set actor_id = ?,droit_id =?
* where user_id=?\"; try { ps = con.prepareStatement(sql1);
* ps.setString(1, user.getName()); ps.setString(2, user.getPassword());
* ps.setString(3, user.getId()); ps.executeUpdate();
*
* ps = con.prepareStatement(sql2); Actor actor = user.getActor(); for
* (Iterator<Droit> it = actor.getDroit().iterator(); it.hasNext();) {
* Droit droit = it.next(); ps.setString(1, actor.getId());
* ps.setString(2, droit.getId()); ps.setString(3, user.getId());
* ps.executeUpdate(); } } catch (SQLException e) { e.printStackTrace(); }
*/
String sql1 = \"update user set name=?,password=?,actor=? where id=?\";
String sql2 = \"update u_d set droit_id =? where user_id=?\";
ps = con.prepareStatement(sql1);
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getActor());
ps.setInt(4, user.getId());
ps.executeUpdate();
ps = con.prepareStatement(sql2);
for(Iterator it = user.getDroits().iterator();it.hasNext();){
Droit droit = (Droit) it.next();
ps.setInt(1, droit.getId());
ps.setInt(2, user.getId());
ps.executeUpdate();
}
}
private Integer sum(String table) {
String sql = \"select max(id) from \" + table;
try {
ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.isLast()){
System.out.println(rs.getInt(1));
return rs.getInt(1);}
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
return 0;
}
public boolean close() {
try {
ps.close();
ConnectionFactory.close();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
public HashSet findByActor(String actor) throws SQLException {
HashSet users = new HashSet();
String sql1 = \"select user.id,user.name,user.password,user.actor from user\";
String sql2 = \"select droit.id,droit.name from u_d,droit where u_d.droit_id=droit.id and u_d.user_id = ?\";
ps = con.prepareStatement(sql1);
ResultSet rs = ps.executeQuery();
while(rs.next()){
User user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setPassword(rs.getString(3));
user.setActor(rs.getString(4));
HashSet<Droit> droits = new HashSet();
ps = con.prepareStatement(sql2);
ps.setInt(1, user.getId());
ResultSet rs2 = ps.executeQuery();
while(rs2.next()){
Droit droit = new Droit();
droit.setId(rs2.getInt(1));
droit.setName(rs2.getString(2));
droits.add(droit);
}
user.setDroits(droits);
users.add(user);
}
return users;
}
}
程序代码：
