servlet

jdbc连接数据库

自己写的jdbc

package DAO;



import java.io.File;
import java.io.FileReader;
import java.sql.*;
import java.util.Properties;

public class jdbcControlClass {
    public static Connection connection;
    public static Statement statement;
    public static PreparedStatement preparedStatement;
    public static Properties properties;
    public static FileReader reader;
    public static ResultSet resultSet;
    public static void connect() throws Exception {
        properties=new Properties();
        reader = new FileReader(new File("D:\\learn\\project\\book\\servletBook\\src\\JDBC.properties"));
        properties.load(reader);
        Class.forName(properties.getProperty("classname"));

        jdbcControlClass.connection= DriverManager.getConnection(properties.getProperty("mysqlurl"),properties.getProperty("username"),properties.getProperty("password"));
    }

    public static ResultSet StatementExe(String sql) throws Exception {
        if (jdbcControlClass.connection==null){
            connect();
        }
        jdbcControlClass.statement=jdbcControlClass.connection.createStatement();
        jdbcControlClass.resultSet=statement.executeQuery(sql);
        return jdbcControlClass.resultSet;
    }
    public static ResultSet PreparedStatementExeQuery(String sql,String...argument) throws Exception {
        if (jdbcControlClass.connection==null){
            connect();
        }

        jdbcControlClass.preparedStatement=jdbcControlClass.connection.prepareStatement(sql);
        for (int i = 0; i <argument.length; i++) {
            preparedStatement.setString(i+1,argument[i]);
        }


        System.out.println(preparedStatement.toString());


        jdbcControlClass.resultSet=preparedStatement.executeQuery();
        return jdbcControlClass.resultSet;
    }
    public static int PreparedStatementExeUpdate(String sql, String...argument) throws Exception {
        if (jdbcControlClass.connection==null){
            connect();
        }

        jdbcControlClass.preparedStatement=jdbcControlClass.connection.prepareStatement(sql);
        for (int i = 0; i <argument.length; i++) {
            preparedStatement.setString(i+1,argument[i]);
        }
        System.out.println(preparedStatement.toString());
        return preparedStatement.executeUpdate();
    }
    public static void closeAll() throws Exception {
        if (resultSet!=null) {
            resultSet.close();
        }

        if (statement!=null) {
            statement.close();
        }
        if (preparedStatement!=null) {
            preparedStatement.close();
        }
        if (connection!=null) {
            connection.close();
            reader.close();
        }


    }


    public static String getPorperties(String key){
        if (jdbcControlClass.preparedStatement==null){
            try {
                connect();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return properties.getProperty(key);
    }

    public static void main(String[] args) throws Exception {
        resultSet=PreparedStatementExeQuery("select * from book ");

        while (resultSet.next()){
            System.out.print(resultSet.getString("bno"));
            System.out.println(resultSet.getString("bname"));
        }




        PreparedStatementExeUpdate("update book set bname=? where bno=999999","123");
        resultSet=PreparedStatementExeQuery("select * from book ");

        while (resultSet.next()){
            System.out.print(resultSet.getString("bno"));
            System.out.println(resultSet.getString("bname"));
        }




    }
}

配置文件

命名为JDBC.properties

classname=com.mysql.cj.jdbc.Driver
mysqlurl=jdbc:mysql://localhost:3306/bookshowcomsysterm?useSSL=false&serverTimezone=UTC&character=utf8
username=root
password=123
administrator=administrator
adminiColumnName=ad_name
addminiAccount=ad_account
adminiPassword=ad_psw
user=user
batteryManager=powerbank
hitoryOrder=order_c
userName =u_name

还有maven中的dependence

<dependency>
    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <version>8.0.30</version>

</dependency>

在servlet中发送json

首先设置发送内容类型和发送内容使用的字符集

resp.setContentType("application/json");
resp.setCharacterEncoding("UTF-8");

新建一个JSONArray用于存放json数据

JSONArray jsonArray = new JSONArray();

再将每一个数据写入JSONObeject中

while (resultSet.next()){
        JSONObject jsonObject = new JSONObject();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            jsonObject.put(metaData.getColumnName(i),resultSet.getString(i));
        }
        jsonArray.put(jsonObject);
}

注意，要使用JSONObject，需要在maven中导入新dependence

<dependency>
    <groupId>org.json</groupId>

    <artifactId>json</artifactId>

    <version>20210307</version>

</dependency>

发送json

resp.getWriter().write(jsonArray.toString());

完整代码

@WebServlet(name = "book",urlPatterns = "/book")
public class book extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String sql="select * from books";
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        try {
            ResultSet resultSet = jdbcControlClass.StatementExe(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            JSONArray jsonArray = new JSONArray();
            while (resultSet.next()){
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    jsonObject.put(metaData.getColumnName(i),resultSet.getString(i));
                }
                jsonArray.put(jsonObject);
            }
            resp.getWriter().write(jsonArray.toString());
            System.out.println(jsonArray.toString());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.getWriter().println("213");
    }
}

servlet中获取参数

使用request.getParmameter(“参数名”)获取参数

protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String sql;
        boolean haveParam = false;
        String bookId = req.getParameter("bookID");
        String category = req.getParameter("category");
    ...略

例子: 根据不同参数返回不同的json数据,建议扔到ai中食用：）

@Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String sql;
        boolean haveParam = false;
        String bookId = req.getParameter("bookID");
        String category = req.getParameter("category");
//        Integer bookId = new Integer();
        if (bookId!="") {
            sql = "select * from books where id = ? ; " ;
            haveParam = true;
        }else if (category!="") {
            sql = "select * from books where category = ? ; " ;
            haveParam = true;
        }else {
            sql = "select * from books";
        }
        //获取sql查到的所有数据，自动返回json
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        try {
            ResultSet resultSet = null;

            if (haveParam){
                if (bookId!=""){
                    resultSet = jdbcControlClass.PreparedStatementExeQuery(sql,bookId);
                }else if (category!=""){
                    resultSet = jdbcControlClass.PreparedStatementExeQuery(sql,category);
                }
            }else {

                resultSet = jdbcControlClass.StatementExe(sql);


            }
            System.out.println(resultSet);
            ResultSetMetaData metaData = resultSet.getMetaData();
            JSONArray jsonArray = new JSONArray();
            while (resultSet.next()){
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    jsonObject.put(metaData.getColumnName(i),resultSet.getString(i));
                }
                jsonArray.put(jsonObject);
            }
            resp.getWriter().write(jsonArray.toString());
            System.out.println(jsonArray.toString());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

注意事项

请在maven的porm.xml文件中配置dependcy

<dependency>
            <groupId>javax.servlet</groupId>

            <artifactId>javax.servlet-api</artifactId>

            <version>4.0.1</version>

</dependency>

新建的就是servlet版本太新导致路径识别不到

使用4.0.1版本就可以识别

当你查询前端返回的参数时可能会查询不到

和前端约定返回的数据是json

转换为json对象

再查询参数

resp.setContentType("application/json");
resp.setCharacterEncoding("UTF-8");

StringBuilder sb = new StringBuilder();
        String line;
        try (BufferedReader reader = req.getReader()) {
            while ((line = reader.readLine()) != null) {
                sb.append(line);
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 处理错误情况
        }
        String requestBody = sb.toString();

        // 检查请求体是否为空或不是有效的 JSON 字符串
        if (requestBody.isEmpty() || !requestBody.trim().startsWith("{")) {
            resp.getWriter().write("{\"status\":\"fail\", \"message\":\"Invalid request body.\"}");
            return;
        }
        // 将读取的内容转换为 JSON 对象
        JSONObject jsonObject = new JSONObject(sb.toString());
        String userId = jsonObject.getNumber("userId").toString();
//        Integer userId = (Integer) jsonObject.getNumber("userId");
        String bookId = jsonObject.getString("bookID");

String userId = jsonObject.getNumber("userId").toString();
//        Integer userId = (Integer) jsonObject.getNumber("userId");
String bookId = jsonObject.getString("bookID");

更新: 2024-12-14 13:09:54
原文: https://www.yuque.com/duifangzhengzaishuru-rqbua/axyc58/emkkf5i6lsbec5f5