Java（九）数据库JDBC：4.JDBC的写法二：全封装JDBCUtil+Dao

---

一、设计思想

• Util工具：JDBCUtil
  将Connection、Statement、PreparedStatement、ResultSet都封装好，JDBCUtil类直接传入String sql，返回查询结果ResultSet或者执行更新操作。
• Model模型：Reader
  对应具体实例中的对象
• Dao(Data Access Object)数据访问对象：LibrarianDao
  将需要Reader实例操作的sql语句封装成一个函数，如可以返回一个Reader对象
• View视图：ReaderView
  一个GUI组件对应事件需要就创建一个ReaderDao，用完关闭readerDao.closeJDBC();，再用再创，不要将ReaderDao写成成员变量。
  比如这里有个点击按钮的事件，为了简单说明我就写在main()中

二、示例

1.JDBCUtil

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {

	// mysql驱动
    private final String JDBC_DRIVER_8UP = "com.mysql.cj.jdbc.Driver";
    // 名为ordermanagement的数据库名url
    private final String DB_URL_8UP = "jdbc:mysql://localhost:3306/ordermanagement?useSSL=false&serverTimezone=UTC&&allowPublicKeyRetrieval=true";
    // 用户名
    private final String user="root";
    // 密码
    private final String password="1234";
    
    private Connection con = null;
    private Statement stmt = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    
    // 链接数据库
    public JDBCUtil() {
		try {
			Class.forName(JDBC_DRIVER_8UP);
			this.con = DriverManager.getConnection(DB_URL_8UP, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
    
    // 关闭用到的Connection、Statement、PreparedStatement、ResultSet
    public void closeAll() throws Exception {
        if(con!=null) {
        	this.con.close();
		}
		if(stmt!=null) {
			this.stmt.close();
		}
		if(pstmt!=null) {
			this.pstmt.close();
		}
		if(rs!=null) {
			this.rs.close();
		}
    }
    
    // Statement的executeUpdate()封装
    public void executeUpdate(String sql) throws Exception{
        this.stmt = con.createStatement();
        this.stmt.executeUpdate(sql);
    }
    
    // Statement的executeQuery()封装
    public ResultSet executeQuery(String sql) throws Exception{
        this.stmt = con.createStatement();
        this.rs = this.stmt.executeQuery(sql);
        return this.rs;
    }
    
    // PreparedStatement的executeUpdate()封装
    public void executeUpdate(String sql, Object[] param) throws Exception{
        this.pstmt = con.prepareStatement(sql);
        if (param != null) {
            for (int i = 0; i < param.length; i++) {
                this.pstmt.setObject(i + 1, param[i]);
            }
        }
        this.pstmt.executeUpdate();
    }
    
    // PreparedStatement的executeQuery()封装
	public ResultSet executeQuery(String sql, Object[] param) throws Exception{
        this.pstmt = con.prepareStatement(sql);
        if (param != null) {
            for (int i = 0; i < param.length; i++) {
                this.pstmt.setObject(i + 1, param[i]);
            }
        }
        this.rs = this.pstmt.executeQuery();
        return this.rs;
    }
}

2.Reader

package Model;

public class Reader {
	// 账号
	private int account;

	// 密码
	private String password;

	// 用户名
	private String name;

	// 邮箱
	private String email;

	public int getAccount() {
		return account;
	}

	public void setAccount(int account) {
		this.account = account;
	}

	public String getPassword() {
		return password;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
}

3.LibrarianDao（注册、获得、编辑、删除等）

package DAO;

import java.sql.ResultSet;
import Model.Librarian;
import Model.Reader;
import Utils.JDBCUtil;

public class LibrarianDAO {

	private JDBCUtil jdbcUtil = new JDBCUtil();

	/**
	 * 将调用jdbcUtil.closeAll()封装成一个实例函数，调用LibrarianDao实例就可
	 * 这样在LibrarianView中就不用引入JDBCUtil类
	 */
	public void closeJDBC() throws Exception {
		jdbcUtil.closeAll();
	}

	/**
	 * 注册一个读者
	 * 
	 * @param reader 传入Reader
	 * @return true表示注册成功，false表示此读者账户已存在
	 */
	public boolean registerReader(Reader reader) throws Exception {
		String sql = "select * from	librarian where account = ?";
		Object[] param = new Object[1];
		param[0] = reader.getAccount();
		ResultSet rs = jdbcUtil.executeQuery(sql, param);
		// 已存在
		if (rs.next()) {
			System.out.println("register: reader already exists.");
			return false;
		}
		// 不存在
		else {
			String sql_insert = "INSERT INTO `mandarin`.`reader` (`account`, `password`,`name`,`email`) VALUES (?,?,?,?);";
			Object[] param_insert = new Object[4];
			param_insert[0] = reader.getAccount();
			param_insert[1] = reader.getPassword();
			param_insert[2] = reader.getName();
			param_insert[3] = reader.getEmail();
			jdbcUtil.executeUpdate(sql_insert, param_insert);
			return true;
		}
	}

	/**
	 * 获得读者的信息
	 * 
	 * @param account 读者账号
	 * 
	 * Reader先为null，这样如果成功获取了数据返回创建好的Reader 失败了就返回null。
	 */
	public Reader getReader(int account) throws Exception {
		Reader reader = null;

		String sql = "select * from	reader where account = ?";
		Object[] param = new Object[1];
		param[0] = account;
		ResultSet rs = jdbcUtil.executeQuery(sql, param);
		if (rs.next()) {
			reader = new Reader();
			reader.setAccount(rs.getInt("account"));
			reader.setPassword(rs.getString("password"));
			reader.setName(rs.getString("name"));
			reader.setEmail(rs.getString("email"));
		}
		return reader;
	}

	/**
	 * 编辑读者信息
	 * 
	 * @param 传入一个Reader
	 */
	public void editReader(Reader reader) throws Exception {
		String sql = "UPDATE `mandarin`.`reader` SET `password` = ?, `name` = ?, `email` = ? WHERE (`account` = ?);";
		Object[] param = new Object[4];
		param[0] = reader.getPassword();
		param[1] = reader.getName();
		param[2] = reader.getEmail();
		param[3] = reader.getAccount();
		jdbcUtil.executeUpdate(sql, param);
	}

	/**
	 * 删除读者
	 * 
	 * @param account 读者账号
	 */
	public void deleteReader(int account) throws Exception {
		String sql = "DELETE from `mandarin`.`reader`	where account = ?;";
		Object[] param = new Object[1];
		param[0] = account;
		jdbcUtil.executeUpdate(sql, param);
	}
}

4.LibrarianView

package View;

import DAO.LibrarianDAO;
import Model.Reader;

public class LibrarianView {
	
	public LibrarianDAO librarianDAO = new LibrarianDAO();
	
	// 注册读者
	public void registerReader() {
		Reader reader = new Reader();
		reader.setAccount(8848);
		reader.setPassword("123456");
		reader.setName("Alpha");
		reader.setEmail("1433223@qq.com");
		
		try {
			boolean flag = librarianDAO.registerReader(reader);
			System.out.println("[register]: " + flag);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	// 获得读者信息
	public void getReader() {
		int account = 8848;
		Reader reader = null;
		try {
			reader = librarianDAO.getReader(account);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(reader != null) {
			System.out.println("[get all right]");
			System.out.println(reader.getAccount());
			System.out.println(reader.getPassword());
			System.out.println(reader.getName());
			System.out.println(reader.getEmail());
		}
	}
	
	// 编辑读者
	public void editReader() {
		Reader reader = new Reader();
		reader.setAccount(8848);
		reader.setPassword("123456780");
		reader.setName("Beta");
		reader.setEmail("2143321@qq.com");
		
		try {
			librarianDAO.editReader(reader);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	// 删除读者
	public void deleteReader() {
		int account = 8848;
		try {
			librarianDAO.deleteReader(account);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		LibrarianView lv = new LibrarianView();
		lv.registerReader();
		lv.getReader();
//		lv.editReader();
//		lv.deleteReader();
	}
}