Web jsp开发学习——数据库的另一种连接方式（配置静态数据库连接池）

1.导包

2.找到sever里的sever.xml，配置静态数据库连接池

      <Context docBase="bookstore" path="/bookstore" reloadable="true" source="org.eclipse.jst.jee.server:bookstore">
             <Resource name="jdbc/bookstore" auth="Container" type="javax.sql.DataSource" maxActive="100"
                maxIdle="30"    maxWait="10000" username="root" password="caiyishuai"
                driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/bookstore?autoReconnect=true" />
      </Context>

3.再在web.xml里引用数据库连接池

web.xml中的

和sever.xml中的

要保持一致

4.在要调用数据库的地方改为

别忘了导包

<%@ page language="java" contentType="text/html; charset=UTF-8"
    import="java.util.*,java.text.*,com.xx17.cys.entity.*" 
    import="java.sql.*,org.apache.commons.dbcp2.*" 
    import="javax.naming.*,javax.sql.*" pageEncoding="UTF-8"%>
<%
//List<News> newslist = new ArrayList<News>();
    List newslist = new ArrayList();
    News news = new News(1,"今天是星期四","gfsggfsg",201,"2019-3-13",0);
    newslist.add(news);
    news = new News(2,"又可以上web课啦！","gfsggfshgffdhg",104,"2019-3-14",0);
    newslist.add(news);
    String newstitle[]={"今天是星期四！","又可以上web课啦！","下午公休，嘿嘿！","人生啊，如此艰难！","现在是上午9点20分"};
    String newsdate[]={"2019-3-13","2019-3-14","2019-3-15","2019-3-16","2019-3-17"};

    /*数据库连接池*/
    /*BasicDataSource ds = new BasicDataSource();
    String url="jdbc:mysql://localhost:3306/bookstore?user=root&password=caiyishuai";
    url += "&useUnicode=true&characterEncoding=utf8";
    ds.setDriverClassName(url);
    ds.setUsername("root");
    ds.setPassword("caiyishuai");
    ds.setMaxTotal(30);
    ds.setMinIdle(5);
    ds.setMaxWaitMillis(10000);
    ds.setRemoveAbandonedTimeout(100);
    ds.setRemoveAbandonedOnBorrow(true);
    ds.setRemoveAbandonedOnMaintenance(true);
    */
    
    /*使用静态数据库连接池*/
    Context initContext = new InitialContext();
    Context envContext = (Context)initContext.lookup("java:/comp/env");
    DataSource ds = (DataSource)envContext.lookup("jdbc/bookstore");
    
    //建立连接池连接
    Connection conn = ds.getConnection();

    /*Class.forName("com.mysql.jdbc.Driver");  ////驱动程序名
    String url = "jdbc:mysql://localhost:3306/bookstore"; //数据库名
    String username = "root";  //数据库用户名
    String password = "caiyishuai";  //数据库用户密码
    Connection conn = DriverManager.getConnection(url, username, password);  //连接状态*/

    if(conn != null){  
        out.print("数据库连接成功！");
        Statement stmt = conn.createStatement();
        //创建结果集合，集合与表的结构类似
        ResultSet rs = stmt.executeQuery("select * from t_news");
        //rs.last
        //out.println("记录数："+rs.getRow());
        while(rs.next()){
            //System.out.println("rs"+rs);
              news = new News(1,rs.getString(1),rs.getString(2),rs.getInt(4),rs.getString(3),1);
              newslist.add(news);
        }
    }

    /*建立连接
    Connection conn = DriverManager.getConnection(url);
    创建语句环境
    Statement stmt = conn.createStatement();
    创建结果集合，集合与表的结构类似
    ResultSet rs = stmt.executeQuery("select * from t_news");
    rs.last
    out.println("记录数："+rs.getRow());
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    com.mysql.jdbc.Driver d = new com.mysql.jdbc.Driver
    String url="jdbc:mysql://localhost:3306/bookstore?"
    +"user=root&password=caiyishuai&useUnicode=true&characterEncoding=utf8";
    List<News> newslist = new ArrayList<News>();
    News news;
    while(rs.next()){
        news = new News(1,rs.getString(1),rs.getString(2),rs.getInt(4),rs.getString(3),1);
        newslist.add(news);
    }*/
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新闻列表</title>
</head>
<body>
<link href="css/style.css" rel="stylesheet" type="text/css"/>
<jsp:include page="head.jsp?col=1"></jsp:include>
<div id="main" class="layout">
<%
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy年MM月dd日");    
    java.util.Date today = new java.util.Date();    
    out.print(formatter.format(today));  
    //out.print(today.toLocaleString()); 
    //int id =Integer.parseInt(str);
%>
<table width="1000" border="1">
    <tr><th>序号</th><th>标题</th><th>日期</th><th>点击</th></tr>
    <% String[] color={"red","orange","yellow","green","blue","purple","pink","black","brown"};%>
    <% for(int font_size=1;font_size<7;font_size++) {%>
        <br>
        <font color="<%= color[font_size-1] %>" size="<%= font_size %>">
            智慧的帅帅，么么哒！
        </font>
    <%}
    News n;
    %>
    
    <% for(int i=0;i<newslist.size();i++){
        n=(News)newslist.get(i);%>        
        <tr>
        <td><% out.print(i+1);%></td>
        <td><a href="news.jsp?nid=<%=n.getNews_id()%>"><%=n.getNews_title()%></a></td>
        <td><%=n.getNews_date()%></td>
        <td><%=n.getNews_read()%></td>
        </tr>    
    <%}    %>        

    <tr><td></td><td></td><td></td><td></td></tr>
    <tr><td></td><td></td><td></td><td></td></tr>
    <tr><td></td><td></td><td></td><td></td></tr>
</table>
</div>
<%@ include file="foot.jsp" %>
</body>
</html>