JDBC003

package day03;

import java.io.Serializable;

/*
 * 为了存放从数据库中查询到的记录的信息,我们可以设计一个对应的类
 * 该类的结构与要操作的数据库对应的表一致
 *
 */
public class User implements Serializable {//序列化
	private Integer id;//用户id
	private String userName;//用户名
	private String userPwd;//用户密码
	private double userSal;//员工薪资
	private Integer useAge;//用户年龄
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPwd() {
		return userPwd;
	}
	public void setUserPwd(String userPwd) {
		this.userPwd = userPwd;
	}
	public double getUserSal() {
		return userSal;
	}
	public void setUserSal(double userSal) {
		this.userSal = userSal;
	}
	public Integer getUseAge() {
		return useAge;
	}
	public void setUseAge(Integer useAge) {
		this.useAge = useAge;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((useAge == null) ? 0 : useAge.hashCode());
		result = prime * result + ((userName == null) ? 0 : userName.hashCode());
		result = prime * result + ((userPwd == null) ? 0 : userPwd.hashCode());
		long temp;
		temp = Double.doubleToLongBits(userSal);
		result = prime * result + (int) (temp ^ (temp >>> 32));
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (useAge == null) {
			if (other.useAge != null)
				return false;
		} else if (!useAge.equals(other.useAge))
			return false;
		if (userName == null) {
			if (other.userName != null)
				return false;
		} else if (!userName.equals(other.userName))
			return false;
		if (userPwd == null) {
			if (other.userPwd != null)
				return false;
		} else if (!userPwd.equals(other.userPwd))
			return false;
		if (Double.doubleToLongBits(userSal) != Double.doubleToLongBits(other.userSal))
			return false;
		return true;
	}
	public User(Integer id, String userName, String userPwd, double userSal, Integer useAge) {
		super();
		this.id = id;
		this.userName = userName;
		this.userPwd = userPwd;
		this.userSal = userSal;
		this.useAge = useAge;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", userName=" + userName + ", userPwd=" + userPwd + ", userSal=" + userSal
				+ ", useAge=" + useAge + "]";
	}
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	
}

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import day02.DButil;

/*
 * DAO(Data Access Object):封装了数据访问逻辑的对象
 * DAO作为数据访问层,把业务层逻辑数据分隔开来,业务逻辑层需要数据就要跟DAO层要数据
 * 业务逻辑层要保存数据,就要DAO,让DAO去保存业务逻辑
 * 如果获取数据,如何保存数据,都是由DAO负责
 * @author 臻冉
 *
 */
public class UserDao {
	//用户注册
	public int insertUser(User user){
		Connection conn = null;
		PreparedStatement ps = null;
		int n=0;
		try {
			conn = DButil.getConnection();
			String sql = "insert into use values (null,?,?,?,?)";
			//对象中的数据插入到数据库中
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getUserPwd());
			ps.setDouble(3, user.getUserSal());
			ps.setInt(4, user.getUseAge());
			n=ps.executeUpdate();
			return n;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return 0;
	}
	
	//根据用户id删除用户数据
	public int deleteUserById(int id){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DButil.getConnection();
			String sql = "delete from user where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int n = ps.executeUpdate();
			return n;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DButil.closeConnection(conn, null, ps);
		}
		return 0;
	}
	
	//用户登录成功并修改用户的密码
	public int updataUserData(User user,String newPwd){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DButil.getConnection();
			String sql = "select user_name,user_password from user where user_name=? and user_password=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1,user.getUserName());
			ps.setString(2, user.getUserPwd());
			rs = ps.executeQuery();
			if(rs.next()){
				System.out.println("登录成功");
				String sql2 = "update user set user_password=? where user_name=?";
				ps = conn.prepareStatement(sql2);
				ps.setString(1, newPwd);
				ps.setString(2, user.getUserName());
				int n = ps.executeUpdate();
				return n;
			} else {
				System.out.println("登录失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DButil.closeConnection(conn, rs, ps);
		}
		
		return 0;
	}
	
	
	//根据员工的编号查询出用户的部门名称
	public String findUserByempno(int empno){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DButil.getConnection();
			String sql = "select d.dname from emp e join dept d on e.deptno = d.deptno where e.empno=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, empno);
			rs = ps.executeQuery();
			if(rs.next()){
				String name = rs.getString("d.dname");
				return name;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DButil.closeConnection(conn, rs, ps);
		}
		
		return null;
	}
	
	
	//查询所有用户的数据<List>
	public List<User> findUserAll(){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			List<User> list = new ArrayList<User>();
			conn = DButil.getConnection();
			String sql = "select * from user";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				User user = new User();//封装后的
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserPwd(rs.getString("user_password"));
				user.setUseAge(rs.getInt("user_age"));
				user.setUserSal(rs.getDouble("user_sal"));
				list.add(user);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DButil.closeConnection(conn, rs, ps);
		}
		return null;
	}
	
	//查询所有用户的数据<List<Map<String,Object>>
	
	
	
	
	
	//查询所有用户的输入Map<String,Object>
	public Map<String,Object> findUserAll3(){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DButil.getConnection();
			String sql = "select * from user";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			Map<String,Object>  map = new HashMap<String,Object>();
			List<User> list = new ArrayList<User>();
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserPwd(rs.getString("user_password"));
				user.setUseAge(rs.getInt("user_age"));
				user.setUserName(rs.getString("user_name"));
			    user.setUserSal(rs.getDouble("user_sal"));
			    list.add(user);
			}
			map.put("list", list);
			   return map;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DButil.closeConnection(conn, rs, ps);
		}
		return null;
		
	}
	
}

package day03;

import java.util.List;

public interface UserDao2 {
	/*用户注册*/
	public void insertUser(User use);
	
	/*根据id删除用户数据*/
	int deleteUserById(int id);
	
	/*根据用户名修改用户密码*/
	int updateUserById(int id);
	
	/*根据用于id查询用户数据*/
	User findUserAll(int id);
	
	/*查询所有用户的数据*/
	List<User> findUserAll();
}

package day03;

import java.util.List;

public class UserDao2impl implements UserDao2 {

	@Override
	public void insertUser(User use) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public int deleteUserById(int id) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int updateUserById(int id) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public User findUserAll(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<User> findUserAll() {
		// TODO Auto-generated method stub
		return null;
	}

}

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import day02.DButil;
import day02.DButilDemo;

/*
 * 根据id查询用户数据并封装在对象中
 */
public class UserDemo {
	public static void main(String[] args) {
		UserDemo userDemo = new UserDemo();
		User user=userDemo.findUserById(1);
		System.out.println(user);
	}
	public User findUserById(int id){
		//业务
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//1.连接数据库
			conn=DButil.getConnection();
			
			//2.根据id查询用户数据
			String sql = "select * from user where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			
			//3.将查询出来的数据封装在User对象中
			User user = new User();
			if(rs.next()){
				int userId = rs.getInt("id");
				String name = rs.getString("user_name");
				String pwd = rs.getString("user_password");
				double sal = rs.getDouble("user_id");
				int age = rs.getInt("user_age");
				//查询 处理的数据封装在User对象中
				user = new User();
				user.setId(id);
				user.setUserName(name);
				user.setUserSal(sal);
				user.setUserPwd(pwd);
				user.setUseAge(age);
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}

DAO

drivername=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/store_ykt
username=root
userpwd=123456
maxActive=1
maxWait=7000