一、创建数据库中的表
1、需求
有一张表叫javabook【创建表要求使用sql语句进行】
表中列 bookid 整数自增类型
表中列 bprice 小数类型
表中列 bookname 字符串类型 长度不能小于50工程和包要求:
domain
dao
service
test
2、实现
create table javabook(bookid int primary key auto_increment,bprice decimal(8,2),bookname varchar(50) not null
);
3、项目结构
二、Idea连接数据库
1、创建文件目录
2、导入文件Jar包
3、连接数据库
package dao;import java.sql.Connection;
import java.sql.DriverManager;/*** @program: Pay.java* @ClassName BaseDao* @description:* @author: 苏芮溪* @create: 2024−11-11 20:10* @Version 1.0**/public class BaseDao {public static final String DRIVERCLASSNAME = "com.mysql.cj.jdbc.Driver";public static final String URL = "jdbc:mysql://localhost:3306/ruixi?sslMode=REQUIRED&characterEncoding=UTF-8&connectionTimeZone=GMT%2B8&forceConnectionTimeZoneToSession=true";public static final String USER = "root";public static final String PASS = "admin";/*** 获取数据库连接*/public Connection getConnection() {//创建连接引用Connection conn = null;try {//装载驱动Class.forName(DRIVERCLASSNAME);//连接数据库conn = DriverManager.getConnection(URL, USER, PASS);//输出连接System.out.println(conn);} catch (Exception e) {e.printStackTrace();}return conn;}
}
三、编写Java代码
1、entity层的实体
package entity;/*** @program: Pay.java* @ClassName Book* @description:* @author: 苏芮溪* @create: 2024−11-12 18:40* @Version 1.0**/public class Book {private int bid;private String bname;private double price;public Book(String bname, double price) {this.bname = bname;this.price = price;}public void setBid(int bid) {this.bid = bid;}public int getBid() {return bid;}public String getBname() {return bname;}public double getPrice() {return price;}@Overridepublic String toString() {return "Book{" +"bid=" + bid +", bname='" + bname + '\'' +", price=" + price +'}';}
}
2、Dao层
2.1、Dao层连接
package dao;import java.sql.*;/*** @program: Pay.java* @ClassName BaseDao* @description:* @author: 苏芮溪* @create: 2024−11-11 20:10* @Version 1.0**/public class BaseDao {public static final String DRIVERCLASSNAME = "com.mysql.cj.jdbc.Driver";public static final String URL = "jdbc:mysql://localhost:3306/ruixi?sslMode=REQUIRED&characterEncoding=UTF-8&connectionTimeZone=GMT%2B8&forceConnectionTimeZoneToSession=true";public static final String USER = "root";public static final String PASS = "admin";/*** 获取数据库连接*/public Connection getConnection() {Connection conn = null;try {//装载驱动.3Class.forName(DRIVERCLASSNAME);//连接数据库conn = DriverManager.getConnection(URL, USER, PASS);System.out.println(conn);} catch (Exception e) {e.printStackTrace();}return conn;}void closedAll(Connection conn, PreparedStatement ps, ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (ps != null) {try {ps.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}
2.2、Dao层接口
package dao;
import entity.Book;import java.util.List;/*** @program: Pay.java* @ClassName BookDao* @description:* @author: 苏芮溪* @create: 2024−11-12 18:35* @Version 1.0**/public interface BookDao {//添加书void addBook(Book book);//更新书void updateBook(Book book);//根据书名删除书void deleteAllBooks(String bname);//模糊查询书List<Book> findBooks(String bname);
}
2.3、Dao层impl实现
package dao.impl;import dao.BaseDao;
import dao.BookDao;
import entity.Book;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;/*** @program: Pay.java* @ClassName BookDaoImpl* @description:* @author: 苏芮溪* @create: 2024−11-12 18:40* @Version 1.0**/public class BookDaoImpl extends BaseDao implements BookDao {@Overridepublic void addBook(Book book) {//创建连接引用Connection conn = null;//创建操作引用PreparedStatement ps = null;//SQL操作语句String sql = "insert into javabook(bprice,bookname) values(?,?)";try {//进行数据库连接(调用BaseDao中的方法)conn = getConnection();//创建操作对象ps = conn.prepareStatement(sql);//处理sql操作参数ps.setDouble(1, book.getPrice());ps.setString(2, book.getBname());//执行 插入修改删除ps.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}finally {closedAll(conn, ps, null);}}@Overridepublic void updateBook(Book book) {//创建连接引用Connection conn = null;//创建操作引用PreparedStatement ps = null;//SQL操作语句String sql = "update javabook set bprice=?,bookname=? where bookid=?";try {//进行数据库连接(调用BaseDao中的方法)conn = getConnection();//创建操作对象ps = conn.prepareStatement(sql);//处理sql操作参数ps.setDouble(1, book.getPrice());ps.setString(2, book.getBname());ps.setInt(3,book.getBid());//执行 插入修改删除ps.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}finally {closedAll(conn, ps, null);}}@Overridepublic void deleteAllBooks(String bname) {
//创建连接引用Connection conn = null;//创建操作引用PreparedStatement ps = null;//SQL操作语句String sql = "delete from javabook where bookname=?";try {//进行数据库连接(调用BaseDao中的方法)conn = getConnection();//创建操作对象ps = conn.prepareStatement(sql);//处理sql操作参数ps.setString(1, bname);//执行 插入修改删除ps.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}finally {closedAll(conn, ps, null);}}@Overridepublic List<Book> findBooks(String bname) {//存储数据List<Book> books = new ArrayList<Book>();//创建连接引用Connection conn = null;//创建操作引用PreparedStatement ps = null;//SQL操作语句String sql = "select * from javabook where bname like ?";try {//进行数据库连接(调用BaseDao中的方法)conn = getConnection();//创建操作对象ps = conn.prepareStatement(sql);//处理sql操作参数ps.setString(1, "%"+bname+"%");//执行 插入修改删除ResultSet resultSet = ps.executeQuery();while (resultSet.next()) {Book book = new Book(resultSet.getString("bname"),resultSet.getDouble("bprice"));book.setBid(resultSet.getInt("id"));books.add(book);}} catch (SQLException e) {throw new RuntimeException(e);}finally {closedAll(conn, ps, null);}return books;}}
3、Service层
3.1、接口
package service;import entity.Book;import java.util.List;/*** @program: Pay.java* @ClassName BookService* @description:* @author: 苏芮溪* @create: 2024−11-12 19:20* @Version 1.0**/public interface BookService {//添加书void addBook(Book book);//更新书void updateBook(Book book);//根据书名删除书void deleteAllBooks(String bname);//模糊查询书List<Book> findBooks(String bname);
}
3.2、实现
package service.impl;import dao.BookDao;
import dao.impl.BookDaoImpl;
import entity.Book;
import service.BookService;import java.util.List;/*** @program: Pay.java* @ClassName BookServiceImpl* @description:* @author: 苏芮溪* @create: 2024−11-12 19:22* @Version 1.0**/public class BookServiceImpl implements BookService {//面向接口编程private BookDao bookDao;public BookServiceImpl() {bookDao = new BookDaoImpl();}@Overridepublic void addBook(Book book) {bookDao.addBook(book);}@Overridepublic void updateBook(Book book) {bookDao.updateBook(book);}@Overridepublic void deleteAllBooks(String bname) {bookDao.deleteAllBooks(bname);}@Overridepublic List<Book> findBooks(String bname) {return bookDao.findBooks(bname);}
}
4、测试类
package test;import entity.Book;
import service.BookService;
import service.impl.BookServiceImpl;import java.util.List;/*** @program: Pay.java* @ClassName Test* @description:* @author: 苏芮溪* @create: 2024−11-12 19:24* @Version 1.0**/public class Test {public static void main(String[] args) {Book book1 = new Book("java入门",12.5);Book book2 = new Book("java初级",25);Book book3 = new Book("java高级",50);//插入三本书BookService bs = new BookServiceImpl();bs.addBook(book1);bs.addBook(book2);bs.addBook(book3);//用汇编覆盖掉java初级Book book4 = new Book("汇编",22.5);book4.setBid(2);bs.updateBook(book4);//通过书名删除String bname = "java入门";bs.deleteAllBooks(bname);//模糊查询书名带有”java“的书List<Book> books = bs.findBooks("java");for (Book book : books) {System.out.println(book);}}
}