java: framework from BLL、DAL、IDAL、MODEL、Factory, using Ms SQL server

sql:

 
 
--基础数据设置BasicDataSet
--书分类目录kind
--BookKindForm
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].BookKindList') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROp TABLE BookKindList
GO
create table BookKindList
(
    BookKindID INT IDENTITY(1,1) PRIMARY KEY,
    BookKindName nvarchar(500) not null,
    BookKindParent int null,
    BookKindCode varchar(100)   ---編號
)
GO
 
alter table BookKindList add BookKindCode varchar(100)
 
select * from BookKindList
 
insert into BookKindList(BookKindName,BookKindParent) values('六福书目录',0)
 
insert into BookKindList(BookKindName,BookKindParent) values('文学',1)
insert into BookKindList(BookKindName,BookKindParent) values('设计艺术',1)
insert into BookKindList(BookKindName,BookKindParent) values('自然科学',1)
 
insert into BookKindList(BookKindName,BookKindParent) values('小说',2)
insert into BookKindList(BookKindName,BookKindParent) values('诗词散曲',2)
 
 
 
IF EXISTS (SELECt * FROM sysobjects WHERe [name] = 'proc_Insert_BookKindList')
DROp PROCEDURE proc_Insert_BookKindList
GO
CREATE PROCEDURE proc_Insert_BookKindList
(
    --@BookKindID Int,
    @BookKindName NVarChar(1000),
    @BookKindCode varchar(100),
    @BookKindParent Int
)
AS
IF NOT EXISTS (SELECT * FROM BookKindList WHERe [BookKindName]=@BookKindName)
BEGIN
INSERT INTO BookKindList
(
    [BookKindName] ,
    [BookKindCode],
    [BookKindParent]
)
    VALUES
(
    @BookKindName ,
    @BookKindCode,
    @BookKindParent
)
END
GO
 
 
 
IF EXISTS (SELECt * FROM sysobjects WHERe [name] = 'proc_Insert_BookKindOut')
DROp PROCEDURE proc_Insert_BookKindOut
GO
CREATE PROCEDURE proc_Insert_BookKindOut
(
    @BookKindName NVarChar(1000),
    @BookKindCode varchar(100),
    @BookKindParent Int,
    @BookKindID Int output
)
AS
IF NOT EXISTS (SELECT * FROM BookKindList WHERe [BookKindName]=@BookKindName)
BEGIN
INSERT INTO BookKindList
(
    [BookKindName] ,
    [BookKindCode],
    [BookKindParent]
)
    VALUES
(
    @BookKindName ,
    @BookKindCode,
    @BookKindParent
)
SELECt @BookKindID=@@IDENTITY
END
GO
 
 
 
 
IF EXISTS (SELECT * FROM sysobjects WHERe [name] = 'proc_Update_BookKindList')
DROP PROCEDURE proc_Update_BookKindList
GO
CREATE PROCEDURE proc_Update_BookKindList
(
    @BookKindID Int,
    @BookKindName NVarChar(1000),
    @BookKindCode varchar(100),
    @BookKindParent Int
)
AS
IF NOT EXISTS (SELECT * FROM BookKindList WHERe [BookKindName]=@BookKindName)
BEGIN
UPDATe BookKindList
    SET
        [BookKindName]=@BookKindName ,
        [BookKindCode]=@BookKindCode,
        [BookKindParent]=@BookKindParent
    where
        [BookKindID]=@BookKindID
END
ELSE
BEGIN
UPDATE BookKindList
    SET
        --[BookKindName]=@BookKindName ,
        [BookKindCode]=@BookKindCode,
        [BookKindParent]=@BookKindParent
    where
        [BookKindID]=@BookKindID
END
GO
 
 
--刪除時，要刪相關的書藉信息
IF EXISTS (select * from sysobjects where [name] = 'proc_Delete_BookKindList')
DROP PROCEDURE proc_Delete_BookKindList
GO
CREATE PROCEDURE proc_Delete_BookKindList
(
    @BookKindID Int
)
as
DELETE
    BookKindList
    WHERe
        BookKindID = @BookKindID
 
GO
 
IF EXISTS (SELECT * FROM sysobjects WHERe [name] = 'proc_Select_BookKindList')
DROP PROCEDURE proc_Select_BookKindList
GO
CREATE PROCEDURE proc_Select_BookKindList
(
    @BookKindID Int
)
AS
SELECT * FROM BookKindList WHERe BookKindID = @BookKindID
GO
 
IF EXISTS (SELECt * FROM sysobjects WHERe [name] = 'proc_Select_BookKindListAll')
DROP PROCEDURE proc_Select_BookKindListAll
GO
CREATE PROCEDURE proc_Select_BookKindListAll
AS
SELECT * FROM BookKindList
GO

Model

 
 
package Geovin.Model;
 
 

public class BookKind {
    //
    private int BookKindID;
 
 
    private String BookKindName;
 
 
    private int BookKindParent;
 
 
    private String BookKindCode;
 
 
    
    public int getBookKindID() {
        return BookKindID;
    }
    
    public void setBookKindID(int bookKindID) {
        this.BookKindID = bookKindID;
    }
    
    public String getBookKindName() {
        return BookKindName;
    }
    
    public void setBookKindName(String bookKindName) {
        this.BookKindName = bookKindName;
    }
    
    public int getBookKindParent() {
        return BookKindParent;
    }
    
    public void setBookKindParent(int bookKindParent) {
        this.BookKindParent = bookKindParent;
    }
 
    
    public void setBookKindCode(String bookKindCode) {
        BookKindCode = bookKindCode;
    }
    
    public String getBookKindCode() {
        return BookKindCode;
    }
}

DAL:

 
//#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end
package Geovin.DAL;
 
 
import Geovin.Interface.*;
import Geovin.Model.*;
import Geovin.Model.OutValue;
import Geovin.UtilitieDB.*;
 
import java.sql.*;
import java.util.ArrayList;
 

 
public class BookKindDAL implements BookKindInterface {
 
 
 
   // SqlHelper sqlHelper=new SqlHelper();
    
    public int Add(BookKind info)
    {
        int ok=0;
        ResultSet resultSet = null;
        try
        {
            String sql = "{call proc_Insert_BookKindList(?,?,?)}";
            String[] parameters = {info.getBookKindName(), String.valueOf(info.getBookKindParent()),info.getBookKindCode() };
            SqlHelper.CallProc(sql,parameters);
            ok=1;
        }
        catch (Exception exception)
        {
            ok=0;
            exception.printStackTrace();
        }
        finally {
            SqlHelper.close(resultSet, SqlHelper.getCs(), SqlHelper.getConnection());
        }
 
 
        return ok;
 
    }
 
    
    public int AddOut(BookKind info)
    {
        int ok=0;
        ResultSet resultSet = null;
        try
        {
            String sql = "{call proc_Insert_BookKindOut(?,?,?,?)}"; //多少个参数，多少个问号，包括输入，输出参数后面，输入，输出的个数量要明晰
            String[] parameters = {info.getBookKindName(), String.valueOf(info.getBookKindParent()),info.getBookKindCode() };
            Integer[] out = { Types.INTEGER };
            CallableStatement cs=(CallableStatement)SqlHelper.CallProcOutInt(sql,parameters,out);
            ok= cs.getInt(3);
 
        }
        catch (Exception exception)
        {
            ok=0;
            exception.printStackTrace();
        }
        finally {
            SqlHelper.close(resultSet, SqlHelper.getCs(), SqlHelper.getConnection());
        }
        return ok;
 
    }
    
    public int AddOut(BookKind info,OutValue outValue)
    {
        int ok=0;
        ResultSet resultSet = null;
        try
        {
            String sql = "{call proc_Insert_BookKindOut(?,?,?,?)}";
            String[] parameters = {info.getBookKindName(), String.valueOf(info.getBookKindParent()),info.getBookKindCode() };
            Integer[] out = { Types.INTEGER };
            CallableStatement cs=(CallableStatement)SqlHelper.CallProcOutInt(sql,parameters,out);
            outValue.setIntValue(cs.getInt(3));
            info.setBookKindID(cs.getInt(3));
            ok=cs.getInt(3);
 
        }
        catch (Exception exception)
        {
            ok=0;
            exception.printStackTrace();
        }
        finally {
            SqlHelper.close(resultSet, SqlHelper.getCs(), SqlHelper.getConnection());
        }
        return ok;
 
    }
    
    public int AddOut2(BookKind info)
    {
        int ok=0;
        ResultSet resultSet = null;
        try
        {
            String sql = "{call proc_Insert_BookKindOut(?,?,?,?)}";
 
            String[] parameters = {info.getBookKindName(), String.valueOf(info.getBookKindParent()),info.getBookKindCode(),""};
            Integer out =Types.INTEGER;
            info.setBookKindParent(out);
            SqlHelper.callProcInputAndOutPutString(sql,parameters);
            ok=out; //不是添加的ID值
        }
        catch (Exception exception)
        {
            ok=0;
            exception.printStackTrace();
        }
        finally {
            SqlHelper.close(resultSet, SqlHelper.getCs(), SqlHelper.getConnection());
        }
        return ok;
 
    }
 
 
    
    public int Update(BookKind info) {
 
        int ok=0;
        ResultSet resultSet = null;
        try
        {
            String sql = "{call proc_Update_BookKindList(?,?,?,?)}";
            String[] parameters = {String.valueOf(info.getBookKindID()), info.getBookKindName(), String.valueOf(info.getBookKindParent()),info.getBookKindCode() };
            SqlHelper.CallProc(sql,parameters);
            ok=1; //
        }
        catch (Exception exception)
        {
            ok=0;
            exception.printStackTrace();
        }
        finally {
            SqlHelper.close(resultSet, SqlHelper.getCs(), SqlHelper.getConnection());
        }
        return ok;
 
 
 
    }
    
    public BookKind SelectSQLBookKindInfo(String id)
    {
        BookKind info=null;
        String sql = "SELECT * FROM BookKindList where BookKindID=?";
        String[] parameters = { id };
        try {
            info=new BookKind();
            ResultSet rs = SqlHelper.DuexecuteQuery(sql, parameters);
            while (rs.next()) {
 
                info.setBookKindID(rs.getInt("BookKindID"));
                info.setBookKindName(rs.getString("BookKindName"));
                info.setBookKindParent(rs.getInt("BookKindParent"));
                info.setBookKindCode(rs.getString("BookKindCode"));
            }
            //rs.close();
            //rs=null;
            //return info;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper
                    .getConnection());
        }
 
        return info;
    }
    
    public ArrayList SelectSQLBookKindAll()
    {
 
        ArrayList list=new ArrayList();
 
        String sql = "SELECT * FROM BookKindList";
 
        try {
            BookKind info=null;
            ResultSet rs = (ResultSet)SqlHelper.DuexecuteQuery(sql,null);
            while (rs.next()) {
                info=new BookKind();
                info.setBookKindID(rs.getInt("BookKindID"));
                info.setBookKindName(rs.getString("BookKindName"));
                info.setBookKindParent(rs.getInt("BookKindParent"));
                info.setBookKindCode(rs.getString("BookKindCode"));
                list.add(info);
            }
            //return info;
            rs.close();
            rs=null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper
                    .getConnection());
        }
        return list;
    }
    
    public  ArrayList SelectSProcBookKindAll()
    {
 
        Connection conn= null;
        //大多数情况下用preparedstatement替代statement
         PreparedStatement ps = null;
        ResultSet rs=null;
        ArrayList list=new ArrayList();
 
        String sql = "call proc_Select_BookKindListAll()";
        BookKind info=null;
        try {
 
 
            //1.
            //conn =MySqlHelper.getConnection();
            //CallableStatement statement = conn.prepareCall(sql);
            //statement.execute();
            //rs =statement.executeQuery();
             //2
             rs =SqlHelper.ExecuteQueryProcNoneData(sql);
 
            if(rs!=null) {
                while (rs.next()) {
                    info = new BookKind();
                    info.setBookKindID(rs.getInt("BookKindID"));
                    info.setBookKindName(rs.getString("BookKindName"));
                    info.setBookKindParent(rs.getInt("BookKindParent"));
                    info.setBookKindCode(rs.getString("BookKindCode"));
                    list.add(info);
                }
            }
            //return info;
 
            rs.close();
            rs=null;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("no");
        } finally {
            SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConnection());
        }
        return list;
    }
 
 
    
    public  ArrayList SelectSProcToBookKindAll()
    {
 
        Connection conn= null;
        //大多数情况下用preparedstatement替代statement
        PreparedStatement ps = null;
 
        ResultSet rs=null;
 
 
        ArrayList list=new ArrayList();
 
        String sql = "{call proc_Select_BookKindListAll()}";
        BookKind info=null;
        try {
            //1
            //conn = DriverManager.getConnection(url,userName,password);//
            //System.out.println("连接成功");
            //conn=getConnection();
 
            //2.
           // conn =MySqlHelper.getConnection();
           // CallableStatement statement = conn.prepareCall(sql);
           // statement.execute();
           // rs =statement.executeQuery();
 
             //3.
             rs =SqlHelper.ExecuteQueryProcNoneData(sql);
 
            if(rs!=null) {
                while (rs.next()) {
                    info = new BookKind();
                    info.setBookKindID(rs.getInt("BookKindID"));
                    info.setBookKindName(rs.getString("BookKindName"));
                    info.setBookKindParent(rs.getInt("BookKindParent"));
                    info.setBookKindCode(rs.getString("BookKindCode"));
                    list.add(info);
                }
            }
            //return info;
 
             rs.close();
             rs=null;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("no");
        } finally {
            SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConnection());
        }
        return list;
    }
 
}

IDAL：

 
 
package Geovin.Interface;
 
import Geovin.Model.BookKind;
import Geovin.Model.OutValue;
 
import java.util.ArrayList;
 

 
public interface BookKindInterface {
 
 
    
    public int Add(BookKind info);
    
    public int AddOut(BookKind info);
    
    public int AddOut(BookKind info, OutValue outValue);
    
    public int Update(BookKind info);
    
    public BookKind SelectSQLBookKindInfo(String id);
 
    
    public ArrayList SelectSQLBookKindAll();
    
    public ArrayList SelectSProcBookKindAll();
 
    public  ArrayList SelectSProcToBookKindAll();
 
}

Factory:

 
 
package Geovin.Factory;
 
 
import Geovin.DAL.BookKindDAL;
import Geovin.Interface.BookKindInterface;
 

 
public class AbstractFactory {
 
 
    
    public static  BookKindInterface CreateBookKind()
    {
        BookKindInterface iBookKindInterface=new BookKindDAL();
        return  iBookKindInterface;
    }
 
}

BLL:

 
 
package Geovin.BLL;
 
 
import Geovin.Factory.AbstractFactory;
import Geovin.Model.*;
import Geovin.Model.OutValue;
import Geovin.Interface.*;
import java.util.ArrayList;
 

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 AddOut(BookKind info,OutValue outValue){return dal.AddOut(info,outValue);}
    
    public int Update(BookKind info)
    {
        return dal.Update(info);
    }
    
    public BookKind SelectSQLBookKindInfo(String id)
    {
        return dal.SelectSQLBookKindInfo(id);
    }
    
    public ArrayList SelectSQLBookKindAll()
    {
        return dal.SelectSQLBookKindAll();
    }
    
    public ArrayList SelectSProcBookKindAll(){ return dal.SelectSProcBookKindAll();}
    
    public  ArrayList SelectSProcToBookKindAll(){return dal.SelectSProcToBookKindAll();}
}

测试：

BookKindBLL bookKindBLL=new BookKindBLL();
String id="2";
BookKind info=bookKindBLL.SelectSQLBookKindInfo(id);
System.out.println("Id:"+id+",名称："+info.getBookKindName()+"父节点："+info.getBookKindParent());
 
ArrayList arrayList=new ArrayList();
arrayList=bookKindBLL.SelectSProcToBookKindAll();
for(BookKind bookKind:arrayList)
{
    System.out.println("Id:"+bookKind.getBookKindID()+",名称："+bookKind.getBookKindName()+"父节点："+bookKind.getBookKindParent()+"编码："+bookKind.getBookKindCode());
}