Java-笔记一(对数据的增、删、改、查训练)

1、需先建立四个类（对sql server 操作）
1.Character类(对象类）
2.DBManager类（显示层）
3.Controller类（业务处理层）
4.Text（显示层）
第一步， Character类创建：

package com.xnzy.demo;

public class Character {
     private int id;//武将编号
     private String name;//武将名称
     private int hp;//武将血量
     private String skill;//武将技能
     private int emotion;//情义值
     private String area;//所属地
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getHp() {
        return hp;
    }
    public void setHp(int hp) {
        this.hp = hp;
    }
    public String getSkill() {
        return skill;
    }
    public void setSkill(String skill) {
        this.skill = skill;
    }
    public int getEmotion() {
        return emotion;
    }
    public void setEmotion(int emotion) {
        this.emotion = emotion;
    }
    public String getArea() {
        return area;
    }
    public void setArea(String area) {
        this.area = area;
    }
    //无参构造函数
    public Character(){}
    //有参构造函数
    public Character(int id,String name,int hp,String skill,int emotion,String area)
    {
        this.id=id;
        this.name=name;
        this.hp=hp;
        this.skill=skill;
        this.emotion=emotion;
        this.area=area;
    }
}

第二步，DBManager类创建

package com.xnzy.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBManager {
       private static final String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";//获取驱动地址
       private static final String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=SanGuoSha";//获得数据库路径，DataBaseName=数据库名
       private static final String user="sa";//数据库用户名
       private static final String pwd="123456";//数据库密码
       private static Connection conn=null;//创建Connection对象

       public static Connection getcon()
       {
           try {
            Class.forName(driver);//加载驱动
            conn=DriverManager.getConnection(url, user, pwd);//连接数据库
        } catch (Exception e) {
            // TODO: handle exception
        }
           return conn;
       }
       //关闭连接
       public static void closecon(ResultSet rs,PreparedStatement pst,Connection conn)
       {
           try {
              if(rs!=null)
              {
                  rs.close();
                  rs=null;
              }
              if(pst!=null)
              {
                  pst.close();
                  pst=null;
              }
              if(conn!=null)
              {
                  conn.close();
                  conn=null;
              }
        } catch (Exception e) {
            // TODO: handle exception
        }
       }
}

第三步，Controller类创建
1.查询操作

package com.xnzy.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Controller {
      private Connection conn=null;
      private ResultSet rs=null;
      private PreparedStatement pst=null;
      private List<Character> list=null;
      /***
       * 查询
       * @param sql
       * @return
       */
      public  List<Character> Selecthero(String sql)
      {
          try {
              //调用JDBC公共类DBManager的getcon()获取Connection对象
            conn=DBManager.getcon();
            pst=conn.prepareStatement(sql);     
            list=new ArrayList<Character>();
            rs=pst.executeQuery();
            while(rs.next())
            {
                Character c=new Character();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setHp(rs.getInt("hp"));
                c.setSkill(rs.getString("skill"));
                c.setEmotion(rs.getInt("emotion"));
                c.setArea(rs.getString("area"));
                list.add(c);
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
          finally{
                DBManager.closecon(rs, pst, conn);
          }
          return list;
      }

}

2.在Text中调用

package com.xnzy.demo;

import java.util.List;

public class Text {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
       Controller bll=new Controller();
       String sql="select * from Wujiang";
       List<Character> list=bll.Selecthero(sql);
       System.out.println("编号\t姓名\t血量\t技能\t情义值\t所属地");
       for(int i=0;i<list.size();i++)
       {                    System.out.println(list.get(i).getId()+"\t"+list.get(i).getName()+"\t"+list.get(i).getHp()+"\t"+list.get(i).getSkill()+"\t"+list.get(i).getEmotion()+"\t"+list.get(i).getArea());
       }
    }

}

1.添加操作

package com.xnzy.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import sun.security.pkcs11.Secmod.DbMode;

public class Controller {
      private Connection conn=null;
      private ResultSet rs=null;
      private PreparedStatement pst=null;

      public int insertHero(Character c)
      {
          int row=0;
          try {
            conn=DBManager.getcon();//加载驱动
            String sql="insert into Wujiang values(?,?,?,?,?,?)";
            pst=conn.prepareStatement(sql);
            pst.setInt(1, c.getId());
            pst.setString(2, c.getName());
            pst.setInt(3, c.getHp());
            pst.setString(4, c.getSkill());
            pst.setInt(5, c.getEmotion());
            pst.setString(6, c.getArea());
            row=pst.executeUpdate();
            if(row>0)
            {
                System.out.println("添加成功！");
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
          finally{
              DBManager.closecon(null, pst, conn);
          }
          return row;
      }

}

2.在Text中操作

package com.xnzy.demo;

public class Text {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
       Controller bll=new Controller();
       Character c=new Character(11,"王五",4,"忠厚",88,"蜀国");
       bll.insertHero(c);

}
}

删除和更改与添加除了sql语句不一样,其余的大体上一致，就不一一阐述了。