JDBC一个项目的包的写法

连接数据库的公共类

首先有一个连接数据库的公共类，一般放在util包里

以下是代码：

import java.sql.*;

public class DBUtil {
    public final static String qudong = "com.mysql.jdbc.Driver";
    public final static String url = "jdbc:mysql://localhost/ningda?user=root&password=root";
    public static Connection getConnection(String qudong,String url) throws Exception {
        //1.加载驱动
        Class.forName(qudong);
        //2.创建连接
        Connection connection = DriverManager.getConnection(url);
        return connection;
    }
    //关闭连接
    public static void closeAll(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection) throws Exception{
        if (connection!=null){
            connection.close();
        }
        if (resultSet!=null){
            resultSet.close();
        }
        if (preparedStatement!=null){
            preparedStatement.close();
        }
    }
}

实体类

有一个实体类，有自己的属性、get()、set()方法，放在Bean包下面

public class Student {
    private int id;
    private String name;
    private String password;
    private String sex;
    private int age;
    private String class1;
    private String dept;
    private String phone;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", dept='" + dept + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }

    public String getClass1() {
        return class1;
    }

    public void setClass1(String class1) {
        this.class1 = class1;
    }

    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 String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

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

    public String getDept() {
        return dept;
    }

    public void setDept(String dept) {
        this.dept = dept;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

Dao类，写一些增删改查的方法

import Bean.TbUser;
import util.DBUtil;

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

public class TbUserDao {
    static ResultSet resultSet = null;
    static Connection connection = null;
    static PreparedStatement preparedStatement = null;

    static {
        try {
            connection = DBUtil.getConnection(DBUtil.qudong, DBUtil.url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //添加
    public static void add(String username, String password) throws Exception {
        //2.创建连接
        String sql = "insert into tb_user(username,password) values(?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.executeUpdate();
        //7.关闭资源
        DBUtil.closeAll(resultSet, preparedStatement, connection);
    }

    //查询所有
    public static List<TbUser> findAll() throws Exception {
        List<TbUser> users = new ArrayList<>();
        //1.加载驱动
        //2.创建连接
        //3.写sql
        String sql = "select * from tb_user";
        //4.得到statement对象
        preparedStatement = connection.prepareStatement(sql);
        //5.执行sql
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            TbUser user = new TbUser();
            user.setId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            users.add(user);
        }
        //7.关闭资源
        DBUtil.closeAll(resultSet, preparedStatement, connection);
        return users;
    }

    //删除
    public static void delete(int id) throws Exception {
        //1.加载驱动
        //2.创建连接
        //3.写sql
        String sql = "delete from tb_user where id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
        //7.关闭资源
        DBUtil.closeAll(resultSet, preparedStatement, connection);
    }

    //修改
    public static void modify(int id,String username, String password) throws Exception {
        //1.加载驱动
        //2.创建连接
        //3.写sql
        String sql = "update tb_user set username=?,password=? where id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.setInt(3, id);
        preparedStatement.executeUpdate();
        //7.关闭资源
        DBUtil.closeAll(resultSet, preparedStatement, connection);
    }

    //登录
    public boolean doLogin(String username, String password) throws Exception {
        String sql = "select * from tb_user where username=? and password=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        resultSet = preparedStatement.executeQuery();
        return resultSet.next();
    }
}

Service包

Service包里装的都是用户界面之类的，调用Dao类的方法

import dao.TbUserDao;

import java.util.Scanner;

public class TbUserService {
    public static void main(String[] args) throws Exception {
        Scanner input = new Scanner(System.in);
        System.out.println("--------------欢迎登入音乐系统--------------");
        System.out.println("请输入用户名：");
        String username = input.next();
        System.out.println("请输入密码：");
        String password = input.next();
        TbUserDao userDao = new TbUserDao();
        boolean flag = userDao.doLogin(username,password);
        if(flag){
            System.out.println("--------------欢迎来到用户管理系统--------------");
            System.out.println("1.音乐管理 2.用户管理 3.退出");
            System.out.println();
        }else{
            System.out.println("--------------登入失败，请先注册--------------");
            System.out.println("请输入注册的用户名：");
            System.out.println("请输入注册的密码：");
        }
    }
}