CSDN博客

img arielxp

第七篇:数据库程序员篇

发表于2004/6/24 7:06:00  2202人阅读

分类: java/jsp

开场白:看你还记得不记得我在“第五篇”中的把“execute这个方法名该为“myDog”可以吗?现在你还不可以,以后我会告诉你怎么可以的啦。

程序要求:程序开始,第一个页面要显示,以这样的信息,一个下拉框用来选择“用户的部门名称”,这个下拉框中的内容要从“部门信息表中取得”(当页面一生成的时候就要获得)。然后按下“提交”按钮,能显示所有该部门的员工。

分析:因为页面一生成的时候就要从“部门表”中获得数据,所以我要采用一种方式让页面生成的时候就能操作action获得数据。数据的显示不再是单一数据,所以在页面上设计到循环。显示出来的一项数据不再是一个数据。

建立数据表:还记得我们在上一篇中建立的数据库吗。

数据库名字叫“user”已经有一个表叫“userTable”,我们现在就在这个数据库中再建立一个

表“depatTable

部门号码             depatNo            文本

部门名称             depatName          文本

填入几个值:

001         生产部

002         工程部

003         人事部

程序开始

从现在开始,我们将按照真正项目开发的思想来做,模块的划分将细致起来

A模块:提供数据库连接

B模块:actionAndForm模块

C模块: 业务逻辑,提供数据的操作

D模块: 数据实体封装

这些模块实际上就是“包”的概念,在eclipse中建立“包”我就不想说了

A模块(2个类)——package dataConnectFactory

ConnectFactory

//数据连接工厂

package dataConnectFactory;

import java.sql.*;

public class ConnectFactory

{

    //定义数据库的驱动程序(以下给出的是连接odbc数据库的)

   private static String strDriver="sun.jdbc.odbc.JdbcOdbcDriver";

   //定义数据库的URL(名称)

   private static String  strConnection="jdbc:odbc:user";

  //数据访问的用户名

   private static String  strUsername="";

  //数据库访问的密码

   private static String  strPassword="";

   

    public static Connection getConnectionByDriver()

             throws MyException

    {

       Connection conn = null;

       try

       {

           Class.forName(strDriver);//注册驱动

           conn = //获得连接

              DriverManager.getConnection(  strConnection,

                                         strUsername,

                                         strPassword);

       }

       catch(ClassNotFoundException ex1)

       {

           ex1.printStackTrace();

           throw new MyException("Class Not Found!");

       }

       catch(SQLException ex2)

       {

           ex2.printStackTrace();

           throw new MyException("SQL Error");

       }

       finally {}

       return conn;

    }

}

                              MyException

package dataConnectFactory;

//定义自己的异常类

public class MyException extends Exception

{

    public MyException()

    {

       super();

    }

   

    public MyException(String message)

    {

       super(message);

    }

}

B模块(3个类)——package  action

UserAction

package action;

//当第一个查询页面一生成的时候就把部门的信息得到并交给页面

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import org.apache.struts.action.ActionError;

import org.apache.struts.action.ActionErrors;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.actions.DispatchAction;

import biz.SearchBiz;

import entity.*;

public class UserAction extends DispatchAction

{

    //转到查询界面

    public ActionForward toSearch(  ActionMapping mapping,

                                    ActionForm form,

                                    HttpServletRequest request,

                                    HttpServletResponse response)

       throws Exception

    {

        

       //定义错误信息封装

       ActionErrors errors = null;

       //获取页面表单信息

       UserForm uf = (UserForm)form;

       //定义部门实体数组

       DepartmentEntity[] departs = null;

      

       SearchBiz searchBizDept = null;

       try

       {

           searchBizDept = new SearchBiz();

           departs =

              searchBizDept.selectDeptEntitys(

                  new DepartmentEntity());

           uf.setDeparts(departs);

          

       }

   

    catch (Exception e)

       {

           errors = new ActionErrors();

           e.printStackTrace();

           errors.add(ActionErrors.GLOBAL_ERROR, new ActionError("system.exception"));

           return mapping.findForward("error");

       }

      

 

       finally

       {

           if (errors != null)

           {

              saveErrors(request, errors);

           }

       }

       return mapping.findForward("toSearch");

    } 

}

package action;

UserForm

//将所有的实体封装到form

import org.apache.struts.action.ActionForm;

 

import entity.*;

 

public class UserForm extends ActionForm

{

    private   UserEntity user = new UserEntity();

    private   UserEntity[] users = null;

    private   DepartmentEntity depart = new DepartmentEntity();

    private   DepartmentEntity[] departs = null;

    public DepartmentEntity getDepart()

    {

       return depart;

    }

    public DepartmentEntity[] getDeparts()

    {

       return departs;

    }

    public UserEntity getUser() {

       return user;

    }

    public UserEntity[] getUsers()

    {

       return users;

    }

    public void setDepart(DepartmentEntity depart)

    {

       this.depart = depart;

    }

    public void setDeparts(DepartmentEntity[] departs)

    {

       this.departs = departs;

    }

    public void setUser(UserEntity user)

    {

       this.user = user;

    }

    public void setUsers(UserEntity[] users)

    {

       this.users = users;

    }

}

ResultAction

package action;

//当页面提交后,按照页面提交的条件来查询

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionErrors;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.actions.DispatchAction;

import biz.SearchBiz;

import entity.*;

public class ResultAction extends DispatchAction

{

    public ActionForward execute   (    ActionMapping mapping,

                                    ActionForm form,

                                    HttpServletRequest request,

                                    HttpServletResponse response)

           throws Exception

       {

           //定义错误信息封装

           ActionErrors errors = null;

           //获取页面表单信息

           UserForm uf = (UserForm)form;

           //定义用户实体数组

           UserEntity[] userEntitys = null;

           //定义用户实体

           UserEntity userEntity=new UserEntity();

              //获得页面上提交的数据

           userEntity=uf.getUser();

          

           System.out.println(userEntity.getDepartNo());

           SearchBiz searchBiz = null;

           try

           {

              searchBiz = new SearchBiz();

              userEntitys = searchBiz.selectUserEntitys(userEntity );

              uf.setUsers(userEntitys);

             

           }

           catch (Exception e)

           {

 

              return mapping.findForward("error");

           }

           finally

           {

      

           }

           return mapping.findForward("success");

       }

}

C模块(1个类)——package biz

SearchBiz

package biz;

//这里完成查询和数据的转换操作等所有数据的操作

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

 

import dataConnectFactory.ConnectFactory;

import dataConnectFactory.MyException;

import entity.*;

 

public class SearchBiz

{

    private String strDriver;

    private String strConnection;

    private String strUsername;

    private String strPassword;

    //查询用户信息 

    public UserEntity[] selectUserEntitys(UserEntity userEntity)

                            throws MyException

    {

       UserEntity[] employeeEntitys = null;

       Connection conn = null;

       PreparedStatement pstmt = null;

       ResultSet rs = null;

       StringBuffer sbSQL = new StringBuffer();

       ArrayList list = null;

       try

       {

           conn = ConnectFactory.getConnectionByDriver( );

           //生成sql

           sbSQL.append(" select ");

           sbSQL.append(" userTable.userNo,");

           sbSQL.append(" userTable.departNo,");

           sbSQL.append(" departTable.departName");

           sbSQL.append(" from userTable,departTable");

           sbSQL.append(" where

 userTable.departNo='"+userEntity.getDepartNo()+"'");

           sbSQL.append(" and ");

           sbSQL.append(" userTable.departNo = departTable.departNo ");

          

           System.out.println(sbSQL.toString());

          

           pstmt = conn.prepareStatement(sbSQL.toString());

           rs = pstmt.executeQuery();

          

           list = parseArray(rs,1);

           list.trimToSize();

           employeeEntitys = new UserEntity[list.size()];

           if (employeeEntitys != null && list.size()>0)

           {

              employeeEntitys =

                  (UserEntity[])list.toArray(employeeEntitys);

           }

       }

       catch(MyException ex1)

       {

           ex1.printStackTrace();

           throw ex1;

       }

       catch(SQLException ex2)

       {

           ex2.printStackTrace();

           throw new MyException("SQL Error");

       }

       finally//数据库访问完毕后,关闭相关的连接,

                //释放资源,是你应该要做的

       {

           try

           {

              if(rs!=null)

          

               {

                  rs.close();

                 rs=null;

               }

               if(pstmt!=null)

               {

               pstmt.close();

               pstmt=null;

               }

           }

           catch(SQLException e)

           {

              e.printStackTrace();

           }

       }

       return employeeEntitys;

    }

    //查询部门信息

    public DepartmentEntity[] selectDeptEntitys(DepartmentEntity departmentEntity)

                                throws MyException

    {

       DepartmentEntity[] departmentEntitys = null;

      

       Connection conn = null;

       //以前用Statement,现在用PrepareStatement

       //那是因为PrepareStatement有一个预编译的过程

       PreparedStatement pstmt = null;

       ResultSet rs = null;

       StringBuffer sbSQL = new StringBuffer();

       ArrayList list = null;

       try

       {

           conn = ConnectFactory.getConnectionByDriver();

           sbSQL.append("select departNo,departName from departTable");

 

           pstmt = conn.prepareStatement(sbSQL.toString());//预编译

           rs = pstmt.executeQuery(); //执行                  

          

           list = parseArray(rs,2);//把结果集转换为实体数组

           list.trimToSize();

           departmentEntitys = new DepartmentEntity[list.size()];

           if (departmentEntitys != null && list.size()>0)

           {

              departmentEntitys =

              (DepartmentEntity[])list.toArray(departmentEntitys);

           }

       }

       catch(MyException ex1)

       {

           ex1.printStackTrace();

           throw ex1;

       }

       catch(SQLException ex2)

       {

           ex2.printStackTrace();

           throw new MyException("SQL Error");

       }

       catch(Exception ex3)

       {

           ex3.printStackTrace();

           throw new MyException("Exception");

       }

       finally//数据库访问完毕后,关闭相关的连接,

               //释放资源,是你应该要做的

       {

           try

           {

              if(rs!=null)

              {

                 rs.close();

                 rs=null;

              }

              if(pstmt!=null)

              {

                  pstmt.close();

                  pstmt=null;

              }

           }

           catch(SQLException e)

           {

              e.printStackTrace();

           }

       }

       return departmentEntitys;

    }

    //把结果集转换为实体数组的方法

    protected ArrayList parseArray(ResultSet rs,long lType) throws SQLException

    {

       ArrayList list = new ArrayList();

       UserEntity userEntity = null;

       DepartmentEntity departmentEntity = null;

       Object object = null;

       try {

          

           while (rs.next())

           {

              if (lType ==1)

              {

                  userEntity = new UserEntity();

                  userEntity.setUserNo(rs.getString("userNo"));

                  userEntity.setDepartNo(rs.getString("departNo"));

                  userEntity.setDepartName(rs.getString("departName"));

                  object = (Object) userEntity;

              }

              else

              {

                  departmentEntity = new DepartmentEntity();

                  departmentEntity.setDepartmentNo(rs.getString("departNo"));

                  departmentEntity.setDepartmentName(rs.getString("departName"));

                  object = (Object) departmentEntity;

              }

              list.add(object);

           }

       }

       catch(SQLException ex)

       {

           ex.printStackTrace();

           throw ex;

       }

       finally {}

       return list;

    }

}

D模块(2个类)——package entity

UserEntity

package entity;

//用户信息实体

public class UserEntity

{

    private String userNo = null;

    private String userPassword = null;

    private String departNo = null;

    private String departName = null;

    public String getDepartName()

    {

       return departName==null?"":departName.trim();

    }

    public String getDepartNo()

    {

       return departNo==null?"":departNo.trim();

    }

    public String getUserNo()

    {

       return userNo==null?"":userNo.trim();

    }

    public String getUserPassword()

    {

       return userPassword==null?"":userPassword.trim();

    }

    public void setDepartName(String departName)

    {

       this.departName = departName;

    }

    public void setDepartNo(String departNo)

    {

       this.departNo = departNo;

    }

    public void setUserNo(String userNo)

    {

       this.userNo = userNo;

    }

    public void setUserPassword(String userPassword)

    {

       this.userPassword = userPassword;

    }

}

DepartmentEntity

package entity;

//部门信息实体

public class DepartmentEntity

{

    private String departmentNo = null;

    private String departmentName = null;

 

    public String getDepartmentName()

    {

       return departmentName==null?"":departmentName.trim();

    }

    public String getDepartmentNo()

    {

       return departmentNo==null?"":departmentNo.trim();

    }

    public void setDepartmentName(String departmentName)

    {

       this.departmentName = departmentName;

    }

 

    public void setDepartmentNo(String departmentNo)

    {

       this.departmentNo = departmentNo;

      

    }

}

配置文件struts-config.xml

<?xml version="1.0" encoding="ISO-8859-1" ?>

 

<!DOCTYPE struts-config PUBLIC

          "-//Apache Software Foundation//DTD Struts Configuration 1.1//EN"

          "http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd">

<!--

     This is the Struts configuration file for the example application,

     using the proposed new syntax.

 

     NOTE:  You would only flesh out the details in the "form-bean"

     declarations if you had a generator tool that used them to create

     the corresponding Java classes for you.  Otherwise, you would

     need only the "form-bean" element itself, with the corresponding

     "name" and "type" attributes.

-->

 

 

<struts-config>

 

  <!-- ========== Form Bean Definitions =================================== -->

    <form-beans>

       <form-bean name="userForm"   type="action.UserForm">

       </form-bean>

       

    </form-beans>

 

 <!-- ========== Global Forward Definitions ============================== -->

  <global-forwards>

 

  </global-forwards>

   

  <!-- ========== Action Mapping Definitions ============================== -->

  <action-mappings>

 

    <action path="/search"

        type="action.UserAction"

            name="userForm"

            scope="request"

            parameter="operate"

       validate="false"

       input="search.jsp">

            <forward name="toSearch" path="/search.jsp"/>

            <forward name="success"  path="/success.jsp"/>

            <forward name="error"    path="/error.jsp"/>

     </action>

     

     <action path="/searchInfo"

        type="action.ResultAction"

            name="userForm"

            scope="request"

       input="search.jsp">

            <forward name="toSearch" path="/search.jsp"/>

            <forward name="success"  path="/success.jsp"/>

            <forward name="error"    path="/error.jsp"/>

     </action>

 

  </action-mappings>

 

</struts-config>

jsp页面

search.jsp

<%@ page contentType="text/html; charset=gb2312" %>

<%@ taglib uri="/WEB-INF/struts-tiles.tld" prefix="tiles" %>

<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>

<%@ taglib uri="/WEB-INF/struts-logic.tld" prefix="logic" %>

<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean" %>

<html>

<head><title>search employee</title></head>

<body>

<center>

<html:form action="searchInfo.do" method="post">

<table>

<tr>

    <td>部门</td>

    <td>

    <html:select property="user.departNo">

    <logic:notEmpty name="userForm" property="departs">

    <logic:iterate  name="userForm" property="departs" id="dept" type="entity.DepartmentEntity">

    <html:option value="<%= String.valueOf(dept.getDepartmentNo()) %>"><%= dept.getDepartmentName() %></html:option>

    </logic:iterate>

    </logic:notEmpty>

    </html:select>

    </td>

</tr>

 

<tr>

     <td><html:submit value="提交"/></td>

     <td><html:reset  value="重置"/></td>

</tr>

</table>

</html:form>

</center>

</body>

</html>   

success.jsp

<%@ page contentType="text/html; charset=gb2312" %>

<%@ page language="java"%>

<%@ page import="java.util.*;"%>

<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>

<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean" %>

<%@ taglib uri="/WEB-INF/struts-logic.tld" prefix="logic" %>

<html:html>

       <body>

       <center>

       <table width="80%" border="1">

       <tr>

         <th>用户号 </th>

         <th>所在部门编号</th>

         <th>所在部门名称 </th>

       </tr>

      

           <logic:iterate 

                    name="userForm"

                       property="users"

                       id="ue"

                       type="entity.UserEntity">

                 <tr>

                   <td><%=ue.getUserNo()%></td>

                   <td><%=ue.getDepartNo()%></td>

                   <td><%=ue.getDepartName()%></td>

                 </tr>

          </logic:iterate>

        </table>

        </center>     

       </body>

</html:html>

error.jsp

<html>

Error!!

</html>

赶快运行吧

http://localhost:8080/temp3/search.do?operate=toSearch

一点提示:

发现没?运行程序的的时候,在ie地址拦上输入的东西好象不一样了吧?

UserAction中没有execute方法了,换成了toSearch方法。好了,现在我允许你把这个toSearch随便改个名字,就改个myDog好吗?然后再在你的ie上输入:

             http://localhost:8080/temp3/search.do?operate=myDog

哈哈,结果一样哦。

很早该提醒你,但是想看你自己是不太笨:

java程序改动后,要重新保存编译成类。

任何的程序改动或者配置文件改动后,请你重新启动你的tomcat

任何的jsp页面改动后,不需要重新启动tomcat,刷新就可以了

重点在这里:

怎么连接oracle数据库:

1:要导入一个classes12.jar这个JAR包,它提供了oracle数据库的相关驱动

2 strDriver="oracle.jdbc.driver.OracleDriver";

    strConnection="jdbc:oracle:thin:@¥¥¥¥¥:1521:!!!!!";

    strUsername="******";

    strPassword="######";

¥¥¥¥¥:数据库服务器的ip地址

!!!!!:数据库的名称

******:访问数据库的名称

######:访问数据库的密码

如何采用连接池的方式连接数据库

1:在D:/tomcat-4.1.18/confserver.xml中找到     

</Host> </Engine></Service></Server>

  在这段之前添加添加:

<Context path="/-----" docBase="/------" debug="0" reloadable="true">

              <Resource name="jdbc/%%%" auth="Container" type="javax.sql.DataSource"/>

              <ResourceParams name="jdbc/%%%">

              <parameter>

              <name>username</name>

              <value>******</value>

              </parameter>

              <parameter>

              <name>password</name>

              <value>######</value>

              </parameter>

              <parameter>

              <name>driverClassName</name>

              <value>oracle.jdbc.driver.OracleDriver</value>

              </parameter>

              <parameter>

              <name>url</name>

                <value>jdbc:oracle:thin:@ ¥¥¥¥¥: !!!!</value>

                </parameter>

                <parameter>

                        <name>maxActive</name>

                        <value>30</value>

                </parameter>

                <parameter>

                      <name>maxIdle</name>

                      <value>10</value>

               </parameter>

               <parameter>

                     <name>maxWait</name>

                     <value>5000</value>

               </parameter>

         </ResourceParams>

       </Context>

-----:你要用该连接池的应用工程目录,例如:user

%%%:随便你取个名字,例如:userDB

2:修改你的应用所对应的那个web.xml

  </web-app>前添加

<resource-ref>

    <description>((((((( </description>

    <res-ref-name> jdbc/%%%</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

  </resource-ref>

</web-app>

(((((((:你高兴写点什么就写点什么

3:这样来使用

还记得你的public class ConnectFactory吗?

改写成下面的样子

package connectDatabase;

import java.sql.*;

import javax.sql.DataSource;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

public class ConnectFactory

{

   

    public static Connection getConnectionByDriver()

             throws MyException

    {  

       DataSource ds;

       Connection conn = null;

       //缓冲池连接

       try

       {

           Context initCtx = new InitialContext();

           Context envCtx = (Context) initCtx.lookup("java:comp/env");

           ds = (DataSource)envCtx.lookup("jdbc/%%%");

         if(ds!=null)

         {

           conn = ds.getConnection();

         }

       }

       catch(NamingException e)

       {

           e.printStackTrace();

       }

       catch(SQLException e1)

       {

           e1.printStackTrace();

       }

       finally {}

       return conn;

    }

}

结束语:

虽然在这篇中,我没有再多给你点解释,但是我相信你能通过自己的摸索得到出一些体会和结论,如果你觉得看了程序有很大的疑惑,那么这就是我要达到的效果了,因为我真心希望你能锻炼自己的编程思想,而不是ctrl+v我的代码。

在上一篇中,我们是在form中直接定义一些属性,但是现在我们已经开始封装实体,在其他的程序中我们来对实体操作。我们已经开始把数据的查询操作与action分开,以后我们还要把数据库的访问与数据的业务操作逻辑分开。

我们还接触到了logic:iterate这个重要的页面循环的标签。

将来,我们还要在public class ConnectFactory中提供各种数据库的连接的“接口”,让我们这个类成为一个在任何地方都实用的类。

这些模块的细分,丰富“接口”的提供。将让我们的代码产生很大重用性,让你一次编写,永久使用。还记得我们导入的那些JAR包吗,那就是别人写好的类,我们拿来利用的。让我期待你自己编写JAR包来发布,让全世界的人都来使用你写的类,通过你写的类,为广大的程序员大大减少工作量吧。。。。。。。。

 

0 0

相关博文

我的热门文章

img
取 消
img