千峰第43天作业

作业"/>

千峰第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;}@Overridepublic 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","山西");
//        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;}
}