IDEA 使用servlet连接mysql的过程笔记

代码实现

开发工具：IDEA

不进行分离数据库连接

在web.xml中进行servlet配置

    <servlet>
        <servlet-name>DataServlet</servlet-name>
        <servlet-class>com.jzk.firstservlet.DataServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DataBase</servlet-name>
        <url-pattern>/data.bin</url-pattern>
    </servlet-mapping>

servlet的具体实现

所需要的包，下面写的时候IDEA会帮助自动导入

package com.jzk.firstservlet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

public class DataServlet extends HttpServlet {
    @Override
    public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Connection cn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");            //建立数据库驱动
            try {
                cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/******","user","123456");  //建立数据库连接，三个参数对应了数据库的url、user、password，第一个****写的是数据库名称
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        PrintWriter out =resp.getWriter();     //新建输出流
        Statement st = null;
        String sql = "select numbers, namess, price from shopping";   //测试用的sql语句
        try {
            st = cn.createStatement();      //用于向已经建立的数据库发送sql语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ResultSet rs = null;
        try {
            rs = st.executeQuery(sql);   //建立结果集，返回执行sql语句之后结果
        } catch (SQLException e) {
            e.printStackTrace();
        }
        while(true){  //反复循环，直到rs搜索到最后一条数据
            try {
                if (rs.next()){
                //访问数据库要干的事情
                }else{
                    break;
                };
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

分离数据库连接

思想：将建立连接的这部分独立出来做成一个servlet，配置在web.xml中

web.xml的配置

设立四个初始参数，分别对应驱动类名、url、user、password，最后load-on-startup参数是用于无mapping时候，servlet的自启动，否则这个servlet将不会启动

    <servlet>
        <servlet-name>DataBase</servlet-name>
        <servlet-class>com.jzk.firstservlet.DataBase</servlet-class>
        <init-param>
            <param-name>driver</param-name>
            <param-value>com.mysql.jdbc.Driver</param-value>
        </init-param>
        <init-param>
            <param-name>url</param-name>
            <param-value>jdbc:mysql://localhost:3306/test</param-value>
        </init-param>
        <init-param>
            <param-name>user</param-name>
            <param-value>root</param-value>
        </init-param>
        <init-param>
            <param-name>pass</param-name>
            <param-value>123456</param-value>
        </init-param>
        <load-on-startup>0</load-on-startup>
    </servlet>

servlet的具体实现

DataBase类

package com.jzk.firstservlet;

import javax.servlet.http.HttpServlet;
import java.sql.Connection;
import java.sql.DriverManager;

public class DataBase extends HttpServlet {
    private static String driver;
    private static String url;
    private static String user;
    private static String pass;
    public void init(){  //通过这个方法来获得配置在web.xml里的四个参数
        driver = this.getInitParameter("driver");
        url = this.getInitParameter("url");
        user = this.getInitParameter("user");
        pass = this.getInitParameter("pass");
    }
    public static Connection getConnection(){  //该方法用于建立数据库连接，返回Connection 对象
        Connection cn= null;
        try{
            Class.forName(driver); 
            cn = DriverManager.getConnection(url,user,pass);
        }catch (Exception e){
            e.printStackTrace();
        }
        return cn;
    }
}

Servlet类

package com.jzk.firstservlet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;


public class DataServlet extends HttpServlet {
    @Override
    public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Connection cn = DataBase.getConnection(); //与不分离连接的区别在这里，所有的连接配置都在DataBase中，这里只需建立连接即可
        PrintWriter out =resp.getWriter();     //新建输出流
        Statement st = null;
        String sql = "select numbers, namess, price from shopping";   //测试用的sql语句
        try {
            st = cn.createStatement();      //用于向已经建立的数据库发送sql语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ResultSet rs = null;
        try {
            rs = st.executeQuery(sql);   //建立结果集，返回执行sql语句之后结果
        } catch (SQLException e) {
            e.printStackTrace();
        }
        while(true){  //反复循环，直到rs搜索到最后一条数据
            try {
                if (rs.next()){
                //访问数据库要干的事情
                }else{
                    break;
                };
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

遇到的问题

1. IDEA需要手动导入数据库驱动的jar包，否则在IDEA调试的时候，会出现如下错误

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

解决方法1：

1. 在WEB-INF下建立目录，并命名lib
2. 将下载好的驱动jar包复制粘贴到该目录下，
3. 右击，“add as Library”

解决方法2：

1. file-Project Structure-Library，点击+号，选择java，找到下载好的jar包，OK。
2. 可能会在Project Structure界面左侧最后一栏Problem出现提示错误，点击fix-add ‘mysql-connector-java-5.1.34’ to the artifacts.