JDBC续集，Druid与DbUtils实现事务

简单实现Druid与DBUtils组合事务

• entity
  • User
  • Book
  • Catagory
• Dao
  • UserDao
  • BookDao
  • CatagoryDao
  • Impl
    • UserDaoImpl
    • BookDaoImpl
    • CatagoryDaoImpl
• Service
  • UserService
  • BookService
  • CatagoryService
  • Impl
    • UserServiceImpl
    • BookServiceImpl
    • CategoryServiceImpl
• utils
  • DruidUtils

DruidUtils

//DruidUtils.java
public class DruidUtils {
    private static DruidDataSource dataSource;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    private static Connection conn;

    static {
        try {
            Properties properties = new Properties();
            InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            is.close();
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            conn = threadLocal.get();
            if (conn == null) {
                conn = dataSource.getConnection();
                threadLocal.set(conn);
            }
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static DataSource getDataSouce() {
        return dataSource;
    }

    public static void startTransation() {
        try {
            Connection connection =  getConnection();
            if(connection!=null){
                connection.setAutoCommit(false);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void commit() {
        try {
            Connection connection =  threadLocal.get();
            if (connection != null) {
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void rollback() {
        try {
            Connection connection =  threadLocal.get();
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //事务关闭
    public static void  close(){
        try {
            Connection connection =  threadLocal.get();
            if(connection!=null){
                if (!connection.getAutoCommit()) {
                	threadLocal.remove();
                    connection.close();
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //普通关闭
    public static void closeConnection(Connection connection) {
        try {
            if (connection != null) {
                if (connection.getAutoCommit()) {
                    threadLocal.remove();
                    connection.close();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Entity

//book.
/**
 * CREATE TABLE `book` (
 *   `id` int(11) NOT NULL AUTO_INCREMENT,
 *   `title` varchar(50) DEFAULT NULL,
 *   `author` varchar(20) DEFAULT NULL,
 *   `publicDate` date DEFAULT NULL,
 *   `publisher` varchar(50) DEFAULT NULL,
 *   `isbn` varchar(15) DEFAULT NULL,
 *   `price` decimal(8,2) DEFAULT NULL,
 *   `picture` varchar(50) DEFAULT NULL,
 *   `cid` int(11) DEFAULT NULL,
 *   PRIMARY KEY (`id`),
 *   KEY `book_cid` (`cid`),
 *   CONSTRAINT `book_cid` FOREIGN KEY (`cid`) REFERENCES `Category` (`cid`)
 * ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
 */

//Category
/**
 * CREATE TABLE `Category` (
 *   `cid` int(11) NOT NULL AUTO_INCREMENT,
 *   `cname` varchar(30) NOT NULL,
 *   PRIMARY KEY (`cid`)
 * ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
 */

//User

/**
 * CREATE TABLE `User` (
 *   `id` int(11) NOT NULL AUTO_INCREMENT,
 *   `username` varchar(20) NOT NULL,
 *   `password` varchar(32) NOT NULL,
 *   `realname` varchar(20) NOT NULL,
 *   `email` varchar(50) NOT NULL,
 *   `gender` varchar(50) NOT NULL,
 *   `flag` int(11) DEFAULT NULL,
 *   `role` int(11) DEFAULT NULL,
 *   PRIMARY KEY (`id`)
 * ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
 */

Dao

//BookDao.java
public interface BookDao {
    List<Book> findAll();

    Book findById(Integer id);

    List<Book> findByTitle(String title);

    void deleteById(Integer id);

    void insert(Book book);

    void update(Book book);
}

//BookDaoImpl.java
public class BookDaoImpl implements BookDao {
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSouce());
    QueryRunner qr1 = new QueryRunner();

    @Override
    public List<Book> findAll() {
        try {
            String sql = "select * from book";
            List<Book> bookList = qr.query(sql, new BeanListHandler<Book>(Book.class));
            if (bookList != null) {
                return bookList;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Book findById(Integer id) {
        try {
            String sql = "select * from book where id = ?";
            Book book = qr.query(sql, new BeanHandler<Book>(Book.class), id);
            if (book != null) {
                return book;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Book> findByTitle(String title) {
        try {
            String sql = "select * from book where title like ?";
            List<Book> bookList = qr.query(sql, new BeanListHandler<Book>(Book.class), "%" + title + "%");
            if (bookList != null) {
                return bookList;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void deleteById(Integer id) {
        try {
            String sql = "delete from book where id= ?";
            qr.update(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insert(Book book) {
        Connection connection = null;
        try {
            connection = DruidUtils.getConnection();
            String sql = "insert into book (title,author,publicDate,publisher,isbn,price,picture,cid)" +
                    "values(?,?,?,?,?,?,?,?)";
            Object[] params = {book.getTitle(), book.getAuthor(), book.getPublicDate(),
                    book.getPublisher(), book.getIsbn(), book.getPrice(), book.getPicture(), book.getCid()};
            qr.update(connection, sql, params);
            System.out.println(connection.hashCode());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println(connection.hashCode());
            DruidUtils.closeConnection(connection);
        }
    }

    @Override
    public void update(Book book) {
        try {
            String sql = "update book set title=?,author=?,publicDate=?,publisher=?,isbn=?,price=?,picture=?,cid=? where id=?";
            Object[] params = {book.getTitle(), book.getAuthor(), book.getPublicDate(),
                    book.getPublisher(), book.getIsbn(), book.getPrice(), book.getPicture(), book.getCid(), book.getId()};
            qr.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

//UserDao.java
public interface UserDao {
    Boolean login(String username,String password);
}

//UserDaoImpl.java
public class UserDaoImpl implements UserDao {
    private QueryRunner qr= new QueryRunner(DruidUtils.getDataSouce());
    @Override
    public Boolean login(String username, String password) {
        try {
            String sql="select * from User where username = ? and password=?";
            Object []params={username,password};
            User user = qr.query(sql, new BeanHandler<User>(User.class),params);
            if(user!=null){
                return true;
            }
        } catch (SQLException e) {
            System.out.println("账号输入错误");
        }
        return false;
    }
}

// CategoryDao.java
public interface CategoryDao {
    Category findByTitle(String title);

    void insert(Category category);
}

//CategoryDaoImpl.java
public class CategoryDaoImpl implements CategoryDao {
    QueryRunner qr2 = new QueryRunner();

    @Override
    public Category findByTitle(String title) {
        Connection connection = null;
        try {
            String sql = "select * from category where cname = ?";
            connection = DruidUtils.getConnection();
            Category category = qr2.query(connection, sql, new BeanHandler<Category>(Category.class), title);
            if (category != null) {
                return category;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                System.out.println(connection.getAutoCommit());
                System.out.println(connection.hashCode());
                DruidUtils.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void insert(Category category) {
        Connection connection = null;
        try {
            connection = DruidUtils.getConnection();
            String sql = "insert into category (cname)values(?)";
            qr2.update(connection, sql, category.getCanme());

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                System.out.println(connection.getAutoCommit());
                System.out.println(connection.hashCode());
                DruidUtils.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Service

//BookService.java
public interface BookService {
    List<Book>findAll();
    Book findById(Integer id);
    List<Book> findByTitle(String title);
    void deleteById(Integer id);
    void insert(Book book);
    void update(Book book);
}

//BookServiceImpl.java
public class BookServiceImpl implements BookService {
    private static BookDaoImpl bookDao = new BookDaoImpl();

    @Override
    public List<Book> findAll() {
        List<Book> bookList = bookDao.findAll();
        if (bookList == null) {
            return null;
        }
        return bookList;
    }

    @Override
    public Book findById(Integer id) {
        Book book = bookDao.findById(id);
        if(book!=null){
            return book;
        }
        return null;
    }

    @Override
    public List<Book> findByTitle(String title) {
        List<Book> bookList = bookDao.findByTitle(title);
        if(bookList!=null){
            return bookList;
        }
        return null;
    }


    @Override
    public void deleteById(Integer id) {
        bookDao.deleteById(id);
        System.out.println("删除成功");
    }

    @Override
    public void insert(Book book) {
        bookDao.insert(book);
        System.out.println("添加成功");
    }

    @Override
    public void update(Book book) {
        bookDao.update(book);
    }
}

//CategoryService.java
public interface CategoryService {
    Category findByTitle(String title);
    void insert(Category category);
}

//CategoryServiceImpl.java
public class CategoryServiceImpl implements CategoryService {
    private CategoryDaoImpl categoryDao=new CategoryDaoImpl();
    @Override
    public Category findByTitle(String title) {
       return categoryDao.findByTitle(title);

    }

    @Override
    public void insert(Category category) {
        categoryDao.insert(category);
    }
}

//UserService.java
public interface UserService {
    Boolean login(String username,String password);

}

//UserServiceImpl.java
public class UserServiceImpl implements UserService {
    private UserDaoImpl userDao = new UserDaoImpl();

    public Boolean login(String username, String password) {
        Boolean flag = userDao.login(username, password);
        return flag;
    }
}

View

// BookShopSystem.java
public class BookShopSystem {
    public static void main(String[] args) throws ParseException {
        BookServiceImpl bookService = new BookServiceImpl();
        UserServiceImpl userService = new UserServiceImpl();
        CategoryServiceImpl categoryService = new CategoryServiceImpl();
        Scanner input = new Scanner(System.in);
        System.out.println("welcome to bookshop");
        System.out.println("please input your username");
        String username = input.next();
        System.out.println("please input your password");
        String password = input.next();
        Boolean flag = userService.login(username, password);
        lable:


        if (flag) {
            System.out.println("登陆成功");
            while (true) {
                System.out.println("1.查询所有数据 2.根据id查询书籍 3.根据书名查询数据");
                System.out.println("4.删除书籍(id) 5.更新书籍 6.添加书籍 7添加书籍类型，并添加书籍 8.离开");
                System.out.println("please input your options");
                Integer options = input.nextInt();
                switch (options) {
                    case 1:
                        List<Book> bookList = bookService.findAll();
                        for (Book book : bookList) {
                            System.out.println(book.toString());
                        }
                        break;
                    case 2:
                        System.out.println("please input id");
                        Integer id = input.nextInt();
                        Book book = bookService.findById(id);
                        System.out.println(book.toString());
                        break;
                    case 3:
                        System.out.println("please input the name of book");
                        String title = input.next();
                        List<Book> bookList1 = bookService.findByTitle(title);
                        for (Book book5 : bookList1) {
                            System.out.println(book5.toString());
                        }
                        break;
                    case 4:
                        System.out.println("please input the id of book");
                        Integer id1 = input.nextInt();
                        bookService.deleteById(id1);
                        break;
                    case 5:
                        System.out.println("please input the id of the book");
                        Integer id2 = input.nextInt();
                        System.out.println("please input the title of the book");
                        input.nextLine();
                        String title2 = input.nextLine();
                        System.out.println("please input the author of the book");
                        String author2 = input.next();
                        System.out.println("please input the publicDate of the book");
                        String publicDate2 = input.next();
                        System.out.println("please input the publisher of the book");
                        String publisher2 = input.next();
                        System.out.println("please input the isbn of the book");
                        String isbn2 = input.next();
                        System.out.println("please input the price of the book");
                        BigDecimal price2 = input.nextBigDecimal();
                        System.out.println("please input the picture of the book");
                        String picture2 = input.next();
                        System.out.println("please input the cid of the book");
                        Integer cid2 = input.nextInt();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = sdf.parse(publicDate2);
                        Book book2 = new Book(id2, title2, author2, date, publisher2, isbn2, price2, picture2, cid2);
                        bookService.update(book2);
                        System.out.println("更新成功");
                        break;
                    case 6:

                        System.out.println("please input the title of the book");
                        input.nextLine();
                        String title3 = input.nextLine();
                        System.out.println("please input the author of the book");
                        String author3 = input.next();
                        System.out.println("please input the publicDate of the book");
                        String publicDate3 = input.next();
                        System.out.println("please input the publisher of the book");
                        String publisher3 = input.next();
                        System.out.println("please input the isbn of the book");
                        String isbn3 = input.next();
                        System.out.println("please input the price of the book");
                        BigDecimal price3 = input.nextBigDecimal();
                        System.out.println("please input the picture of the book");
                        String picture3 = input.next();
                        System.out.println("please input the cid of the book");
                        Integer cid3 = input.nextInt();
                        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
                        Date date1 = sdf1.parse(publicDate3);
                        Book book3 = new Book(null, title3, author3, date1, publisher3, isbn3, price3, picture3, cid3);
                        bookService.insert(book3);
                        System.out.println("插入成功");
                        break;
                    case 7:
                        try {

                            DruidUtils.startTransation();
                            System.out.println("please input cname");
                            String cname = input.next();
                            Category category=new Category(null,cname);
                            categoryService.insert(category);
                            Category category1 = categoryService.findByTitle(cname);


                            System.out.println("please input the title of the book");
                            input.nextLine();
                            String title4 = input.nextLine();
                            System.out.println("please input the author of the book");
                            String author4 = input.next();
                            System.out.println("please input the publicDate of the book");
                            String publicDate4 = input.next();
                            System.out.println("please input the publisher of the book");
                            String publisher4 = input.next();
                            System.out.println("please input the isbn of the book");
                            String isbn4 = input.next();
                            System.out.println("please input the price of the book");
                            BigDecimal price4 = input.nextBigDecimal();
                            System.out.println("please input the picture of the book");
                            String picture4 = input.next();
                            SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
                            Date date2 = sdf2.parse(publicDate4);
                            Book book4 = new Book(0, title4, author4, date2, publisher4, isbn4, price4, picture4, category1.getCid());
                            bookService.insert(book4);
                            System.out.println("插入成功");
                            DruidUtils.commit();
                        } catch (Exception e) {
                            System.out.println("失败了");
                            e.printStackTrace();
                            DruidUtils.rollback();
                        } finally {
                            DruidUtils.close();
                        }
                        break;
                    case 8:
                        break lable;
                    default:
                        System.out.println("输入错误");
                        break;
                }
            }
        } else {
            System.out.println("登陆失败");
        }


    }
}