usermodel.java

网上商城源码,功能包括商品管理,订单管理,用户管理等

package com.shop.model;

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.Collection;

import com.shop.common.Dbconn;
import com.shop.view.UserVo;

public class UserModel {
	Dbconn db;

	public void insertUserinfo(UserVo vo) {
		db = new Dbconn();
		PreparedStatement ps = null;

		String sql = "insert into shop_userinfo(userinfo_name,userinfo_pwd,"
				+ "userinfo_mail,userinfo_question ,userinfo_answer,"
				+ "userinfo_realname,userinfo_sex,userinfo_hobby,"
				+ "userinfo_address,userinfo_identity ,userinfo_phone,"
				+ "userinfo_role,userinfo_vertiry,userinfo_money,userinfo_date) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = db.getConn().prepareStatement(sql);
			ps.setString(1, vo.getUsername());
			ps.setString(2, vo.getPassword());
			ps.setString(3, vo.getMail());
			ps.setString(4, vo.getQuestion());
			ps.setString(5, vo.getAnswer());
			ps.setString(6, vo.getRealname());
			ps.setString(7, vo.getSex());
			ps.setString(8, vo.getHobby());
			ps.setString(9, vo.getAddress());
			ps.setString(10, vo.getIdentity());
			ps.setString(11, vo.getPhone());
			ps.setInt(12, vo.getRole());
			ps.setInt(13, vo.getVertify());
			ps.setString(14, String.valueOf(vo.getMoney()));
			ps.setDate(15, new Date(new java.util.Date().getTime()));
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if (ps!=null) {
				try {
					ps.close();
					if (db!=null) {
						db.closeConn();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public void deleteUserinfo(int id) {
		db = new Dbconn();
		Statement sta = null;
		String sql = "delete from shop_userinfo where userinfo_id=" + id;
		try {
			sta = db.getConn().createStatement();
			sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateUserinfo(UserVo vo) {
		db = new Dbconn();
		PreparedStatement ps = null;

		String sql = "update shop_userinfo set userinfo_money=? where userinfo_id="+vo.getId();
		try {
			ps = db.getConn().prepareStatement(sql);
			ps.setFloat(1, vo.getMoney());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if (ps!=null) {
				try {
					ps.close();
					if (db!=null) {
						db.closeConn();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}	
	}

	public UserVo selectUserinfo(String name) {
		UserVo vo=new UserVo();
		db=new Dbconn();
		Statement sta=null;
		ResultSet rs=null;
		String sql="select * from shop_userinfo where userinfo_name='"+name+"'";
		try {
			sta=db.getConn().createStatement();
			rs=sta.executeQuery(sql);
			while (rs.next()) {
				vo.setId(rs.getInt(1));
				vo.setUsername(rs.getString(2));
				vo.setPassword(rs.getString(3));
				vo.setMail(rs.getString(4));
				vo.setQuestion(rs.getString(5));
				vo.setAnswer(rs.getString(6));
				vo.setRealname(rs.getString(7));
				vo.setSex(rs.getString(8));
				vo.setHobby(rs.getString(9));
				vo.setAddress(rs.getString(10));
				vo.setIdentity(rs.getString(11));
				vo.setPhone(rs.getString(12));
				vo.setRole(rs.getInt(13));
				vo.setVertify(rs.getInt(14));
				vo.setMoney(rs.getFloat(15));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if (rs!=null) {
				try {
					rs.close();
					if (sta!=null) {
						sta.close();
						if (db!=null) {
							db.closeConn();
						}
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return vo;
	}

	public Collection selectAllUserinfo() {
		Collection c=new ArrayList();
		return c;
	}
}