千锋逆战班:孙华建
在千锋学习第43天
“未来的你会感谢今天奋斗的自己”
今天我学习了封装工具类,ORM封装,DAO数据访问对象,Date时间类型
#中国加油!武汉加油!千锋加油!也为自己加油!!!#…
课后练习:根据今日所学,结合数据库componydb中的User表格,完成增删改查
数据库工具类(可跨平台)
db.properties
driver = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/componydb?useUnicode=true&characterEncoding=utf8
user = root
password = 19951023sun
DBUtils
package t2.example;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 数据库工具类
* 1,获取连接
* 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 (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection = null;
try {
connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
} catch (Exception 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();
}
}
}
ORM封装User
package t2.example;
import java.util.Date;
public class User {
private int user_id;
private String user_name;
private String usr_pwd;
private Date user_borndate;
private String user_email;
private String address;
public User() {
}
public User(int user_id, String user_name, String usr_pwd, Date user_borndate, String user_email, String address) {
this.user_id=user_id;
this.user_name = user_name;
this.usr_pwd = usr_pwd;
this.user_borndate = user_borndate;
this.user_email = user_email;
this.address = address;
}
@Override
public String toString() {
return "User{" +
"user_id='" + user_id + '\'' +
", user_name='" + user_name + '\'' +
", usr_pwd='" + usr_pwd + '\'' +
", user_borndate=" + user_borndate +
", user_email='" + user_email + '\'' +
", address=" + 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 getUsr_pwd() {
return usr_pwd;
}
public void setUsr_pwd(String usr_pwd) {
this.usr_pwd = usr_pwd;
}
public java.util.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 getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
Dao实现类
package t3.example;
import t2.example.User;
import t2.example.DBUtils;
import t4.example.DateUtils;
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){
connection= DBUtils.getConnection();
String sql = "insert into user(user_id,user_name,user_pwd,user_borndate,user_email,user_address) values(?,?,?,?,?,?)";
try {
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,user.getUser_id());
preparedStatement.setString(2,user.getUser_name());
preparedStatement.setString(3,user.getUsr_pwd());
preparedStatement.setDate(4,DateUtils.utilToSql(user.getUser_borndate()));
preparedStatement.setString(5,user.getUser_email());
preparedStatement.setString(6,user.getAddress());
int i = preparedStatement.executeUpdate();
return i;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,null);
}
return 0;
}
//删
public int delete(int id){
connection = DBUtils.getConnection();
String sql = "delete from user where user_id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return 0;
}
//改
public int update(User user){
connection = DBUtils.getConnection();
String sql = "update user set user_name=?,user_pwd=?,user_borndate=?,user_email =?,user_address=? where user_id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(6,user.getUser_id());
preparedStatement.setString(1,user.getUser_name());
preparedStatement.setString(2,user.getUsr_pwd());
preparedStatement.setDate(3, DateUtils.utilToSql(user.getUser_borndate()));
preparedStatement.setString(4,user.getUser_email());
preparedStatement.setString(5,user.getAddress());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return 0;
}
//查单个
public User select(int id){
connection = DBUtils.getConnection();
String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from user where user_id = ?;";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id1 = resultSet.getInt("user_id");
String username = resultSet.getString("user_name");
String password = resultSet.getString("user_pwd");
java.sql.Date user_borndate = resultSet.getDate("user_borndate");
String email = resultSet.getString("user_email");
String address = resultSet.getString("user_address");
User user = new User(id1,username,password,user_borndate,email,address);
return user;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return null;
}
//查多个
public List<User> selectAll(){
connection = DBUtils.getConnection();
String sql = "select user_id,user_name,User_pwd,User_borndate,user_email,user_address from user";
List<User> userList = new ArrayList<>();
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("user_id");
String username = resultSet.getString("user_name");
String password = resultSet.getString("user_pwd");
java.sql.Date user_borndate = resultSet.getDate("user_borndate");
String email = resultSet.getString("user_email");
String address = resultSet.getString("user_address");
User user = new User(id,username,password,user_borndate,email,address);
userList.add(user);//每封装完一个对象,添加到集合当中
}
return userList;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return null;
}
}
时间类型转换
package t4.example;
import java.text.ParseException;
import java.text.SimpleDateFormat;
/**
* 日期转换
* 字符串转UtilDate
* 字符串转SqlDate
* utilDate转成Sqldate
*/
public class DateUtils {
private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//字符串转Util
public static java.util.Date strToUtilDate(String str) {
try {
return simpleDateFormat.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
//字符串转sql
// public static java.sql.Date strToSqlDate(String str){
// SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
// try {
// java.util.Date date = simpleDateFormat.parse(str);
// return new java.sql.Date(date.getTime());
// } 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());
}
}
测试
package t3.example;
import t2.example.User;
import t4.example.DateUtils;
public class TestUser {
public static void main(String[] args) {
UserDaoImpl UD = new UserDaoImpl();
User user = new User(1,"小红","33333", DateUtils.strToUtilDate("1999-01-04"),"1111111111@qq.com","山东省青岛市");
User user1 = new User(2,"小萍","55555", DateUtils.strToUtilDate("2000-11-24"),"2222222222@qq.com","山东省枣庄市");
User user2 = new User(3,"小王","6666", DateUtils.strToUtilDate("1997-03-04"),"3333333333@qq.com","山东省菏泽市");
User user3 = new User(4,"小黑","77777", DateUtils.strToUtilDate("1995-10-04"),"44444444444@qq.com","山东省济宁市");
// UD.insert(user);
// UD.insert(user1);
// UD.insert(user2);
// UD.insert(user3);
UD.delete(3);
System.out.println(UD.update(user));
System.out.println(UD.select(1));
UD.selectAll();
}
}