3.31

user\userinfo

列名	类型	说明
user_id	整数、主键	用户编号
user_name	字符串，唯一，非空	用户名称
user_pwd	字符串，非空	用户密码
user_borndate	DATE	出生日期
user_email	字符串，非空	邮箱
user_address	字符串	地址

注意：采用DAO+Entity完成

• com.qf.xxx.entity
  User

• com.qf.xxx.dao
  UserDaoImpl

• 完成五个方法、增、删、改、查、查所有

t_user

CREATE TABLE t_user(
	user_id INT PRIMARY KEY,
	user_name VARCHAR(20) UNIQUE NOT NULL,
	user_pwd VARCHAR(20) NOT NULL,
	user_borndate DATE,
	user_email VARCHAR(30) NOT NULL,
	user_address VARCHAR(30)
)CHARSET utf8;

SELECT * FROM t_user;

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8
username=root
password=1234

DBUtil

package com.qf.day3_31.p1;

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

public class DBUtil {
    private static final Properties properties = new Properties();

    static {

        try {
            InputStream is = DBUtil.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 connection = null;
        try {
            connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

DateUtils

package com.qf.day3_31.p1;


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

public class DateUtils {
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    //String转util.date
    public static java.util.Date strToUtilDate(String str) {
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    //util转sql
    public static java.sql.Date utilToSql(java.util.Date date) {
        return new java.sql.Date(date.getTime());
    }
}

User

package com.qf.day3_31.p1;

import java.util.Date;

public class User {
    private int user_id;
    private String user_name;
    private String user_pwd;
    private Date user_borndate;
    private String user_email;
    private String user_address;

    public User() {
    }

    public User(int user_id, String user_name, String user_pwd, Date user_borndate, String user_email, String user_address) {
        this.user_id = user_id;
        this.user_name = user_name;
        this.user_pwd = user_pwd;
        this.user_borndate = user_borndate;
        this.user_email = user_email;
        this.user_address = user_address;
    }

    @Override
    public String toString() {
        return "User{" +
                "user_id=" + user_id +
                ", user_name='" + user_name + '\'' +
                ", user_pwd='" + user_pwd + '\'' +
                ", user_borndate=" + user_borndate +
                ", user_email='" + user_email + '\'' +
                ", user_address='" + user_address + '\'' +
                '}';
    }

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_pwd() {
        return user_pwd;
    }

    public void setUser_pwd(String user_pwd) {
        this.user_pwd = user_pwd;
    }

    public Date getUser_borndate() {
        return user_borndate;
    }

    public void setUser_borndate(Date user_borndate) {
        this.user_borndate = user_borndate;
    }

    public String getUser_email() {
        return user_email;
    }

    public void setUser_email(String user_email) {
        this.user_email = user_email;
    }

    public String getUser_address() {
        return user_address;
    }

    public void setUser_address(String user_address) {
        this.user_address = user_address;
    }
}

UserDAOImpl

package com.qf.day3_31.p1;

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

public class UserDAOImpl {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    //增
    public int insert(User user) {
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into t_user(user_id,user_name,user_pwd,user_borndate,user_email,user_address)values(?,?,?,?,?,?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, user.getUser_id());
            preparedStatement.setString(2, user.getUser_name());
            preparedStatement.setString(3, user.getUser_pwd());
            preparedStatement.setDate(4, DateUtils.utilToSql(user.getUser_borndate()));
            preparedStatement.setString(5, user.getUser_email());
            preparedStatement.setString(6, user.getUser_address());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(connection, preparedStatement, resultSet);
        }
        return 0;

    }

    //删
    public int delete(int user_id) {
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from t_user where user_id = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, user_id);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(connection, preparedStatement, resultSet);
        }

        return 0;
    }

    //查单个
    public User select(int user_id) {
        User user = null;
        connection = DBUtil.getConnection();
        String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from t_user where user_id = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, user_id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                user = new User();
                user.setUser_id(resultSet.getInt("user_id"));
                user.setUser_name(resultSet.getString("user_name"));
                user.setUser_pwd(resultSet.getString("user_pwd"));
                user.setUser_address(resultSet.getString("user_address"));
                user.setUser_email(resultSet.getString("user_email"));
                user.setUser_borndate(resultSet.getDate("user_borndate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(connection, preparedStatement, resultSet);
        }
        return user;
    }

    //查全部
    public List<User> selectAll() {
        List<User> list = new ArrayList<User>();
        connection = DBUtil.getConnection();
        String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from t_user;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setUser_id(resultSet.getInt("user_id"));
                user.setUser_name(resultSet.getString("user_name"));
                user.setUser_pwd(resultSet.getString("user_pwd"));
                user.setUser_address(resultSet.getString("user_address"));
                user.setUser_email(resultSet.getString("user_email"));
                user.setUser_borndate(resultSet.getDate("user_borndate"));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(connection, preparedStatement, resultSet);
        }
        return null;
    }

    //改
    public int update(User user) {
        try {
            connection = DBUtil.getConnection();
            String sql = "update t_user set user_name = ?,user_pwd = ?,user_borndate = ?,user_email = ?,user_address = ? where user_id = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(6, user.getUser_id());
            preparedStatement.setString(1, user.getUser_name());
            preparedStatement.setString(2, user.getUser_pwd());
            preparedStatement.setDate(3, DateUtils.utilToSql(user.getUser_borndate()));
            preparedStatement.setString(4, user.getUser_email());
            preparedStatement.setString(5, user.getUser_address());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(connection, preparedStatement, resultSet);
        }
        return 0;
    }
}

TestUserDAO

package com.qf.day3_31.p1;

public class TestUserDAO {
    public static void main(String[] args) {
        UserDAOImpl userDAO = new UserDAOImpl();
        User user = new User(1, "tom", "1234", DateUtils.strToUtilDate("1996-01-01"), "1111@163.com", "xxx.xxx.xxx");
        User user2 = new User(2, "mary", "1234", DateUtils.strToUtilDate("1997-04-21"), "1211@163.com", "xxx.xxx.xxx");
        User user3 = new User(3, "jack", "1234", DateUtils.strToUtilDate("1995-01-11"), "1331@163.com", "xxx.xxx.xxx");
//        userDAO.insert(user);
//        userDAO.insert(user2);
//        userDAO.insert(user3);
//        System.out.println(userDAO.delete(1));
        System.out.println(userDAO.select(2));
        userDAO.selectAll().forEach(System.out::println);
    }
}