Servelt查询

Servlet文章目录

一、Servlet查询
二、Servlet增加
三、Servlet删除
四、Servelt修改
五、Servlet提交数据
六、Servlet获取对象

---

---

Servlet查询

Servlet学习笔记

---

一、创建数据库

--id设置为主键,自增,不为空
create database Hero;
go
use Hero;
go
create table hero
(
	id int primary key not null identity(1,1),
	name varchar(30),
	hp float,
	damage int,
)

二、创建Servlet

1.创建DAO

在src下创建一个dao包然后创建HeroDAO
使用的是Server-SQL,注意DAO的函数中无法设置null,top后面无法使用占位符

package com.dao;

import com.entity.Hero;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName $HeroDAO
 * @Author $童一
 * @Description $
 * @Param $
 * @return $
 * @Date $ $
 **/

public class HeroDAO {
    //加载数据库
    public HeroDAO() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("加载数据库失败");
        }
    }
	//连接数据库,Server-SQL的端口为1433,MySQL的端口为3306
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=Hero", "sa", "tmj20000509");
    }
	//获取总数
    public int getTotal() {
        int total = 0;
        try (Connection conn = getConnection(); Statement s = conn.createStatement();) {
            String sql = "select count(*) from hero";
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
            System.out.println("Total:" + total);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
	//增加,这里的values中id因为为自增,不能设置
    public void add(Hero hero) {
        String sql = "insert into hero values(?,?,?)";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);) {
            ps.setString(1, hero.name);
            ps.setFloat(2, hero.hp);
            ps.setInt(3, hero.damage);
            ps.execute();
            //获取自增主键
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                hero.id = id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //更新
    public void update(Hero hero) {
        String sql = "update hero set name = ?,hp = ?, damage = ? where id = ?";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
            ps.setString(1, hero.name);
            ps.setFloat(2, hero.hp);
            ps.setInt(3, hero.damage);
            ps.setInt(4, hero.id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
	//通过id进行删除
    public void delete(int id) {
        try (Connection conn = getConnection(); Statement s = conn.createStatement();) {
            String sql = "delete from hero where id = " + id;
            s.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //通过id获取该hero的数据
    public Hero get(int id) {
        Hero hero = null;
        try (Connection conn = getConnection(); Statement s = conn.createStatement();) {
            String sql = "select * from hero where id =" + id;
            ResultSet rs = s.executeQuery(sql);
            if (rs.next()) {
                hero = new Hero();
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                hero.id = id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return hero;
    }

    public List<Hero> list() {
        return list(1, Short.MAX_VALUE);
    }
    //使用max进行分页查询
    public List<Hero> list(int start, int count) {
        List<Hero> heros = new ArrayList<Hero>();
        //Server-SQL top后面不能跟?当作占位符
        String sql = "select top " + count + " * from hero where id>=(select max(id)from (select top " + start + " id from hero order by id)temp)";
        //String sql = "select * from (select ROW_NUMBER() over(order by id asc)as rowid,* from hero)t where t.rowid>? and t.rowid<=?";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Hero hero = new Hero();
                int id = rs.getInt(1);
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                hero.id = id;
                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                heros.add(hero);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return heros;
    }
}

2.创建实体类

在src下建立entity包,创建Hero类

package com.entity;

/**
 * @ClassName Hero
 * @Author 童一$童一
 * @Description $
 * @Param $
 * @return $
 * @Date $ $
 **/

public class Hero {
    public int id;
    public String name;
    public float hp;
    public int damage;

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setHp(float hp) {
        this.hp = hp;
    }

    public void setDamage(int damage) {
        this.damage = damage;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public float getHp() {
        return hp;
    }

    public int getDamage() {
        return damage;
    }
}

3.创建HeroListServlet

在src下建立Servlet包,创建HeroListServlet类用于查询所有的数据

package com.servlet;

import com.dao.HeroDAO;
import com.entity.Hero;

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.util.List;

public class HeroListServlet extends HttpServlet {
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        //获取数据库中所有的数据
        List<Hero> heros = new HeroDAO().list();
        StringBuffer sb = new StringBuffer();
        int total = new HeroDAO().getTotal();
        //创建一个表格用于显示数据,使用StringBUffer方便修改
        sb.append("<table align='center' border='1' cellspacing='1'>\r\n");
        sb.append("<tr><td>id</td><td>name</td><td>hp</td><td>damage</td><td>delete</td><td>edit</td></tr>\r\n");
        String trFormat = "<tr><td>%d</td><td>%s</td><td>%f</td><td>%d</td><td><a href='deleteHero?id=%d'>删除</a></td>" +
                "<td><a href='updateHero?id=%d'>修改</a></td></tr>\r\n";
        for (Hero hero : heros) {
            String str = String.format(trFormat, hero.getId(), hero.getName(), hero.getHp(), hero.getDamage(), hero.getId(),hero.getId());
            sb.append(str);
        }
        String str1 = "<tr><td colspan='3'>Total: </td><td colspan='2'>%d</td></tr>";
        String str2 = String.format(str1,total);
        sb.append(str2);
        sb.append("</table>");
        response.getWriter().write(sb.toString());
    }
}

3.配置web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">

    <servlet>
        <servlet-name>HeroListServlet</servlet-name>
        <servlet-class>com.servlet.HeroListServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>HeroListServlet</servlet-name>
        <url-pattern>/listHero</url-pattern>
    </servlet-mapping>
<web-app>

---

总结

启动tomcat，访问http://localhost:8080/ServletCRUD/listHero
HeroListServlet通过HeroDAO()中的list方法获取所有数据并以表格的形式显示