1..javaweb的代码在没学 框架知识之前，有很多的复写的代码
package com.xt.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
public class DBHelper {
private Connection sqlcon=null;
private PreparedStatement pst=null;
private ResultSet rst=null;
// 用于 加载 找到 数据库的 地址，
private String url="jdbc:sqlserver://localhost:1433;databaseName=MyStudy";
private String user="sa";
private String password="123456";
// 加载 驱动类
private String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
private Connection GetConnection() throws SQLException{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
// 实例化 驱动类
return DriverManager.getConnection(url, user, password);
}
/**
* 执行sql语句，返回受影响的行数
* @param sql=》insert,update,delete
* @param objs
* @return
* @throws SQLException
*/
public int MyExecuteUpdate(String sql , Object ...objs) throws SQLException{
sqlcon=GetConnection();
pst=sqlcon.prepareStatement(sql);
if(objs!=null){
int idx=1;
for(Object obj : objs){
pst.setObject(idx, obj);// 先把 变量 置换为 ？ 就是完善 整个 sql语句
idx++;
}
}//就是 执行之后 返回 受影响的 行数
int iline = pst.executeUpdate();
CloseAll();
return iline;
}
/**
* 执行sql语句
* @param sql=>select
* @param objs
* @return
* @throws SQLException
*/
public Result GetResult(String sql,Object ...objs) throws SQLException{
sqlcon=GetConnection();
// 先 获得 pst 对象， 里面 没有 结果集
pst=sqlcon.prepareStatement(sql);
if(objs!=null){
int idx=1;
for(Object obj : objs){
pst.setObject(idx, obj);
idx++;
}
}// 这是 返回的 结果集，
rst=pst.executeQuery();
// 转化 rst 得到 可以 被 接收的 result
Result rs=ResultSupport.toResult(rst);
CloseAll();
return rs;
}
/**
* 关闭所有数据库连接资源
* @throws SQLException
*/
private void CloseAll() throws SQLException{
if(rst!=null){
rst.close();
}
if(pst!=null){
pst.close();
}
if(sqlcon!=null){
sqlcon.close();
}
}
/**
* 实现事务操作
* @param sqlList 要执行的 sql语句
* @param maps 要执行sql语句的 参数信息
* <Integer=>对应的是sqlList中的集合的索引
* <Object [] =>对应sqlList中的要执行sql语句的参数
* @return
*/ // 这是 事务的 执行 ， 就是 很多的 sql 语句和 参数
public String MyTransaction(List<String> sqlList,Map<Integer, Object[]> maps){
String msg="error";
try{
sqlcon=GetConnection();
sqlcon.setAutoCommit(false);//取消自动事务提交
if(sqlList!=null){
int idx=0;
for(String sql : sqlList){
// 根据 每个 sql 生成对应的 pst 对象
pst=sqlcon.prepareStatement(sql);
Object [] objs=maps.get(idx);
if(objs!=null){
int icount=1;
for(Object obj : objs){
pst.setObject(icount, obj);
icount++;
}
}
pst.execute();
idx++;
}
sqlcon.commit();
msg="success";
}
}catch (Exception e) {
e.printStackTrace();
try {
sqlcon.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
try {
CloseAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return msg;
}
<span style="background-color: rgb(51, 204, 0);">》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》</span>
// 上面的都是常用的，下面就是 为了 简化 操作 引用了 存储过程<pre name="code" class="java">// 带有 输入参数的 ，返回 结果集的
public static Result Getcunchu(String id) throws SQLException{
cst=GetCon().prepareCall("{call Ap(?)}");
cst.setObject(1, id);
cst.execute();//执行
ResultSet rst=cst.getResultSet();// 获得结果集
//Result类型是一个脱离数据库链接的结果集
Result rs=ResultSupport.toResult(rst);//将查询结果集转换为Result类型
if(pst!=null){
pst.close();//释放执行对象所占资源
}
if(sqlcon!=null){
sqlcon.close();//释放链接对象所占资源
}
return rs;
}
// 又是一 存储过程，带有 输入输出参数的
public static int Getcunguocheng(String pid,String userid ) throws SQLException{
cst=GetCon().prepareCall("{call proc_getGoodsInfo(?,?,?)}");
cst.setObject(1, pid);
cst.setObject(2, userid);
cst.registerOutParameter(3, java.sql.Types.INTEGER);
System.out.println("DB>>储存过程里面有问题5");
cst.execute();//执行
System.out.println("DB>>储存过程里面有问题7");
int rs=cst.getInt(3);
System.out.println("DB>>储存过程里面有问题8");
if(pst!=null){
pst.close();//释放执行对象所占资源
}
if(sqlcon!=null){
sqlcon.close();//释放链接对象所占资源
}
return rs;
}
//放入购物车
public static int shopcares(orderinfos pid,String userid ) throws SQLException{
cst=GetCon().prepareCall("{call proc_getGoodses(?,?,?,?,?,?)}");
cst.setObject(1, pid.getOid());
cst.setObject(2, pid.getPid());
cst.setObject(3, pid.getOdsum());
cst.setObject(4, pid.getOdcost());
cst.setObject(5, userid);
cst.registerOutParameter(6, java.sql.Types.INTEGER);
cst.execute();//执行
int rs=cst.getInt(6);
if(pst!=null){
pst.close();//释放执行对象所占资源
}
if(sqlcon!=null){
sqlcon.close();//释放链接对象所占资源
}
return rs;
}
//初始化购物车
public static int initaddshop(String pid,String userid ) throws SQLException{
cst=GetCon().prepareCall("{call proc_addshopcar(?,?,?)}");
cst.setObject(1, pid);
cst.setObject(2, userid);
cst.registerOutParameter(3, java.sql.Types.INTEGER);
cst.execute();//执行
int rs=cst.getInt(3);
if(pst!=null){
pst.close();//释放执行对象所占资源
}
if(sqlcon!=null){
sqlcon.close();//释放链接对象所占资源
}
return rs;
}
public static void Getupdate(String id) throws SQLException{
cst=GetCon().prepareCall("{call Aps(?)}");
cst.setObject(1, id);
cst.execute();//执行
if(pst!=null){
pst.close();//释放执行对象所占资源
}
if(sqlcon!=null){
sqlcon.close();//释放链接对象所占资源
}
}
}
/*
if exists(select *from sysobjects where name='Ap')
drop proc Ap
go
create proc Ap(@id varchar(20))
as
begin
select ou4.name as shopname,
ou4.fname as imgname,ou4.price as prices,
ou5.stus as ostuss,ou5.ppid as pids,
ou5.danhao as danhaohao,ou5.ssum as sumsum,
ou5.timme as timetime
from Easy_produ as ou4,
(select ou1.odid as danhao,
ou1.pid as ppid,ou1.odsum as ssum,
ou2.otime as timme,ou2.ostus as stus
from Easy_orderdetail as ou1,
(select * from Easy_order where ouserid=@id)as ou2
where ou1.oid=ou2.oid)as ou5
where ou4.pid=ou5.ppid
end
exec AP '123'
if exists(select * from sysobjects where name='proc_getGoodsInfo')
drop proc proc_getGoodsInfo
go
create proc proc_getGoodsInfo
(
@pid int,
@userid int,
@flag int output --返回标志
)
as
begin
update Easy_produ set stock=stock-1 where pid=@pid
declare @mon money
select @mon=price from Easy_produ where pid=@pid
update Easy_order set ocost=ocost-@mon where ouserid=@userid and oid=1
if(@mon>0)
set @flag=1
else
set @flag=0
end
declare @mons int
exec proc_getGoodsInfo 1,'123',@mons out
print 'you '+convert(nvarchar(20),@mons)
*/<pre name="code" class="sql">select ous.otime as time,oc.price as price,
od.odsum as cnts ,od.odid as ooid,
ous.ostus as stus,
oc.name as name,oc.fname as imgs
from Easy_orderdetail od,
Easy_produ oc ,Easy_order ous
where od.pid=oc.pid
and od.oid=ous.oid
select oc.price as price,
od.odsum as cnts ,
oc.name as name,oc.fname as imgs
from Easy_orderdetail od,
Easy_produ oc ,Easy_order ous
where od.pid=oc.pid
and od.oid=ous.oid
select ous.otime as time,oc.price as price,
od.odsum as cnts ,od.odid as ooid,
ous.ostus as stus,
oc.name as name,oc.fname as imgs
from Easy_orderdetail od,
Easy_produ oc ,Easy_order ous
where od.pid=oc.pid
and od.oid=ous.oid
delete from (select ous.otime as time,oc.price as price,
od.odsum as cnts ,od.odid as ooid,
ous.ostus as stus,
oc.name as name,oc.fname as imgs
from Easy_orderdetail od,
Easy_produ oc ,Easy_order ous
where od.pid=oc.pid
and od.oid=ous.oid) as yu where yu.ooid=13
if exists(select *from sysobjects where name='Aps')
drop proc Aps
go
create proc Aps(@id varchar(20))
as
begin
delete from Easy_order where oid=@id
delete from Easy_orderdetail where odid=@id
end
exec Aps 13
select *from Easy_orderdetail
--
select * from Easy_order where ouserid='123'
--
select ou1.odid as danhao,
ou1.pid as ppid,ou1.odsum as ssum,
ou2.otime as timme,ou2.ostus as stus
from Easy_orderdetail as ou1,
(select * from Easy_order where ouserid='123')as ou2
where ou1.oid=ou2.oid
--
select ou4.name as shopname,
ou4.fname as imgname,ou4.price as prices,
ou5.stus as ostuss,ou5.ppid as pids,
ou5.danhao as danhaohao,ou5.ssum as sumsum,
ou5.timme as timetime
from Easy_produ as ou4,
(select ou1.odid as danhao,
ou1.pid as ppid,ou1.odsum as ssum,
ou2.otime as timme,ou2.ostus as stus
from Easy_orderdetail as ou1,
(select * from Easy_order where ouserid='123')as ou2
where ou1.oid=ou2.oid)as ou5
where ou4.pid=ou5.ppid
--这是 客户的 所有的 购买的商品展示
if exists(select *from sysobjects where name='Ap')
drop proc Ap
go
create proc Ap(@id varchar(20))
as
begin
select ou4.name as shopname,
ou4.fname as imgname,ou4.price as prices,
ou5.stus as ostuss,ou5.ppid as pids,
ou5.danhao as danhaohao,ou5.ssum as sumsum,
ou5.timme as timetime
from Easy_produ as ou4,
(select ou1.odid as danhao,
ou1.pid as ppid,ou1.odsum as ssum,
ou2.otime as timme,ou2.ostus as stus
from Easy_orderdetail as ou1,
(select * from Easy_order where ouserid=@id)as ou2
where ou1.oid=ou2.oid)as ou5
where ou4.pid=ou5.ppid
end
exec AP '123'
--直接购买的
if exists(select * from sysobjects where name='proc_getGoodsInfo')
drop proc proc_getGoodsInfo
go
create proc proc_getGoodsInfo
(
@pid int,
@userid int,
@flag int output --返回标志
)
as
begin
update Easy_produ set stock=stock-1 where pid=@pid
declare @mon money
declare @username varchar(20)
declare @addr varchar(200)
select @mon=price from Easy_produ where pid=@pid
select @username =username ,@addr=addr from Easy_user where userid=@userid
insert Easy_order values(@userid,@username,@addr,'2000-02-08',@mon,2,2)
if(@mon>0)
set @flag=1
else
set @flag=0
end
declare @mons int
exec proc_getGoodsInfo 1,'123',@mons out
print 'you '+convert(nvarchar(20),@mons)
--加入购物车，但是 还没结算
if exists(select * from sysobjects where name='proc_getGoodsInfo')
drop proc proc_getGoodsInfo
go
create proc proc_addshopcar
(
@pid int,
@userid int,
@flag int output --返回标志
)
as
begin
update Easy_produ set stock=stock-1 where pid=@pid
declare @mon money
declare @oid int
declare @username varchar(20)
declare @addr varchar(200)
select @mon=price from Easy_produ where pid=@pid
select @username =username ,@addr=addr from Easy_user where userid=@userid
insert Easy_order values(@userid,@username,@addr,'2000-02-08',@mon,1,2)
select @oid=oid from Easy_order where ouserid=@userid
insert Easy_orderdetail values(@oid,@pid,1,@mon)
if(@mon>0)
set @flag=1
else
set @flag=0
end
update Easy_order set ocost=50000 where ouserid='123'
go
--select *from Easy_category
update Easy_produ set name='三井不动产' , price=4545 where pid=6
select *from Easy_produ
select *from Easy_orderdetail
select count(odid) as mo from Easy_orderdetail
select *from Easy_order
select Max(oid) from Easy_orderdetail
delete from Easy_user where userid='admin'
insert Easy_user values('admin','测试方便','123456','1','','1','1','1','1',1,1)
select *from Easy_user
delete top(1) from Easy_user where email<>'111@qq.com' or email<>'11@qq.com'
--加入购物车就会 生成 购物车，
if exists(select * from sysobjects where name='proc_getGoodses')
drop proc proc_getGoodses
go
create proc proc_getGoodses
(
@pid int,
@oid int,
@osum int,
@odcost int,--根据商品的 单价得出来的
@userid int,
@flag int output --返回标志
)
as
begin
declare @er int =0
insert Easy_orderdetail values(@pid,@oid,@osum,@odcost)
set @er+=@@ERROR
declare @username varchar(20)
declare @addr varchar(200)
select @username =username ,@addr=addr from Easy_user where userid=@userid
insert Easy_order values(@userid,@username,@addr,GETDATE(),@odcost,1,2)
set @er+=@@ERROR
if(@er<>0)
set @flag=1
else
set @flag=0
end
select top(1)* from Easy_order order by oid desc
select * from Easy_order where oid=(select MAX(oid) from Easy_order)
public boolean getproc(String proc_name ,Object[] objs)throws SQLException{sqlcon=GetConnection();CallableStatement cst=null;String proc="{call "+proc_name+"(";for(int i=0;i<objs.length;i++){proc+="?,";//把 proc 补充 完整}proc=proc.substring(0,proc.length()-1)+")}";cst=sqlcon.prepareCall(proc);for(int i=0;i<objs.length;i++){if(objs[i]!=null){// 不为空， 就是 输入 参数cst.setObject(i+1, objs[i]);//cst是从 1开始赋值的，}else{cst.registerOutParameter(i+1, java.sql.Types.VARCHAR);}}cst.executeUpdate();//已经执行， 没有把 返回值 取出来int oo=0;for(int i=0;i<objs.length;i++){if(objs[i]==null){// 为空， 就是 输出 参数objs[i]=cst.getInt(i+1); //cst是从 1开始赋值的， objs 从 0开始}}return true;}/* Object[] objs = { userid, pwd, null };DB db = new DB();db.getproc("proc_ne", objs);if (!objs[2].toString().equals("0")) {out.print("欢迎你" + userid);System.out.println("执行成功");} else {out.print("你的 登录不正确" + userid);System.out.println("失败,密码不正确");}*///分页public static int getrowall() throws SQLException{String sql="select COUNT(1)id from tb_odetail";DBM db = new DBM();Result re=db.GetResult(sql, null);int ii=0;ii=Integer.parseInt( re.getRows()[0].get("id").toString());return ii;}public List<orderde> xianshi(int no ,int size) throws SQLException{int row=getrowall();int totalpage=(row%size==0)?row/size:row/size+1; String sql="select top "+size+"* " +"from tb_odetail where id not " +"in(select top ("+size*(no-1)+")id from " +"tb_odetail order by id asc) " +"order by id asc"; DBM db= new DBM(); List<orderde> mlist=new ArrayList<orderde>(); try { Result re =db.GetResult(sql); Map[] map=re.getRows();for(Map mm:map){int id=Integer.parseInt(mm.get("id").toString());int orderid=Integer.parseInt(mm.get("orderid").toString());String shopname=mm.get("shopname").toString();double price=Double.parseDouble(mm.get("price").toString());int shopsum=Integer.parseInt(mm.get("shopsum").toString());orderde od=new orderde(id,orderid,shopname, price, shopsum); mlist.add(od);}} catch (Exception e) {e.printStackTrace();}return mlist;}}
扫一扫
967
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
