所谓的MVC,就是指Model,View,Control三个层,java中要求是视图和后台分离,而控制层就是连接其余两层的纽带,昨天已经开始学习模型层的建立了,今天把模型层给完成,具体呢就是实现数据库的增删改查。
import java.util.Date;
public class Goddess {
<span style="white-space:pre"> </span>private Integer id;
<span style="white-space:pre"> </span>private String user_name;
<span style="white-space:pre"> </span>private Integer sex;
<span style="white-space:pre"> </span>private Integer age;
<span style="white-space:pre"> </span>private Date birthday;
<span style="white-space:pre"> </span>private String email;
<span style="white-space:pre"> </span>private String mobile;
<span style="white-space:pre"> </span>private String create_user;
<span style="white-space:pre"> </span>private String update_user;
<span style="white-space:pre"> </span>private Date creat_date;
<span style="white-space:pre"> </span>private Date update_date;
<span style="white-space:pre"> </span>private Integer isdel;
<span style="white-space:pre"> </span>public Integer getId() {
<span style="white-space:pre"> </span>return id;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setId(Integer id) {
<span style="white-space:pre"> </span>this.id = id;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public String getUser_name() {
<span style="white-space:pre"> </span>return user_name;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setUser_name(String user_name) {
<span style="white-space:pre"> </span>this.user_name = user_name;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public Integer getSex() {
<span style="white-space:pre"> </span>return sex;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setSex(Integer sex) {
<span style="white-space:pre"> </span>this.sex = sex;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public Integer getAge() {
<span style="white-space:pre"> </span>return age;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setAge(Integer age) {
<span style="white-space:pre"> </span>this.age = age;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public Date getBirthday() {
<span style="white-space:pre"> </span>return birthday;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setBirthday(Date birthday) {
<span style="white-space:pre"> </span>this.birthday = birthday;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public String getEmail() {
<span style="white-space:pre"> </span>return email;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setEmail(String email) {
<span style="white-space:pre"> </span>this.email = email;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public String getMobile() {
<span style="white-space:pre"> </span>return mobile;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setMobile(String mobile) {
<span style="white-space:pre"> </span>this.mobile = mobile;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public String getCreate_user() {
<span style="white-space:pre"> </span>return create_user;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setCreate_user(String create_user) {
<span style="white-space:pre"> </span>this.create_user = create_user;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public String getUpdate_user() {
<span style="white-space:pre"> </span>return update_user;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setUpdate_user(String update_user) {
<span style="white-space:pre"> </span>this.update_user = update_user;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public Date getCreat_date() {
<span style="white-space:pre"> </span>return creat_date;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setCreat_date(Date creat_date) {
<span style="white-space:pre"> </span>this.creat_date = creat_date;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public Date getUpdate_date() {
<span style="white-space:pre"> </span>return update_date;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setUpdate_date(Date update_date) {
<span style="white-space:pre"> </span>this.update_date = update_date;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public Integer getIsdel() {
<span style="white-space:pre"> </span>return isdel;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setIsdel(Integer isdel) {
<span style="white-space:pre"> </span>this.isdel = isdel;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public String toString() {
<span style="white-space:pre"> </span>return "Goddess [id=" + id + ", user_name=" + user_name + ", sex="
<span style="white-space:pre"> </span>+ sex + ", age=" + age + ", birthday=" + birthday + ", email="
<span style="white-space:pre"> </span>+ email + ", mobile=" + mobile + ", create_user=" + create_user
<span style="white-space:pre"> </span>+ ", update_user=" + update_user + ", creat_date=" + creat_date
<span style="white-space:pre"> </span>+ ", update_date=" + update_date + ", isdel=" + isdel + "]";
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
}
这个可以看成是我们数据库的映射内容,里面包括的是记录的各个字段,set和get方法可以自动生成,右键—source—generate getters and setters;最后还重写了tostring方法,作用是我们在查询的时候能够看到转化成字符串之后的结果;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import jdbc_chuan.Jdbc_util;
import jdbc_chuan.model.Goddess;
public class GoddessDao {
public void addGodness(Goddess g) throws SQLException{
Connection conn=Jdbc_util.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 pt=conn.prepareStatement(sql);
pt.setString(1,g.getUser_name());
pt.setInt(2, g.getSex());
pt.setInt(3,g.getAge());
pt.setDate(4,new Date(g.getBirthday().getTime()));
pt.setString(5,g.getEmail());
pt.setString(6,g.getMobile());
pt.setString(7, g.getCreate_user());
pt.setString(8, g.getUpdate_user());
pt.setInt(9,g.getIsdel());
pt.execute();
}
public void updateGoddess(Goddess g) throws SQLException{
Connection conn=Jdbc_util.getConnection();
String sql=""+
" update imooc_goddess "+
" set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?,"+
" update_user=?,update_date=current_date(),isdel=?"+
" where id=? ";
PreparedStatement pt=conn.prepareStatement(sql);
pt.setString(1,g.getUser_name());
pt.setInt(2, g.getSex());
pt.setInt(3,g.getAge());
pt.setDate(4,new Date(g.getBirthday().getTime()));
pt.setString(5,g.getEmail());
pt.setString(6,g.getMobile());
pt.setString(7, g.getUpdate_user());
pt.setInt(8,g.getIsdel());
pt.setInt(9, g.getId());
pt.execute();
}
public void delGoddess(Integer id) throws SQLException{
Connection conn=Jdbc_util.getConnection();
String sql=""+
" delete from imooc_goddess "+
" where id=? ";
PreparedStatement pt=conn.prepareStatement(sql);
pt.setInt(1, id);
pt.execute();
}
public List<Goddess> query() throws SQLException{
Connection conn=Jdbc_util.getConnection();
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("select user_name,age from imooc_goddess");
List<Goddess> qs=new ArrayList<Goddess>();
Goddess q=null;
while(rs.next()){
q=new Goddess();
q.setUser_name(rs.getString("user_name"));
q.setAge(rs.getInt("age"));
qs.add(q);
}
return qs;
}
public Goddess getquery( Integer i) throws SQLException{
Connection conn=Jdbc_util.getConnection();
String sql=""+
" select * from imooc_goddess "+
" where id=? ";
PreparedStatement pt=conn.prepareStatement(sql);
pt.setInt(1, i);
ResultSet rs=pt.executeQuery();
Goddess h=null;
while(rs.next()){
h=new Goddess();
h.setId(rs.getInt("id"));
h.setUser_name(rs.getString("user_name"));
h.setAge(rs.getInt("age"));
h.setSex(rs.getInt("sex"));
h.setBirthday(rs.getDate("birthday"));
h.setEmail(rs.getString("email"));
h.setMobile(rs.getString("mobile"));
h.setCreat_date(rs.getDate("create_date"));
h.setCreate_user(rs.getString("create_user"));
h.setUpdate_date(rs.getDate("update_date"));
h.setUpdate_user(rs.getString("update_user"));
h.setIsdel(rs.getInt("isdel"));
}
return h;
}
public List<Goddess> query(List<Map<String,Object>>params) throws SQLException{
Connection conn=Jdbc_util.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"));
}
}
System.out.println(sb.toString());
PreparedStatement ptmt=conn.prepareStatement(sb.toString());
ResultSet rs=ptmt.executeQuery();
List<Goddess> result =new ArrayList<Goddess>();
Goddess h=null;
while(rs.next()){
h=new Goddess();
h.setId(rs.getInt("id"));
h.setUser_name(rs.getString("user_name"));
h.setAge(rs.getInt("age"));
h.setSex(rs.getInt("sex"));
h.setBirthday(rs.getDate("birthday"));
h.setEmail(rs.getString("email"));
h.setMobile(rs.getString("mobile"));
h.setCreat_date(rs.getDate("create_date"));
h.setCreate_user(rs.getString("create_user"));
h.setUpdate_date(rs.getDate("update_date"));
h.setUpdate_user(rs.getString("update_user"));
h.setIsdel(rs.getInt("isdel"));
result.add(h);
}
return result;
}
}
增。删。改。方法都大同小异,这是因为他们没有返回值,而查的过程稍微复杂一点,需要返回list(多个结果),或者是单个结果,所以这里也写了不同的查询方法,query是返回list,getquery是返回单个结果,同时query方法也可以根据输入的参数不同,方法也不同,;
可以看到有几个地方要注意一下:1.SQL语句中,字符是需要空格隔开的,所以在java中我们也不能忘记
2.针对上一个问题,我们可以在查询结果的时候同时打印SQL语句(
<span style="white-space:pre"> </span>System.out.println(sb.toString());
)
3.在我们筛选条件为like的时候,在value前后要加上占位符%;eg:pt.setString(1,"%"+"g.getuser_name"+"%");
4.在我们连接条件的时候合理使用小技巧1=1,见:
sb.append(" select * from imooc_goddess where 1=1 "); <pre name="code" class="java">sb.append(" "+" and"+" "+map.get("name")+" "+map.get("rela")+" "+map.get("value"));
5.通过创建LIst<Map<string,Object>>我们可以通过任意参数得到我们想查询的值,但是要定义,如下:
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jdbc_chuan.dao.GoddessDao;
import jdbc_chuan.model.Goddess;
public class GoddessAction {
public static void main(String[] args) throws SQLException{
GoddessDao g=new GoddessDao();
List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();
Map<String,Object>map=new HashMap<String,Object>();
map.put("name", "user_name");
map.put("rela", "=");
map.put("value", "'小兰'");
params.add(map);
List<Goddess> g3=g.query(params);
for(int i=0;i<g3.size();i++){
System.out.println(g3.get(i).toString());
}
}
}
万事具备啦,看看结果:
首先看看表中的记录:
有两条记录,下面增加一条试试看:
g1.setUser_name("小夏");
g1.setAge(21);
g1.setSex(1);
g1.setBirthday(new Date());
g1.setEmail("xinxiaoxia@qq.com");
g1.setMobile("18211112222");
g1.setIsdel(1);
g1.setId(3);
g.addGodness(g1);
更新记录,这里我们定义的方法是通过Goddess类来确定更新记录的位置,我们可以看到小夏的id是4;
那我们就把更新的id设置为4,代码:
GoddessDao g=new GoddessDao();
Goddess g1=new Goddess();
g1.setUser_name("小晓");
g1.setAge(21);
g1.setSex(1);
g1.setBirthday(new Date());
g1.setEmail("xiaoxiao@qq.com");
g1.setMobile("18888888888");
g1.setIsdel(1);
g1.setId(4);
g.updateGoddess(g1);
结果:小夏没了,变成了小晓;
最后呢,就是查询了,代码如下:
List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();
Map<String,Object>map=new HashMap<String,Object>();
map.put("name", "user_name");
map.put("rela", "=");
map.put("value", "'小兰'");
params.add(map);
List<Goddess> g3=g.query(params);
for(int i=0;i<g3.size();i++){
System.out.println(g3.get(i).toString());
}
查询结果显示在控制台:
select * from imooc_goddess where 1=1 and user_name = '小兰'
Goddess [id=2, user_name=小兰, sex=0, age=24, birthday=null, email=null, mobile=18811112222, create_user=null, update_user=null, creat_date=null, update_date=null, isdel=0]
如果发生了错误,首先可以看SQL语句有没有写错。