如何連接資料庫

1.安裝依賴

首先要在pom.xml檔案中引入dbcp和mysql的依賴，

		<dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-dbcp</artifactId>
            <version>10.1.0-M2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

2.創建DBUtil.class

創建DBUtil.class工具類，方便對連接池進行管理，

package dao;

import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
    public static final String IP = "****";
    public static final String username = "****";
    public static final String password = "****";
    public static final String port = "****";
    public static final String hostname = "****";
    public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    public static final String DB_URL = "jdbc:mysql://****:*****/****";

    public static BasicDataSource basicDataSource = null;

    static {
        basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName(JDBC_DRIVER);
        basicDataSource.setUrl(DB_URL);
        basicDataSource.setUsername(username);
        basicDataSource.setPassword(password);
    }

    public static Connection getConn(){
        try{
            return basicDataSource.getConnection();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    public  static void release(Connection conn, Statement stat, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(stat != null){
            try {
                stat.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

}

3.使用DBCP連接池（以登錄為例）

使用DBUtil.class工具類，來實作相應的資料庫操作，

package com.pipi.servlet;

import dao.DBUtil;

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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class loginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("utf-8");
        req.setCharacterEncoding("utf-8");
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConn();
            String sql = "select * from user where name = ? and password = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            ps.setString(2,password);
            rs = ps.executeQuery();
            if(rs.next()){
                resp.sendRedirect("main.jsp");
            }else {
                req.getRequestDispatcher("/index.jsp?result=賬號不存在或密碼錯誤").forward(req,resp);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

如何回傳JSON資料格式

1.安裝依賴

fastjson.jar是阿里巴巴開發的一款專門用于Java開發的包，可以方便的實作json物件與JavaBean物件的轉換，實作JavaBean物件與json字串的轉換，實作json物件與json字串的轉換，除了這個fastjson以外，還有Google開發的Gson包，其他形式的如net.sf.json包，都可以實作json的轉換，方法名稱不同而已，最后的實作結果都是一樣的，

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.78</version>
        </dependency>

2.如何使用fastjson

這里實作了資料以json資料回傳，并分頁

protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/json");
        req.setCharacterEncoding("utf-8");
        int page = Integer.parseInt(req.getParameter("page"));
        int size = Integer.parseInt(req.getParameter("limit"));
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        Map resultData = new HashMap();
        try{
            conn = DBUtil.getConn();
            String sql = "select * from info limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1,(page - 1) * size);
            ps.setInt(2,(page - 1) * size + size);
            rs = ps.executeQuery();

            resultData.put("code",0);
            resultData.put("msg","資料獲取成功！");
            resultData.put("count",userCount());
            resultData.put("data",UserList(rs));

            JSONObject jsonObject = new JSONObject();
            String info = jsonObject.toJSONString(resultData);

            PrintWriter out = resp.getWriter();
            out.write(info);
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

Userlist方法

private static List UserList(ResultSet rs) throws SQLException{
        List list = new ArrayList();
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int coulmnCount = resultSetMetaData.getColumnCount();
        while (rs.next()){
            Map rowData = new HashMap();
            for (int i =1 ; i <= coulmnCount ; i++){
                rowData.put(resultSetMetaData.getColumnName(i),rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

userCount方法

private static int userCount() throws SQLException{
        int Count = 0;
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;

        conn = DBUtil.getConn();
        String sql = "SELECT COUNT(id) from info";

        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while(rs.next()){
             Count = rs.getInt(1);
        }
        return Count;
    }

實作效果