1.创建javaBean封装数据：User
package cn.itcast.jdbc.example;
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;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return "id="+id+" "
+"name="+username+" "
+"password="+password+" "
+"email="+email+" "
+"birthday="+birthday+" ";
}
}
2.创建工具类：JDBCUtils,在开发中提取工具类，是为了简化咱们的代码，使用起来简单方便。
package cn.itcast.jdbc.example;
import java.sql.Connection;
public class JDBCUtils {
// 获取连接对象的方法
public static Connection getCon() throws Exception {
// 1.注册和加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ydd01", "root", "123456");
return con;
}
// 关闭连接，释放资源
public static void realse(ResultSet rs, Statement stmt, Connection con) {
// 6.关闭连接，释放资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
con = null;
}
//System.out.println("关闭资源代码已执行");
}
}
3.创建一个Dao类，UserDao
package cn.itcast.jdbc.example;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.StatementEvent;
/**
* 完成对数据库的增删改查
*
* @author 12113
*
*/
public class UsersDao {
// 1.提供一个添加方法
public boolean insert(User user) {
Connection con = null;
Statement stmt = null;
try {
// 1.获取连接对象
con = JDBCUtils.getCon();
// 2.获取执行sql的对象
stmt = con.createStatement();
// 3.执行sql
java.util.Date birthday = user.getBirthday();
String sqlBirthday = String.format("%tF", birthday);
String sql01 = "insert into users(id,name,password,email,birthday)" + "values('" + user.getId() + "','"
+ user.getUsername() + "','" + user.getPassword() + "','" + user.getEmail() + "','" + sqlBirthday
+ "'" + ")";
// 打印sql语句
System.out.println(sql01);
int row = stmt.executeUpdate(sql01);
if (row > 0) {
// 插入成功
return true;
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.realse(null, stmt, con);
}
return false;
}
// 2.提供查询所有的方法
public List<User> findAllUser() {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1.获取连接对象
con = JDBCUtils.getCon();
// 2.获取执行sql语句的对象
stmt = con.createStatement();
String sql01 = "select * from users";
rs = stmt.executeQuery(sql01);
// 4.遍历rs
List<User> list = new ArrayList<User>();
while (rs.next()) {
// 一行数据对应一个对象，获取每一行对象，就设置给一个User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
java.sql.Date birthday = rs.getDate("birthday");
user.setBirthday(birthday);
// user.setBirthday(rs.getDate("birthday"));//和上面两行一样
// 把对象添加到集合中
list.add(user);
}
return list;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.realse(rs, stmt, con);
}
}
// 3.根据id查询记录
public User findUserById(int id) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 1.获取连接对象
con = JDBCUtils.getCon();
// 2.获取执行sql语句的对象
String sql01 = "select * from users where id=?";
stmt = con.prepareStatement(sql01);
// 3.执行sql，给id赋值
stmt.setInt(1, id);
rs = stmt.executeQuery();
// 4.遍历rs
if (rs.next()) {
// 一行数据对应一个对象，获取每一行对象，就设置给一个User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
java.sql.Date birthday = rs.getDate("birthday");
user.setBirthday(birthday);
// user.setBirthday(rs.getDate("birthday"));//和上面两行一样
return user;
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.realse(rs, stmt, con);
}
return null;
}
// 4.根据id修改记录
public boolean updateUserById(User user) {
Connection con = null;
PreparedStatement stmt = null;
try {
// 1.获取连接对象
con = JDBCUtils.getCon();
// 2.获取执行sql语句的对象
String sql01 = "update users set name=?,password=? where id=?";
stmt = con.prepareStatement(sql01);
// 3.执行sql，给id赋值
stmt.setString(1, user.getUsername());
stmt.setString(2, user.getPassword());
stmt.setInt(3, user.getId());
int row = stmt.executeUpdate();
if (row > 0) {
return true;
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.realse(null, stmt, con);
}
return false;
}
// 5.删除
public boolean deleteById(int id) {
Connection con = null;
PreparedStatement stmt = null;
try {
// 1.获取连接对象
con = JDBCUtils.getCon();
// 2.获取执行sql语句的对象
String sql01 = "delete from users where id=?";
stmt = con.prepareStatement(sql01);
// 3.执行sql，给id赋值
stmt.setInt(1, id);
int row = stmt.executeUpdate();
if (row > 0) {
return true;
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.realse(null, stmt, con);
}
return false;
}
}
4.创建测试类：添加，查询，修改，删除的测试类。
package cn.itcast.jdbc.example;
import java.util.Date;
import java.util.List;
public class FindAllUserTest {
public static void main(String[] args) {
UsersDao dao = new UsersDao();
List<User> list = dao.findAllUser();
System.out.println(list.size());
}
}
评论区