在项目文件下的src目录下新建一个example包
User:
package example.domain;
import java.util.Date;
//用于保存用户数据的User类
public class User {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public void setId(int id) {
this.id = id;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
public void setEmail(String email) {
this.email = email;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public int getId() {
return id;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
public String getEmail() {
return email;
}
public Date getBirthday() {
return birthday;
}
}
JDBCUtils(用于建立与mysql的连接):
package example.utils;
import java.sql.*;
//专门用于数据库相关操作的工具类
public class JDBCUtils {
//加载驱动,并建立数据库连接
public static Connection getConnection() throws SQLException, ClassNotFoundException{
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
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:
package example.dao;
import example.domain.User;
import example.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
//该类封装了对表Users的添加、查询、删除和更新等操作
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;
}
}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;
}
}
下面是几个测试类:
JdbcInsertTest:
package example;
import example.dao.UsersDao;
import example.domain.User;
import java.util.Date;
//测试类,实现向users表中添加数据的操作
public class JdbcInsertTest {
public static void main(String[] args) {
//向users表插入一个用户信息
UsersDao ud = new UsersDao();
User user = new User();
user.setId(5);
user.setUsername("h1");
user.setPassword("123");
user.setEmail("h1@sina.com");
user.setBirthday(new Date());
boolean b = ud.insert(user);
System.out.println(b);
}
}
FIndAllUsersTest:
package example;
import example.dao.UsersDao;
import example.domain.User;
import java.util.ArrayList;
//测试类,实现读取users表中所有的数据
public class FIndAllUsersTest {
public static void main(String[] args) {
//创建一个名称为usersDao的对象
UsersDao usersDao = new UsersDao();
//将UsersDao对象的findAll()方法执行后的结果放入list集合
ArrayList<User> list = usersDao.findAll();
//循环输出集合中的数据
for(int i = 0; i < list.size(); i ++ ){
System.out.println("第" + (i + 1) + "条数据的username值为:"
+ list.get(i).getUsername());
}
}
}
FindUserByIdTest:
package example;
import example.dao.UsersDao;
import example.domain.User;
//测试类,实现读取users表中指定的数据
public class FindUserByIdTest {
public static void main(String[] args) {
UsersDao usersDao = new UsersDao();
User user = usersDao.find(1);
System.out.println("id为1的User对象的name值为:" + user.getUsername());
}
}
UpdateUserTest:
package example;
import example.dao.UsersDao;
import example.domain.User;
import java.util.Date;
//测试类,实现修改users表中数据的操作
public class UpdateUserTest {
public static void main(String[] args) {
//修改User对象的数据
UsersDao usersDao = new UsersDao();
User user = new User();
user.setId(4);
user.setUsername("zhaoxiaoliu");
user.setPassword("456");
user.setEmail("zhaoxiaoliu@sina.com");
user.setBirthday(new Date());
boolean b = usersDao.update(user);
System.out.println(b);
}
}
DeleteUserTest:
package example;
import example.dao.UsersDao;
//测试类,实现删除users表中数据的操作
public class DeleteUserTest {
public static void main(String[] args) {
UsersDao usersDao = new UsersDao();
boolean b = usersDao.delete(4);
System.out.println(b);
}
}