实现步骤
1.创建User类
package cn.itcast.jdbc.example.domain;
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;
}
}
2.创建JDBCUtils类
package cn.itcast.jdbc.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/jdbc?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 coon) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
release(stmt, coon);
}
}
3.创建UsersDao类
package cn.itcast.jdbc.example.dao;
import cn.itcast.jdbc.example.domain.User;
import cn.itcast.jdbc.example.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
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();
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;
}
}
4.创建JdbcInsertTest类
package cn.itcast.jdbc.example;
import cn.itcast.jdbc.example.dao.UsersDao;
import cn.itcast.jdbc.example.domain.User;
import java.util.Date;
public class JdbcInsertTest {
public static void main(String[] args) {
// 向users表插入一个用户信息
UsersDao ud = new UsersDao();
User user = new User();
user.setId(5);
user.setUsername("hl");
user.setPassword("123");
user.setEmail("hl@sina.com");
user.setBirthday(new Date());
boolean b = ud.insert(user);
System.out.println(b);
}
}
5.创建FindAllUsersTest类
package cn.itcast.jdbc.example;
import cn.itcast.jdbc.example.dao.UsersDao;
import cn.itcast.jdbc.example.domain.User;
import java.util.ArrayList;
public class FindAllUsersTest {
public static void main(String[] args) {
UsersDao usersDao = new UsersDao();
ArrayList<User> list = usersDao.findAll();
for (int i=0;i<list.size();i++) {
System.out.println("第" + (i+1) + "条数据的username值为:" + list.get(i).getUsername());
}
}
}
6.创建FindUserByIdTest类
package cn.itcast.jdbc.example;
import cn.itcast.jdbc.example.dao.UsersDao;
import cn.itcast.jdbc.example.domain.User;
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());
}
}
7.创建UpdateUserTest类
package cn.itcast.jdbc.example;
import cn.itcast.jdbc.example.dao.UsersDao;
import cn.itcast.jdbc.example.domain.User;
import java.util.Date;
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);
}
}
8.创建DeleteUserTest类
package cn.itcast.jdbc.example;
import cn.itcast.jdbc.example.dao.UsersDao;
public class DeleteUserTest {
public static void main(String[] args) {
// 删除操作
UsersDao usersDao = new UsersDao();
boolean b = usersDao.delete(4);
System.out.println(b);
}
}