一:实现步骤 (tomcat7+jdk1.7+mysql5.1)
1:实现简单的模糊查询
2:servlet中调用查询方法
3:页面使用ajax实现异步查询
项目组结构如下
测试结果如下:
db.properties
mysql_driver=com.mysql.jdbc.Driver
mysql_url=jdbc:mysql://localhost:3306/test
mysql_username=root
mysql_password=root
1: JdbcUtils 连接数据库
package com.scxs.dao;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
static{
try{
//读取db.properties文件中的数据库连接信息
InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(in);
//获取数据库连接驱动
driver = prop.getProperty("mysql_driver");
//获取数据库连接URL地址
url = prop.getProperty("mysql_url");
//获取数据库连接用户名
username = prop.getProperty("mysql_username");
//获取数据库连接密码
password = prop.getProperty("mysql_password");
//加载数据库驱动
Class.forName(driver);
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* @Method: getConnection
* @Description: 获取数据库连接对象
* @Anthor:wdq
*
* @return Connection数据库连接对象
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, username,password);
}
/**
* @Method: release
* @Description: 释放资源,
* 要释放的资源包括Connection数据库连接对象,负责执行SQL命令的Statement对象,存储查询结果的ResultSet对象
* @Anthor:孤傲苍狼
*
* @param conn
* @param st2
* @param rs
*/
public static void release(Connection conn,Statement st2,ResultSet rs){
if(rs!=null){
try{
//关闭存储查询结果的ResultSet对象
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if(st2!=null){
try{
//关闭负责执行SQL命令的Statement对象
st2.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
//关闭Connection数据库连接对象
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
UserDao
package com.scxs.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
public class UserDao {
public List<String> getUsersByName(String str) throws SQLException{
String sql = "select username from user where username like ?";
String []params = {str};
return BaseDao.executeQuery(sql, params);
}
public List<String> find(String str){
List<String> lists = new ArrayList<String>();
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select * from pis_consignment where CREATEDBY like '%"+str+"%'";
st = conn.createStatement();
rs = st.executeQuery(sql);
while(rs.next()){
lists.add(rs.getString("UPDATEDBY"));
}
}catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn, st, rs);
}
return lists;
}
@Test
public void test(){
List<String> lists = this.find("test");
for(String list:lists){
System.out.println(list);
}
}
}
SuggestServlet.java
package com.scxs.SuggestServlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.scxs.dao.UserDao;
public class SuggestServlet extends HttpServlet {
private static final long serialVersionUID = -1281745468076896300L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
PrintWriter out = resp.getWriter();
String keyword = req.getParameter("keyword");
// 利用service来进行测试
if (!"".equals(keyword.trim())) {
StringBuffer buffer = new StringBuffer();
UserDao userService = new UserDao();
List<String> results = userService.find(keyword);
for (int i = 0; i < results.size(); i++) {
buffer.append(results.get(i));
if (i != results.size() - 1) {
buffer.append(",");
}
}
if (buffer.toString() != null && buffer.length() > 0) {
out.println(buffer.toString());
} else {
out.println("");
}
}
out.close();
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
this.doGet(req, resp);
}
}
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<!-- 使用jquery -->
<script type="text/javascript" src="js/jquery-1.8.2.js"></script>
<script type="text/javascript">
//1.找对象;2.定事件;匿名事件;
$(function(){
$("#txtSearch").keyup(function(){
//发送post请求,设置请求url,请求数据,成功返回函数;
$.post("SuggestServlet",{keyword:$("#txtSearch").val()},callback);
});
});
function callback(data){
var sobj=$("#suggest");
sobj.innerHTML="";
var suggest="";
var str=data.split(",");
// alert(data);
if(str.length>0 && str[0].length>0){
for(i=0;i<str.length;i++){
suggest+="<div id='selectValue' οnmοuseοver='mouseOver(this);' οnmοuseοut='mouseOut(this);' οnclick='setSearch(this.innerHTML);' onBlur='onBlurText();'";
suggest+=">"+str[i]+"</div>";
}
sobj.html(suggest).css("display","block");
}else{
sobj.css("display","none");
}
}
//将内容填充到搜索框中;
function setSearch(obj){
$("#txtSearch").val(obj);
$("#txtSearch2").val(obj);
$("#suggest").css("display","none");
$("#txtSearch").css("display","none");
}
//鼠标移入
function mouseOver(obj)
{
obj.style.background = "blue";
}
//鼠标移出
function mouseOut(obj)
{
obj.style.background = "white";
}
//失去焦点
function onBlurText()
{
document.getElementById("txtSearch").style.display="none";
}
//得到焦点
function onFocusText()
{
document.getElementById("txtSearch").style.display="block";
}
window.onload = function(){
document.getElementById("txtSearch").style.display="none";
}
</script>
<!-- <script type="text/javascript" >
var xhr;
function createXhr(){
try{
return new XMLHttpRequest();
}catch(e){
return new ActiveXObject("Microsoft.XMLHTTP");
}
}
//用户名文本框失去焦点的处理函数;
function findSuggest(){
xhr=createXhr();
var keyword=document.getElementById("txtSearch").value;
// alert(keyword);
xhr.onreadystatechange=callback;
xhr.open("post","SuggestServlet",true);
xhr.setRequestHeader("Content-type","application/x-www-form-urlencoded");
xhr.send("keyword="+keyword);
}
function callback(){
if(xhr.readyState==4){
if(xhr.status==200){
var sobj=document.getElementById("suggest");
sobj.innerHTML="";
var str=xhr.responseText.split(",");
var suggest="";
if(str.length>0 && str[0].length>0){
for(i=0;i<str.length;i++){
suggest+="<div id='selectValue' οnmοuseοver='mouseOver(this);' οnmοuseοut='mouseOut(this);' οnclick='setSearch(this.innerHTML);'";
suggest+=">"+str[i]+"</div>";
}
sobj.innerHTML=suggest;
document.getElementById("suggest").style.display="block";
}else{
document.getElementById("suggest").style.display="none";
}
}
}
}
function setSearch(obj){
document.getElementById("txtSearch").value=obj;
document.getElementById("suggest").style.display="none";
}
function mouseOver(obj)
{
obj.style.background = "blue";
}
function mouseOut(obj)
{
obj.style.background = "white";
}
</script> -->
</head>
<body>
<h3>利用Ajax实现搜索自动补齐提示!</h3>
<div style="width:500px">
<input name="text" id="txtSearch2" onFocus="onFocusText();" />
<input type="submit" id="cmdFind" name="cmdFind" value="搜索"/><br/>
<input type="text" id="txtSearch" name="txtSearch" οninput="findSuggest();" onpropertychange="findSuggest();" />
</div>
<div style="border: 1px solid #00F ;width:196px;display:none;" id="suggest" ></div>
</body>
</html>