#千锋逆战班#
在千锋“逆战”学习第 43 天,
今天学习的是JDBC的相关内容。
中国加油!武汉加油!千锋加油!
学习的脚步不停止!
天道酬勤,继续加油!
Day 43
作业:user\userinfo
列名 | 类型 | 说明 |
---|---|---|
user_id | 整数、主键 | 用户编号 |
user_name | 字符串,唯一,非空 | 用户名称 |
user_pwd | 字符串,非空 | 用户密码 |
user_borndate | DATE | 出生日期 |
user_email | 字符串,非空 | 邮箱 |
user_address | 字符串 | 地址 |
注意:采用DAO+Entity完成
完成五个方法、增、删、改、查、查所有
DBUtil
package com.qf.day43.entity;
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 (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("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();
}
}
}
user
package com.qf.day43.entity;
import java.util.Date;
/*
user_id INT PRIMARY KEY,
user_name VARCHAR(20) UNIQUE NOT NULL ,
user_pwd VARCHAR(20) NOT NULL,
user_borndate DATE ,
user_email VARCHAR(50) NOT NULL,
user_address VARCHAR(50)
*/
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() {
}
@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;
}
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;
}
public User(String user_name, String user_pwd, Date user_borndate, String user_email, String user_address) {
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;
}
}
DateUtil
package com.qf.day43.dao;
import java.text.ParseException;
import java.text.SimpleDateFormat;
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());
}
}
UserDaoImpl
package com.qf.day43.dao;
import com.qf.day43.entity.DBUtils;
import com.qf.day43.entity.User;
import com.sun.org.apache.regexp.internal.RE;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl {
private Connection connection = null;
PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
//增
public int insert(User user) {
try {
connection = DBUtils.getConnection();
String sql = "insert into user(user_id,user_name,user_pwd,user_borndate,user_email,user_address) value (?,?,?,?,?,?);";
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());
int update = preparedStatement.executeUpdate();
return update;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
return 0;
}
//删
public int delete(int user_id) {
try {
connection = DBUtils.getConnection();
String sql = "delete from user where user_id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, user_id);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
return 0;
}
//改
public int update(User user) {
try {
connection = DBUtils.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.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());
preparedStatement.setInt(6, user.getUser_id());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
return 0;
}
//查
public User select(int user_id) {
try {
connection = DBUtils.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, user_id);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("user_id");
String user_name = resultSet.getString("user_name");
String user_pwd = resultSet.getString("user_pwd");
Date user_borndate = resultSet.getDate("user_borndate");
String user_email = resultSet.getString("user_email");
String user_address = resultSet.getString("user_address");
User user = new User(id, user_name, user_pwd, user_borndate, user_email, user_address);
return user;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
return null;
}
//查所有
public List<User> selectAll() {
List<User> userList = new ArrayList<>();
try {
connection = DBUtils.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("user_id");
String user_name = resultSet.getString("user_name");
String user_pwd = resultSet.getString("user_pwd");
Date user_borndate = resultSet.getDate("user_borndate");
String user_email = resultSet.getString("user_email");
String user_address = resultSet.getString("user_address");
User user = new User(user_id, user_name, user_pwd, user_borndate, user_email, user_address);
userList.add(user);
}
return userList;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
return null;
}
}
TestDao
package com.qf.day43.dao;
import com.qf.day43.entity.User;
import java.util.List;
public class TestDao {
public static void main(String[] args) {
UserDaoImpl udi = new UserDaoImpl();
User user = new User(3,"王小明","123456",DateUtils.strToUtilDate("2002-2-2"),"wxm@qq.com","aaa");
int result = udi.insert(user);
if(result > 0){
System.out.println("新增成功!");
}else{
System.out.println("新增失败!");
}
int result1 = udi.delete(2);
System.out.println(result1);
User user2 = new User("张三疯","1234",DateUtils.strToUtilDate("2003-3-3"),"zsf@qq.com","武当山");
int result2 = udi.update(user2);
System.out.println(result2);
User dd = udi.select(1);
System.out.println(dd);
List<User> userList = udi.selectAll();
userList.forEach(System.out::println);
}
}
运行结果
D:\java\jdk1.8.0_231\bin\java...
新增成功!
1
0
User{user_id=1, user_name='xiaoming', user_pwd='1111', user_borndate=2000-01-01, user_email='xiaoming@qq.com', user_address='地球村'}
User{user_id=1, user_name='xiaoming', user_pwd='1111', user_borndate=2000-01-01, user_email='xiaoming@qq.com', user_address='地球村'}
User{user_id=3, user_name='王小明', user_pwd='123456', user_borndate=2002-02-02, user_email='wxm@qq.com', user_address='aaa'}
Process finished with exit code 0