目录
三、Dao模式
1、Dao模式概念
前面我们在使用JDBC时解决的都是一些很简单的问题,例如简单的增、删、改、查等等,所以直接把代码写在了main方法中。
这种写法很容易出现代码冗余、耦合度高、不能模块化开发等等诸多弊端,特别是将来我们做大型实战项目时该怎么办呢?DAO模式就可以解决这个问题。
DAO(Data Access Object)即:数据存取对象,它是位于业务逻辑和底层数据库之间,专门使用JDBC实现数据持久化的一种“套路”,通常称之为DAO模式
使用DAO模式进行项目开发主要有以下两个好处:
①隔离了业务逻辑代码和数据访问代码,分工明确,降低耦合性,提高可重用性。
②采用面向接口编程,提高了项目的可扩展性和可维护性。
2、Dao模式的组成
一个典型的DAO模式主要有以下几部分组成:
①一个BaseDao类,主要负责操作数据库
②DAO接口,定义业务方法(一般情况下 一个表对应一个接口)
③DAO实现类,实现DAO接口,完成具体功能
④实体类,储存和传递数据(一个表对应一个实体类)
⑤测试类
3、具体代码
3.1 BaseDao
public class BaseDao {
public static final String URL="jdbc:mysql://localhost:3306/test_zzz_test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
public static final String USER = "用户名";
public static final String PASS = "密码";
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConcation(){
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USER, PASS);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return connection;
}
public static void setParam(PreparedStatement preparedStatement,Object... obj){
if (obj!=null){
for (int i = 0; i < obj.length; i++) {
try {
preparedStatement.setObject(i+1,obj[i]);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
public static void closeAll(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
System.out.println("关闭了resultSet");
}
if (preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
System.out.println("关闭了preparedStatement");
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
System.out.println("关闭了connection");
}
public static int addOrUpdate(String sql,Object... obj){
int i = -1;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection=getConcation();
preparedStatement = connection.prepareStatement(sql);
setParam(preparedStatement,obj);
i = preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}
closeAll(null,preparedStatement,connection);
return i;
}
public static List<Map<String,Object>> query(String sql,Object... obj){
List<Map<String,Object>> mapList = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConcation();
preparedStatement = connection.prepareStatement(sql);
setParam(preparedStatement,obj);
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while(resultSet.next()){
Map<String,Object> map = new HashMap<>();
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i+1);
map.put(columnLabel,resultSet.getObject(i+1));
}
mapList.add(map);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} closeAll(resultSet,preparedStatement,connection);
return mapList;
}
}
3.2 Dao接口
Dao模式为了提高代码的可扩展性和可维护性引入了接口作为系统的骨架
public interface IStudentDao {
int addStudent(Student student);
int updateStudent(Student student);
int deleteStudent(Student student);
List<Map<String,Object>> selectStudent(Student student);
}
3.3 Dao接口的实现
public class StudentDaoImpl extends BaseDao implements IStudentDao {
@Override
public int addStudent(Student student) {
String sql = "insert into student(sid,sname) values(?,?)";
int i = addOrUpdate(sql, student.getSid(), student.getSname());
return i;
}
@Override
public int updateStudent(Student student) {
String sql = "update student set sname=? where sid = ?";
int i = addOrUpdate(sql, student.getSname(), student.getSid());
return i;
}
@Override
public int deleteStudent(Student student) {
String sql = "delete from student where sid=?";
int i = addOrUpdate(sql, student.getSid());
return i;
}
@Override
public List<Map<String, Object>> selectStudent(Student student) {
String sql = "select * from student where sid=? and sname=?";
List<Map<String, Object>> query = query(sql, student.getSid(), student.getSname());
return query;
}
}
3.4 实体类
public class Student {
private String sid;
private String sname;
public Student(String sid, String sname) {
this.sid = sid;
this.sname = sname;
}
public Student() {
}
public String getSid() {
return sid;
}
public void setSid(String sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
}
3.5 测试类
public static void main(String[] args) {
//添加操作
// Student student = new Student("91","学生添加");
// IStudentDao iStudentDao = new StudentDaoImpl();
// int i = iStudentDao.addStudent(student);
// System.out.println("此次添加操作中受影响的行数是:"+i);
//修改操作
// Student student = new Student("857","学生修改");
// IStudentDao iStudentDao = new StudentDaoImpl();
// int i = iStudentDao.updateStudent(student);
// System.out.println("此次修改操作中受影响的行数是:"+i);
//删除操作
// Student student = new Student("857","学生删除");
// IStudentDao iStudentDao = new StudentDaoImpl();
// int i = iStudentDao.deleteStudent(student);
// System.out.println("此次删除操作中受影响的行数是:"+i);
//查询操作
//实例化学生对象
Student student = new Student("2","学生查询");
//
IStudentDao iStudentDao = new StudentDaoImpl();
List<Map<String, Object>> mapList = iStudentDao.selectStudent(student);
System.out.println("此次查询结果是:"+mapList);
}
案例:图书管理系统
所使用的的配置文件
jdbc.url=jdbc:mysql://localhost:3306/test_zzz_test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
jdbc.user=用户名
jdbc.pass=密码
jdbc.driver=com.mysql.cj.jdbc.Driver
BaseDao
public class BaseDao {
public static final String URL;
public static final String USER;
public static final String PASS;
static{
String url="";
String user="";
String pass="";
Properties properties = new Properties();
InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
properties.load(inputStream);
String driver = properties.getProperty("jdbc.driver");
url = properties.getProperty("jdbc.url");
user = properties.getProperty("jdbc.user");
pass = properties.getProperty("jdbc.pass");
Class.forName(driver);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
URL=url;
USER=user;
PASS=pass;
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(URL,USER,PASS);
return connection;
}
public static void setparam(PreparedStatement preparedStatement,Object... obj) throws SQLException {
if (obj!=null){
for (int i = 0; i < obj.length; i++) {
preparedStatement.setObject(i+1,obj[i]);
}
}
}
public static void closeAll(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) throws SQLException {
if (preparedStatement!=null){
preparedStatement.close();
}
if (resultSet!=null){
resultSet.close();
}
if (connection!=null){
connection.close();
}
}
public static int addf(String sql,Object... obj) throws SQLException {
int i =-1;
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
setparam(preparedStatement,obj);
i = preparedStatement.executeUpdate();
return i;
}
public static List<Map<String,Object>> query(String sql, Object... obj) throws SQLException {
List<Map<String,Object>> mapList = new ArrayList<>();
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
setparam(preparedStatement,obj);
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()){
Map<String,Object> map = new HashMap<>();
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i+1);
map.put(columnLabel,resultSet.getObject(i+1));
}
mapList.add(map);
}
closeAll(resultSet,preparedStatement,connection);
return mapList;
}
}
Dao接口
public interface IBookDao {
int addBook(Book book);
int updateBook(Book book);
int deleteBook(Book book);
List<Map<String,Object>> queryBook(Book book);
List<Map<String, Object>> queryAll();
}
Dao接口的实现
public class BookDaoImpl extends BaseDao implements IBookDao {
@Override
public int addBook(Book book) {
String sql = "insert into book(bookname,price,author,date) values(?,?,?,?)";
int i = -1;
try {
i = addf(sql, book.getBookname(), book.getPrice(), book.getAuthor(), book.getDate());
} catch (SQLException e) {
throw new RuntimeException(e);
}
return i;
}
@Override
public int updateBook(Book book) {
int i = 0;
String sql = "update book set bookname=? , price=?, author=?, date=? where id=?";
try {
i = addf(sql, book.getBookname(), book.getPrice(), book.getAuthor() ,book.getDate(),book.getId());
} catch (SQLException e) {
throw new RuntimeException(e);
}
return i;
}
@Override
public int deleteBook(Book book) {
String sql = "delete from book where id=? ";
int addf = 0;
try {
addf = addf(sql, book.getId());
} catch (SQLException e) {
throw new RuntimeException(e);
}
return addf;
}
@Override
public List<Map<String, Object>> queryBook(Book book) {
String sql = "select * from book where id=? ";
List<Map<String, Object>> query = null;
try {
query = query(sql, book.getId());
} catch (SQLException e) {
throw new RuntimeException(e);
}
return query;
}
@Override
public List<Map<String, Object>> queryAll() {
String sql = "select * from book";
try {
return query(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
实体类
public class Book {
private Integer id;
private String bookname;
private Double price;
private String author;
private Date date;
public Book(String bookname, Double price, String author, Date date) {
this.bookname = bookname;
this.price = price;
this.author = author;
this.date = date;
}
public Book() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBookname() {
return bookname;
}
public void setBookname(String bookname) {
this.bookname = bookname;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
测试类
public static void main(String[] args) throws ParseException {
while (true) {
//操作管理
System.out.println("----------欢迎来到新华书店AAA分店-------------");
System.out.println("1.添加图书到购物车");
System.out.println("2.从购物车删除图书");
System.out.println("3.修改想要购买图书");
System.out.println("4.查询图书馆的图书信息");
Scanner zsgc = new Scanner(System.in);
//操作选择
switch (zsgc.nextInt()) {
case 1:
//添加操作
Book bookj = setBook();
IBookDao iBookDaoj = new BookDaoImpl();
int i = iBookDaoj.addBook(bookj);
if (i>0){
System.out.println("成功添加了" + i + "本图书到购物车,请选择接下来的操作");
}
break;
case 2:
//删除图书
System.out.println("选择你要删除的图书id");
Book books = new Book();
Scanner scans = new Scanner(System.in);
books.setId(scans.nextInt());
IBookDao iBookDaos = new BookDaoImpl();
int s = iBookDaos.deleteBook(books);
System.out.println("成功删除了"+s+"本图书,请选择接下来的操作");
break;
case 3:
//修改图书
Book bookc = new Book();
System.out.println("请输入要查询的图书id");
Scanner scannerg = new Scanner(System.in);
bookc.setId(scannerg.nextInt());
IBookDao iBookDaoc = new BookDaoImpl();
List<Map<String, Object>> mapList = iBookDaoc.queryBook(bookc);
if (mapList.size() == 0) {
System.out.println("该图书id不存在");
} else {
System.out.println("当前的图书信息为" + "\n" + mapList);
System.out.println("开始修改这本图书");
Book bookg = setBook();
bookg.setId(bookc.getId());
IBookDao iBookDao1 = new BookDaoImpl();
int g = iBookDao1.updateBook(bookg);
if (g>0){
System.out.println("成功修改了" + g + "条记录");
List<Map<String, Object>> mapList1 = iBookDaoc.queryBook(bookg);
System.out.println("修改后的图书信息为:"+"\n"+mapList1);
}
}
break;
case 4:
//查询图书
Book book6 = new Book();
System.out.println("请输入要查询的图书id");
Scanner scanner6 = new Scanner(System.in);
book6.setId(scanner6.nextInt());
IBookDao iBookDao1 = new BookDaoImpl();
List<Map<String, Object>> mapList1 = iBookDao1.queryBook(book6);
if (mapList1.size() == 0) {
System.out.println("该图书id不存在");
} else {
System.out.println("当前的图书信息为" + "\n" + mapList1+"\n"+"请选择接下来的操作");
}
default:
System.out.println("*******目前图书信息如下,请重新输入你的选择*******");
IBookDao iBookDao = new BookDaoImpl();
List<Map<String, Object>> mapList2 = iBookDao.queryAll();
System.out.println("id\t图书名字\t图书价格\t图书作者\t出版日期");
for (int j = 0; j < mapList2.size(); j++) {
Map<String, Object> map = mapList2.get(j);
System.out.println(map.get("id")+"\t"+map.get("bookname")+"\t"+map.get("price")+"\t"+map.get("author")+"\t"+map.get("date"));
}
}
}
}
//封装图书信息赋值操作
public static Book setBook(){
Book bookj = new Book();
Scanner scannerj = new Scanner(System.in);
System.out.print("请输入图书名字(bookname):");
bookj.setBookname(scannerj.next());
System.out.print("请输入图书价格(price):");
bookj.setPrice(scannerj.nextDouble());
System.out.print("请输入图书作者(author):");
bookj.setAuthor(scannerj.next());
System.out.print("请输入图书出版日期(date):");
SimpleDateFormat simpleDateFormatj = new SimpleDateFormat("yyyy-MM-dd");
Date parse1 = null;
try {
parse1 = simpleDateFormatj.parse(scannerj.next());
} catch (ParseException e) {
throw new RuntimeException(e);
}
bookj.setDate(parse1);
return bookj;
}