JDBC 女神管理器(控制台)-原创手记-慕课网

JDBC 女神管理器

说明:JDBC调用存储过程，进行增、删、查、改。

存储过程

insert_procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_procedure`(in name1 varchar(10),in age1 tinyint unsigned,in brithday1 varchar(10),in address1 varchar(20),in mobile1 varchar(20))
BEGIN
insert into goddess (name,age,brithday,address,mobile) values (name1,age1,brithday1,address1,mobile1);
END

delete_procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_procedure`(in id1 smallint)
BEGIN
DELETE FROM goddess WHERE id = id1;
END

view_base_procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_base_procedure`()
BEGIN
select id,name,age,mobile from goddess;
END

view_procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_procedure`(IN  type varchar(10) , IN parm varchar(20))
BEGIN
if(type is null or type = '') then
select * from goddess;
else
if(type = 'id') then
select * from goddess where id = parm;
else
if(type = 'name') then
select * from goddess where name like concat('%',parm,'%');
else 
if(type = 'age') then
select * from goddess where age = cast(parm as unsigned);
else
if(type = 'address') then
select * from goddess where address like concat('%',parm,'%');
end if;
end if;
end if;
end if;
end if;
END

update_all_procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_all_procedure`(in id1 smallint,in name1 varchar(10),in age1 tinyint unsigned,in brithday1 varchar(10),in address1 varchar(20),in mobile1 varchar(20))
BEGIN
UPDATE goddess SET name = name1 , age = age1 , brithday = brithday1 , address = address1 ,mobile = mobile1 WHERE id = id1;
END

update_procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_procedure`(in id1 smallint,in type varchar(10),in parm varchar(20))
BEGIN
if(type = 'name') then
update  goddess set name = parm where id = id1;
else
if(type = 'age') then
update  goddess set age = cast(parm as unsigned) where id = id1;
else
if(type = 'brithday') then
update  goddess set brithday = parm where id = id1;
else
if(type = 'address') then
update  goddess set address = parm where id = id1;
else
if(type = 'mobile') then
update  goddess set mobile = parm where id = id1;
end if;
end if;
end if;
end if;
end if;
END

Java代码

Goddess.java

package com.xk.model;

public class Goddess {
    private int id;
    private String name;
    private int age;
    private String brithday;
    private String address;
    private String mobile;

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getBrithday() {
        return brithday;
    }

    public void setBrithday(String brithday) {
        this.brithday = brithday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

}

DButil.java

package com.xk.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DButil {
    private final static String URL = "jdbc:mysql://localhost:3306/goddess_manage";
    private final static String USER = "root";
    private final static String PWD = "root";

    /*
     * 获取数据库连接
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PWD);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

GoddessDao.java

package com.xk.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.xk.db.DButil;
import com.xk.model.Goddess;

public class GoddessDao {
    DButil dbu = new DButil();
    Connection con = null;

    /*
     * 添加信息
     */
    public boolean insert(Goddess goddess) throws SQLException {
        con = dbu.getConnection();
        String sql = "call insert_procedure(?,?,?,?,?)";
        CallableStatement cs = con.prepareCall(sql);
        cs.setString(1, goddess.getName());
        cs.setInt(2, goddess.getAge());
        cs.setString(3, goddess.getBrithday());
        cs.setString(4, goddess.getAddress());
        cs.setString(5, goddess.getMobile());
        if (cs.executeUpdate() > 0) {
            close();
            return true;
        }
        return false;
    }

    /*
     * 修改所有信息
     */
    public boolean updateAll(Goddess goddess) throws SQLException {
        con = dbu.getConnection();
        String sql = "call update_all_procedure(?,?,?,?,?,?)";
        CallableStatement cs = con.prepareCall(sql);
        cs.setInt(1, goddess.getId());
        cs.setString(2, goddess.getName());
        cs.setInt(3, goddess.getAge());
        cs.setString(4, goddess.getBrithday());
        cs.setString(5, goddess.getAddress());
        cs.setString(6, goddess.getMobile());
        if (cs.executeUpdate() > 0) {
            close();
            return true;
        }
        close();
        return false;
    }

    /*
     * 修改某条信息
     */
    public boolean update(int id, String type, String parm) throws SQLException {
        con = dbu.getConnection();
        String sql = "call update_procedure(?,?,?)";
        CallableStatement cs = con.prepareCall(sql);
        cs.setInt(1, id);
        cs.setString(2, type);
        cs.setString(3, parm);
        if (cs.executeUpdate() > 0) {
            close();
            return true;
        }
        close();
        return false;
    }

    /*
     * 查看基本信息
     */
    public List<Goddess> queryBase() throws SQLException {
        List<Goddess> goddessList = new ArrayList<>();
        con = dbu.getConnection();
        String sql = "call view_base_procedure()";
        CallableStatement cs = con.prepareCall(sql);
        cs.execute();
        ResultSet rs = cs.getResultSet();
        Goddess g = null;
        while (rs.next()) {
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setName(rs.getString("name"));
            g.setAge(rs.getInt("age"));
            g.setMobile(rs.getString("mobile"));
            goddessList.add(g);
        }
        close();
        return goddessList;
    }

    /*
     * 查找某条件的详细信息
     */
    public List<Goddess> query(String type, String parm) throws SQLException {
        List<Goddess> goddessList = new ArrayList<>();
        con = dbu.getConnection();
        String sql = "call view_procedure(?,?)";
        CallableStatement cs = con.prepareCall(sql);
        cs.setString(1, type);
        cs.setString(2, parm);
        cs.execute();
        ResultSet rs = cs.getResultSet();
        Goddess g = null;
        while (rs.next()) {
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setName(rs.getString("name"));
            g.setAge(rs.getInt("age"));
            g.setBrithday(rs.getString("brithday"));
            g.setAddress(rs.getString("address"));
            g.setMobile(rs.getString("mobile"));
            goddessList.add(g);
        }
        close();
        return goddessList;
    }

    /*
     * 删除信息
     */
    public boolean delete(int id) throws SQLException {
        con = dbu.getConnection();
        String sql = "call delete_procedure(?)";
        CallableStatement cs = con.prepareCall(sql);
        cs.setInt(1, id);
        if (cs.executeUpdate() > 0) {
            close();
            return true;
        }
        close();
        return false;
    }

    /*
     * 关闭数据库连接
     */
    public void close() {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

GoddessManage.java

package com.xk.view;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import com.xk.dao.GoddessDao;
import com.xk.model.Goddess;

public class GoddessManage {
    GoddessDao gd = new GoddessDao();
    List<Goddess> gList = null;
    Scanner input = new Scanner(System.in);

    /*
     * 添加女神信息
     */
    public void insert(Goddess g) throws Exception {
        if (gd.insert(g)) {
            System.out.println("添加女神信息成功,返回主菜单");
            System.out.println("****************************");
        } else {
            System.out.println("添加女神信息失败,返回主菜单!");
            System.out.println("****************************");
        }
    }

    /*
     * 查看女神基本信息
     */
    public void viewBase() throws Exception {
        gList = new ArrayList<>();
        gList = gd.queryBase();
        System.out.println("************************************");
        System.out.println("编号\t姓名\t年龄\t手机号");
        for (Goddess goddess : gList) {
            System.out.println(goddess.getId() + "\t" + goddess.getName() + "\t" + goddess.getAge() + "\t"
                    + goddess.getMobile() + "\t");
        }
        System.out.println("************************************");
    }

    /*
     * 查看某一女神的详细信息
     */
    public void query(String type, String parm) throws Exception {
        gList = new ArrayList<>();
        gList = gd.query(type, parm);
        System.out.println("***********************************************************");
        System.out.println("编号\t姓名\t年龄\t出生日期\t\t居住地\t手机号");
        for (Goddess goddess : gList) {
            System.out.println(goddess.getId() + "\t" + goddess.getName() + "\t" + goddess.getAge() + "\t"
                    + goddess.getBrithday() + "\t" + goddess.getAddress() + "\t" + goddess.getMobile() + "\t");
        }
        System.out.println("***********************************************************");
    }

    /*
     * 更新某一女神的全部信息
     */
    public void updateAll(Goddess g) throws Exception {
        if (gd.updateAll(g)) {
            System.out.println("修改女神信息成功,返回主菜单!");
            System.out.println("****************************");
        } else {
            System.out.println("修改女神信息失败,返回主菜单!!");
            System.out.println("****************************");
        }
    }

    /*
     * 更新某一女神的某一信息
     */
    public void update(int id, String type, String parm) throws Exception {
        if (gd.update(id, type, parm)) {
            System.out.println("修改女神信息成功,返回主菜单!");
            System.out.println("****************************");
        } else {
            System.out.println("修改女神信息失败,返回主菜单!");
            System.out.println("****************************");
        }
    }

    /*
     * 删除某一女神的信息
     */
    public void delete(int id) throws Exception {
        if (gd.delete(id)) {
            System.out.println("删除女神信息成功,返回主菜单!");
            System.out.println("****************************");
        } else {
            System.out.println("删除女神信息失败,请重新输入!");
            System.out.println("****************************");
        }
    }

    /*
     * 查找女神界面
     */
    public void queryMain() throws Exception {
        String type;
        String parm;
        while (true) {
            System.out.println("*********女神详细信息查询器*********");
            System.out.println("1.编号\n2.姓名\n3.年龄\n4.居住地\n5.返回主界面");
            System.out.println("请输入要查询女神详细信息的条件:");
            String in = input.next();
            switch (in) {
            case "1":
                type = "id";
                System.out.println("请输入要查询女神的编号:");
                parm = input.next();
                query(type, parm);
                break;
            case "2":
                type = "name";
                System.out.println("请输入要查询女神的姓名:");
                parm = input.next();
                query(type, parm);
                break;
            case "3":
                type = "age";
                System.out.println("请输入要查询女神的年龄:");
                parm = input.next();
                query(type, parm);
                break;
            case "4":
                type = "address";
                System.out.println("请输入要查询女神的居住地:");
                parm = input.next();
                query(type, parm);
                break;
            case "5":
                System.out.println("****************************");
                break;
            default:
                System.out.println("指令输入错误请重新输入!");
                continue;
            }
            showMain();
        }

    }

    /*
     * 更新女神界面
     */
    public void updateMain() throws Exception {
        String type;
        String parm;
        int id;
        while (true) {
            System.out.println("*********女神信息更新器*********");
            System.out.print("请输入要更新女神的编号:");
            id = input.nextInt();
            System.out.println("1.姓名\n2.年龄\n3.出生日期\n4.居住地\n5.手机号\n6.返回主界面");
            System.out.println("请输入要更新女神的字段:");
            String in = input.next();
            switch (in) {
            case "1":
                type = "name";
                System.out.println("请输入更新后女神的姓名:");
                parm = input.next();
                update(id, type, parm);
                break;
            case "2":
                type = "age";
                System.out.println("请输入更新后女神的年龄:");
                parm = input.next();
                update(id, type, parm);
                break;
            case "3":
                type = "brithday";
                System.out.println("请输入更新后女神出生日期(例:1995-04-06):");
                parm = input.next();
                update(id, type, parm);
                break;
            case "4":
                type = "address";
                System.out.println("请输入更新后女神的居住地:");
                parm = input.next();
                update(id, type, parm);
                break;
            case "5":
                type = "mobile";
                System.out.println("请输入要更新女神的手机号:");
                parm = input.next();
                update(id, type, parm);
                break;
            case "6":
                System.out.println("****************************");
                break;
            default:
                System.out.println("指令输入错误请重新输入!");
                continue;
            }
            showMain();
        }

    }

    /*
     * 用户界面
     */
    public void showMain() throws Exception {
        while (true) {
            System.out.println("1.查看所有女神基本信息\n2.查询某位女神详细信息\n3.添加女神信息\n4.更新某位女神所有信息\n5.更新某位女神某一信息\n6.删除某位女神信息\n7.退出管理器");
            System.out.println("请输入指令:");
            String in = input.next();
            switch (in) {
            case "1":
                viewBase();
                break;
            case "2":
                queryMain();
                break;
            case "3":
                System.out.println("****************************");
                Goddess g = new Goddess();
                System.out.println("请输入女神名字:");
                g.setName(input.next());
                System.out.println("请输入女神年龄:");
                g.setAge(input.nextInt());
                System.out.println("请输入女神出生日期(例:1995-04-06):");
                g.setBrithday(input.next());
                System.out.println("请输入女神居住地:");
                g.setAddress(input.next());
                System.out.println("请输入女神手机号:");
                g.setMobile(input.next());
                insert(g);
                break;
            case "4":
                System.out.println("****************************");
                Goddess g1 = new Goddess();
                System.out.println("请输入要更新女神编号:");
                g1.setId(input.nextInt());
                System.out.println("请输入更新后女神名字:");
                g1.setName(input.next());
                System.out.println("请输入更新后女神年龄:");
                g1.setAge(input.nextInt());
                System.out.println("请输入更新后女神出生日期(例:1995-04-06):");
                g1.setBrithday(input.next());
                System.out.println("请输入更新后女神居住地:");
                g1.setAddress(input.next());
                System.out.println("请输入更新后女神手机号:");
                g1.setMobile(input.next());
                updateAll(g1);
                break;
            case "5":
                updateMain();
                break;
            case "6":
                System.out.println("****************************");
                System.out.println("请输入要删除女神的编号:");
                int id = input.nextInt();
                delete(id);
                break;
            case "7":
                System.out.println("欢迎下次使用,bye!");
                input.close();
                System.exit(0);
            default:
                System.out.println("错误指令请重新输入!");
                continue;
            }
        }
    }

    public static void main(String[] args) {
        System.out.println("*******欢迎来到女神管理器*******");
        GoddessManage gm = new GoddessManage();
        try {
            gm.showMain();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

运行效果

图片描述