jdbcproject001

今天在公司在昨天的基础上，更进一步的系统的连接用数据库里的数据，对其实现增删查改功能。比较难的是要在项目下，分门别类的建包，建类，还有理清楚他们之间的联系，为了方便高效还建立工具类，就是有点难理解。发现之前模糊的点比如只会用视图建表，今天用语句就卡壳，还是不能似是而非，要稳扎稳打。

 

 package dao.impl;

import bean.Student;
import dao.StudentDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import static tools.DBTools.*;
//数据访问层
public class StudentDaoImpl implements StudentDao {
    @Override
    public int insert(Student info) {
        String sql = "insert into student values(?,?,?,?)";
        return executeUpdate(sql, info.getId(), info.getName(), info.getPwd(), info.getClassid());
    }

    @Override
    public int delete(int id) {
        String sql = "delete from student where id=?";
        return executeUpdate(sql, id);
    }

    @Override
    public int update(Student info) {
        String sql = "update student set name=?,pwd=?,classid=? where id=?";
        return executeUpdate(sql, info.getName(), info.getPwd(), info.getClassid(), info.getId());
    }

    @Override
    public List<Student> selectAll() {
        List<Student> list = new ArrayList<>();
        Connection connection = getConn();
        try {
            String sql = "select * from student";
            PreparedStatement pps = connection.prepareStatement(sql);

            ResultSet rs = pps.executeQuery();
            while (rs.next()) {
                Student info = new Student();
                info.setId(rs.getInt("id"));
                info.setName(rs.getString("name"));
                info.setPwd(rs.getString("pwd"));
                info.setClassid(rs.getInt("classid"));

                list.add(info);
            }
            rs.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

 

package manager;

import bean.Student;
import dao.StudentDao;
import dao.impl.StudentDaoImpl;

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

public class StudentManager {
    Scanner input=new Scanner(System.in);
    StudentDao dao=new StudentDaoImpl();
    public void main() {
        for (int i = 0; i < 5; i++) {
            System.out.println("1.add");
            System.out.println("2.update");
            System.out.println("3.delect");
            System.out.println("4.select");

            int menu = getInt("请选择");
            switch (menu) {
                case 1:
                    addStudent();
                    break;
                case 2:
                    updateStudent();
                    break;
                case 3:
                    deleteStudent();
                    break;
                case 4:
                    selectStudentAll();
                    break;

            }
        }
    }

    private void deleteStudent() {
        dao.selectAll();
        int id=getInt("请输入删除的学号");
        int i = dao.delete(id);
        if (i>0)
            System.out.println("ok");
        else
            System.out.println("delete error");
        dao.selectAll();
    }

    private void selectStudentAll() {
        List<Student> list = dao.selectAll();
        for (Student info:list){
            System.out.println(info);
        }
    }

    private void updateStudent() {
        dao.selectAll();
        int id=getInt("请输入要修改的学生编号");
        Student info=new Student();
        info.setId(id);
        info.setName(getString("请重新输入姓名："));
        info.setPwd(getString("请重新输入密码："));
        info.setClassid(getInt("请重新输入classid："));
        int i = dao.update(info);

        if (i>0)
            System.out.println("ok");
        else
            System.out.println("update error");
        dao.selectAll();
    }

    private void addStudent() {
        Student info=new Student();
        info.setId(getInt("请输入编号:"));
        info.setName(getString("请输入姓名:"));
        info.setPwd(getString("请输入密码:"));
        info.setClassid(getInt("请输入班级ID:"));

        int i = dao.insert(info);
        if (i>0)
            System.out.println("ok");
        else
            System.out.println("insert error");
    }
    public String getString(String msg){
        System.out.print(msg);
        return input.next();
    }

    public int getInt(String msg){
        System.out.print(msg);
        return input.nextInt();
    }
}