环境配置:
c3p0抽取数据库连接池
package com.heima.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 目的:
* 1. 保证DataSource只有一个
* 2. 提供连接(DataSource获得)
* 3. 释放资源
*/
public class C3P0Utils {
//创建C3P0数据源(连接池)
private static DataSource dataSource = new ComboPooledDataSource();
/**
* 提供数据源
* @return
*/
public static DataSource getDataSource(){
return dataSource;
}
/**
* 从dataSource(连接池)获得连接对象
*
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
Connection connection = dataSource.getConnection();
return connection;
}
/**
* 释放资源
*
* @param resultSet
* @param statement
* @param connection
*/
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();//看Connection来自哪里, 如果Connection是从连接池里面获得的, close()方法其实是归还; 如果Connection是创建的, 就是销毁
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
c3p0配置文件:
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day31</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
</default-config>
</c3p0-config>
前端页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="jquery-3.3.1.min.js"></script>
</head>
<body>
<center>
<h1>搜索</h1>
<input id="inputId" type="text" style="width: 500px; height: 38px;" /><input
type="button" style="height: 38px;" value="搜索"/>
<div id="divId"
style="width: 500px; border: 1px red solid; height: 300px; position: absolute; left: 910px;">
<table id="tabId" width="100%" height="100%" border="1px">
<tr><td><font color="red">香港幼儿园学校停课</font></td></tr>
<tr><td><font color="red">云南旅游</font> </td></tr>
<tr><td>秦皇岛现海市蜃楼</td></tr>
<tr><td>京东暂停与申通合作</td></tr>
<tr><td>演员谢园去世</td></tr>
</table>
</div>
</center>
</body>
<script>
// jq给文本框增加键盘弹起事件
$("#inputId").keyup(function () {
$.ajax({
url: "word",
// 值就是文本框的输入内容
data: "keyWord=" + $("#inputId").val(),
type: "POST",
dataType: "json",
success: function (result) {
// 遍历: 每次请求之前需要清除旧的内容
$("#tabId").html("");
var htmlStr = "";
for(ele of $(result)){
htmlStr = htmlStr + "<tr><td>"+ele.word+"</td></tr>";
}
$("#tabId").html(htmlStr);
}
})
})
</script>
</html>
web层:
package com.heima.web;
import com.alibaba.fastjson.JSON;
import com.heima.bean.Words;
import com.heima.service.WordsService;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/word")
public class wordsServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//乱码处理
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
//获取参数
String keyWord = request.getParameter("keyWord");
//调用业务逻辑层
WordsService wordsService= new WordsService();
List<Words> wordsList = null;
try {
wordsList = wordsService.findBywords(keyWord);
} catch (Exception e) {
e.printStackTrace();
}
//响应数据给前台
String jsonString = JSON.toJSONString(wordsList);
//前台显示数据列表
response.getWriter().write(jsonString);
}
}
Service层:
package com.heima.service;
import com.heima.bean.Words;
import com.heima.dao.WordDao;
import java.util.List;
public class WordsService {
public List<Words> findBywords(String keyWord) throws Exception {
WordDao wordDao = new WordDao();
//调用Dao层
List<Words> keyWordlist = wordDao.findBykeyWord(keyWord);
return keyWordlist;
}
}
Dao层:
package com.heima.dao;
import com.heima.bean.Words;
import com.heima.util.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class WordDao {
public List<Words> findBykeyWord(String keyWord) throws Exception {
QueryRunner qr =new QueryRunner(C3P0Utils.getDataSource());
//sql语句模糊查询
String sql="select * from words where word like ?";
List<Words> words = qr.query(sql, new BeanListHandler<>(Words.class), "%" + keyWord + "%");
return words;
}
}
运行效果: