千锋Java&Day43课后作业

mysql语句

CREATE TABLE `user`(
	user_id INT PRIMARY KEY,
	user_name VARCHAR(20) UNIQUE NOT NULL,
	user_pwd VARBINARY(20) NOT NULL,
	user_borndate DATE,
	user_email VARCHAR(20) NOT NULL,
	user_address VARCHAR(20) NOT NULL
)CHARSET=utf8;
INSERT INTO USER VALUES(1,'小明','123','2020-02-02','xiaoming@qq.com','北京');
INSERT INTO USER VALUES(2,'小红','123','2019-01-19','xiaohong@qq.com','上海');

测试类

import java.util.List;

public class TestUser {
    public static void main(String[] args) {
        UserDaoImpl udi = new UserDaoImpl();
//        User user = new User(3,"泰罗","123", DateUtiles.strToUtil("2010-10-10"),"qq.com","m78星云");
//        int reslut = udi.insert(user);
//        System.out.println(reslut);

//        System.out.println(udi.select(2));

        List<User> l = udi.selectAll();
        l.forEach(System.out::println);

    }
}

user类

import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;
    private Date borndate;
    private String email;
    private String address;

    public User() {
    }

    public User(int id, String username, String password, Date borndate, String email, String address) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.borndate = borndate;
        this.email = email;
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", borndate=" + borndate +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public Date getBorndate() {
        return borndate;
    }

    public String getEmail() {
        return email;
    }

    public String getAddress() {
        return address;
    }
}

DAO实现类

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl {
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;
    public int insert(User user){
        try {
            connection = DButil.getConnection();
            String sql = "insert into user(user_id,user_name,user_pwd,user_borndate,user_email,user_address) values(?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,user.getUserid());
            preparedStatement.setString(2,user.getName());
            preparedStatement.setString(3,user.getPwd());
            preparedStatement.setDate(4, DateUtil.utilToSqlDate(user.getBorndate()));
            preparedStatement.setString(5,user.getEmail());
            preparedStatement.setString(6,user.getAddress());

            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    public int delete(int userid){
        try {
            connection = DButil.getConnection();
            String sql = "delete from user where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,userid);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.closeAll(connection,preparedStatement,null);
        }
        return 0 ;
    }
    public int update(User user){
        try {
            connection = DButil.getConnection();
            String sql = "update user set user_name=?,user_pwd=?,user_borndate=?,user_email=?,user_address=? where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getName());
            preparedStatement.setString(2,user.getPwd());
            preparedStatement.setDate(3,DateUtil.utilToSqlDate(user.getBorndate()));
            preparedStatement.setString(4,user.getEmail());
            preparedStatement.setString(5,user.getAddress());
            preparedStatement.setInt(6,user.getUserid());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    public User select(int userid){
        try{
            connection = DButil.getConnection();
            String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from user where user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,userid);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                int user_id = resultSet.getInt(1);
                String user_name = resultSet.getString(2);
                String user_pwd = resultSet.getString(3);
                String user_borndate= resultSet.getString(4);
                String user_email = resultSet.getString(5);
                String user_address = resultSet.getString(6);
                User user = new User(user_id,user_name,user_pwd,user_borndate,user_email,user_address);
                return user;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButil.closeAll(connection,preparedStatement,null);
        }
        return null;
    }
    public List<User> selectAll(){
        List<User> list = new ArrayList<User>();
        try{
            connection = DButil.getConnection();
            String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from user";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int user_id = resultSet.getInt(1);
                String user_name = resultSet.getString(2);
                String user_pwd = resultSet.getString(3);
                String user_borndate= resultSet.getString(4);
                String user_email = resultSet.getString(5);
                String user_address = resultSet.getString(6);
                User user = new User(user_id,user_name,user_pwd,user_borndate,user_email,user_address);
                list.add(user);
            }
            return list;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButil.closeAll(connection,preparedStatement,null);
        }
        return null;
    }
}

工具类

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 数据库工具类
 * 1、获取连接 connection
 * 2、释放资源
 * 可跨平台方案
 */
public class DBUtils {
    private static final Properties properties = new Properties();

    static {
        try {
            InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
            properties.load(is);//通过流将文件中的内容分割成键值对

            Class.forName(properties.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public static Connection getConnection(){
        Connection conn = null;

        try {
            conn = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }
    //释放资源
    public static void closeAll(Connection conn, Statement st, ResultSet re){
        try {
            if(conn != null){
                conn.close();
            }
            if(st != null){
                st.close();
            }
            if(re != null){
                re.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

import java.text.ParseException;
import java.text.SimpleDateFormat;

public class DateUtil {
    public static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    //字符串转utilDate
    public static java.util.Date strToUtilDate(String date){
        try {
            return simpleDateFormat.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //Util转sqlDate
    public static java.sql.Date utilToSqlDate(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
    //字符串转sqlDate
    public static java.sql.Date strToSqlDate(String date){
        try {
            java.util.Date utilDate = simpleDateFormat.parse(date);
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
}