一、创建javabean：User
public class User {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
//补充getter setter
二、创建操作数据库的工具类
public class JDBCUtils {
// 加载驱动，并建立数据库连接
public static Connection getConnection() throws SQLException,
ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username,
password);
return conn;
}
// 关闭数据库连接，释放资源
public static void release(Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(ResultSet rs, Statement stmt,
Connection conn){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
release(stmt, conn);
}
}
三、创建UsersDao
public class UsersDao {
// 添加用户的操作
public boolean insert(User user) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.getBirthday());
String sql = "INSERT INTO users(id,name,password,email,birthday) "+
"VALUES("
+ user.getId()
+ ",'"
+ user.getUsername()
+ "','"
+ user.getPassword()
+ "','"
+ user.getEmail()
+ "','"
+ birthday + "')";
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
// 查询所有的User对象
public ArrayList<User> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<User> list = new ArrayList<User>();
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
String sql = "SELECT * FROM users";
rs = stmt.executeQuery(sql);
// 处理结果集
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
list.add(user);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
// 根据id查找指定的user
public User find(int id) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
String sql = "SELECT * FROM users WHERE id=" + id;
rs = stmt.executeQuery(sql);
// 处理结果集
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
return user;
}
return null;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
// 删除用户
public boolean delete(int id) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
String sql = "DELETE FROM users WHERE id=" + id;
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
// 修改用户
public boolean update(User user) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.getBirthday());
String sql = "UPDATE users set name='" + user.getUsername()
+ "',password='" + user.getPassword() + "',email='"
+ user.getEmail() + "',birthday='" + birthday
+ "' WHERE id=" + user.getId();
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
}
四、创建测试类(使用junit5)
public class UsersDaoTest {
private UsersDao ud;
@BeforeEach
void init() {
ud = new UsersDao();
}
@Test
void insertTest() {
// 向users表插入一个用户信息
User user = new User();
user.setUsername("hl");
user.setPassword("123");
user.setEmail("hl@sina.com");
user.setBirthday(new Date());
boolean b = ud.insert(user);
System.out.println(b);
}
@Test
void findAllUsersTest() {
// 将UsersDao对象的findAll()方法执行后的结果放入list集合
ArrayList<User> list = ud.findAll();
// 循环输出集合中的数据
for (int i = 0; i < list.size(); i++) {
System.out.println("第" + (i + 1) + "条数据的username值为:" + list.get(i).getUsername());
}
}
@Test
void findUserByIdTest() {
User user = ud.find(1);
System.out.println("id为1的User对象的name值为：" + user.getUsername());
}
@Test
void updateUserTest() {
User user = new User();
user.setId(4);
user.setUsername("zhaoxiaoliu");
user.setPassword("456");
user.setEmail("zhaoxiaoliu@sina.com");
user.setBirthday(new Date());
boolean b = ud.update(user);
System.out.println(b);
}
@Test
void deleteUserTest() {
boolean b = ud.delete(4);
System.out.println(b);
}
}
|