Java简单数据库应用

基于Servlet的简单数据库应用

项目的创建

1. 这里选择创建一个Java Web项目，命名为hero（数据库数据就参考自上网的数据了，这里自己随便改）

2. 导入必要的lib包，并配置Tomcat

3. 连接MySql数据库

输入自己数据库的账号和密码，最好最后检查一下连接是否成功

4. 数据库的创建

drop database if exists hero;
create database hero default charset utf8;

create table hero(
  id int(11) not null primary key,
  name varchar(255) not null,
  hp double not null,
  damage double not null
)engine = InnoDB default charset utf8;

然后自己随便插入几条数据进行测试就行，这里插入的是

类的编写

由于项目比较简单，因此就不写DBUtil了，但是建议写项目的时候，最好单独写一个工具类。

• 在【bean】下创建Hero类

package bean;

public class Hero {

    private int id;   //id
    private String name;    //名字
    private double hp;   //血量
    private double damage;    //伤害


    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public double getHp() {
        return hp;
    }

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

    public double getDamage() {
        return damage;
    }

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

}

• 在【dao】下创建HeroDAO类

package dao;

import bean.Hero;

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

public class HeroDAO {

    private String url = "jdbc:mysql://localhost:3306"; //数据库网址
    private String database = "hero";//数据库名
    private String userName = "root"; //数据库账号
    private String password = "62556609"; //数据库密码

    public HeroDAO(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return  DriverManager.getConnection(url + "/" + database
                , userName, password);
    }

    public int getTotal(){
        int total = 0;
        try(Connection connection = getConnection(); Statement statement = connection.createStatement()){
            String sql = "select * from hero";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                total = resultSet.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return total;
    }

    public void add(Hero hero){
        String sql = "insert into hero values(null, ?, ?, ?)";
        try(Connection connection = getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)){

            preparedStatement.setString(1, hero.getName());
            preparedStatement.setDouble(2,hero.getHp());
            preparedStatement.setDouble(3,hero.getDamage());

            preparedStatement.execute();
        }catch (SQLException e){

            e.printStackTrace();
        }
    }

    public void delete(int id){
        String sql = "delete from hero where id = ?";
        try(Connection connection = getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)){
            preparedStatement.setInt(1,id);

            preparedStatement.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    public void update(Hero hero){
        String sql = "update hero set name = ?, hp = ?, damage = ?";
        try(Connection connection = getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)){
            preparedStatement.setString(1, hero.getName());
            preparedStatement.setDouble(2, hero.getHp());
            preparedStatement.setDouble(3,hero.getDamage());

            preparedStatement.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    public Hero getById(int id){
        Hero hero = new Hero();
        String sql = "select * from student where id = " + id;

        try(Connection connection = getConnection(); Statement statement = connection.createStatement()){

            ResultSet resultSet = statement.executeQuery(sql);


            if (resultSet.next()){
                String name = resultSet.getString("name");
                double hp = resultSet.getDouble("hp");
                double damage = resultSet.getDouble("damage");
                hero.setName(name);
                hero.setHp(hp);
                hero.setDamage(damage);
            }


        }catch (SQLException e){
            e.printStackTrace();
        }

        return hero;
    }
    public List<Hero> list(){
        //return list(0, Short.MAX_VALUE);
        return list(0, 2);
    }

    public List<Hero> list(int start, int count){

        List<Hero> heroList = new ArrayList<>();

        String sql = "select * from hero order by id desc limit ?, ?";
        try(Connection connection = getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)){
            preparedStatement.setInt(1, start);
            preparedStatement.setInt(2, count);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Hero hero = new Hero();
                hero.setId(resultSet.getInt("id"));
                hero.setName(resultSet.getString("name"));
                hero.setHp(resultSet.getDouble("hp"));
                hero.setDamage(resultSet.getDouble("damage"));
                heroList.add(hero);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return heroList;

    }

}

• 在【servlet】包下新建HeroListServlet类

package servlet;


import bean.Hero;
import dao.HeroDAO;

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 {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        List<Hero> heros = new HeroDAO().list();

        req.setAttribute("heros", heros);
        req.getRequestDispatcher("listHero.jsp").forward(req, resp);

    }
}

Web层

• listHero.jsp的编写

<%--
  Created by IntelliJ IDEA.
  User: Sheva
  Date: 2019/7/5
  Time: 11:31
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java"
         pageEncoding="UTF-8" import="java.util.*"%>

<%@taglib uri="http://java.sun.com/jsp/jstl/core"
          prefix="c"%>

<table align="center" border="1" cellspacing="0">
  <tr>
    <td>id</td>
    <td>name</td>
    <td>hp</td>
    <td>damage</td>
    <td>edit</td>
    <td>delete</td>
  </tr>
  <c:forEach items="${heros}" var="hero" varStatus="st">
    <tr>
      <td>${hero.id}</td>
      <td>${hero.name}</td>
      <td>${hero.hp}</td>
      <td>${hero.damage}</td>
      <%--  这两个是业务逻辑按钮，目前还没写 。。。 --%>
      <td>edit</td>
      <td>delete</td>

    </tr>
  </c:forEach>
</table>

• 配置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>servlet.HeroListServlet</servlet-class>


    </servlet>

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

运行

只是做了一个简单的视图展示，基本的操作逻辑都还没有加上，这个项目等以后有空再把坑填上吧。

• 注：连接数据库的时候可能会出现时区问题，解决方法：

url后面增加：

jdbc.url=jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC

试了很多次这个方法都不管用后来直接修改了时区。

修改数据库时区：

set global time_zone = ‘+8:00’; ##修改mysql全局时区为北京时间，即我们所在的东8区
set time_zone = ‘+8:00’; ##修改当前会话时区
flush privileges; #立即生效

• 注：运行的时候还碰到一个问题，tomcat无法连接到数据库，后来发现要把mysql驱动要放到tomcat目录下的lib文件夹，切记切记，因为这个问题浪费了不少时间