从数据库中读取数据（直连版本）

qmug

浏览: 197504 次
性别:
来自: 上海

最近访客更多访客>>

shyc

sb12345

robustwang

绕弯儿无

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

J2EE

Struts JSP Apache SQL MySQL

从数据库中读取数据（直连版本）

下面的这里例子实现的是在注册的页面里面要注册的是用户名、电话、城市。其中城市是在给定的数据库中读取出来的。然后注册。

所用到的是数据库test728

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.22-community-nt


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema test728
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ test728;
USE test728;

--
-- Table structure for table `test728`.`t`
--

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `regtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `user` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `test728`.`t`
--

/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` (`id`,`regtime`,`user`) VALUES 
 (1,'2008-07-28 18:59:31','1'),
 (2,'2008-07-28 19:06:20','123');
/*!40000 ALTER TABLE `t` ENABLE KEYS */;


--
-- Table structure for table `test728`.`user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(45) NOT NULL,
  `tel` varchar(45) default NULL,
  `city` varchar(45) NOT NULL,
  `regtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `test728`.`user`
--

/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`id`,`username`,`tel`,`city`,`regtime`) VALUES 
 (1,'aa','123','shenyang','2008-07-28 19:07:28'),
 (2,'aabb','111','shenyang','2008-07-28 19:07:45'),
 (3,'abbbba','11123','xian','2008-07-28 19:08:05'),
 (4,'ll','lll','shenyang','2008-07-29 19:09:35'),
 (5,'rr','rr','xian','2008-07-29 19:16:55');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

其中字段 `regtime` 的类型是timestamp 他可以把系统的时间自动添加到数据库里面。在注册时间比较有用。

所用到的jsp页面 index.jsp reg.jsp success.jsp
Index.jsp


<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <jsp:forward page="index.do"></jsp:forward>
  </body>
</html>

说明：这里使用了<jsp:forward page="index.do"></jsp:forward> 标签

Reg.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head>
<% %>
<body>
<form id="form1" name="form1" method="post" action="reg.do">
  用户名：
  <label>
  <input name="username" type="text" id="username" />
  </label>
  <p>电话：
    <label>
    <input name="tel" type="text" id="tel" />
    </label>
  </p>
  <p>城市：
    <label>
    <select name="city" id="city">
			   <c:forEach items="${b}" var="b">
			   <option>${b.city}</option>			
 				</c:forEach>
    </select>
    </label>
  </p>
  <p>
    <label>
    <input type="submit" name="Submit" value="提交" />
    </label>
  </p>
</form>

</body>
</html>

说明：其中
<c:forEach items="${b}" var="b">
<option>${b.city}</option>
</c:forEach>
使用的是jstl语言，需要注意的是要使用的时候需要在前面加上
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

Success.jsp

<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head>

<body>
登录成功
</body>

</html>

这个页面就没有什么好说的了。

1个form

package form;

import javax.servlet.http.HttpServletRequest;
import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionMapping;

public class RegForm extends ActionForm {
	

	/** username property */
	private String username;

	/** tel property */
	private String tel;

	/** city property */
	private String city;

	/*
	 * Generated Methods
	 */

	/** 
	 * Method validate
	 * @param mapping
	 * @param request
	 * @return ActionErrors
	 */
	public ActionErrors validate(ActionMapping mapping,
			HttpServletRequest request) {
		// TODO Auto-generated method stub
		return null;
	}

	/** 
	 * Method reset
	 * @param mapping
	 * @param request
	 */
	public void reset(ActionMapping mapping, HttpServletRequest request) {
		// TODO Auto-generated method stub
	}

	/** 
	 * Returns the username.
	 * @return String
	 */
	public String getUsername() {
		return username;
	}

	/** 
	 * Set the username.
	 * @param username The username to set
	 */
	public void setUsername(String username) {
		this.username = username;
	}

	/** 
	 * Returns the tel.
	 * @return String
	 */
	public String getTel() {
		return tel;
	}

	/** 
	 * Set the tel.
	 * @param tel The tel to set
	 */
	public void setTel(String tel) {
		this.tel = tel;
	}

	/** 
	 * Returns the city.
	 * @return String
	 */
	public String getCity() {
		return city;
	}

	/** 
	 * Set the city.
	 * @param city The city to set
	 */
	public void setCity(String city) {
		this.city = city;
	}
}

在RegAction用到的

2 个action
Index

package action;

import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import db.DB;


public class IndexAction extends Action {
	/**
	 * 
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		// TODO Auto-generated method stub
		ArrayList a=new DB().yonghu();//通过yonghu这个方法把city对象取出来
		request.setAttribute("b", a);//给ArrayList里面的a进行赋值."b"所对应的是reg.jsp中forEach里面的。
		return mapping.findForward("ok");//跳转到注册页面
	}
}

Reg

package action;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import db.DB;
import form.RegForm;


public class RegAction extends Action {
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		RegForm regForm = (RegForm) form;// TODO Auto-generated method stub
		String username=regForm.getUsername();//下面的三句话是从form中取得名字、电话、城市
		String tel=regForm.getTel();
		String city=regForm.getCity();
		new DB().adduser(username, tel, city);//通过adduser方法插入到数据库里面
		
		return mapping.findForward("ok");//跳转到成功页面
	}
}

DB包

package db;

import java.sql.*;//为了能使用数据库，我们加载了数据库包
import java.text.SimpleDateFormat;
import java.util.*;

import javabean.yonghu;


public class DB {
	private Connection conn;//用来连接数据库的“数据库连接对象”

	private PreparedStatement stmt;//数据库操作对象

	private ResultSet rs;

	
	public DB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test728", "root", "1234");
					} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 添加用户adduser方法  测试完毕
	 * @author qmug
	 * @version 1.0
	 */
	public boolean adduser(String username,String tel,String city) {
		try {

		stmt = conn.prepareStatement("insert into test728.user(username,tel,city) values(?,?,?)");
			//stmt.setInt(1, name);
			stmt.setString(1, username);
			stmt.setString(2, tel);
			stmt.setString(3, city);

			stmt.execute();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;

	}
	
	
	
	
	
	/**
	 * 此方法用于查询客户信息    测试完毕
	 * @author qmug
	 * @version 1.0
	 * @return
	 */

		 public ArrayList yonghu(){
			ArrayList a=new ArrayList();
			try {
				
				stmt=conn.prepareStatement("select distinct city from test728.user");
	
				
				rs=stmt.executeQuery();
				while(rs.next()){

					yonghu c=new yonghu();
					//c.setId(Integer.parseInt(rs.getString("id")));
					//c.setUsername(rs.getString("username"));
					//c.setTel("tel");
					c.setCity(rs.getString("city"));
					a.add(c);
					
				}
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return a;
		}
}

所用到的javabean

package javabean;

public class yonghu {
	private int id;
	private String username;
	private String tel;
	private String city;
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}

}

struts-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.2//EN" "http://struts.apache.org/dtds/struts-config_1_2.dtd">

<struts-config>
  <data-sources />
  <form-beans >
    <form-bean name="regForm" type="form.RegForm" />

  </form-beans>

  <global-exceptions />
  <global-forwards />
  <action-mappings >
    <action path="/index" type="action.IndexAction">
      <forward name="ok" path="/reg.jsp" />
    </action>
    <action
      attribute="regForm"
      name="regForm"
      path="/reg"
      scope="request"
      type="action.RegAction">
      <forward name="ok" path="/success.jsp" />
    </action>

  </action-mappings>

  <message-resources parameter="com.yourcompany.struts.ApplicationResources" />
</struts-config>

总结：想要实现功能首先把所要用到的页面做好。然后再往里面填写其中的逻辑。这个例子的逻辑思路是首先由一个引导页index.jsp跳转到index.do。在index.do所调用DB中的yonghu方法来查询出来城市，然后跳转到reg.jsp页面。在提交表单时候调用reg.do执行DB中的adduser方法来向数据库中插值。最后跳转到成功页面。

test729.rar (769 KB)
下载次数: 7

分享到：

从数据库中读取数据（AJAX版本） | Tomcat 的数据库连接池设置与应用

2008-07-30 18:06
浏览 1785
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论