JDBC之模型层的完成

所谓的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语句有没有写错。




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值