javaweb基础(struts+mysql)查询项目()

 数据库准备（mySql）:

CREATE TABLE t_project (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
start_date date DEFAULT NULL,
end_date date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000001','2008-01-02','2008-01-15');

INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000002','2008-02-01','2008-02-20');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000003','2008-03-01','2008-03-08');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000004','2008-03-08','2008-03-10');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000005','2008-01-02','2008-01-15');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000006','2008-02-01','2008-02-20');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000007','2008-03-01','2008-03-08');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000008','2008-03-08','2008-03-10');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000009','2008-01-02','2008-01-15');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000010','2008-02-01','2008-02-20');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000011','2008-03-01','2008-03-08');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000012','2008-03-08','2008-03-10');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000013','2008-01-02','2008-01-15');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000014','2008-02-01','2008-02-20');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000015','2008-03-01','2008-03-08');

SQL> INSERT INTO t_project (name,start_date,end_date)
VALUES ('pm000016','2008-03-08','2008-03-10');

 

第一步：配置web.xml和struts.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<filter>
  <filter-name>Struts2</filter-name>
   <filter-class>
   org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
   </filter-class>
</filter>

<filter-mapping>
    <filter-name>Struts2</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.1//EN"
"http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
  <package name="helloworld" extends="struts-default" namespace="/day01">
       <action name="projectlist" class="outman.tarena.outman.ProjectListAction">
           <result name="success">/jsp/projectlist.jsp</result>
       </action>
  </package>

</struts>

 

第2步，新建一个实体类。

package outman.tarena.outman.entity;

import java.util.Date;

public class Project {
    private int id;
    private String name;
    private Date startDate;
    private Date endDate;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getStartDate() {
        return startDate;
    }

    public void setStartDate(Date startDate) {
        this.startDate = startDate;
    }

    public Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }
  
}

第3步：创建一个dao接口

package outman.tarena.outman.dao;

import java.util.List;

import outman.tarena.outman.entity.Project;

public interface ProjectDAO {
 public List<Project> findAll();
}

第4步：创建接口的实现类。

package outman.tarena.outman.dao.impl;

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

import outman.tarena.outman.dao.ProjectDAO;
import outman.tarena.outman.entity.Project;
import outman.tarena.outman.util.ConnectionUtils;


public class ProjectDaoImpl implements ProjectDAO {
  
    private static final String findAll = "select id,name,start_date,end_date from t_project";

    private static final String findAll2 = "select id,name,start_date, end_date from t_project limit ?,?";

    private static final String getTotalPages = "select count(*) from t_project";

    public int getTotalPages(int rowsPerPage) {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionUtils.openConnection();
            stmt = con.prepareStatement(getTotalPages);
            rs = stmt.executeQuery();
            rs.next();
            int totalRows = rs.getInt(1);
            if (totalRows % rowsPerPage == 0) {
                return totalRows / rowsPerPage;
            } else {
                return totalRows / rowsPerPage + 1;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public List<Project> findAll(int page, int rowsPerPage) {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionUtils.openConnection();
            stmt = con.prepareStatement(findAll2);
            stmt.setInt(1, (page - 1) * rowsPerPage);
            stmt.setInt(2, rowsPerPage);
            rs = stmt.executeQuery();
            List<Project> list = new ArrayList<Project>();
            while (rs.next()) {
                Project project = new Project();
                project.setId(rs.getInt(1));
                project.setName(rs.getString(2));
                project.setStartDate(rs.getDate(3));
                project.setEndDate(rs.getDate(4));
                list.add(project);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Project> findAll() {

        Connection con = null;
        java.sql.PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionUtils.openConnection();
            stmt = con.prepareStatement(findAll);
            rs = stmt.executeQuery();
            List<Project> list = new ArrayList<Project>();
            while (rs.next()) {
                Project project = new Project();
                project.setId(rs.getInt(1));
                project.setName(rs.getString(2));
                project.setStartDate(rs.getDate(3));
                project.setEndDate(rs.getDate(4));
                list.add(project);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) {
        ProjectDaoImpl dao = new ProjectDaoImpl();
        // List<Project> list = dao.findAll();
        List<Project> list = dao.findAll(2, 5);
        for (Project p : list) {
            System.out.println(p.getName());
        }
    }
}

第5步：创建一个action

package outman.tarena.outman;

import java.util.List;

import outman.tarena.outman.dao.impl.ProjectDaoImpl;
import outman.tarena.outman.entity.Project;

public class ProjectListAction {
    
    private int page = 1;

    private List<Project> projectList;

    private int totalPages;

    public int getTotalPages() {
        return totalPages;
    }

    public void setTotalPages(int totalPages) {
        this.totalPages = totalPages;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public List<Project> getProjectList() {
        return projectList;
    }

    public void setProjectList(List<Project> projectList) {
        this.projectList = projectList;
    }

    public String execute() {
        ProjectDaoImpl projectDao = new ProjectDaoImpl();
        // projectList = projectDao.findAll();
        projectList = projectDao.findAll(page, 5);
        totalPages = projectDao.getTotalPages(5);

        return "success";
    }
}

第6步：编写数据库访问类。

package outman.tarena.outman.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Statement;

public class ConnectionUtils {
    private static String url;
    private static String driver;
    private static String username;
    private static String password;
    static {
        Properties props = new Properties();
        try {
            // 从属性文件中读取数据库配置信息
            props.load(ConnectionUtils.class.getClassLoader()
                    .getResourceAsStream("database.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (props != null) {
            url = props.getProperty("url");
            driver = props.getProperty("driver");
            username = props.getProperty("username");
            password = props.getProperty("password");
            // 装载并注册数据库驱劢
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    public static Connection openConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    public static void closeConnection(Connection con) {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeStatement(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        Connection con = openConnection();
        System.out.println(con);

    }
}

username=root
password=zhujn
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test2

第7步：在webRoot下分别新建2个jsp文件。

<%@ page language="java" import="java.util.*"
 pageEncoding="utf-8"%>
<%@taglib uri="/struts-tags" prefix="s"%>
<html>
  <head>
    
  </head>
  
  <body>
  <h1>ProJectLst</h1>
  <h2>
    <s:if test="page > 1">
          <a href="projectlist.action?page=${page-1}">上一页 </a>
     </s:if>
    <s:else>
                          上一页
    </s:else>
          |第
          <s:property value="page" />
                              页|
<s:if test="page < totalPages">
<a href="projectlist.action?page=${page+1}">下一页 </a>
</s:if>
<s:else>
下一页
</s:else>
  </h2>
  <table width="90%" border="2" >
    <tr>
       <td>
         ID
       </td>
       <td>
        Name
       </td>
        <td>
         Start Date
        </td>
        <td>
         End Date
         </td>
    </tr>
    <s:iterator value="projectList">
  <tr>
<td>
<s:property value="id" />
</td>
<td>
<s:property value="name" />
</td>
<td>
<s:property value="startDate" />
</td>
<td>
<s:property value="startDate" />
</td>
  </tr>
  </s:iterator>
  </table>
  </body>
</html>