连接数据库—初识JavaBean

• 关于输入输出过程中出现的编码问题，这个博文写的很明白，重点注意：要在获取输出流之前设置客户端和服务器端的编码方式，我因为这个弄了好久。
  关键的两个函数：
  response.setCharEncoding("UTF-8")
  response.setContentType("text/html;charset=utf-8")
  https://blog.csdn.net/lxf512666/article/details/52939573
• 在webapp中连接数据库需要以下几步：
  1. 在web-INF目录下的lib中引入对应数据库的jar包，这里的位置一定要放对。
  2. 按照常规JDBC编程步骤开展编程

我们来看一个小例子：

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 ShowRs extends HttpServlet {


    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        resp.setContentType("text/html;charset=utf-8");
        PrintWriter out = resp.getWriter();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/bbs?characterEncoding=utf8&useSSL=false";
            connection = DriverManager.getConnection(url, "root", "332999");
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from article");
            while (resultSet.next()){
                out.println(resultSet.getString("title"));
                out.println("\n");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

                try {
                    if (resultSet != null)
                        resultSet.close();
                    if (statement != null)
                        statement.close();
                    if (connection != null)
                        connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }
}

• 从上面的例子可以看出，手续极其繁琐，代码中有很多try...catch...，可读性不高。在实际项目中，我们不能每次请求数据都要过一遍JDBC编程，最好的方法是将它封装在一个类里，然后再调用相关的方法连接数据库，这样使得代码简洁。这种在servlet中使用类的方法，也就是广义上的javabean
  同样我们看两段代码：分别1.是对数据库的封装类，和2.使用类，同时与上文方法做对比，体会javabean带给我们的好处。

1. db.java

import com.sun.jdi.event.StepEvent;

import java.sql.*;

// 在servlet中使用javabean就是在使用封装数据库的类
public class DB {
    public static Connection getConn() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/bbs?characterEncoding=utf8&useSSL=false";
            connection = DriverManager.getConnection(url, "root", "332999");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static Statement getStatement(Connection connection){
        Statement statement = null;
        try {
            if (connection != null){
                statement = connection.createStatement();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return statement;
    }
    public static ResultSet getResultSet(Connection connection,String sql){
        Statement statement = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ResultSet resultSet  = getResultSet(statement, sql);
        close(statement);
        return resultSet;
    }

    public static ResultSet getResultSet(Statement statement,String sql){
        ResultSet resultSet = null;
        try {
            if (statement != null){
                resultSet = statement.executeQuery(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public static void close(Connection connection){
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement statement){
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet resultSet){
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2. ShowRsUseBean.java

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.desktop.SystemSleepEvent;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ShowRsUseBean extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("charset=utf-8");
        PrintWriter out = resp.getWriter();
        String sql = "select * from article";
        Connection connection = DB.getConn();
        Statement statement = DB.getStatement(connection);
        ResultSet resultSet = DB.getResultSet(statement,sql);
        try {
            while (resultSet.next())
            {
                out.println(resultSet.getString("title"));
                System.out.println(resultSet.getString("title"));
                out.println("\n\n\r");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DB.close(resultSet);
            DB.close(statement);
            DB.close(connection);
        }

    }
}