JDBC工具类
1.工具类的抽取
- 配置文件(在src下创建config.properties)
driverClass=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/db14username=rootpassword=itheima
- 工具类
/*
JDBC工具类
*/
public class JDBCUtils {
//1.私有构造方法
private JDBCUtils(){};
//2.声明配置信息变量
private static String driverClass;
private static String url;
private static String username;
private static String password;
private static Connection con;
//3.静态代码块中实现加载配置文件和注册驱动
static{
try{
//通过类加载器返回配置文件的字节流
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
//创建Properties集合，加载流对象的信息
Properties prop = new Properties();
prop.load(is);
//获取信息为变量赋值
driverClass = prop.getProperty("driverClass");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
//注册驱动
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
//4.获取数据库连接的方法
public static Connection getConnection() {
try {
con = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//5.释放资源的方法
public static void close(Connection con, Statement stat, ResultSet rs) {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement stat) {
close(con,stat,null);
}
}
2.使用工具类优化student表的CRUD
- 查询全部
/*
查询所有学生信息
*/
@Override
public ArrayList<Student> findAll() {
ArrayList<Student> list = new ArrayList<>();
Connection con = null;
Statement stat = null;
ResultSet rs = null;
try{
con = JDBCUtils.getConnection();
//3.获取执行者对象
stat = con.createStatement();
//4.执行sql语句，并且接收返回的结果集
String sql = "SELECT * FROM student";
rs = stat.executeQuery(sql);
//5.处理结果集
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
//封装Student对象
Student stu = new Student(sid,name,age,birthday);
//将student对象保存到集合中
list.add(stu);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.close(con,stat,rs);
}
//将集合对象返回
return list;
}
- 条件查询
/*
条件查询，根据id查询学生信息
*/
@Override
public Student findById(Integer id) {
Student stu = new Student();
Connection con = null;
Statement stat = null;
ResultSet rs = null;
try{
con = JDBCUtils.getConnection();
//3.获取执行者对象
stat = con.createStatement();
//4.执行sql语句，并且接收返回的结果集
String sql = "SELECT * FROM student WHERE sid='"+id+"'";
rs = stat.executeQuery(sql);
//5.处理结果集
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
//封装Student对象
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.close(con,stat,rs);
}
//将对象返回
return stu;
}
- 新增数据
/*
添加学生信息
*/
@Override
public int insert(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
con = JDBCUtils.getConnection();
//3.获取执行者对象
stat = con.createStatement();
//4.执行sql语句，并且接收返回的结果集
Date d = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(d);
String sql = "INSERT INTO student VALUES ('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);
} catch(Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.close(con,stat);
}
//将结果返回
return result;
}
- 修改数据
/*
修改学生信息
*/
@Override
public int update(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
con = JDBCUtils.getConnection();
//3.获取执行者对象
stat = con.createStatement();
//4.执行sql语句，并且接收返回的结果集
Date d = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(d);
String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);
} catch(Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.close(con,stat);
}
//将结果返回
return result;
}
- 删除数据
/*
删除学生信息
*/
@Override
public int delete(Integer id) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
con = JDBCUtils.getConnection();
//3.获取执行者对象
stat = con.createStatement();
//4.执行sql语句，并且接收返回的结果集
String sql = "DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);
} catch(Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.close(con,stat);
}
//将结果返回
return result;
}
3.student表的CRUD整合页面
- 用户表的数据准备
-- 创建用户表
CREATE TABLE USER(
uid VARCHAR(50) PRIMARY KEY, -- 用户id
ucode VARCHAR(50), -- 用户标识
loginname VARCHAR(100), -- 登录用户名
PASSWORD VARCHAR(100), -- 登录密码
username VARCHAR(100), -- 用户名
gender VARCHAR(10), -- 用户性别
birthday DATE, -- 出生日期
dutydate DATE -- 入职日期
);
-- 添加一条测试数据
INSERT INTO `user` VALUES ('11111111', 'zhangsan001', 'zhangsan', '1234', '张三', '男', '2008-10-28', '2018-10-28');
- 将student表的dao层操作复制到项目中的dao层即可
public class StudentDaoImpl implements StudentDao {
/*
查询所有学生信息
*/
@Override
public ArrayList<Student> findAll() {
Connection con = null;
Statement stat = null;
ResultSet rs = null;
ArrayList<Student> list = new ArrayList<>();
try {
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
String sql = "SELECT * FROM student";
rs = stat.executeQuery(sql);
//4.处理结果，将每条记录封装成一个Student对象。将多个Student对象保存到集合中
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
Student stu = new Student(sid,name,age,birthday);
list.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.释放资源
JDBCUtils.close(con,stat,rs);
}
return list;
}
/*
条件查询，根据id查询学生信息
*/
@Override
public Student findById(Integer id) {
Connection con = null;
Statement stat = null;
ResultSet rs = null;
Student stu = new Student();
try {
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
String sql = "SELECT * FROM student WHERE sid='"+id+"'";
rs = stat.executeQuery(sql);
//4.处理结果，将记录封装成一个Student对象。
if(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.释放资源
JDBCUtils.close(con,stat,rs);
}
return stu;
}
/*
新增学生信息
*/
@Override
public int insert(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
Date date = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(date);
String sql = "INSERT INTO student VALUES (null,'"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.释放资源
JDBCUtils.close(con,stat);
}
return result;
}
/*
修改学生信息
*/
@Override
public int update(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
Date date = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(date);
String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.释放资源
JDBCUtils.close(con,stat);
}
return result;
}
/*
删除学生信息
*/
@Override
public int delete(Integer id) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
String sql = "DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.释放资源
JDBCUtils.close(con,stat);
}
return result;
}
}
若有收获，就点个赞吧
0 人点赞
