后台-插件-广告管理-内容页广告位一(手机)

您现在的位置是:首页 > 新兴行业 > 云计算云计算

JavaWeb连接mysql数据库并返回json格式数据(超详细整理)

2021-09-15 00:17:01云计算人已围观

简介如何连接数据库1.安装依赖首先要在pom.xml文件中引入dbcp和mysql的依赖。 <dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-dbcp</artifactId> <version&

如何连接数据库

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;
    }
实现效果

在这里插入图片描述

Tags:MySQL JSON javaweb 

很赞哦! ()

后台-插件-广告管理-内容页广告位二(手机)

相关文章

后台-插件-广告管理-内容页广告位三(手机)

随机图文

后台-插件-广告管理-内容页广告位四(手机)

文章评论

留言与评论(共有 0 条评论)
   
验证码:

本栏推荐

站点信息

  • 文章统计141439篇文章
  • 浏览统计17469次浏览
  • 评论统计1个评论
  • 标签管理标签云
  • 统计数据:统计代码
  • 微信公众号:扫描二维码,关注我们