JDBC操作数据库3

JavaBean和DAO模式

贯穿案例-管理员信息管理系统

使用DAO模式封装JDBC操作

重构管理员信息表的增删改查操作

1、db.properties配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///news_week1?characterEncoding=utf-8
user=root
pwd=root

2、BaseDao工具类封装连接方法

package com.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class BaseDao {

    private static String url = null;
    private static String user = null;
    private static String pwd = null;

    static {
        init();
    }

    //加载配置文件，并加载驱动
    private static void init() {
        Properties p = new Properties();
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");

        try {
            p.load(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        String driver = p.getProperty("driver");
        url = p.getProperty("url");
        user = p.getProperty("user");
        pwd = p.getProperty("pwd");
        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }//封装创建连接方法
    public static Connection getConn(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,user,pwd);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return  conn;
    }public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        try {
            if (null != rs)
                rs.close();
            if (null != ps)
                ps.close();
            if (null != conn)
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

3、创建admin实体类封装

package com.bean;
//实体类
public class Admin {
    private Integer adminId;
    private String adminName;
    private String adminpwd;

    public Admin() {
    }

    public Admin(Integer adminId, String adminName, String adminpwd) {
        this.adminId = adminId;
        this.adminName = adminName;
        this.adminpwd = adminpwd;
    }

    public Integer getAdminId() {
        return adminId;
    }

    public void setAdminId(Integer adminId) {
        this.adminId = adminId;
    }

    public String getAdminName() {
        return adminName;
    }

    public void setAdminName(String adminName) {
        this.adminName = adminName;
    }

    public String getAdminpwd() {
        return adminpwd;
    }

    public void setAdminpwd(String adminpwd) {
        this.adminpwd = adminpwd;
    }

    @Override
    public String toString() {
        return "Admin{" +
                "adminId=" + adminId +
                ", adminName='" + adminName + '\'' +
                ", adminpwd='" + adminpwd + '\'' +
                '}';
    }
}

4、接口IAdminDao  创建方法

package com.dao;

import com.bean.Admin;

import java.util.ArrayList;

//接口
public interface IAdminDao {
    //1、查询全部内容
    public ArrayList<Admin> getAll();
    //2、根据编号查询
    public Admin getById(int adminId);
    //3、添加
    public int insert(Admin admin);
    //4、修改
    public int update(Admin admin);
    //5、删除
    public int delete(int adminId);
}

5、IAdminDao   实现类AdminDaoImpl   实现它的方法

package com.dao.impl;

import com.bean.Admin;
import com.dao.IAdminDao;
import com.utils.BaseDao;

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

public class AdminDaoImpl implements IAdminDao {
    @Override
    public ArrayList<Admin> getAll() {
        Connection conn = BaseDao.getConn();
        ArrayList<Admin> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement("select  * from admin");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Admin admin = new Admin(rs.getInt(1),rs.getString(2),rs.getString(3));
                list.add(admin);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public Admin getById(int adminId) {
        Connection conn = BaseDao.getConn();
        Admin admin = null;
        try {
            PreparedStatement ps = conn.prepareStatement("select  * from admin where admin_id = ?");
            ps.setObject(1, adminId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                admin = new Admin(rs.getInt(1), rs.getString(2), rs.getNString(3));


            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return admin;
    }

    @Override
    public int insert(Admin admin) {
        Connection conn = BaseDao.getConn();
        int i = 0;
        try {
            PreparedStatement ps = conn.prepareStatement("insert into admin values (null,?,?)");
            ps.setObject(1, admin.getAdminName());
            ps.setObject(2, admin.getAdminpwd());
            i = ps.executeUpdate();


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return i;
    }

    @Override
    public int update(Admin admin) {
        Connection conn = BaseDao.getConn();
        int i = 0;
        try {
            PreparedStatement ps = conn.prepareStatement("update admin set admin_name = ?,admin_pwd = ? where admin_id = ?");
            ps.setObject(1, admin.getAdminName());
            ps.setObject(2, admin.getAdminpwd());
            ps.setObject(3, admin.getAdminId());
            i = ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return i;
    }

    @Override
    public int delete(int adminId) {
        Connection conn = BaseDao.getConn();
        int i = 0;
        try {
            PreparedStatement ps = conn.prepareStatement("delete from admin where admin_id = ?");
            ps.setObject(1, adminId);

            i = ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return i;
    }
}

6、测试类

package com.test;

import com.bean.Admin;
import com.dao.IAdminDao;
import com.dao.impl.AdminDaoImpl;
import com.utils.BaseDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

public class Test {
    public static void main(String[] args) throws SQLException {
        menu();
    }

    private static void menu() {
        IAdminDao dao = new AdminDaoImpl();

        boolean flag = false;
        do {
            System.out.println("管理员信息管理系统");
            System.out.println("1.查询 2.根据用户编号查询 3.添加 4.修改 5.删除 0.退出");
            System.out.println("请输入菜单：");
            Scanner sc = new Scanner(System.in);
            int i = sc.nextInt();
            switch (i) {
                case 1:
                    //你要查询表里面所有信息
                    ArrayList<Admin> list = dao.getAll();
                    for (Admin admin : list) {
                        System.out.println(admin);
                    }
                    break;
                case 3:
                    Admin admin = new Admin();
                    Scanner a = new Scanner(System.in);
                    System.out.println("请输入需要添加的用户名：");
                    String name = a.next();
                    System.out.println("请输入需要添加的密码：");
                    String pwd = a.next();
                    admin.setAdminName(name);
                    admin.setAdminpwd(pwd);
                    int j = dao.insert(admin);
                    if (j > 0) {
                        System.out.println("添加成功！");
                    }else {
                        System.out.println("添加失败！");
                    }
                    break;
                case 4:
                    Admin admin1 = new Admin();
                    Scanner a1 = new Scanner(System.in);
                    System.out.println("请输入需要修改的用户名：");
                    String name1 = a1.next();
                    System.out.println("请输入需要修改的密码：");
                    String pwd1 = a1.next();
                    admin1.setAdminName(name1);
                    admin1.setAdminpwd(pwd1);
                    int j1 = dao.update(admin1);
                    if (j1 > 0){
                        System.out.println("修改成功！");
                    }else {
                        System.out.println("修改失败！");
                    }
                    break;

                case 0:
                    flag = false;
                default:
                    break;
            }


        } while (flag);
    }
}