培训记录-3

JDBC的进阶学习

使用MVC的架构

MAC框架

在src文件夹下新建bean、dao、util、service四个文件夹

util

package util;

import java.sql.*;

public class DBUtil {
    private static String qudong = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost/ningda?user=root&password=123456";

    public static Connection getConnection()  throws Exception{
        Class.forName(qudong);
        Connection connection = DriverManager.getConnection(url);
        return connection;
    }

    public static void closeAll(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

工具类用静态方法，在DAO方法中可以直接调用 getConnection() 和 closeAll() 方法。

bean

package baen;

public class TbUser {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}
    @Override
    public String toString() {
        return "baen.TbUser{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

新建用户类，设置get/set方法。

DAO

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

    public void add(String usename, String password) throws Exception {
        String sql = "insert into tb_user(username,password) value('" + usename + "','" + password + "')";
        try {
            preparedStatement = (PreparedStatement) connection.createStatement();
            preparedStatement.executeUpdate(sql);
            DBUtil.closeAll(null, preparedStatement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, preparedStatement, connection);
        }
    }

    public void del(int id) {
        String sql = "delete from tb_user where id='" + id + "'";
        try {
            connection = DBUtil.getConnection();
            preparedStatement = (PreparedStatement) connection.createStatement();
            preparedStatement.executeUpdate(sql);
            DBUtil.closeAll(null, preparedStatement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, preparedStatement, connection);
        }
    }

    public List<TbUser> findAll() {


        List<TbUser> users = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from tb_user";
            preparedStatement = connection.prepareStatement(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);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, preparedStatement, connection);
        }
        return users;
    }

    public void update(int id, String name, String passwore) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;

        String sql = "update tb_user set PASSWORD='" + passwore + "',username='" + name + "' where id='" + id + "'";
        try {
            preparedStatement = (PreparedStatement) conn.createStatement();
            preparedStatement.executeUpdate(sql);
            DBUtil.closeAll(null, preparedStatement, conn);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, preparedStatement, conn);
        }
    }

    public boolean doLogin(String username,String password)  {
        String sql = "select * from tb_user where username=? and password=?";
        try {
            connection=DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                return true;
            }else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(resultSet,preparedStatement,connection);
        }
       return false;
    }
}

实现了增删查改的方法。

Service

public class UserService {
    public static void main(String arg[]) {
        Scanner input = new Scanner(System.in);
        System.out.println("欢迎登陆，请输入用户名");
        String username = input.next();
        System.out.println("请输入密码");
        String password = input.next();

        TbUserDao tbUserDao = new TbUserDao();
        boolean flag = tbUserDao.doLogin(username,password);
        if (flag){
            System.out.println("欢迎来到用户管理系统");
            System.out.println("1.Xx");
        }else{
            System.out.println("登入失败，请先注册");
        }
    }
}