jsp第六次作业

package com.sykdl.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

//进行数据的增删改查
public class UserDao {
	//1  提供添加方法
	public boolean insert(User user) {
		Connection con = null;
		Statement stmt = null;
		try {
			//1创建连接对象
			con = JDBCUtils.getCon();
			//2 获取执行SQL语句的对象
			stmt = con.createStatement();
			//3执行sql语句
			java.util.Date birthday = user.getBirthDay();
			String sqlBirthDay = String.format("%tF", birthday);
			String sql = "insert into user(id,name,password,email,birthday)"+"values('"
			           +user.getId()+"','"
			           +user.getUsername()+"','"
			           +user.getPassword()+"','"
			           +user.getEmail()+"','"
			           +sqlBirthDay+"'"
			           +")";
			System.out.println(sql);
			int row = stmt.executeUpdate(sql);
			if(row>0) {
				return true;
			}
		}catch(Exception e){
			e.printStackTrace();
			}finally {
			JDBCUtils.release(null, stmt, con);
		}
		return false;
	}
	// 2 提供全部查找方式
	public ArrayList<User> findAllUser(){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			//1创建连接对象
			con = JDBCUtils.getCon();
			//2 获取执行SQL语句的对象
			stmt = con.createStatement();
			//3执行sql语句
			String sql = "select * from user";
			rs = stmt.executeQuery(sql);
			//遍历 rs
			ArrayList<User> list = new ArrayList<User>();
			while(rs.next()) {
				//一行数据对应一个对象 获取每一行数据，就设置一个user对象
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				java.sql.Date birthDay = rs.getDate("birthDay");
				user.setBirthDay(birthDay);
				//把对象添加到集合中
				list.add(user);
			}
			return list;
		}catch(Exception e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtils.release(rs, stmt, con);
			}
		
	}
	//3根据ID来查找user
	public User findUseiId(int id) {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//1创建连接对象
			con = JDBCUtils.getCon();
			//2 获取执行SQL语句的对象
			String sql = "select * from user where id =?";
			stmt = con.prepareStatement(sql);
			//3执行sql语句
			stmt.setInt(1,id);
			System.out.println(sql);
			rs = stmt.executeQuery();
			//遍历 rs
			if(rs.next()) {
				//一行数据对应一个对象 获取每一行数据，就设置一个user对象
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				java.sql.Date birthDay = rs.getDate("birthDay");
				user.setBirthDay(birthDay);
			}
			
		}catch(Exception e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtils.release(rs, stmt, con);
			}
		return null;
	}
	// 4 根据id来修改 User
	public boolean updateUserId(User user) {

		Connection con = null;
		PreparedStatement stmt = null;
		try {
			//1创建连接对象
			con = JDBCUtils.getCon();
			//2 获取执行SQL语句的对象
			String sql = "update user set name=?,password=? where id =?";
			stmt = con.prepareStatement(sql);
			stmt.setString(1,user.getUsername());
			stmt.setString(2,user.getPassword());
			stmt.setInt(3,user.getId());
			//3执行sql语句
			int row = stmt.executeUpdate();
			if(row>0) {
				return true;
			}
		}catch(Exception e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtils.release(null, stmt, con);
			}
		return false;
	
	}
	//根据id来删除User
	public boolean delectUser(int id) {

		Connection con = null;
		PreparedStatement stmt = null;
		try {
			//1创建连接对象
			con = JDBCUtils.getCon();
			//2 获取执行SQL语句的对象
			String sql = "delete from user where id =?";
			stmt = con.prepareStatement(sql);
			stmt.setInt(1,id);
			//3执行sql语句
			int row = stmt.executeUpdate();
			if(row>0) {
				return true;
			}
		}catch(Exception e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtils.release(null, stmt, con);
			}
		return false;
	
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}

　　

package com.sykdl.jdbc;

import java.util.Date;

public class User {
	private int id;
	private String username;
	private String password;
	private String email;
	private Date birthday;
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
				+ ", birthday=" + birthday + ", birthDay=" + birthDay + "]";
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	private Date birthDay;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public Date getBirthDay() {
		return birthDay;
	}
	public void setBirthDay(Date birthDay) {
		this.birthDay = birthDay;
	}
}

　　

package com.sykdl.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
	//获取连接的方法	
	public static Connection getCon() throws Exception {
		//1注册和加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");
		//2.获取连接
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/keshe?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
		return con;
	}
	//关闭资源 释放资源
	public static void release(ResultSet rs,Statement stmt,Connection con) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rs = null;
		}
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
		}
		if(con!=null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			con = null;
		}
	}
}