JDBC查数据

目录

BaseDao

UserDao

impl

---

项目目录

---

BaseDao

package com.hz.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.hz.util.ConfigManager;

/**
 * 公共的数据库连接信息 获得连接信息
 * 
 * @author XXX
 * 
 */
public class BaseDao {

	protected Connection conn = null;
	protected PreparedStatement ps = null;
	protected ResultSet rs = null;
	Properties properties = new Properties();
	
	
	/**
	 * 获得连接信息
	 * 
	 * @return
	 */
	public boolean getConnection() {
		
		String url = ConfigManager.getInstance().getString("jdbc.url");
		String username = ConfigManager.getInstance().getString("jdbc.username");
		String pwd = ConfigManager.getInstance().getString("jdbc.pwd");
		String Driver=ConfigManager.getInstance().getString("jdbc.Driver");
		try {
			Class.forName(Driver);
			// 3.使用DriverManager获得Connection对象
			conn = DriverManager.getConnection(url, username, pwd);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} 
	}

	/**
	 * 增删改
	 */
	public int executeUpdate(String sql, Object[] objs) {
		int res = 0;//初始化执行结果  失败0
		if (this.getConnection()) {
			try {
				ps = conn.prepareStatement(sql);
				// 填充占位符
				for (int i = 0; i < objs.length; i++) {
					ps.setObject(i + 1, objs[i]);
				}
				res = ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		return res;
	}

	/**
	 * 查
	 */
	public ResultSet executeSQL(String sql, Object[] objs) {
		ResultSet rs = null;
		try {
			if (getConnection()) {
				ps = conn.prepareStatement(sql);
			    //判断是否有参数
				if (objs != null) {
				    //循环封装参数
					for (int i = 0; i < objs.length; i++) {
						ps.setObject(i + 1, objs[i]);
					}
				}
				rs = ps.executeQuery();
			}

		} catch (Exception e) {
			e.printStackTrace();
		} 
		return rs;
	}

	/**
	 * 释放资源
	 */
	public void closeResources() {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}

 

UserDao

public interface UserDao {
	
	
	
	/**
	 * 一对多
	 * 根据用户id 或的对象信息 并返回获得地址集合
	 * @param id
	 * @return
	 */
	public User findUserAddressById(Long id); 

	/**
	 * 用户登录
	 * @param userCode
	 * @param userPassword
	 * @return
	 */
	public User loginUser(String userCode, String userPassword);
	/**
	 * 新增用户
	 * @param user
	 * @return
	 */
	public int saveUser(User user);
	/**
	 * 根据ID删除用户
	 * @param id
	 * @return
	 */
	public int deleteUserById(Long id);
	
	/**
	 * 获取所有用户信息
	 * @return
	 */
	public List<User> findUserList();
	
	/**
	 * 根据id获得单个用户对象
	 * @return
	 */
	public User finUserById(Long id);
	/**
	 * 根据用户ID 修改用户信息
	 * @param user
	 * @return
	 */
	public int updateUser(User user);

	
}

impl

package com.hz.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hz.dao.AddressDao;
import com.hz.dao.BaseDao;
import com.hz.dao.UserDao;
import com.hz.pojo.Address;
import com.hz.pojo.User;

//用户类
public class UserDaoImpl extends BaseDao implements UserDao, AddressDao {
	
	//一对一    返回集合
	// 获得所有用户下所有收获地址信息集合并显示所属用户名与用户编码
	
	@Override
	public List<Address> findAddressListById1() {
		List<Address> addrList = new ArrayList<Address>();
		Address addr =  new Address();
		try {
			if (getConnection()) {
				//收获地址ID 收货人姓名 详细地址 所有用户编号 所属用户名
				String sql = "SELECT a.id,a.contact,a.addressDesc,b.id uid,b.userName FROM smbms_address a left join smbms_user b on a.userId=b.id";
				rs = executeSQL(sql, new Object[] {});
				while (rs.next()) {
					User user = new User();
					user.setId(rs.getLong("id"));
					user.setUserName(rs.getString("contact"));
					user.setAddress(rs.getString("addressDesc"));
					addr =  new Address();
					addr.setUser(user);
					addr.setId(rs.getLong("uid"));
					addr.setContact(rs.getString("userName"));
					addrList.add(addr);
				}
	
			return addrList;

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	

	@Override
	public User findAddressListById() {
		User user = new User(); 
		List<Address> addrList = new ArrayList<Address>();
		try {
			if (getConnection()) {
				//收获地址ID 收货人姓名 详细地址 所有用户编号 所属用户名
				String sql = "SELECT a.id,a.contact,a.addressDesc,b.id uid,b.userName FROM smbms_address a left join smbms_user b on a.userId=b.id";
				rs = executeSQL(sql, new Object[] {});
				while (rs.next()) {
					User user1 = new User();
					user1.setId(rs.getLong("id"));
					user1.setUserName(rs.getString("contact"));
					user1.setAddress(rs.getString("addressDesc"));
					Address addr =  new Address();
					addr.setUser(user1);
					addr.setId(rs.getLong("uid"));
					addr.setContact(rs.getString("userName"));
					addrList.add(addr);
				}
				user.setAddressList(addrList);
				
			return user;

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	//一对多
	public User findUserAddressById(Long id) {
		User user = new User();
		List<Address> addrList = new ArrayList<Address>();
		try {
			if (getConnection()) {
				String sql = "SELECT a.id,a.userName,b.id addrid,b.contact,b.addressDesc FROM smbms_user a left join smbms_address b on a.id=b.userId WHERE a.id=?";
				rs = executeSQL(sql, new Object[] { id });
				while (rs.next()) {
					user.setId(rs.getLong("id"));
					user.setUserName(rs.getString("userName"));

					Address addr = new Address();
					addr.setId(rs.getLong("addrid"));
					addr.setContact(rs.getString("contact"));
					addr.setAddressDesc(rs.getString("addressDesc"));
					addrList.add(addr);

				}
				user.setAddressList(addrList);

				return user;

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeResources();
		}

		return null;
	}

	// 1.-------------------------------------------
	@Override
	public User loginUser(String userCode, String userPassword) {
		try {
			if (getConnection()) {
				String sql = "select * from smbms_user a where a.userCode=? and a.userPassword=?";
				// 4.创建Statementd对象 用于执行SQL语句
				ps = conn.prepareStatement(sql);
				ps.setString(1, userCode);
				ps.setString(2, userPassword);
				// 5.执行sql语句
				rs = ps.executeQuery();
				User user = null;
				while (rs.next()) {
					user = new User();
					user.setId(rs.getLong("id"));
					user.setUserPassword(rs.getString("userPassword"));
					user.setUserName(rs.getString("userName"));
				}
				return user;// 返回用户登录对象
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeResources();// 释放资源
		}
		return null;
	}

	// 2.新增用户-------------------------------------------
	@Override
	public int saveUser(User user) {
		int res = 0;
		try {
			String sql = "INSERT into smbms_user(userCode,userName,userPassword) VALUES(?,?,?)";
			Object[] objs = { user.getUserCode(), user.getUserName(), user.getUserPassword() };
			res = this.executeUpdate(sql, objs);
			if (res > 0) {
				System.out.println("添加成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

	// 3.根据ID删除用户-------------------------------------------
	@Override
	public int deleteUserById(Long id) {
		int res = 0;
		try {
			String sql = "DELETE from smbms_user WHERE id=?";
			Object[] objs = { id };
			res = this.executeUpdate(sql, objs);
			if (res > 0) {
				System.out.println("删除成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

	// 4.获取所有用户信息-------------------------------------------
	@Override
	public List<User> findUserList() {
//		try {
//			String sql = "select * from smbms_user ";
//			Object[] objs = {};
//			ResultSet rs = executeSQL(sql, objs);
//			List<User> userList = new ArrayList<User>();
//			while (rs.next()) {
//				User user = new User(rs.getLong("id"), rs.getString("userCode"), rs.getString("userName"),
//						rs.getString("userPassword"));
//				userList.add(user);
//				System.out.println(user.toString());
//			}
//			return userList;
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return null;
		
		List<User> userList = new ArrayList<User>();
		try {
			if (getConnection()) {
				String sql = "select * from smbms_user ";
				rs = executeSQL(sql, null);
				while (rs.next()) {
				 	User user = new User();
					user.setId(rs.getLong("id"));
					user.setUserName(rs.getString("userName"));
					user.setUserCode(rs.getString("userCode"));
					userList.add(user);
				}
				return userList;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return userList;
		
		
		
		
		
	}

	// 5.根据id获得单个用户对象-------------------------------------------
	@Override
	public User finUserById(Long id) {
		try {
			String sql = "select id,userCode,userName,userPassword  from smbms_user where id=? ";
			Object[] objs = { id };
			ResultSet rs = this.executeSQL(sql, objs);
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setId(rs.getLong("id"));
				user.setUserPassword(rs.getString("userPassword"));
				user.setUserName(rs.getString("userName"));
				user.setUserCode(rs.getString("userCode"));
			}
			return user;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 6.根据用户ID 修改用户信息-------------------------------------------
	@Override
	public int updateUser(User user) {
		int res = 0;
		try {
			String sql = "UPDATE smbms_user SET userPassword = 6666 WHERE userCode = ?";
			Object[] objs = { user.getUserCode() };
			res = this.executeUpdate(sql, objs);
			if (res > 0) {
				System.out.println("修改成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}



}