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());
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）