javaServlet封装常用数据库查询逻辑

封装常用的数据库查询语句可以提高代码的复用性和可维护性，以下是一个 JavaServlet 封装常用查询语句的实例，包括增、删、改、查。

查询一条记录

public Map<String, Object> queryOne(String sql, Object... params) {
    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        try (ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, Object> resultMap = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object columnValue = rs.getObject(columnName);
                    resultMap.put(columnName, columnValue);
                }
                return resultMap;
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

查询多条记录

public List<Map<String, Object>> queryList(String sql, Object... params) {
    List<Map<String, Object>> resultList = new ArrayList<>();
    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, Object> resultMap = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object columnValue = rs.getObject(columnName);
                    resultMap.put(columnName, columnValue);
                }
                resultList.add(resultMap);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return resultList;
}

插入一条记录

public int insert(String sql, Object... params) {
    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        return stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return 0;
}

更新一条记录

public int update(String sql, Object... params) {
    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        return stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return 0;
}

删除一条记录

public int delete(String sql, Object... params) {
    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        return stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return 0;
}

调用实例

要使用以上代码，需要首先创建一个数据源，例如

import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
public class DataSourceFactory {
    public static DataSource createDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setInitialSize(5);
        dataSource.setMaxActive(100);
        dataSource.setMinIdle(1);
        dataSource.setMaxWait(60000);
        return dataSource;
    }
}

然后，在 Servlet 中创建一个数据库操作对象：

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import java.sql.SQLException;

@WebServlet("/database")
public class DatabaseServlet extends HttpServlet {
    private DataSource dataSource;
    private DatabaseOperation databaseOperation;

    @Override
    public void init() throws ServletException {
        dataSource = DataSourceFactory.createDataSource();
        databaseOperation = new DatabaseOperation(dataSource);
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String sql = "SELECT * FROM user WHERE id = ?";
        Map<String, Object> user = databaseOperation.queryOne(sql, 1);
        System.out.println(user);

        sql = "SELECT * FROM user";
        List<Map<String, Object>> userList = databaseOperation.queryList(sql);
        System.out.println(userList);
    }

    @Override
    public void destroy() {
        if (dataSource != null) {
            try {
                dataSource.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

在 Servlet 的 init 方法中初始化数据源和数据库操作对象，然后在 doGet 方法中使用这个对象调用封装好的方法进行数据库操作。最后，在 Servlet 的 destroy 方法中关闭数据源。以上代码仅作为示例，具体实现需要根据具体的需求进行修改和完善。