package cn.itcast.jdbc.mainjdbc;
import java.util.Date;
public class User {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthDay() {
return birthday;
}
public void setBirthDay(Date birthday) {
this.birthday = birthday;
}
}
package cn.itcast.jdbc.mainjdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
public class JDBCUtils {
// 获取连接对象的方法
public static Connection getConnection() throws Exception {
// 注册并加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取链接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
return connection;
}
// 关闭连接，并释放资源
public static void realse(ResultSet resultSet,Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception exception) {
exception.printStackTrace();
}
connection = null;
}
}
}
package cn.itcast.jdbc.mainjdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/*
* 完成对数据库的crud操作
*/
public class UsersDao {
// 1.提供添加方法
public boolean insert(User user) {
Connection connection = null;
Statement statement = null;
try {
// 1.获取连接对象
connection = JDBCUtils.getConnection();
// 2.获取执行sql语句的对象
statement = connection.createStatement();
// 3.执行sql
java.util.Date birthday = user.getBirthDay();
String sqlBirthDay = String.format("%tF", birthday);
String sql = "insert into users(id,name,password,email,birthday)" + "Values('" + user.getId() + "','"
+ user.getUsername() + "','" + user.getPassword() + "','" + user.getEmail() + "','" + sqlBirthDay
+ "'" + ")";
int row = statement.executeUpdate(sql);
if (row > 0) {
// 插入成功
return true;
}
} catch (Exception exception) {
throw new RuntimeException(exception);
} finally {
JDBCUtils.realse(null, statement, connection);
}
return false;
}
// 2.提供查询方法
public List<User> findAllUser() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 1.获取连接对象
connection = JDBCUtils.getConnection();
// 2.获取执行sql语句的对象
statement = connection.createStatement();
// 3.执行sql
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
// 4.遍历
List<User> list = new ArrayList<User>();
while (resultSet.next()) {
// 一行数据对应一个对象，获取每一行的数据，就设置给一个user对象
User user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
java.sql.Date birthday = resultSet.getDate("birthday");
user.setBirthDay(birthday);
// 把对象添加到集合中
list.add(user);
}
return list;
} catch (Exception exception) {
throw new RuntimeException(exception);
} finally {
JDBCUtils.realse(resultSet, statement, connection);
}
}
// 3.根据id查询记录
public User findUserById(int id) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
// 1.获取连接对象
connection = JDBCUtils.getConnection();
// 2.获取执行sql语句的对象
String sql = "select * from users where id = ?";
statement = connection.prepareStatement(sql);
// 3.执行sql，给id赋值
statement.setInt(1, id);
resultSet = statement.executeQuery();
// 4.遍历
if (resultSet.next()) {
// 一行数据对应一个对象，获取每一行的数据，就设置给一个user对象
User user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
java.sql.Date birthday = resultSet.getDate("birthday");
user.setBirthDay(birthday);
return user;
// 把对象添加到集合中
}
} catch (Exception exception) {
throw new RuntimeException(exception);
} finally {
JDBCUtils.realse(resultSet, statement, connection);
}
return null;
}
// 4.修改方法，根据id修改记录
public boolean update(User user) {
Connection connection = null;
PreparedStatement statement = null;
try {
// 1.获取连接对象
connection = JDBCUtils.getConnection();
// 2.获取执行sql语句的对象
String sql = "update users set name = ?,password = ?,email =? where id = ?";
statement = connection.prepareStatement(sql);
// 3.执行sql,给占位符赋值
statement.setString(1, user.getUsername());
statement.setString(2, user.getPassword());
statement.setString(3, user.getEmail());
statement.setInt(4, user.getId());
int row = statement.executeUpdate();
if (row > 0) {
// 插入成功
return true;
}
} catch (Exception exception) {
throw new RuntimeException(exception);
} finally {
JDBCUtils.realse(null, statement, connection);
}
return false;
}
//5.删除
public boolean delete(int id) {
Connection connection = null;
PreparedStatement statement = null;
try {
// 1.获取连接对象
connection = JDBCUtils.getConnection();
// 2.获取执行sql语句的对象
String sql = "delete from users where id = ?";
statement = connection.prepareStatement(sql);
// 3.执行sql,给占位符赋值
statement.setInt(1, id);
int row = statement.executeUpdate();
if (row > 0) {
// 插入成功
return true;
}
} catch (Exception exception) {
throw new RuntimeException(exception);
} finally {
JDBCUtils.realse(null, statement, connection);
}
return false;
}
}
