千峰第43天作业

在千峰学习的第43天，
中国加油！武汉加油！千峰加油！我自己加油！

CREATE TABLE `user`(
	user_id INT PRIMARY KEY,
	user_name VARCHAR(10) UNIQUE NOT NULL,
	user_pwd VARCHAR(10) NOT NULL,
	user_borndate DATE,
	user_email VARCHAR(20) NOT NULL,
	user_address VARCHAR(20)
)CHARSET=utf8;

user类

import java.util.Date;

public class User {
    private int userid;
    private String name;
    private String pwd;
    private Date borndate;
    private String email;
    private String address;
    public void User(){}
    public User(int userid, String name, String pwd, String  borndate, String email, String address) {
        this.userid = userid;
        this.name = name;
        this.pwd = pwd;
        this.borndate = DateUtil.strToUtilDate(borndate);
        this.email = email;
        this.address = address;
    }
    @Override
    public String toString() {
        return "User{" +
                "userid=" + userid +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", borndate=" + borndate +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public Date getBorndate() {
        return borndate;
    }

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

    public String getEmail() {
        return email;
    }

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

    public String getAddress() {
        return address;
    }

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

测试类

package day43.user;

import com.qf.day43.user.dao.UserDaoImpl;
import com.qf.day43.user.entity.User;

import java.util.List;

public class TestUser {
    public static void main(String[] args) {
        UserDaoImpl userDao = new UserDaoImpl();
//        User user = new User(1001,"decade","453145","2009-01-25","123@126.com","山西");
//        int i = userDao.insert(user);
//        System.out.println(userDao.insert(user););
//        System.out.println(userDao.delete(1001));
//        System.out.println(userDao.update(user));
//        User user1 = userDao.select(1002);
//        System.out.println(user1);
        List<User> list = userDao.selectAll();
        list.forEach(System.out::println);
    }
}

DAO实现类

package day43.user.dao;

import com.qf.day43.test.DButil;
import com.qf.day43.test.DateUtil;
import com.qf.day43.user.entity.User;

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;

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");

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

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

    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;
    }
}