JDBC2

_imooc_JDBC之对面的女孩看过来

20170316 JDBC
JDBC：Java DateBase connectivity （JAVA数据库连接）
2.使用详情
明确目的：需求
 增删改查
指导思想：概设详设
工具：Mysql,MyEclipse,Navicat(数据库管理tool)
编码
测试

2.JDBC变成步骤
·加载驱动程序：Class.forName(driverClass)[在工程下建立lib文件夹，把驱动包拷入 Class.forName(X)反射技术，通过类名反射加载]
 加载Mysql驱动：Class.forName("com.mysql.jdbc.Driver");
 加载Oracle驱动：Class.forName("oracle.jdbc.driver.OracleDriver");
·获得数据库连接：
 DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/imooc","root","root");
·创建Statement对象:conn.createStatement();

上View-Controller-Model-DB
开发方式：自上而下的开发 或者自下而上

DB：加载驱动，获得数据库连接
Model:1定义表中的项目为私有，加get,set方法
      2.DAO:增删改查
public class GoddessDao {

 public void addGoddess(Goddess g) throws Exception{
  Connection conn=DBUtil.getConnection();
  String sql="" +
    "insert into imooc_goddess" +
    "(user_name,sex,age,birthday,email,mobile," +
    "create_user,create_date,update_user,update_date,isdel)" +
    "values(" +
    "?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
  PreparedStatement ptmt=conn.prepareStatement(sql);
  
  ptmt.setString(1, g.getUser_name());
  ptmt.setInt(2, g.getSex());
  ptmt.setInt(3, g.getAge());
  ptmt.setDate(4, new Date(g.getBirthday().getTime()));
  ptmt.setString(5, g.getEmail());
  ptmt.setString(6, g.getMobile());
  ptmt.setString(7, g.getCreate_user());
  ptmt.setString(8, g.getUpdate_user());
  ptmt.setInt(9, g.getIsdel());
  ptmt.execute();
 }
 public void updateGoddess(Goddess g) throws SQLException{
  Connection conn=DBUtil.getConnection();
  String sql="" +
    " update imooc_goddess " +
    " set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, " +
    " update_user=?,update_date=current_date(),isdel=? " +
    " where id=? ";
  PreparedStatement ptmt=conn.prepareStatement(sql);
  
  ptmt.setString(1, g.getUser_name());
  ptmt.setInt(2, g.getSex());
  ptmt.setInt(3, g.getAge());
  ptmt.setDate(4, new Date(g.getBirthday().getTime()));
  ptmt.setString(5, g.getEmail());
  ptmt.setString(6, g.getMobile());
  ptmt.setString(7, g.getUpdate_user());
  ptmt.setInt(8, g.getIsdel());
  ptmt.setInt(9, g.getId());
  ptmt.execute();
 }
 
 public void delGoddess(Integer id) throws SQLException{
  Connection conn=DBUtil.getConnection();
  String sql="" +
    " delete from imooc_goddess " +
    " where id=? ";
  PreparedStatement ptmt=conn.prepareStatement(sql);
  
  ptmt.setInt(1, id);
  ptmt.execute();
 }
 public List<Goddess> query() throws Exception{
  List<Goddess> result=new ArrayList<Goddess>();
  
  Connection conn=DBUtil.getConnection();
  StringBuilder sb=new StringBuilder();
  sb.append("select id,user_name,age from imooc_goddess  ");
  
  PreparedStatement ptmt=conn.prepareStatement(sb.toString());
  
  ResultSet rs=ptmt.executeQuery();
  
  Goddess g=null;
  while(rs.next()){
   g=new Goddess();
   g.setId(rs.getInt("id"));
   g.setUser_name(rs.getString("user_name"));
   g.setAge(rs.getInt("age"));
   result.add(g);
  }
  return result;
 }
 public List<Goddess> query(String name,String mobile,String email) throws Exception{
  List<Goddess> result=new ArrayList<Goddess>();
  
  Connection conn=DBUtil.getConnection();
  StringBuilder sb=new StringBuilder();
  sb.append("select * from imooc_goddess  ");
  
  sb.append(" where user_name like ? and mobile like ? and email like ?");
  
  PreparedStatement ptmt=conn.prepareStatement(sb.toString());
  ptmt.setString(1, "%"+name+"%");
  ptmt.setString(2, "%"+mobile+"%");
  ptmt.setString(3, "%"+email+"%");
  System.out.println(sb.toString());
  ResultSet rs=ptmt.executeQuery();
  
  Goddess g=null;
  while(rs.next()){
   g=new Goddess();
   g.setId(rs.getInt("id"));
   g.setUser_name(rs.getString("user_name"));
   g.setAge(rs.getInt("age"));
   g.setSex(rs.getInt("sex"));
   g.setBirthday(rs.getDate("birthday"));
   g.setEmail(rs.getString("email"));
   g.setMobile(rs.getString("mobile"));
   g.setCreate_date(rs.getDate("create_date"));
   g.setCreate_user(rs.getString("create_user"));
   g.setUpdate_date(rs.getDate("update_date"));
   g.setUpdate_user(rs.getString("update_user"));
   g.setIsdel(rs.getInt("isdel"));
   
   result.add(g);
  }
  return result;
 }
 public List<Goddess> query(List<Map<String, Object>> params) throws Exception{
  List<Goddess> result=new ArrayList<Goddess>();
  
  Connection conn=DBUtil.getConnection();
  StringBuilder sb=new StringBuilder();
  sb.append("select * from imooc_goddess where 1=1 ");
  
  if(params!=null&&params.size()>0){
   for (int i = 0; i < params.size(); i++) {
    Map<String, Object> map=params.get(i);
    sb.append(" and  "+map.get("name")+" "+map.get("rela")+" "+map.get("value")+" ");
   }
  }
  
  PreparedStatement ptmt=conn.prepareStatement(sb.toString());
  
  System.out.println(sb.toString());
  ResultSet rs=ptmt.executeQuery();
  
  Goddess g=null;
  while(rs.next()){
   g=new Goddess();
   g.setId(rs.getInt("id"));
   g.setUser_name(rs.getString("user_name"));
   g.setAge(rs.getInt("age"));
   g.setSex(rs.getInt("sex"));
   g.setBirthday(rs.getDate("birthday"));
   g.setEmail(rs.getString("email"));
   g.setMobile(rs.getString("mobile"));
   g.setCreate_date(rs.getDate("create_date"));
   g.setCreate_user(rs.getString("create_user"));
   g.setUpdate_date(rs.getDate("update_date"));
   g.setUpdate_user(rs.getString("update_user"));
   g.setIsdel(rs.getInt("isdel"));
   
   result.add(g);
  }
  return result;
 }
 public Goddess get(Integer id) throws SQLException{
  Goddess g=null;
  Connection conn=DBUtil.getConnection();
  String sql="" +
    " select * from imooc_goddess " +
    " where id=? ";
  PreparedStatement ptmt=conn.prepareStatement(sql);
  
  ptmt.setInt(1, id);
  ResultSet rs=ptmt.executeQuery();
  while(rs.next()){
   g=new Goddess();
   g.setId(rs.getInt("id"));
   g.setUser_name(rs.getString("user_name"));
   g.setAge(rs.getInt("age"));
   g.setSex(rs.getInt("sex"));
   g.setBirthday(rs.getDate("birthday"));
   g.setEmail(rs.getString("email"));
   g.setMobile(rs.getString("mobile"));
   g.setCreate_date(rs.getDate("create_date"));
   g.setCreate_user(rs.getString("create_user"));
   g.setUpdate_date(rs.getDate("update_date"));
   g.setUpdate_user(rs.getString("update_user"));
   g.setIsdel(rs.getInt("isdel"));
  }
  return g;
 }
}

--------------------------------------------------
Map<String,Object> param = new HashMap<String,Object>();
param.put("name","user_name");
param.put("rela","like");
param.put("value","'%小美%'");//小美属于字符串 需要加''
params.add(param);
param= new HashMap<String,Object>();
param.put("name","mobile");
param.put("rela","=");
param.put("value","%'1872222225'%");
params.add(param);
GoddessDao g= new GoddessDao();
List<Goddess> result=g.query(params);