我的漫漫程序之旅

专注于JavaWeb开发

Servlet + FreeMarker 简单实现CRUD

1.数据库脚本:

drop table users

create table users
(
 id int primary key identity(1,1),
 username varchar(50)
)

drop proc adduser

create proc adduser
as
   declare @i int
   set @i  = 1
  begin 
    while(@i <= 100) 
    begin
     insert into users values('name' + convert(varchar(10),@i))
       set @i = @i + 1
    end
 
  end    
go

exec adduser

select count(*) as rows from users

2.数据库连接类:
package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 数据库连接类
 * 
 * @author zdw
 * 
 */
public class DB
{
    private static final String DBDRIVER = "net.sourceforge.jtds.jdbc.Driver";
    private static final String DBURL = "jdbc:jtds:sqlserver://localhost:1433/pubs;user=sa;pwd=";

    private DB()
    {
    }

    public static DB getInstance()
    {
        return new DB();
    }

    public Connection getConn()
    {
        Connection conn = null;
        try
        {
            Class.forName(DBDRIVER);
            conn = DriverManager.getConnection(DBURL);
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return conn;
    }

}

3.UserDAO:
package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.DB;
import com.vo.User;

/*******************************************************************************
 * userdao 对用户实现增删改查
 * 
 * @author zdw
 * 
 */
public class UserDAO
{
    public List<User> queryAll()
    {
        List<User> list = new ArrayList<User>();
        String sql = "select * from users";
        Connection conn = DB.getInstance().getConn();
        try
        {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next())
            {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                list.add(user);
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return list;
    }

    public User queryById(Integer id)
    {
        User user = null;
        String sql = "select * from users where id = ?";
        Connection conn = DB.getInstance().getConn();
        try
        {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next())
            {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return user;
    }

    public void save(User user)
    {
        String sql = "insert into users values(?)";
        Connection conn = DB.getInstance().getConn();
        try
        {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.executeUpdate();
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    public void update(User user)
    {
        String sql = "update users set name = ? where id = ?";
        Connection conn = DB.getInstance().getConn();
        try
        {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setInt(2, user.getId());
            pstmt.executeUpdate();
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    public void delete(Integer id)
    {
        String sql = "delete from users where id = ?";
        Connection conn = DB.getInstance().getConn();
        try
        {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

}
4.UserServlet:
package com.web;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.UserDAO;
import com.vo.User;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

@SuppressWarnings("serial")
public class UserServlet extends HttpServlet
{
    private Configuration cfg = null;

    @Override
    public void init() throws ServletException
    {
        cfg = new Configuration();
        cfg.setServletContextForTemplateLoading(this.getServletContext(), null);
    }

    @SuppressWarnings("unchecked")
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException
    {
        // 网站全路径
        String basePath = req.getScheme() + "://" + req.getServerName() + ":"
                + req.getServerPort() + req.getContextPath() + "/";
        // 得到要操作的方法名
        String method = req.getParameter("method");

        Map map = new HashMap();
        map.put("path", basePath);
        // 设置编码
        res.setCharacterEncoding("gbk");
        // 得到输出流
        PrintWriter out = res.getWriter();
        Template t = null;
        // 查询所有用户
        if (method.equals("queryAll"))
        {
            List list = this.doQueryAll(req, res);
            map.put("list", list);
            t = cfg.getTemplate("WEB-INF/templates/index.ftl");
            System.out.println("ssdfs");
        }
        // 添加用户前的跳转
        if (method.equals("forwardSave"))
        {
            t = cfg.getTemplate("WEB-INF/templates/form.ftl");
        }
        // 保存用户
        if (method.equals("save"))
        {
            System.out.println("save");
            this.doSave(req, res);
            req.getRequestDispatcher("/UserServlet?method=queryAll").forward(
                    req, res);
            return;
        }
        // 删除用户
        if (method.equals("delete"))
        {
            this.doDel(req, res);
            req.getRequestDispatcher("/UserServlet?method=queryAll").forward(
                    req, res);
            return;
        }
        // 更新用户前的跳转
        if (method.equals("forwardUpdate"))
        {
            Integer id = Integer.parseInt(req.getParameter("id"));
            UserDAO userDAO = new UserDAO();
            User user = userDAO.queryById(id);
            map.put("user", user);
            t = cfg.getTemplate("WEB-INF/templates/update.ftl");
        }
        // 更新用户
        if (method.equals("update"))
        {
            this.doUpdate(req, res);
            req.getRequestDispatcher("/UserServlet?method=queryAll").forward(
                    req, res);
            return;
        }
        try
        {
            // 处理map和输出流
            t.process(map, out);
            out.flush();
            out.close();
    &nbs