DAO层
package cn.happy.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class BaseDao {
Connection con = null;
PreparedStatement ps = null;
public ResultSet rs=null;
private static final String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String url = "jdbc:sqlserver://localhost:1433;DatabaseName=PetShop";
private static final String username = "sa";
private static final String pwd = "1";
public Connection getCon()throws Exception{
Class.forName(driver);
if(con==null||con.isClosed()){
con = DriverManager.getConnection(url, username, pwd);
}
return con;
}
//执行查询
public ResultSet executeQuery(String sql,Object... obje) throws Exception{
con=getCon();
ps=con.prepareStatement(sql);
for(int i=0;i<obje.length;i++){
ps.setObject(i+1, obje[i]);
}
rs=ps.executeQuery();
return rs;
}
//测试修改
//执行修改
public int execteUpdate(String sql,Object...object) throws Exception{
con=getCon();
ps=con.prepareStatement(sql);
for(int i=0;i<object.length;i++){
ps.setObject(i+1, object[i]);
}
int count=ps.executeUpdate();
return count;
}
//单测增加
//执行增加
public int execteadd(String sql,Object...object) throws Exception{
con=getCon();
ps=con.prepareStatement(sql);
ps.executeUpdate();
for(int i=0;i<object.length;i++){
ps.setObject(i+1, object[i]);
}
int count=ps.executeUpdate();
return count;
}
//执行删除
//执行删除
public int exectedelete(String sql,Object...object) throws Exception{
con=getCon();
ps=con.prepareStatement(sql);
ps.executeUpdate();
for(int i=0;i<object.length;i++){
ps.setObject(i+1, object[i]);
}
int count=ps.executeUpdate();
return count;
}
public void closeAll() throws Exception{
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}
}
//
}
package cn.happy.dao; import java.util.List; import cn.happy.entity.Pet; import cn.happy.entity.PetOwner; public interface Petdao { public List<Pet> select1() throws Exception; public List<Pet> se1() throws Exception; public boolean up(int id) throws Exception; public List<Pet> poselect(int id) throws Exception; public List<Pet> selectquan() throws Exception; Pet getPetById(int id) throws Exception; int buyPet(int petid,int ownerid) throws Exception; int sellPet(int storeid,int petid) throws Exception; public List<Pet> getOwnerPet(int ownerid) throws Exception ; }package cn.happy.dao; import java.util.List; import cn.happy.entity.Pet; import cn.happy.entity.PetOwner; public interface PetOwnerdao { //显示宠物主人名称 public List<PetOwner> select2() throws Exception; public PetOwner DL(String name1) throws Exception; public boolean update1(int money,int id) throws Exception; public List<PetOwner> getnamebyid()throws Exception; public int getIdByName(String name) throws Exception ; public int updateByName(int money,String name) throws Exception ; public int updateMoneyByName(int money, String name) throws Exception ; }
package cn.happy.dao; import java.util.List; import cn.happy.entity.PetStore; public interface PetStoredao { public List<PetStore> select3() throws Exception; public PetStore DL(String name1) throws Exception; }
package cn.happy.dao; public interface Accountdao { public boolean add(int id)throws Exception; int addAccount(int dealtype,int petid,int ownerid) throws Exception; int addSellAccount(int dealtype,int petid,int ownerid,int storeid) throws Exception; }
DAO Impl package cn.happy.Impl; import cn.happy.dao.Accountdao; import cn.happy.dao.BaseDao; public class AccountdaoImpl extends BaseDao implements Accountdao { @Override //添加一条台账信息 //宠物主人登录购买的宠物 public boolean add(int id) throws Exception { boolean flag=false; String sql="insert into Account values(?,?,?)"; int count=this.execteUpdate(sql,id); if(count>0){ flag=true; } return flag; } @Override public int addAccount(int dealtype, int petid, int ownerid) throws Exception { String sql="insert into account (DEAL_TYPE,pet_id,SELLER_ID) values(?,?,?)"; Object[] objs={dealtype,petid,ownerid}; int count = execteUpdate(sql, objs); return count; } @Override public int addSellAccount(int dealtype, int petid, int ownerid, int storeid) throws Exception { String sql="insert into account (DEAL_TYPE,pet_id,SELLER_ID,BUYER_ID) values(?,?,?,?)"; Object[] objs={dealtype,petid,ownerid,storeid}; int count = execteUpdate(sql, objs); return count; } }
package cn.happy.Impl; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.happy.dao.BaseDao; import cn.happy.dao.Petdao; import cn.happy.entity.Account; import cn.happy.entity.Pet; import cn.happy.entity.PetOwner; public class PetdaoImpl extends BaseDao implements Petdao { @Override //显示宠物表的所有信息 public List<Pet> select1() throws Exception { List<Pet> list=new ArrayList<Pet>(); String sql="select * from Pet"; ResultSet rs=this.executeQuery(sql); if(rs!=null){ while(rs.next()){ Pet pet=new Pet(); pet.setName(rs.getString("name")); pet.setId(rs.getInt("id")); list.add(pet); } } return list; } @Override //查询表的库存宠物 public List<Pet> se1() throws Exception { List<Pet> list=new ArrayList<Pet>(); String sql="select a.id, a.name,a.typename,b.price from Pet a,Account b where a.id=b.pet_id"; //String sql="select id,name,typename,price from Pet "; ResultSet rs=this.executeQuery(sql); if(rs!=null){ while(rs.next()){ Pet owner=new Pet(); owner.setId(rs.getInt("id")); owner.setName(rs.getString("name")); owner.setTypename(rs.getString("typename")); owner.getAccount().setPrice(rs.getInt("price")); list.add(owner); } } return list; } @Override public List<Pet> poselect(int id) throws Exception { List<Pet> list=new ArrayList<Pet>(); String sql="select * from Pet where Owner_id=?"; ResultSet rs=this.executeQuery(sql,id); if(rs!=null){ while(rs.next()){ Pet pet=new Pet(); pet.setId(rs.getInt("id")); pet.setName(rs.getString("name")); pet.setTypename(rs.getString("typename")); list.add(pet); } } return list; } @Override public boolean up(int id) throws Exception { boolean flag=false; String sql="update Pet set Owner_id=?"; int count=this.execteUpdate(sql,id); if(count>0){ flag=true; } return flag; } @Override public Pet getPetById(int id) throws Exception { String sql="select name,typename from pet where id=?"; Object[] objs={id}; ResultSet rs = executeQuery(sql, objs); Pet pet=new Pet(); if(rs.next()){ pet.setName(rs.getString("name")); pet.setTypename(rs.getString("typename")); System.out.println("宠物名字叫:"+pet.getName()+"\t宠物类别是："+pet.getTypename()); } return pet; } @Override public int buyPet(int petid, int ownerid) throws Exception { String sql="update pet set owner_id=? where id=?"; Object[] objs={ownerid,petid}; int count = execteUpdate(sql, objs); return count; } @Override public int sellPet(int storeid, int petid) throws Exception { String sql="update pet set owner_id=?,store_id=? where id=?"; Object[] objs={null,storeid,petid}; int count = execteUpdate(sql, objs); return count; } @Override public List<Pet> selectquan() throws Exception { List<Pet> list=new ArrayList<Pet>(); String sql="select id,name,typename from Pet a,Account b where a.id=b.pet_id"; ResultSet rs=this.executeQuery(sql); if(rs!=null){ while(rs.next()){ Pet owner=new Pet(); owner.setId(rs.getInt("id")); owner.setName(rs.getString("name")); owner.setTypename(rs.getString("typename")); owner.getAccount().setPrice(rs.getInt("price")); list.add(owner); } } return list; } @Override public List<Pet> getOwnerPet(int ownerid) throws Exception { List<Pet> list=new ArrayList<Pet>(); String sql="select pet.id,pet.name,typename from pet,petowner where pet.owner_id=petowner.id and petowner.id=?"; Object[] objs={ownerid}; ResultSet rs =executeQuery(sql,objs); if(rs!=null){ while(rs.next()){ Pet pet=new Pet(); pet.setId(rs.getInt("id")); pet.setName(rs.getString("name")); pet.setTypename(rs.getString("typename")); list.add(pet); } } return list; } }
package cn.happy.Impl; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.happy.dao.BaseDao; import cn.happy.dao.PetStoredao; import cn.happy.entity.Pet; import cn.happy.entity.PetOwner; import cn.happy.entity.PetStore; public class PetStoredaoImpl extends BaseDao implements PetStoredao { @Override //显示宠物商店信息 public List<PetStore> select3() throws Exception { List<PetStore> list=new ArrayList<PetStore>(); String sql="select * from PetStore"; ResultSet rs=this.executeQuery(sql); if(rs!=null){ while(rs.next()){ PetStore ps=new PetStore(); ps.setName(rs.getString("name")); ps.setId(rs.getInt("id")); list.add(ps); } } return list; } //宠物商店登录 @Override public PetStore DL(String name1) throws Exception { String sql="select * from PetStore where name=?"; ResultSet rs=this.executeQuery(sql,name1); PetStore pse=null; if(rs!=null){ if(rs.next()){ pse=new PetStore(); pse.setName(rs.getString("name")); pse.setPassword(rs.getString("password")); pse.setBalance(rs.getInt("Balance")); } } return pse; } }
package cn.happy.Impl; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.happy.dao.BaseDao; import cn.happy.dao.PetOwnerdao; import cn.happy.entity.Pet; import cn.happy.entity.PetOwner; public class PetOwnerdaoImpl extends BaseDao implements PetOwnerdao { @Override //显示主人表所有信息 public List<PetOwner> select2() throws Exception { List<PetOwner> list=new ArrayList<PetOwner>(); String sql="select * from PetOwner"; ResultSet rs=this.executeQuery(sql); if(rs!=null){ while(rs.next()){ PetOwner owner=new PetOwner(); owner.setName(rs.getString("name")); owner.setId(rs.getInt("id")); owner.setMoney(rs.getInt("money")); list.add(owner); } } return list; } //宠物主人登录 @Override public PetOwner DL(String name1) throws Exception { String sql="select * from PetOwner where name=?"; ResultSet rs=this.executeQuery(sql,name1); PetOwner por=null; if(rs!=null){ if(rs.next()){ por=new PetOwner(); por.setName(rs.getString("name")); por.setPassword(rs.getString("password")); por.setMoney(rs.getInt("money")); } } return por; } @Override public boolean update1(int money,int id) throws Exception { boolean flag=false; String sql="update PetOwner set money=? where id=?"; int count=this.execteUpdate(sql,money,id); if(count>0){ flag=true; } return flag; } @Override public List<PetOwner> getnamebyid() throws Exception { List<PetOwner> list=new ArrayList<PetOwner>(); String sql="select * from PetOwner where name=?"; ResultSet rs=this.executeQuery(sql); if(rs!=null){ while(rs.next()){ PetOwner owner=new PetOwner(); owner.setName(rs.getString("name")); owner.setId(rs.getInt("id")); list.add(owner); } } return list; } @Override public int getIdByName(String name) throws Exception { String sql="select id from petowner where name=?"; Object [] objs={name}; ResultSet rs =executeQuery(sql,objs); PetOwner po=new PetOwner(); if(rs.next()){ po.setId(rs.getInt("id")); } return po.getId(); } @Override public int updateByName(int money, String name) throws Exception { String sql="update petowner set money=? where name=?"; Object [] objs={money,name}; int count = execteUpdate(sql, objs); return count; } @Override public int updateMoneyByName(int money, String name) throws Exception { String sql="update petowner set money=? where name=?"; Object [] objs={money,name}; int count = execteUpdate(sql, objs); return count; } }
service package cn.happy.service; import java.security.acl.Owner; import java.util.List; import java.util.Scanner; import cn.happy.Impl.AccountdaoImpl; import cn.happy.Impl.PetOwnerdaoImpl; import cn.happy.Impl.PetStoredaoImpl; import cn.happy.Impl.PetdaoImpl; import cn.happy.dao.Accountdao; import cn.happy.dao.PetOwnerdao; import cn.happy.dao.PetStoredao; import cn.happy.dao.Petdao; import cn.happy.entity.Pet; import cn.happy.entity.PetOwner; import cn.happy.entity.PetStore; public class Petservice { Scanner input=new Scanner(System.in); Petdao pdao=new PetdaoImpl(); PetOwnerdao odao=new PetOwnerdaoImpl(); PetStoredao sdao=new PetStoredaoImpl(); Accountdao adao=new AccountdaoImpl(); PetOwner po=new PetOwner(); PetStore ps=new PetStore(); public void show() throws Exception{ System.out.println("wonderland醒来，所有宠物从sqlserver中醒来"); System.out.println("*********************************************************"); List<Pet> p1 = pdao.select1(); System.out.println("序号\t宠物名称"); for(Pet item:p1){ System.out.print(item.getId()+"\t"); System.out.println(item.getName()); } System.out.println("所有宠物主人从sqlserver中醒来"); System.out.println("***********************************************************"); po=new PetOwner(); List<PetOwner> p2=odao.select2(); System.out.println("序号\t主人姓名"); for(PetOwner item1:p2){ System.out.print(item1.getId()+"\t"); System.out.println(item1.getName()); } System.out.println("所有宠物商店从sqlserver中醒来"); System.out.println("***********************************************************"); List<PetStore> p3=sdao.select3(); System.out.println("序号\t宠物商店名称"); for(PetStore item2:p3){ System.out.print(item2.getId()+"\t"); System.out.println(item2.getName()); } System.out.println("请选择输入登录模式，输入1为宠物主人登录，输入2位宠物商店登录。"); int isLogin=input.nextInt(); if(isLogin==1){ //宠物主人登录 System.out.println("请先登录，输入主人的姓名："); String name=input.next(); po=odao.DL(name); System.out.println("请输入密码:"); String pwd = input.next(); if(po!=null){ if (pwd.equals(po.getPassword().trim())) { System.out.println("-------------恭喜您，登录成功-----------------"); System.out.println("-----------------您的基本信息--------------------------"); System.out.println("姓名："+po.getName()); System.out.println("元宝数："+po.getMoney()); System.out.println("登录成功，您可以购买和卖出宠物，如果你想购买宠物请输入1，如果你想卖出宠物请输入2"); System.out.println("1.购买宠物"); System.out.println("2.卖出宠物"); int t=input.nextInt(); switch(t){ case 1: System.out.println("------------------请输入选择要购买范围：只输入选择项序号----------------------------"); System.out.println("1.购买库存宠物"); System.out.println("2.购买新培育宠物"); int m=input.nextInt(); if(m==1){ //主人购买宠物 System.out.println("----------------以下是库存列表----------------"); System.out.println("宠物序号\t宠物名称\t宠物类型\t 元宝数"); List<Pet> p11=pdao.se1(); //循环遍历Pet 和account 表 for(Pet item:p11){ System.out.println(item.getId()+"\t"+item.getName()+"\t"+item.getTypename()+" "+item.getAccount().getPrice()); } System.out.println("----------------------请选择要购买哪一个宠物，并输出项的序号----------------"); int i=input.nextInt();//输入购买宠物的序号 //增加台账信息，并删除宠物表记录 int masid=getIdByName(); adao.addAccount(isLogin, i, masid); buyPet(i, masid); updateByName(); System.out.println("----------------------台账正确插入一天信息----------------------"); System.exit(0); }else if(m==2){ System.out.println("---------------以下是购买新培育的宠物-----------------"); System.out.println("宠物序号\t宠物名称\t宠物类型\t 元宝数"); List<Pet> p11=pdao.se1(); //循环遍历Pet 和account 表 for(Pet item:p11){ System.out.println(item.getId()+"\t"+item.getName()+"\t"+item.getTypename()+" "+item.getAccount().getPrice()); } }else{ System.out.println("序号选错了"); } break; case 2: //主人卖出宠 System.out.println("--------------------我的宠物列表------------------------"); System.out.println("序号\t宠物名称\t类型"); int ownerid=getIdByName(); getPetInfoList(ownerid); System.out.println("-----------请选择你要出售的宠物编号--------------------------"); int i=input.nextInt(); System.out.println("--------------------------您要卖出的宠物信息如下----------------------"); getPetById(i); System.out.println("确认是否卖出，y代表卖出 ， n代表不卖"); String sure=input.next(); if(sure.equals("y")){ System.out.println("------下面是现有的宠物商店，请你选择要卖给的商家序号-----------"); System.out.println("序号\t宠物商店名称"); List<PetStore> pse=sdao.select3(); for(PetStore item3:pse){ System.out.print(item3.getId()+"\t"); System.out.println(item3.getName()); } sdao.select3(); int storeid=input.nextInt(); pdao.sellPet(storeid, i); updateMoneyByName(); adao.addSellAccount(t, i, ownerid, storeid); System.out.println("台账正确插入一条信息"); } else if(sure.equals("n")){ System.out.println("不卖就别进,一天天的"); return ; } break; } }else{ System.out.println("登录失败！！！！！"); } }else{ System.out.println("沒有這个用户"); } } else if(isLogin==2){ //宠物商店登录 System.out.println("请先登录，请输入宠物商店名字"); String name=input.next(); ps=sdao.DL(name); System.out.println(ps.getPassword()); System.out.println("请输入密码:"); String pwd = input.next(); if (ps!=null) { if(pwd.equals(ps.getPassword().trim())){ System.out.println("-------------恭喜您，登录成功-----------------"); System.out.println("-----------------宠物商店的基本信息--------------------------"); System.out.println("名字："+ps.getName()); System.out.println("元宝数："+ps.getBalance()); System.out.println("登录成功，您可以进行如下操作："); System.out.println("1.购买宠物"); System.out.println("2.卖出宠物"); System.out.println("3.培育宠物"); System.out.println("4.查询代售宠物"); System.out.println("5.查询商店结余"); System.out.println("6.查看商店账目"); System.out.println("7.开宠物商店"); System.out.println("请根据需要执行的操作。请选择序号输入，退出请输入0"); }else{ System.out.println("密码错误，登录失败"); } }else{ System.out.println("没有这个用户"); } } } public int getIdByName() throws Exception{ int ownerid= odao.getIdByName(po.getName()); return ownerid; } public void updateByName() throws Exception{ odao.updateByName(po.getMoney()-10, po.getName()); } public void updateMoneyByName() throws Exception{ odao.updateByName(po.getMoney()+10, po.getName()); } public void addAccount(int dealtype,int petid,int ownerid) throws Exception{ adao.addAccount(dealtype,petid,ownerid); } public void addSellAccount(int dealtype,int petid,int ownerid,int storeid) throws Exception{ adao.addSellAccount(dealtype, petid, ownerid, storeid); } public void getPetById(int id) throws Exception{ pdao.getPetById(id); } public void buyPet(int petid,int ownerid) throws Exception{ pdao.buyPet(petid, ownerid); } public void sellPet(int storeid,int petid) throws Exception { pdao.sellPet(storeid, petid); } public void getPetInfoList(int ownerid) throws Exception{ List<Pet> petList=pdao.getOwnerPet(ownerid); for (Pet pet : petList) { System.out.print(pet.getId()+"\t"+pet.getName()+"\t"+pet.getTypename()); System.out.println(); } } }
版权声明：本文为qq_36074043原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。