catalogue
1, Understand the idea of layered programming
2, Composition of DAO design pattern and development of each part
1, Understand the idea of layered programming
The idea of stratification can be seen everywhere in daily life. For example, hospitals are stratified according to departments. Each department is completely independent, but it can also communicate completely with other departments. The same is true of the programming layering introduced in this paper.
2, Composition of DAO design pattern and development of each part
2.1 general
DAO (Data Access Object) is mainly used for data operation. It belongs to the operation of data layer in the standard development framework of the program.
2.2 role of Dao mode
- Isolate business logic code and data access code
- Implementation of isolating different databases
2.3 composition of Dao mode
java implements the operation of adding, deleting, changing and querying after database connection. If there is no hierarchical optimization, it is a headache. An error needs to be found for a long time, and there will be many problems. The emergence of DAO mode is to distinguish and manage the code according to specific rules and modes for easy use. The mode of differentiated management is differentiated by different package s.
(1) Database connection and closing tool class: it avoids the repeated use of database connection and closing code and is convenient for modification.
(2) Entity class: used to store and transfer object data
(3) DAO interface: defines all operations on the database as abstract methods, which can provide a variety of implementations
(4) DAO implementation class: specific implementation test of DAO interface definition method for different databases
(5) Display class: in the display layer, display data
(6) Test class: Test addition, deletion, modification and query
According to the above information, if you need to use DAO mode to realize the addition, deletion, modification and query after database link, you need to establish different packages in the creation project to manage the program; When using Dao, the package is strictly named (com. Company name. Project name. Module name) in the following format:
com. Company name. Project name. utils—— Database connection and closing tool class
com. Company name. Project name. entity—— Used to store and transfer object data
com. Company name. Project name. DAO—— DAO interface and DAO implementation class
com. Company name. Project name. ui—— Display class
com. Company name. Project name. test—— test class
3, Adding, deleting, modifying and searching the console version of library information management system based on DAO mode
After opening eclipse, create a new Java project called bookManager. The final project file structure is as follows:
DBHelper: database help class (1. Load driver 2. Establish database connection 3. Close connection)
Book: entity object, used to store and transfer object data.
IBookDao and BookDaoImpl: DAO interface and implementation class, mainly responsible for data addition, deletion, modification and query
3.1 preparation process
-
Database and table building
-
Create a project, create a new folder, name lib, copy sqljdbc 4.jar, and right-click builder path--add
-
Create 5 packages com. Company name. Project name. Package name in the specified project;
-
Toolkit com.zz.bookmanager.utils;
-
Entity package com.zz.bookmanager.entity;
-
Data access layer package com.zz.bookmanager.dao;
-
Display layer package com.zz.bookmanager.ui;
-
Test package com.zz.bookmanager.test;
-
-
Write the com.zz.bookmanager.utils package and create the database help class DBHelper class under the package
-
com.zz.bookmanager.entity package, under which the entity class Book class is created
-
com.zz.bookmanager.dao package. Create BookDAO class under this package to add, delete, modify and query.
-
com.zz.bookmanager.ui package. Create a UI class under this package and call the addition, deletion, modification and query methods of BookDAO class.
-
com.zz.bookmanager.test package to call the methods encapsulated in the UI package.
3.2 code implementation
1. Database building, table building and data adding
--Create library management system
create database db_bookManager_1206;
--Use current database
use db_bookManager_1206;
--Create book information table
create table tb_book
(
bid int primary key identity(1,1),
bname varchar(200),
btype varchar(100),
bprice int,
bauthor varchar(100),
)
insert into tb_book values
('President Kang falls in love with the domineering female president','Campus romance',99,'Tang San'),
('Huang Huihui 2009','Fantasy',99,'Deng Ting'),
('Midnight bell','terror',99,'Liao Jun'),
('Back to 1900','pass through',99,'Lao Wang next door');2. Create a project, create a new folder, name lib, copy sqljdbc 4.jar, and right-click builder path--add
3. Create 5 new packages in the specified project
com.zz.bookManager.utils database Toolkit
com.zz.bookManager.entity package
com.zz.bookManager.dao data access layer package
com.zz.bookManager.ui display layer package
com.zz.bookManager.test test test package
4. Write the com.zz.bookManager.utils package and create the database help class DBHelper under the package
package com.zz.bookManager.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* Database help class
*
* @author zz
*
*/
public class DBHelper {
private static final String URL = "jdbc:sqlserver://localhost:1433;DatabaseName=db_bookManager_1206";
private static final String USER = "sa";
private static final String PASSWORD = "123";
/**
* Load driver
*/
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
;
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Method function: establish connection with database
*/
public static Connection getConn() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* Method function: close connection
*
* @param Connection
* conn Connection object
* @param PreparedStatement
* ps target of execution
* @param ResultSet
* rs Result set object
* @return No return type
*/
public static void myClose(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
if (ps != null) {
ps.close();
}
if (rs != null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
5.com.zz.bookManager.entity package, under which the entity class Book is created
package com.zz.bookManager.entity;
/**
* entity
*
* @author zz
*
*/
public class Book {
private int bid;
private String bname;
private String btype;
private int bprice;
private String bauthor;
public Book() {
// TODO Auto-generated constructor stub
}
public Book(String bname, String btype, int bprice, String bauthor) {
super();
this.bname = bname;
this.btype = btype;
this.bprice = bprice;
this.bauthor = bauthor;
}
public Book(int bid, String bname, String btype, int bprice, String bauthor) {
super();
this.bid = bid;
this.bname = bname;
this.btype = btype;
this.bprice = bprice;
this.bauthor = bauthor;
}
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getBname() {
return bname;
}
public void setBname(String bname) {
this.bname = bname;
}
public String getBtype() {
return btype;
}
public void setBtype(String btype) {
this.btype = btype;
}
public int getBprice() {
return bprice;
}
public void setBprice(int bprice) {
this.bprice = bprice;
}
public String getBauthor() {
return bauthor;
}
public void setBauthor(String bauthor) {
this.bauthor = bauthor;
}
@Override
public String toString() {
return "Book [bid=" + bid + ", bname=" + bname + ", btype=" + btype + ", bprice=" + bprice + ", bauthor="
+ bauthor + "]";
}
}
6.com.zz.bookManager.dao package, under which IBookDao interface and BookDaoImpl class are created to add, delete, modify and query.
IBookDao interface
package com.zz.bookManager.dao;
import java.util.List;
import com.zz.bookManager.entity.Book;
import com.zz.bookManager.utils.DBHelper;
public interface IBookDao {
public int addBook(Book book);
public int deleteBookByBid(int bid);
public int editBookByBid(int bid, Book book);
public List<Book> queryBookAll();
public List<Book> queryBookAll(String strName);
public Book queryBookAll(int bid);
}
BookDaoImpl implementation class:
package com.zz.bookManager.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.zz.bookManager.entity.Book;
import com.zz.bookManager.utils.DBHelper;
/**
* DAO layer class for books (addition, deletion, modification and query)
*/
public class BookDao implements IBookDao {
/**
* Method function: add operation
*
* @param book
* @return
*/
public int addBook(Book book) {
/** 1.Declare all required variables and data objects in the addition, deletion and modification query */
int n = 0;// Number of rows affected
Connection conn = null;// Connection object
PreparedStatement ps = null;// target of execution
String sql = "";// sql statement
/** 2.Perform relevant assignment and acquisition for the variables and database objects defined above */
try {
// (1) Get database connection object
conn = DBHelper.getConn();
// (2) Assign a value to sql (add a new statement)
sql = "insert into tb_book values (?,?,?,?)";
// (3) Pass the sql statement into the method called through conn and return its ps object
ps = conn.prepareStatement(sql);
// (4) Assign a value to the corresponding placeholder
ps.setString(1, book.getBname());
ps.setString(2, book.getBtype());
ps.setInt(3, book.getBprice());
ps.setString(4, book.getBauthor());
// (5) Start up (return affected rows)
n = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);// Close connection
}
return n;
}
/**
* Method function: delete books
*
* @param bid
* @return
*/
public int deleteBookByBid(int bid) {
/** 1.Declare all required variables and data objects in the addition, deletion and modification query */
int n = 0;// Number of rows affected
Connection conn = null;// Connection object
PreparedStatement ps = null;// target of execution
String sql = "";// sql statement
/** 2.Perform relevant assignment and acquisition for the variables and database objects defined above */
try {
// (1) Get database connection object
conn = DBHelper.getConn();
// (2) Assign value to sql (delete statement)
sql = "delete from tb_book where bid = ?";
// (3) Pass the sql statement into the method called through conn and return its ps object
ps = conn.prepareStatement(sql);
// (4) Assign a value to the corresponding placeholder
ps.setInt(1, bid);
// (5) Start up (return affected rows)
n = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);// Close connection
}
return n;
}
/**
* Method function: modify
*
* @param bid
* @param book
* @return
*/
public int editBookByBid(int bid, Book book) {
/** 1.Declare all required variables and data objects in the addition, deletion and modification query */
int n = 0;// Number of rows affected
Connection conn = null;// Connection object
PreparedStatement ps = null;// target of execution
String sql = "";// sql statement
/** 2.Perform relevant assignment and acquisition for the variables and database objects defined above */
try {
// (1) Get database connection object
conn = DBHelper.getConn();
// (2) Assign value to sql (modify statement)
sql = "update tb_book set bname = ? , btype = ? , bprice = ? , bauthor = ? where bid = ?";
// (3) Pass the sql statement into the method called through conn and return its ps object
ps = conn.prepareStatement(sql);
// (4) Assign a value to the corresponding placeholder
ps.setString(1, book.getBname());
ps.setString(2, book.getBtype());
ps.setInt(3, book.getBprice());
ps.setString(4, book.getBauthor());
ps.setInt(5, bid);
// (5) Start up (return affected rows)
n = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);// Close connection
}
return n;
}
/**
* Query all
*/
public List<Book> queryBookAll() {
/** 1.Declare all required variables and data objects in the addition, deletion and modification query */
Connection conn = null;// Connection object
PreparedStatement ps = null;// target of execution
String sql = "";// sql statement
// For query
ResultSet rs = null;// Result set object
List<Book> list = new ArrayList<Book>();// Collection class object
Book b = null;// entity
/** 2.Perform relevant assignment and acquisition for the variables and database objects defined above */
try {
// (1) Get database connection object
conn = DBHelper.getConn();
// (2) Assign a value to sql (add a new statement)
sql = "select * from tb_book";
// (3) Pass the sql statement into the method called through conn and return its ps object
ps = conn.prepareStatement(sql);
// (4) Unloading (return result set object)
rs = ps.executeQuery();
// (5) Traversal
while (rs.next()) {
b = new Book(rs.getInt("bid"), rs.getString("bname"), rs.getString("btype"), rs.getInt("bprice"),
rs.getString("bauthor"));
list.add(b);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);// Close connection
}
return list;
}
/**
* Fuzzy query by book title
*
* @param strName
* @return
*/
public List<Book> queryBookAll(String strName) {
/** 1.Declare all required variables and data objects in the addition, deletion and modification query */
Connection conn = null;// Connection object
PreparedStatement ps = null;// target of execution
String sql = "";// sql statement
// For query
ResultSet rs = null;// Result set object
List<Book> list = new ArrayList<Book>();// Collection class object
Book b = null;// entity
/** 2.Perform relevant assignment and acquisition for the variables and database objects defined above */
try {
// (1) Get database connection object
conn = DBHelper.getConn();
// (2) Assign a value to sql (add a new statement)
sql = "select * from tb_book where bname like '%" + strName + "%'";
// (3) Pass the sql statement into the method called through conn and return its ps object
ps = conn.prepareStatement(sql);
// (4) Unloading (return result set object)
rs = ps.executeQuery();
// (5) Traversal
while (rs.next()) {
b = new Book(rs.getInt("bid"), rs.getString("bname"), rs.getString("btype"), rs.getInt("bprice"),
rs.getString("bauthor"));
list.add(b);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);// Close connection
}
return list;
}
/**
* Query single
*/
public Book queryBookAll(int bid) {
/** 1.Declare all required variables and data objects in the addition, deletion and modification query */
Connection conn = null;// Connection object
PreparedStatement ps = null;// target of execution
String sql = "";// sql statement
// For query
ResultSet rs = null;// Result set object
Book b = null;// entity
/** 2.Perform relevant assignment and acquisition for the variables and database objects defined above */
try {
// (1) Get database connection object
conn = DBHelper.getConn();
// (2) Assign a value to sql (add a new statement)
sql = "select * from tb_book where bid = " + bid;
// (3) Pass the sql statement into the method called through conn and return its ps object
ps = conn.prepareStatement(sql);
// (4) Unloading (return result set object)
rs = ps.executeQuery();
// (5) Traversal
if (rs.next()) {
b = new Book(rs.getInt("bid"), rs.getString("bname"), rs.getString("btype"), rs.getInt("bprice"),
rs.getString("bauthor"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);// Close connection
}
return b;
}
}
7.com.zz.bookManager.ui package. Create a UI class under this package and call the addition, deletion, modification and query methods of BookDAO class.
package com.zz.bookManager.ui;
import java.util.List;
import java.util.Scanner;
import com.zz.bookManager.dao.BookDao;
import com.zz.bookManager.entity.Book;
public class MainPageUI {
private Scanner sc = new Scanner(System.in);
//Instantiate the BookDAOL class of the data access layer
BookDao bd = new BookDao();
public void myShow() {
while(true) {
System.out.println("Library management system");
System.out.println("1.Add operation");
System.out.println("2.Delete operation");
System.out.println("3.Modify operation");
System.out.println("4.Query operation");
System.out.println("5.Exit operation");
System.out.println("Please select: ");
int num = sc.nextInt();
if(num == 1) {
System.out.println("You have selected a new operation");
System.out.println("Please enter book name: ");
String bname = sc.next();
System.out.println("Please enter the type: ");
String btype = sc.next();
System.out.println("Please enter price: ");
int bprice = sc.nextInt();
System.out.println("Please enter author: ");
String bauthor = sc.next();
//The input properties are encapsulated into a whole with a Book object
Book Book = new Book(bname, btype, bprice, bauthor);
int n = bd.addBook(Book);
if(n >0 ) {
System.out.println("OK");
}else {
System.out.println("NO");
}
}else if(num == 2) {
System.out.println("You have selected the delete operation");
System.out.println("Please enter the student number of the deleted book");
int bid = sc.nextInt();
int n = bd.deleteBookByBid(bid);
System.out.println(n>0?"OK":"NO");
}else if(num == 3) {
System.out.println("You have selected the modify operation");
System.out.println("Please enter the number of the book to be modified");
int bid = sc.nextInt();
System.out.println("Please enter book name: ");
String bname = sc.next();
System.out.println("Please enter the type: ");
String btype = sc.next();
System.out.println("Please enter price: ");
int bprice = sc.nextInt();
System.out.println("Please enter author: ");
String bauthor = sc.next();
//The input properties are encapsulated into a whole with a Book object
Book Book = new Book(bname, btype, bprice, bauthor);
//Call modified methods in DAO
int n = bd.editBookByBid(bid, Book);
System.out.println(n>0?"OK":"NO");
}else if(num == 4) {
System.out.println("You have selected query all operations");
//Call directly to query all DAO methods
List<Book> queryBookAll = bd.queryBookAll();
for (Book Book : queryBookAll) {
System.out.println(Book);
}
}else if(num == 5) {
System.exit(0);
}else {
System.out.println("This feature has not yet been developed,Coming soon");
}
}
}
}8.com.zz.bookManager.test package, call the methods encapsulated in the UI package.
package com.zz.bookManager.test;
import com.zz.bookManager.ui.MainPageUI;
public class Test {
public static void main(String[] args) {
new MainPageUI().myShow();
}
}
design sketch:
