MySQL

CREATE DATABASE Arknights;
USE Arknights;

CREATE TABLE Rhodes
(
id int NOT NULL,
name varchar(30),
Ename varchar(30),
starlevel int,
tag varchar(50),
postion varchar(50),
skills varchar(10),
update_date date,
PRIMARY KEY(id)
);

View Code

结构

com.arknights
    |
    |action
        GoddessAction  //控制层
    |
    |curd
        GoddessCurd  //数据库操作
    |
    |jdbc
        DBUtil  //连接数据库
    |
    |model
        Goddess  //模型层
    |
    |view
        View  //视图层

DBUtil

package com.imooc.jdbc;
import java.sql.*;

public class DBUtil{
    private static final String URL = "jdbc:mysql://localhost:3306/imooc?serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "277519";

    private static Connection con = null;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("加载驱动失败");
        }
        try {
            con = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }

    public static Connection getConnection() {
        return con;
    }
}

View Code

Goddess

package com.arknights.model;

import java.util.Date;

public class Goddess {
    private Integer id;
    private String name;
    private String Ename;
    private Integer starlevel;
    private String tag;
    private String postion;
    private String skills;
    private Date update_date;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEname() {
        return Ename;
    }

    public void setEname(String ename) {
        Ename = ename;
    }

    public Integer getStarlevel() {
        return starlevel;
    }

    public void setStarlevel(Integer starlevel) {
        this.starlevel = starlevel;
    }

    public String getTag() {
        return tag;
    }

    public void setTag(String tag) {
        this.tag = tag;
    }

    public String getPostion() {
        return postion;
    }

    public void setPostion(String postion) {
        this.postion = postion;
    }

    public String getSkills() {
        return skills;
    }

    public void setSkills(String skills) {
        this.skills = skills;
    }

    public Date getUpdate_date() {
        return update_date;
    }

    public void setUpdate_date(Date update_date) {
        this.update_date = update_date;
    }

    @Override
    public String toString() {
        return "Goddess{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", Ename='" + Ename + '\'' +
                ", starlevel=" + starlevel +
                ", tag='" + tag + '\'' +
                ", postion='" + postion + '\'' +
                ", skills='" + skills + '\'' +
                ", update_date=" + update_date +
                '}';
    }
}

View Code

GoddessCurd

package com.arknights.curd;

import com.arknights.jdbc.DBUtil;
import com.arknights.model.Goddess;

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

public class GoddessCurd {
    public void addGoddess(Goddess g) throws SQLException {
        Connection con = DBUtil.getConnection();
        String sql = "" +
                "insert into rhodes" +
                " (id, name, Ename, starlevel, tag, postion, skills, update_date)" +
                " values(" +
                " ?, ?, ?, ?, ?, ?, ?, current_date())";
        PreparedStatement ptmt = con.prepareCall(sql);  //预编译

        ptmt.setInt(1, g.getId());
        ptmt.setString(2, g.getName());
        ptmt.setString(3, g.getEname());
        ptmt.setInt(4, g.getStarlevel());
        ptmt.setString(5, g.getTag());
        ptmt.setString(6, g.getPostion());
        ptmt.setString(7, g.getSkills());
        ptmt.execute();
    }

    public void updateGoddess(Goddess g) throws SQLException {
        Connection con = DBUtil.getConnection();
        String sql = "" +
                " update rhodes" +
                " set name=?, Ename=?, starlevel=?, tag=?, postion=?, skills=?," +
                " update_date=current_date()" +
                " where id=?";

        PreparedStatement ptmt = con.prepareCall(sql);  //预编译

        ptmt.setString(1, g.getName());
        ptmt.setString(2, g.getEname());
        ptmt.setInt(3, g.getStarlevel());
        ptmt.setString(4, g.getTag());
        ptmt.setString(5, g.getPostion());
        ptmt.setString(6, g.getSkills());
        ptmt.setInt(7, g.getId());
        ptmt.execute();
    }

    public void delGoddess(Integer id) throws SQLException {
        Connection con = DBUtil.getConnection();
        String sql = "" +
                " delete from rhodes" +
                " where id=?";
        PreparedStatement ptmt = con.prepareCall(sql);  //预编译

        ptmt.setInt(1, id);
        ptmt.execute();
    }

    public List<Goddess> query() throws SQLException {
        Goddess g;
        List<Goddess> gs = new ArrayList<>();
        Connection con = DBUtil.getConnection();
        Statement state = con.createStatement();
        ResultSet res = state.executeQuery("select * from rhodes");
        while (res.next()) {
            g = new Goddess();
            g.setId(res.getInt("id"));
            g.setName(res.getString("name"));
            g.setEname(res.getString("Ename"));
            g.setStarlevel(res.getInt("starlevel"));
            g.setTag(res.getString("tag"));
            g.setPostion(res.getString("postion"));
            g.setSkills(res.getString("skills"));
            g.setUpdate_date(res.getDate("update_date"));

            gs.add(g);
        }
        return gs;
    }


    public List<Goddess> query(List<Map<String, Object>> params) throws SQLException {
        Goddess g;
        List<Goddess> gs = new ArrayList<>();
        Connection con = DBUtil.getConnection();

        StringBuilder sb = new StringBuilder();
        sb.append("select * from rhodes where 1=1"); //这样避免了第一个

        if (params != null && params.size() > 0) {
            for (Map<String, Object> map : params) {
                sb.append(" and ").append(map.get("name")).append(" ").append(map.get("rela")).
                        append(" ").append(map.get("value"));
            }
        }

        PreparedStatement ptmt = con.prepareStatement(sb.toString());
        System.out.println(sb.toString());
        ResultSet res = ptmt.executeQuery();

        while (res.next()) {
            g = new Goddess();
            g.setId(res.getInt("id"));
            g.setName(res.getString("name"));
            g.setEname(res.getString("Ename"));
            g.setStarlevel(res.getInt("starlevel"));
            g.setTag(res.getString("tag"));
            g.setPostion(res.getString("postion"));
            g.setSkills(res.getString("skills"));
            g.setUpdate_date(res.getDate("update_date"));

            gs.add(g);
        }
        return gs;
    }
}

View Code

GoddessAction

package com.arknights.action;

import com.arknights.curd.GoddessCurd;
import com.arknights.model.Goddess;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class GoddessAction {

    public void add(Goddess goddess) throws SQLException {
        GoddessCurd curd = new GoddessCurd();
        curd.addGoddess(goddess);
    }

    public void update(Goddess goddess) throws SQLException {
        GoddessCurd curd = new GoddessCurd();
        curd.updateGoddess(goddess);
    }

    public void del(Integer id) throws SQLException {
        GoddessCurd curd = new GoddessCurd();
        curd.delGoddess(id);
    }

    public List<Goddess> query() throws SQLException {
        GoddessCurd curd = new GoddessCurd();
        return curd.query();
    }

    public List<Goddess> query(List<Map<String, Object>> params) throws SQLException {
        GoddessCurd curd = new GoddessCurd();
        return curd.query(params);
    }
}

View Code

View

package com.arknights.view;

import com.arknights.action.GoddessAction;
import com.arknights.model.Goddess;

import java.sql.SQLException;
import java.util.*;

public class View {

    private static final String CONTEXT = "欢迎：\n" +
            "下面是功能列表：\n" +
            "[MAIN/M]: 主菜单\n" +
            "[QUERY/Q]: 查看全部信息\n" +
            "[ADD/A]: 添加信息\n" +
            "[UPDATE/U]: 更新信息\n" +
            "[DELETE/D]: 删除信息\n" +
            "[SEARCH/S]: 查询信息\n" +
            "[EXIT/E]: 退出";

    private static final String OPERATION_MAIN = "MAIN";
    private static final String OPERATION_QUERY = "QUERY";
    private static final String OPERATION_ADD = "ADD";
    private static final String OPERATION_UPDATE = "UPDATE";
    private static final String OPERATION_DELETE = "DELETE";
    private static final String OPERATION_SEARCH = "SEARCH";
    private static final String OPERATION_EXIT = "EXIT";
    private static final String OPERATION_NO = "NO";

    public static void main(String[] args) {
        System.out.println(CONTEXT);
        Scanner scanner = new Scanner(System.in);
        Goddess goddess = new Goddess();
        GoddessAction action = new GoddessAction();

        while (scanner.hasNext()) {
            String in = scanner.next();
            if (OPERATION_EXIT.equals(in.toUpperCase())
                    || OPERATION_EXIT.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println("Bye");
                break;
            }

            else if (OPERATION_QUERY.equals(in.toUpperCase())
                    || OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())) {
                try {
                    List<Goddess> list = action.query();
                    for (Goddess g : list) {
                        System.out.println(g.toString());
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            else if (OPERATION_DELETE.equals(in.toUpperCase())
                    || OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println("请输入删除干员的[ID]");
                in = scanner.next();
                try {
                    action.del(Integer.valueOf(in));
                    System.out.println("删除成功");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    System.out.println("删除失败");
                }
            }

            else if (OPERATION_ADD.equals(in.toUpperCase())
                    || OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println("请输入[ID]");
                in = scanner.next();
                goddess.setId(Integer.valueOf(in));
                System.out.println("请输入[代号]");
                in = scanner.next();
                goddess.setName(in);
                System.out.println("请输入[英文名]");
                in = scanner.next();
                goddess.setEname(in);
                System.out.println("请输入[星级]");
                in = scanner.next();
                goddess.setStarlevel(Integer.valueOf(in));
                System.out.println("输入[标签]");
                in = scanner.next();
                goddess.setTag(in);
                System.out.println("请输入[职位]");
                in = scanner.next();
                goddess.setPostion(in);
                System.out.println("请输入[技能]");
                in = scanner.next();
                goddess.setSkills(in);
                try {
                    action.add(goddess);
                    System.out.println("新增成功");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    System.out.println("新增失败");
                }
            }

            else if (OPERATION_SEARCH.equals(in.toUpperCase())
                    || OPERATION_SEARCH.substring(0, 1).equals(in.toUpperCase())) {
                List<Map<String, Object>> params = new ArrayList<>();
                Map<String, Object> param;
                System.out.println("添加查询关系, 例[name like '%金'],[starlevel = 6]");
                while (scanner.hasNext()) {
                    param = new HashMap<>();
                    in = scanner.next();
                    param.put("name", in);
                    in = scanner.next();
                    param.put("rela", in);
                    in = scanner.next();
                    param.put("value", in);
                    params.add(param);
                    System.out.println("是否继续添加? 任意键继续/No");
                    in = scanner.next();
                    if (OPERATION_NO.equals(in.toUpperCase())
                            || OPERATION_NO.substring(0, 1).equals(in.toUpperCase())) {
                        break;
                    } else {
                        System.out.println("请继续添加");
                    }
                }
                List<Goddess> res;
                try {
                    res = action.query(params);
                    System.out.println(res.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    System.out.println("查询失败");
                }
            }

            else if (OPERATION_UPDATE.equals(in.toUpperCase())
                    || OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println("请输入[ID]");
                in = scanner.next();
                goddess.setId(Integer.valueOf(in));
                System.out.println("请输入[代号]");
                in = scanner.next();
                goddess.setName(in);
                System.out.println("请输入[英文名]");
                in = scanner.next();
                goddess.setEname(in);
                System.out.println("请输入[星级]");
                in = scanner.next();
                goddess.setStarlevel(Integer.valueOf(in));
                System.out.println("输入[标签]");
                in = scanner.next();
                goddess.setTag(in);
                System.out.println("请输入[职位]");
                in = scanner.next();
                goddess.setPostion(in);
                System.out.println("请输入[技能]");
                in = scanner.next();
                goddess.setSkills(in);
                try {
                    action.update(goddess);
                    System.out.println("更新成功");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    System.out.println("更新失败");
                }
            }

            else if (OPERATION_MAIN.equals(in.toUpperCase())
                    || OPERATION_MAIN.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println(CONTEXT);
            }
        }
    }
}

View Code

posted @ 2020-11-07 14:40 CPJ31415 阅读(132) 评论(0) 收藏举报

刷新页面返回顶部

CPJ31415

JDBC连接数据库

公告