java: eidt DAL

 https://home.unicode.org/

 

 人生是一種心境,生活是一種藝術,成功是一種心態,幸福是一種感覺,學習是一種成長,競爭是一種建構,情感是一種融合

 

   java.sql.Types 数据类型 java 数据类型 CallableStatement set和 registerOutParameter 数据类型 PreparedStatement set 数据类型 之间转换相对应的数据类型弄清楚

 

 数据类型可以自动判断，优化代码

https://docs.oracle.com/javase/8/docs/api/java/sql/CallableStatement.html
https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html

https://docs.oracle.com/en/java/javase/14/docs/api/java.sql/java/sql/PreparedStatement.html
https://docs.oracle.com/en/java/javase/14/docs/api/java.sql/java/sql/CallableStatement.html

https://docs.oracle.com/en/java/javase/14/docs/api/java.sql/java/sql/Types.html

https://docs.oracle.com/en/java/javase/14/docs/api/allclasses-index.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/Data-Types.html

 

 

/*
 * 版权所有 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-27 修改：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.*;


/**
 * 数据业务层操作
 * @author geovindu 涂聚文 Geovin Du
 * @version 1.0
 *
 *
 * */

public class GeovinDuDAL {

    DuMySqlHelper duMySqlHelper=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("Integer");
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteInsertSql(sql,duParameters);
        return isok;

    }

    /**
     *SQL 语句添加
     * @param bookKind
     * @return bool
     * */
    public Boolean AddAutoSql(BookKind bookKind)
    {
        Boolean isok=false;
        String sql="INSERT INTO BookKindList(BookKindName,BookKindParent) values(? ,?)";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindName());
        duParameters.add(duParameter);
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindParent());
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteAutoInsertSql(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("Integer");
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteInsert(sql,duParameters);
        return  isok;

    }

    /**
     * 存储过程添加
     * @param bookKind
     * @return bool
     * **/
    public Boolean AddAutoProc(BookKind bookKind)
    {
        Boolean isok=false;
        String sql="{CALL proc_Insert_BookKindList(? ,?)}";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindName());

        duParameters.add(duParameter);
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindParent());

        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteAutoInsert(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("Integer");
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteInsertOutSingleInt(sql,duParameters,outValue);
        return  isok;

    }
    /**
     * 添加有返回值
     * @param bookKind 输入参数
     * @param outValue 返回参数
     * @return  bool 返回添加是否成功
     *
     * */
    public Boolean AddAutoProc(BookKind bookKind,int outValue)
    {
        Boolean isok=false;
        String sql="{CALL proc_Insert_BookKindList(? ,?)}";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindName());

        duParameters.add(duParameter);
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindParent());

        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteAutoInsertOutSingleInt(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("Integer");
        duParameters.add(duParameter);
        duParameter.setParameterValue(bookKind.getBookKindID());
        duParameter.setDataType("Integer");
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteUpdateSql(sql,duParameters);
        return isok;
    }

    /**
     * 修改 sql
     * @param bookKind
     * @return  int
     * */
    public  int EditAutoSQL(BookKind bookKind)
    {
        int isok=0;
        String sql="UPDATE BookKindList SET BookKindName=?,BookKindParent=? where BookKindID=?";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindName());
        duParameters.add(duParameter);
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindParent());
        duParameter=new ParameterObject();
        duParameters.add(duParameter);
        duParameter.setDuObject(bookKind.getBookKindID());
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteAutoUpdateSql(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("Integer"); //类型可以判断，不需要去定义
        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("Integer");
        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteUpdate(sql,duParameters);
        return isok;
    }


    /**
     *修改 存储过程
     * @param bookKind
     * @return  int
     * */
    public  int EditAutoProc(BookKind bookKind)
    {
        int isok=0;
        String sql="CALL proc_Update_BookKindList(?,?,?)";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindID());

        duParameters.add(duParameter);
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindName());

        duParameters.add(duParameter);
        duParameter=new ParameterObject();
        duParameter.setDuObject(bookKind.getBookKindParent());

        duParameters.add(duParameter);
        isok=duMySqlHelper.ExecuteAutoUpdate(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("Integer");
        duParameters.add(duParameter);
        isok= duMySqlHelper.ExecuteDeletSql(sql,duParameters);
        return isok;
    }

    /**
     * 删除 SQL语句
     * @param id
     * @return  int
     *
     * */
    public  int DelAutoSQL(int id) {
        int isok=0;
        String sql="Delete From bookkindlist WHERE BookKindID =?";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(id);
        duParameters.add(duParameter);
        isok= duMySqlHelper.ExecuteAutoDelteSQL(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("Integer");
        duParameters.add(duParameter);
        isok= duMySqlHelper.ExecuteDelte(sql,duParameters);
        return isok;
    }

    /**
     * 删除 存储过程语句
     * @param id
     * @return  int
     *
     * */
    public  int DelAutoProc(int id) {
        int isok=0;
        String sql="{CALL DeleteBookKind(?)}";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(id);
        duParameters.add(duParameter);
        isok= duMySqlHelper.ExecuteAutoDelte(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 = duMySqlHelper.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 id
     * @return
     * */
    public BookKind selectAutoSQL(int id)
    {
        ResultSet resultSet=null;
        BookKind bookKind=null;
        String sql = "SELECT * FROM BookKindList where BookKindID=?";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(id);
        duParameters.add(duParameter);
        try {

            resultSet = duMySqlHelper.ExecuteAutoQuery(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("Integer");
        duParameters.add(duParameter);
        try {

            resultSet = duMySqlHelper.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 selectAutoProc(int id)
    {
        ResultSet resultSet=null;
        BookKind bookKind=null;
        String sql = "{CALL proc_Select_BookKindList(?)}";
        ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
        ParameterObject duParameter=null;
        duParameter=new ParameterObject();
        duParameter.setDuObject(id);
        duParameters.add(duParameter);
        try {

            resultSet = duMySqlHelper.ExecuteAutoQuery(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 = duMySqlHelper.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 = duMySqlHelper.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 fields 要查询的字段，用逗号(,)分隔
     * @param table 查询的表
     * @param where 查询条件
     * @param orderby 排序规则
     * @param pageindex  查询页码
     * @param pageSize 每页记录数
     * @param outValues1 两个反回参数  总记录数 总页数
     * */
    public ArrayList<BookKind> selectPaging(String fields,String table,String where,String orderby,int pageindex,int pageSize,ArrayList<OutObject> outValues1)
    {
        ArrayList<BookKind> list=new ArrayList<BookKind>();
       // outValues1=new ArrayList<OutObject>();
        ResultSet resultSet=null;
        BookKind bookKind=null;
        String sql = "{CALL sp_viewPage(?,?,?,?,?,?,?,?)}";
        try {

            ArrayList<DuParameter> duParameters=new ArrayList<DuParameter>();
            DuParameter duParameter=null;

            duParameter=new DuParameter();
            duParameter.setParameterValue(fields);
            duParameter.setDataType("String");
            duParameters.add(duParameter);

            duParameter=new DuParameter();
            duParameter.setParameterValue(table);
            duParameter.setDataType("String");
            duParameters.add(duParameter);

            duParameter=new DuParameter();
            duParameter.setParameterValue(where);
            duParameter.setDataType("String");
            duParameters.add(duParameter);

            duParameter=new DuParameter();
            duParameter.setParameterValue(orderby);
            duParameter.setDataType("String");
            duParameters.add(duParameter);
            duParameter=new DuParameter();
            duParameter.setParameterValue(pageindex);
            duParameter.setDataType("Integer");
            duParameters.add(duParameter);
            duParameter=new DuParameter();
            duParameter.setParameterValue(pageSize);
            duParameter.setDataType("Integer");
            duParameters.add(duParameter);


            ArrayList<DuParameter> outParameters=new ArrayList<DuParameter>();
            duParameter=new DuParameter();
            duParameter.setParameterValue("totalcount");
            duParameter.setDataType("Integer");
            outParameters.add(duParameter);
            duParameter=new DuParameter();
            duParameter.setParameterValue("pagecount");
            duParameter.setDataType("Integer");
            outParameters.add(duParameter);
            ArrayList<OutObject> outValues2=new ArrayList<OutObject>();
            resultSet = duMySqlHelper.ExecuteQuery(sql, duParameters,outParameters,outValues1);
            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;

    }
    /**
     * 分页查询  geovindu Geovin Du 涂聚文
     * @param fields 要查询的字段，用逗号(,)分隔
     * @param table 查询的表
     * @param where 查询条件
     * @param orderby 排序规则
     * @param pageindex  查询页码
     * @param pageSize 每页记录数
     * @param outValues1 两个反回参数  总记录数 总页数
     * */
    public ArrayList<BookKind> selectAutoPaging(String fields,String table,String where,String orderby,int pageindex,int pageSize,ArrayList<OutObject> outValues1)
    {
        ArrayList<BookKind> list=new ArrayList<BookKind>();
        // outValues1=new ArrayList<OutObject>();
        ResultSet resultSet=null;
        BookKind bookKind=null;
        String sql = "{CALL sp_viewPage(?,?,?,?,?,?,?,?)}";
        try {

            ArrayList<ParameterObject> duParameters=new ArrayList<ParameterObject>();
            ParameterObject duParameter=null;

            duParameter=new ParameterObject();
            duParameter.setDuObject(fields);
            duParameters.add(duParameter);

            duParameter=new ParameterObject();
            duParameter.setDuObject(table);
            duParameters.add(duParameter);

            duParameter=new ParameterObject();
            duParameter.setDuObject(where);
            duParameters.add(duParameter);
            duParameter=new ParameterObject();
            duParameter.setDuObject(orderby);
            duParameters.add(duParameter);
            duParameter=new ParameterObject();
            duParameter.setDuObject(pageindex);
            duParameters.add(duParameter);
            duParameter=new ParameterObject();
            duParameter.setDuObject(pageSize);
            duParameters.add(duParameter);


            ArrayList<ParameterObject> outParameters=new ArrayList<ParameterObject>();
            duParameter=new ParameterObject();
            duParameter.setDuObject("Integer");
            outParameters.add(duParameter);
            duParameter=new ParameterObject();
            duParameter.setDuObject("Integer");
            outParameters.add(duParameter);
            ArrayList<OutObject> outValues2=new ArrayList<OutObject>();
            resultSet = duMySqlHelper.ExecuteAutoQuery(sql, duParameters,outParameters,outValues1);
            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;

    }

}