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