第四步:建立數據庫操作對象 (完成對表的增刪修改與查詢)
package mystruts;
import java.sql.*;
import ConnectionPool.*;
import java.util.*;
public class userdao {
getDbConnection db=null;
Connection con=null;
public userdao() {
}
//-------------------------------------------------------------------
public void userInsert(userActionForm uaf1)
{
db=new getDbConnection();
con=db.getCon();
userActionForm uaf=uaf1;
PreparedStatement ps = null;
String sql="insert into myuser (user_id,user_name,dep,tel) values(?,?,?,?)";
try {
ps = con.prepareStatement(sql);
ps.setString(1,uaf.getUser_id());
ps.setString(2,uaf.getUser_name());
ps.setString(3,uaf.getDep());
ps.setString(4,uaf.getTel());
ps.executeUpdate();
}
catch (SQLException e)
{
System.out.println("sql error");
}
finally
{
try{
con.close();
db.d_close();
}
catch(Exception e) {}
}
}
//---------------------------------------------
public void userUpdate(userActionForm uaf1)
{
db=new getDbConnection();
con=db.getCon();
userActionForm uaf=uaf1;
PreparedStatement ps = null;
String sql="update myuser set user_name=?,dep=?,tel=? where user_id=?";
try {
ps = con.prepareStatement(sql);
ps.setString(1,uaf.getUser_name());
ps.setString(2,uaf.getDep());
ps.setString(3,uaf.getTel());
ps.setString(4,uaf.getUser_id());
ps.executeUpdate();
}
catch (SQLException e)
{
System.out.println("sql error");
}
finally
{
try{
con.close();
db.d_close();
}
catch(Exception e) {}
}
}
//---------------------------------------------
public userActionForm getUser(String key)
{
db=new getDbConnection();
con=db.getCon();
ResultSet rs=null;
userActionForm uaf=new userActionForm();
PreparedStatement ps = null;
String sql="select * from myuser where user_id=?";
try {
ps = con.prepareStatement(sql);
ps.setString(1,key.trim());
rs=ps.executeQuery();
if(rs.next())
{
uaf.setUser_id(rs.getString("user_id"));
uaf.setUser_name(rs.getString("user_name"));
uaf.setDep(rs.getString("dep"));
uaf.setTel(rs.getString("tel"));
uaf.setAction("edit");
}
}
catch (SQLException e)
{
System.out.println("sql error");
}
finally
{
try{
con.close();
db.d_close();
}
catch(Exception e) {}
}
System.out.println("mod"+key);
return uaf;
}
//----------------------------------------
public void delUser(String key)
{
db=new getDbConnection();
con=db.getCon();
PreparedStatement ps = null;
String sql="delete from myuser where user_id=?";
try {
ps = con.prepareStatement(sql);
ps.setString(1,key.trim());
ps.executeUpdate();
}
catch (SQLException e)
{
System.out.println("sql error");
}
finally
{
try{
con.close();
db.d_close();
}
catch(Exception e) {}
}
System.out.println("del"+key);
}
//----------------------------------
public Collection getAlluser()
{
db=new getDbConnection();
con=db.getCon();
ResultSet rs=null;
userActionForm uaf=new userActionForm();
PreparedStatement ps = null;
ArrayList rslist = new ArrayList();
String sql="select * from myuser u,mydep d where u.dep=d.dep_id";
try {
ps = con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next())
{
HashMap rscol = new HashMap();
rscol.put("user_id",rs.getString("user_id"));
rscol.put("user_name",rs.getString("user_name"));
rscol.put("dep",rs.getString("dep"));
rscol.put("dep_name",rs.getString("dep_name"));
rscol.put("tel",rs.getString("tel"));
rslist.add(rscol);
}
}
catch (SQLException e)
{
System.out.println("sql error");
}
finally
{
try{
con.close();
db.d_close();
}
catch(Exception e) {}
}
return rslist;
}
//----------------------------------
}