sql:
drop table BookKindList;
#书目录
create table BookKindList
(
BookKindID INT NOT NULL AUTO_INCREMENT, #自动增加
BookKindName nvarchar(500) not null,
BookKindParent int null,
PRIMARY KEY(BookKindID) #主键
);
#删除
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`DeleteBookKind` $$
CREATE PROCEDURE `geovindu`.`DeleteBookKind` (IN param1 INT)
BEGIN
Delete From bookkindlist WHERE BookKindID = param1;
END $$
DELIMITER ;
delete from bookkindlist WHERE BookKindID =10;
SELECT * FROM bookkindlist;
execute DeleteBookKind(10)；
#查询所有
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindListAll` $$
CREATE PROCEDURE `geovindu`.`proc_Select_BookKindListAll` ()
BEGIN
SELECT * FROM bookkindlist;
END $$
DELIMITER ;
DROP PROCEDURE proc_Select_BookKindListAll;
select * from `geovindu`.`bookkindlist`;
SELECT * FROM bookkindlist;
#统计
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`BookKindCount` $$
CREATE PROCEDURE `geovindu`.`BookKindCount` (OUT param1ID INT)
BEGIN
select COUNT(*) into param1ID From bookkindlist;
END $$
DELIMITER ;
#更新
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Update_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Update_BookKindList` (IN param1ID Int,IN param1Name NVarChar(1000),IN param1Parent Int)
BEGIN
IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then #如果存在相同的记录，不更新名称
UPDATE BookKindList
SET
BookKindName=param1Name ,
BookKindParent=param1Parent
where
BookKindID=param1ID;
ELSE
UPDATE BookKindList
SET BookKindParent=param1Parent
where
BookKindID=param1ID;
END IF;
END $$
DELIMITER ;
#查询一条
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Select_BookKindList` (IN param1 INT)
BEGIN
SELECT * FROM BookKindList WHERE BookKindID = param1;
END $$
DELIMITER ;
#插入一条
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindList` (IN param1Name NVarChar(1000),IN param1Parent Int)
BEGIN
insert into BookKindList(BookKindName,BookKindParent) values(param1Name,param1Parent);
END $$
DELIMITER ;
#插入一条返回值
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$
CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Parent Int,OUT ID INT)
BEGIN
IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then #如果存在相同的记录，不添加
INSERT INTO BookKindList (BookKindName,BookKindParent)VALUES(param1Name ,param1Parent);
#set ID=Last_insert_id()
SELECT LAST_INSERT_ID() into ID;
end if;
END $$
DELIMITER ;
MODEL:
/*
* 版权所有 2021 涂聚文有限公司
* 许可信息查看：
* 描述：实体类，连接MySQL
*
* 历史版本： JDK 14.02
* 数据库：My SQL 8.0
* IDE: IntelliJ IDEA 2021.2.3
* OS: Windows 10 x64
* 2021-12-12 创建者 geovindu
* 2021-12-15 添加 Lambda
* 2021-12-15 修改：date
* 接口类
* 2021-12-15 修改者：Geovin Du
* 生成API帮助文档的指令：
*javadoc - -encoding Utf-8 -d apidoc BookKind.java
* 配置文件：
* driver=com.mysql.jdbc.Driver
*url=jdbc\:mysql\://localhost\:3306/数据库名称
*user=root
*password=root
*
* */
package Geovin.Model;
/**
* 实体类
*@author geovindu 涂聚文 Geovin Du
* @
*
* */
public class BookKind {
//
private int bookKindID;
private String bookKindName;
private int bookKindParent;
/**
* @param
* @return 得到ID
* */
public int getBookKindID() {
return bookKindID;
}
/**
* @param bookKindID 设置输入参数
*
* */
public void setBookKindID(int bookKindID) {
this.bookKindID = bookKindID;
}
/**
* @param
* @return 得到目录名称
* */
public String getBookKindName() {
return bookKindName;
}
/**
* @param bookKindName 设置输入参数
*
* */
public void setBookKindName(String bookKindName) {
this.bookKindName = bookKindName;
}
/**
* @param
* @return 得到父节点的值
* */
public int getBookKindParent() {
return bookKindParent;
}
/**
* @param bookKindParent 设置输入参数
*
* */
public void setBookKindParent(int bookKindParent) {
this.bookKindParent = bookKindParent;
}
}
DAL
/*
* 版权所有 2021 涂聚文有限公司
* 许可信息查看：
* 描述：DAL数据访问层 数据业务层，连接MySQL
*
* 历史版本： JDK 14.02
* 数据库：My SQL 8.0
* IDE: IntelliJ IDEA 2021.2.3
* OS: Windows 10 x64
* 2021-12-22 创建者 geovindu
* 2021-12-25 添加 Lambda
* 2021-12-25 修改：date
* 接口类
* 2021-12-25 修改者：Geovin Du
* 生成API帮助文档的指令：
*javadoc - -encoding Utf-8 -d apidoc GeovinDuDAL.java
* 配置文件：
* driver=com.mysql.jdbc.Driver com.mysql.cj.jdbc.Driver
*url=jdbc\:mysql\://localhost\:3306/数据库名称
*user=root
*password=root
*
* */
//#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end
package Geovin.DAL;
import java.awt.print.Book;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.sql.*;
import java.lang.reflect.Parameter;
import Geovin.Model.*;
import Geovin.UtilitieDB.DuMySqlHelper;
/**
* 数据业务层操作
* @author geovindu 涂聚文 Geovin Du
* @version 1.0
*
*
* */
public class GeovinDuDAL {
DuMySqlHelper duMySqlHelperr=new DuMySqlHelper();
/**
*SQL 语句添加
* @param bookKind
* @return bool
* */
public Boolean AddSql(BookKind bookKind)
{
Boolean isok=false;
String sql="INSERT INTO BookKindList(BookKindName,BookKindParent) values(? ,?)";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindName());
duParameter.setDataType("String");
duParameters.add(duParameter);
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindParent());
duParameter.setDataType("int");
duParameters.add(duParameter);
isok=duMySqlHelperr.ExecuteInsertSql(sql,duParameters);
return isok;
}
/**
* 存储过程添加
* @param bookKind
* @return bool
* **/
public Boolean AddProc(BookKind bookKind)
{
Boolean isok=false;
String sql="{CALL proc_Insert_BookKindList(? ,?)}";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindName());
duParameter.setDataType("String");
duParameters.add(duParameter);
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindParent());
duParameter.setDataType("int");
duParameters.add(duParameter);
isok=duMySqlHelperr.ExecuteInsert(sql,duParameters);
return isok;
}
/**
* 添加有返回值
* @param bookKind 输入参数
* @param outValue 返回参数
* @return bool 返回添加是否成功
*
* */
public Boolean AddProc(BookKind bookKind,int outValue)
{
Boolean isok=false;
String sql="{CALL proc_Insert_BookKindList(? ,?)}";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindName());
duParameter.setDataType("String");
duParameters.add(duParameter);
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindParent());
duParameter.setDataType("int");
duParameters.add(duParameter);
isok=duMySqlHelperr.ExecuteInsertOutSingleInt(sql,duParameters,outValue);
return isok;
}
/**
* 修改 sql
* @param bookKind
* @return int
* */
public int EditSQL(BookKind bookKind)
{
int isok=0;
String sql="UPDATE BookKindList SET BookKindName=?,BookKindParent=? where BookKindID=?";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindName());
duParameter.setDataType("String");
duParameters.add(duParameter);
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindParent());
duParameter.setDataType("int");
duParameters.add(duParameter);
duParameter.setParameterValue(bookKind.getBookKindID());
duParameter.setDataType("int");
duParameters.add(duParameter);
isok=duMySqlHelperr.ExecuteUpdateSql(sql,duParameters);
return isok;
}
/**
*修改 存储过程
* @param bookKind
* @return int
* */
public int EditProc(BookKind bookKind)
{
int isok=0;
String sql="CALL proc_Update_BookKindList(?,?,?)";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindID());
duParameter.setDataType("int");
duParameters.add(duParameter);
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindName());
duParameter.setDataType("String");
duParameters.add(duParameter);
duParameter=new DuParameter();
duParameter.setParameterValue(bookKind.getBookKindParent());
duParameter.setDataType("int");
duParameters.add(duParameter);
isok=duMySqlHelperr.ExecuteUpdate(sql,duParameters);
return isok;
}
/**
* 删除 SQL语句
* @param id
* @return int
*
* */
public int DelSQL(int id) {
int isok=0;
String sql="Delete From bookkindlist WHERE BookKindID =?";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(id);
duParameter.setDataType("int");
duParameters.add(duParameter);
isok= duMySqlHelperr.ExecuteDeletSql(sql,duParameters);
return isok;
}
/**
* 删除 存储过程语句
* @param id
* @return int
*
* */
public int DelProc(int id) {
int isok=0;
String sql="{CALL DeleteBookKind(?)}";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(id);
duParameter.setDataType("int");
duParameters.add(duParameter);
isok= duMySqlHelperr.ExecuteDelte(sql,duParameters);
return isok;
}
/**
*SQL语句查询
* @param id
* @return
* */
public BookKind selectSQL(int id)
{
ResultSet resultSet=null;
BookKind bookKind=null;
String sql = "SELECT * FROM BookKindList where BookKindID=?";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(id);
duParameter.setDataType("int");
duParameters.add(duParameter);
try {
resultSet = duMySqlHelperr.ExecuteQuery(sql, duParameters);
while (resultSet.next()) {
bookKind =new BookKind();
bookKind.setBookKindID(resultSet.getInt("BookKindID"));
bookKind.setBookKindName(resultSet.getString("BookKindName"));
bookKind.setBookKindParent(resultSet.getInt("BookKindParent"));
}
}
catch (Exception exception)
{
exception.printStackTrace();
}
return bookKind;
}
/**
*存储过程语句
* @param id
* @return
* */
public BookKind selectProc(int id)
{
ResultSet resultSet=null;
BookKind bookKind=null;
String sql = "{CALL proc_Select_BookKindList(?)}";
ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
DuParameter duParameter=null;
duParameter=new DuParameter();
duParameter.setParameterValue(id);
duParameter.setDataType("int");
duParameters.add(duParameter);
try {
resultSet = duMySqlHelperr.ExecuteQuery(sql, duParameters);
while (resultSet.next()) {
bookKind =new BookKind();
bookKind.setBookKindID(resultSet.getInt("BookKindID"));
bookKind.setBookKindName(resultSet.getString("BookKindName"));
bookKind.setBookKindParent(resultSet.getInt("BookKindParent"));
}
}
catch (Exception exception)
{
exception.printStackTrace();
}
return bookKind;
}
/**
*SQL语句查询
* @param
* @return
* */
public ArrayList<BookKind> selectAllSQL()
{
ArrayList<BookKind> list=new ArrayList<BookKind>();
ResultSet resultSet=null;
BookKind bookKind=null;
String sql = "SELECT * FROM BookKindList";
try {
resultSet = duMySqlHelperr.ExecuteQuery(sql, null);
while (resultSet.next()) {
bookKind =new BookKind();
bookKind.setBookKindID(resultSet.getInt("BookKindID"));
bookKind.setBookKindName(resultSet.getString("BookKindName"));
bookKind.setBookKindParent(resultSet.getInt("BookKindParent"));
list.add(bookKind);
}
}
catch (Exception exception)
{
exception.printStackTrace();
}
return list;
}
/**
*存储过程语句
* @param
* @return
* */
public ArrayList<BookKind> selectAllProc()
{
ArrayList<BookKind> list=new ArrayList<BookKind>();
ResultSet resultSet=null;
BookKind bookKind=null;
String sql = "{CALL proc_Select_BookKindListAll()}";
try {
resultSet = duMySqlHelperr.ExecuteQuery(sql, null);
while (resultSet.next()) {
bookKind =new BookKind();
bookKind.setBookKindID(resultSet.getInt("BookKindID"));
bookKind.setBookKindName(resultSet.getString("BookKindName"));
bookKind.setBookKindParent(resultSet.getInt("BookKindParent"));
list.add(bookKind);
}
}
catch (Exception exception)
{
exception.printStackTrace();
}
return list;
}
}
IDAL:
/*
* 版权所有 2021 涂聚文有限公司
* 许可信息查看：
* 描述：Interface 接口层，连接MySQL
*
* 历史版本： JDK 14.02
* 数据库：My SQL 8.0
* IDE: IntelliJ IDEA 2021.2.3
* OS: Windows 10 x64
* 2021-12-12 创建者 geovindu
* 2021-12-15 添加 Lambda
* 2021-12-15 修改：date
* 接口类
* 2021-12-15 修改者：Geovin Du
* 生成API帮助文档的指令：
*javadoc - -encoding Utf-8 -d apidoc BookKindInterface.java
* 配置文件：
* driver=com.mysql.jdbc.Driver com.mysql.cj.jdbc.Driver
*url=jdbc\:mysql\://localhost\:3306/数据库名称
*user=root
*password=root
*
* */
package Geovin.Interface;
import Geovin.Model.BookKind;
import java.util.ArrayList;
/**
* #parse("接口")
* @author geovindu 涂聚文 Geovin Du
* @version 1.0
* */
public interface BookKindInterface {
/**
* @param info
* @return
* */
public int Add(BookKind info);
/**
*
* @param info
* @return
*
* */
public int AddOut(BookKind info);
/**
*
* @param info
* @return
*
* */
public int Update(BookKind info);
/**
*
* @param id
* @return
*
* **/
public BookKind SelectSQLBookKindInfo(String id);
/**
*
* @param
* @return
*
* */
public ArrayList<BookKind> SelectSQLBookKindAll();
}
Factory:
/*
* 版权所有 2021 涂聚文有限公司
* 许可信息查看：
* 描述：工厂层，抽象工厂 连接MySQL
*
* 历史版本： JDK 14.02
* 数据库：My SQL 8.0
* IDE: IntelliJ IDEA 2021.2.3
* OS: Windows 10 x64
* 2021-12-12 创建者 geovindu
* 2021-12-15 添加 Lambda
* 2021-12-15 修改：date
* 接口类
* 2021-12-15 修改者：Geovin Du
* 生成API帮助文档的指令：
*javadoc - -encoding Utf-8 -d apidoc BookKind.java
* 配置文件：
* driver=com.mysql.jdbc.Driver
*url=jdbc\:mysql\://localhost\:3306/数据库名称
*user=root
*password=root
*
* */
package Geovin.Factory;
import Geovin.DAL.BookKindDAL;
import Geovin.Interface.BookKindInterface;
/**
* #parse("抽象工厂")
* @author geovindu 涂聚文 Geovin Du
* @version 1.0
*
* */
public class AbstractFactory {
/**
*
*
* */
public static BookKindInterface CreateBookKind()
{
BookKindInterface iBookKindInterface=new BookKindDAL();
return iBookKindInterface;
}
}
BLL:
/*
* 版权所有 2021 涂聚文有限公司
* 许可信息查看：
* 描述：业务逻辑层，连接MySQL
*
* 历史版本： JDK 14.02
* 数据库：My SQL 8.0
* IDE: IntelliJ IDEA 2021.2.3
* OS: Windows 10 x64
* 2021-12-12 创建者 geovindu
* 2021-12-15 添加 Lambda
* 2021-12-15 修改：date
* 接口类
* 2021-12-15 修改者：Geovin Du
* 生成API帮助文档的指令：
*javadoc - -encoding Utf-8 -d apidoc BookKind.java
* 配置文件：
* driver=com.mysql.jdbc.Driver
*url=jdbc\:mysql\://localhost\:3306/数据库名称
*user=root
*password=root
*
* */
package Geovin.BLL;
import Geovin.Model.*;
import Geovin.Factory.AbstractFactory;
import Geovin.Interface.*;
import java.util.ArrayList;
/**
* #parse("业务逻辑层")
* @author geovindu 涂聚文 Geovin Du
* @
* */
public class BookKindBLL {
private static BookKindInterface dal=AbstractFactory.CreateBookKind();
/**
*
* */
public int Add(BookKind info)
{
return dal.Add(info);
}
/**
*
* */
public int AddOut(BookKind info)
{
return dal.AddOut(info);
}
/**
*
* */
public int Update(BookKind info)
{
return dal.Update(info);
}
/**
*
* */
public BookKind SelectSQLBookKindInfo(String id)
{
return dal.SelectSQLBookKindInfo(id);
}
/**
*
* */
public ArrayList<BookKind> SelectSQLBookKindAll()
{
return dal.SelectSQLBookKindAll();
}
}
测试：
//CustomerDAL dal=new CustomerDAL();
//dal.SelectSQLCustomer("1");
// BookKindDAL dal=new BookKindDAL();
BookKindBLL dal=new BookKindBLL();
BookKind info=dal.SelectSQLBookKindInfo("1");
System.out.println("\t\n实体读出：id-"+info.getBookKindID()+";类目名称:"+info.getBookKindName()+"；父节点ID:"+info.getBookKindParent());
BookKind newinfo=new BookKind();
newinfo.setBookKindID(5);
newinfo.setBookKindName("聚文小说");
newinfo.setBookKindParent(2);
int ok=dal.Update(newinfo);
if(ok>0) {
System.out.println("更新记录，ok"+String.valueOf(ok));
}
else
{
System.out.println("更新不成功，no");
}
info=new BookKind();
info=dal.SelectSQLBookKindInfo("5");
System.out.println("\t\n"+info.getBookKindName());
