JDBC之MVC模式 示例

package com.j1702.model;
//定义类
public class Student {

    private Integer id;
    private String name;
    private Integer age;
    private String address;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

package com.j1702.db;

import java.sql.*;

public class DBUtil {//创建工具类

    private static final String URL="jdbc:mysql://127.0.0.1:3306/jdbc_test";
    private static final String USER="root";
    private static final String PASSWORD="123456";
    //实例化一个链接对象conn
    private static Connection conn=null; 
    
    static{//静态方法块
        try {
            //1， 加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2， 获得数据库链接对象 conn
            conn=DriverManager.getConnection(URL, USER, PASSWORD);//直接获得conn实例对象     或者用下面这种
            //setConn(DriverManager.getConnection(URL, USER, PASSWORD));//通过set方法获得conn实例对象
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConn() {
        return conn;
    }
    public static void setConn(Connection conn) {
        DBUtil.conn = conn;
    }
}

 

package com.j1702.dao;

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

import com.j1702.db.DBUtil;
import com.j1702.model.Student;

public class StudentDao {
    
    //1、增
    public static void add(Student stu) throws Exception{
        //通过工具类，获取数据库链接对象
        Connection conn=DBUtil.getConn();
        //创建 sql 语句(?占坑)
        String sql="insert Student (name,age,address) values(?,?,?)";
        //创建欲加载的sql语句执行对象
        PreparedStatement ptmt=conn.prepareStatement(sql);
        //给名字赋值
        ptmt.setString(1,stu.getName());
        //给年龄赋值
        ptmt.setInt(2, stu.getAge());
        //给地址赋值
        ptmt.setString(3, stu.getAddress());
        //执行sql语句
        ptmt.execute();
    }
    //2、删
    public static void delete(Integer id) throws Exception{
        //通过工具类，获取数据库链接对象
                Connection conn=DBUtil.getConn();
                //创建 sql 语句(?占坑)
                String sql="delete from Student where id=?";
                //创建欲加载的sql语句执行对象
                PreparedStatement ptmt=conn.prepareStatement(sql);
                //给id赋值
                ptmt.setInt(1,id);
                //执行sql语句
                ptmt.execute();
    }
    //3、改
    public static void update(Student stu,Integer id) throws Exception{
                //通过工具类，获取数据库链接对象
                Connection conn=DBUtil.getConn();
                //创建 sql 语句(?占坑)
                String sql="update Student set name=?,age=?,address=? where id=?";
                //创建欲加载的sql语句执行对象
                PreparedStatement ptmt=conn.prepareStatement(sql);
                //给名字赋值
                ptmt.setString(1,stu.getName());
                //给年龄赋值
                ptmt.setInt(2, stu.getAge());
                //给地址赋值
                ptmt.setString(3, stu.getAddress());
                //给id赋值
                ptmt.setInt(4, id);
                //执行sql语句
                ptmt.execute();
    }
    //4、查所有
    public static void query() throws Exception{
            //通过工具类，获取数据库链接对象
            Connection conn=DBUtil.getConn();
            //创建 sql 语句(?占坑)
            String sql="select * from Student";
            //创建欲加载的sql语句执行对象（在只执行一句sql语句时使用该语句，需要循环执行sql语句时用另一种）
            Statement stmt=conn.createStatement();
            //用结果集ResultSet创建对象来接收sql语句返回的数据集
            ResultSet rs=stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println("id:"+rs.getInt("id")+"\tname:"+rs.getString("name")+
            "\tage:"+rs.getInt("age")+"\taddress:"+rs.getString("address"));
            }
    }
    //5、查某个
    public static void queryWithId(Integer id) throws Exception{
        //通过工具类，获取数据库链接对象
        Connection conn=DBUtil.getConn();
        //创建 sql 语句(?占坑)
        String sql="select * from Student where id=?";
        //创建欲加载的sql语句执行对象
        PreparedStatement ptmt=conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        ResultSet rs=ptmt.executeQuery();
        while (rs.next()) {
            System.out.println("id:"+rs.getInt("id")+"\tname:"+rs.getString("name")+
            "\tage:"+rs.getInt("age")+"\taddress:"+rs.getString("address"));
        }
    }
    //6、条件查询
    public static void queryWithParam(List<Map<String, Object>> param) throws Exception{
        Connection conn=DBUtil.getConn();//得到链接对象
        StringBuffer sql=new StringBuffer();
        sql.append("select * from Student where 1=1");
        for(Map<String, Object>map:param){
            sql.append(" and "+map.get("key")+" = '"+map.get("value") + "'");
        }
        PreparedStatement ptmt=conn.prepareStatement(sql.toString());
        ResultSet rs=ptmt.executeQuery();
        while(rs.next()){
            System.out.println("id:"+rs.getInt("id")+"\tname:"+rs.getString("name")+
            "\tage:"+rs.getInt("age")+"\taddress:"+rs.getString("address"));
        }
    }
    //7、按包含某个字符查询
    public static void queryWithChar(List<Map<String, Object>> param) throws Exception{
        Connection conn=DBUtil.getConn();//得到链接对象
        StringBuffer sql=new StringBuffer();
        sql.append("select * from Student where 1=1");
        for(Map<String, Object>map:param){
            sql.append(" and "+map.get("key")+" like '%"+map.get("value") + "%'");
        }
        PreparedStatement ptmt=conn.prepareStatement(sql.toString());
        ResultSet rs=ptmt.executeQuery();
        while(rs.next()){
            System.out.println("id:"+rs.getInt("id")+"\tname:"+rs.getString("name")+
            "\tage:"+rs.getInt("age")+"\taddress:"+rs.getString("address"));
        }
    }
}

package com.j1702.action;

import java.util.List;
import java.util.Map;

import com.j1702.dao.StudentDao;
import com.j1702.model.Student;
//这一层是Controller 层:是为view层和model层服务的
public class StudentAction {

    //向数据库中插入一条数据
    public static void insert(Student stu) throws Exception{
        StudentDao.add(stu);
    }
    //根据传入的id删除学生
    public static void delete(Integer id) throws Exception{
        StudentDao.delete(id);
    }
    //更新数据
    public static void update(Student stu,Integer id) throws Exception{
        StudentDao.update(stu,id);
    }
    //查找数据
    public static void find(List<Map<String, Object>> param) throws Exception{
        StudentDao.queryWithParam(param);
    }
    //按某个字符查询
    public static void findOfChar(List<Map<String, Object>> param) throws Exception{
        StudentDao.queryWithChar(param);
    }
    //查看详情
    public static void show() throws Exception{
        StudentDao.query();
    }
}

package com.j1702.view;
import java.util.*;
import com.j1702.model.Student;
import com.j1702.action.StudentAction;
//注意：以下例子并不完整，存在许多BUG和不合理之处。
public class TestView {
    public static void main(String[] args) throws Exception {
    
        //编程以终端为view层，实现数据的 增、删、改、查的操作
        /**
         * 请输入你要做的操作：A 添加，D 删除，U 更新，F 查询，exit 退出
         * 
         * 请输入插入数据的name age address
         * 
         * 请输入你要做的操作：A 添加，D 删除，U 更新，F 查询，exit 退出
         * */
        action();
    }
    public static void action() throws Exception{
        while(true){
            System.out.println("A 添加    D 删除    U 更新    F 查询    E 退出\n请选择您想要进行的操作：");
            Scanner scan0=new Scanner(System.in);
            String key0=scan0.next();
            //scan0.close();
            switch (key0) {
            case "A":
                Add();
                break;
            case "D":
                Del();
                break;
            case "U":
                UpDat();
                break;
            case "F":
                Find();
                break;
            case "E":
                return;
            default:
                System.out.println("对不起！没有你输入的选项，请重写输入！");
                break;
            }
        }
    }
    //添加学生
    public static void Add() throws Exception{
        Student stu=creatCode();
        StudentAction.insert(stu);
    }
    //删除学生
    public static void Del() throws Exception{
        System.out.println("请输入你要删除的学生的id号：");
        Scanner scan4=new Scanner(System.in);
        int id=scan4.nextInt();
        StudentAction.delete(id);
        scan4.close();
    }
    //替换学生
    public static void UpDat() throws Exception{
        Student stu=creatCode();//创建Student
        System.out.println("请输入你要修改的学生的id号：");
        Scanner scan5=new Scanner(System.in);
        int id=scan5.nextInt();
        StudentAction.update(stu, id);
        scan5.close();
    }
    //查找学生
    public static void Find() throws Exception{
        System.out.println("该数据库的字段有：id  name  age  address");
        List<Map<String, Object>> li=new ArrayList<Map<String,Object>>();
        a:
        while (true) {
            Map<String, Object> map=mp();
            li.add(map);
            b:
            while (true) {
                System.out.println("请选择：    1，继续输入查询条件     2，开始查询");
                Scanner scan8=new Scanner(System.in);
                int key2=scan8.nextInt();
                switch (key2) {
                case 1:
                    continue a;
                case 2:
                    break a;
                default:
                    System.out.println("对不起没有您输入的选项！请重新选择！");
                    continue b;
                }
            }
        }
        //StudentAction.find(li);//多条件查询
        StudentAction.findOfChar(li);//模糊多条件查询
    }
    //创建map对象
    public static Map<String, Object> mp(){
        Map<String,Object> map=new HashMap<String,Object>();
        System.out.println("请输入您想要查询的字段：");
        Scanner scan7=new Scanner(System.in);
        String key1=scan7.next();
        map.put("key",key1);
        
        System.out.println("请输入您想要查询字段中的值：");
        Scanner scan6=new Scanner(System.in);
        String input=scan6.next();
        if(key1.equals("age") | key1.equals("id")){
            Integer integer=Integer.parseInt(input);
            map.put("value", integer);
        }else if(key1.equals("name") | key1.equals("address")){
            map.put("value", input);
        }
        return map;
    }
    //创建新学生对象
     public static Student creatCode(){
        Student stu=new Student();
        System.out.println("请输入该学生的姓名：");
        Scanner scan1=new Scanner(System.in);
        String name=scan1.next();
        stu.setName(name);
        //scan1.close();
        System.out.println("请输入该学生的年龄：");
        Scanner scan2=new Scanner(System.in);
        int age=scan2.nextInt();
        stu.setAge(age);
        //scan2.close();
        System.out.println("请输入该学生的地址：");
        Scanner scan3=new Scanner(System.in);
        String address=scan3.next();
        stu.setAddress(address);
        //scan3.close();
        return stu;
    }
}