A Simple CRUD Sample Using Java Servlet / JSP and MySQL

Create database and table with mysql

CREATE DATABASE LEARNING;
USE  LEARNING;
CREATE TABLE `hero` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `hp` float DEFAULT NULL,
  `damage` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

bean sample

package bean;
public class Hero {
    public int id;
    public String name;
    public float hp;
    public int 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 float getHp() {
        return hp;
    }
    public void setHp(float hp) {
        this.hp = hp;
    }
    public int getDamage() {
        return damage;
    }
    public void setDamage(int damage) {
        this.damage = damage;
    }
     
}

DAO class sample

package dao;



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

/**
 *Provides CRUD methods
 *
 * @auther baobaochu
 * @create 2017/6/20
 */
public class HeroDAO {
    static String ip = "127.0.0.1";
    static int port = 3306;
    static String database = "LEARNING";
    static String encoding = "UTF-8";
    static String loginName = "root";
    static String password = "*****";

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

    public static Connection getConnection() throws SQLException {
        String url = String.format("jdbc:mysql://%s:%d/%s?characterEncoding=%s", ip, port, database, encoding);
        return DriverManager.getConnection(url, loginName, password);
    }

    public int getTotal(){
        int total = 0;
        try (Connection c = getConnection(); Statement s = c.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;

    }

    public void add(Hero hero) {

        String sql = "insert into hero values(null,?,?,?)";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            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 c = getConnection(); PreparedStatement ps = c.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();
        }

    }

    public void delete(int id) {

        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "delete from hero where id = " + id;

            s.execute(sql);

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public Hero get(int id) {
        Hero hero = null;

        try (Connection c = getConnection(); Statement s = c.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(0, Short.MAX_VALUE);
    }

    public List<Hero> list(int start, int count) {
        List<Hero> heros = new ArrayList<Hero>();

        String sql = "select * from hero order by id desc limit ?,? ";

        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setInt(1, start);
            ps.setInt(2, count);

            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;
    }



}

Recommended Posts

A Simple CRUD Sample Using Java Servlet / JSP and MySQL
Create a memo app with Tomcat + JSP + Servlet + MySQL using Eclipse
Create a simple bulletin board with Java + MySQL
CICS-Run Java application-(1) Run a simple sample app
[Java] I tried to connect using a connection pool with Servlet (tomcat) & MySQL & Java
[Java] I installed JDBC and tried to connect with servlet + MySQL. (There is a version using DAO / Bean)
Create a portfolio app using Java and Spring Boot
How to convert A to a and a to A using AND and OR in Java
Create a Java Servlet and JSP WAR file to deploy to Apache Tomcat 9 in Gradle
How to deploy a simple Java Servlet app on Heroku
[For myself] Browser display using JSP files and Servlet classes
[JSP, Servlet] Launch a new project and check the connection !!
Java Servlet / JSP View drawing
Reasons to use Servlet and JSP separately in Java development
Socket communication with a web browser using Java and JavaScript ②
Make a rhombus using Java
I made a simple MVC sample system using Spring Boot
Comparison of WEB application development with Rails and Java Servlet + JSP
Java Servlet / JSP Request Scope Part 1
3 Implement a simple interpreter in Java
Java Servlet / JSP Request Scope Part 2
Java 9 new features and sample code
A sample Servlet that downloads Excel.
Create a Java project using Eclipse
[MySQL] [java] Receive date and time
Sample code using Minio from Java
Create a simple CRUD with SpringBoot + JPA + Thymeleaf ② ~ Screen and function creation ~
Java beginner tried to make a simple web application using Spring Boot
[Java] Create and apply a slide master
A look at Jenkins, OpenJDK 8 and Java 11
Connect from Java to MySQL using Eclipse
A Java engineer compared Swift, Kotlin, and Java.
Java and Derby integration using JDBC (using NetBeans)
Implement simple CRUD with Go + MySQL + Docker
Hello World with Java Servlet and JSP (Easy web server startup with Maven + Jetty)
When using a list in Java, java.awt.List comes out and an error occurs
A Simple CRUD Sample Using Java Servlet / JSP and MySQL
Prepare a transcendentally simple PHP & Apache environment on Mac with Docker
Get weather forecasts from Watson Weather Company Data with a simple JSP
Run a simple model made with Keras on iOS using CoreML
Create a simple gateway server by setting masquerade with firewall-cmd of CentOS8