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&¶ms.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);
