创建配置文件
driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db11
username=root
password=123456
编写JDBC的工具类
- 私有空参构造方法
- 声明所需要的配置变量
- 声明连接对象
- 读取配置文件 将需要配置的变量进行赋值
- 注册驱动
- 提供获取数据库连接的方法
- 释放资源 分为查询和增删改两种重载的方法
package com.itheima01.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private JDBCUtils(){}
private static String driverClass;
private static String url;
private static String username;
private static String password;
private static Connection con;
static {
try {
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.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 (IOException ex) {
throw new RuntimeException(ex);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
con = DriverManager.getConnection(url,username,password);
return con;
}
public static void close(Connection con, Statement stat, ResultSet res) throws SQLException {
if(con!= null){
con.close();
}
if(stat!= null){
stat.close();
}
if(res != null){
res.close();
}
}
public static void close(Connection con, Statement stat) throws SQLException {
if(con!= null){
con.close();
}
if(stat!= null){
stat.close();
}
}
}
优化学生信息管理CRUD的案例
package com.itheima01.controller;
import com.itheima01.domain.Student;
import com.itheima01.service.StudentService;
import com.itheima01.service.StudentServiceImpl;
import org.junit.Test;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
public class StudentController {
private StudentService service = new StudentServiceImpl();
@Test
public void findAll() throws SQLException, ClassNotFoundException {
ArrayList<Student> list = service.findAll();
for (Student student : list) {
System.out.println(student);
}
}
public void findById() throws SQLException {
Student stu = service.findById(3);
System.out.println(stu);
}
@Test
public void insert() throws SQLException {
Student stu = new Student(10,"周七",27,new Date());
int insert = service.insert(stu);
if(insert != 0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
}
@Test
public void update() throws SQLException {
Student stu = service.findById(5);
stu.setName("周星星");
int update = service.update(stu);
if(update != 0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
}
@Test
public void delete() throws SQLException {
int delete = service.delete(5);
if(delete != 0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}
}
package com.itheima01.dao;
import com.itheima01.domain.Student;
import java.sql.SQLException;
import java.util.ArrayList;
public interface StudentDao {
public abstract ArrayList<Student> findAll() throws ClassNotFoundException, SQLException;
public abstract Student findById(Integer id) throws SQLException;
public abstract int insert(Student stu) throws SQLException;
public abstract int update(Student stu) throws SQLException;
public abstract int delete(Integer id) throws SQLException;
}
package com.itheima01.dao;
import com.itheima01.domain.Student;
import com.itheima01.utils.JDBCUtils;
import javax.swing.plaf.InsetsUIResource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
public class StudentDaoImpl implements StudentDao{
@Override
public ArrayList<Student> findAll() throws ClassNotFoundException, SQLException {
ArrayList<Student> list = new ArrayList<>();
Connection con = null;
Statement statement = null;
ResultSet resultSet = null;
try{
con = JDBCUtils.getConnection();
statement = con.createStatement();
String sql = "SELECT * FROM student";
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int sid = resultSet.getInt("sid");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Date birthday = resultSet.getDate("birthday");
Student stu = new Student(sid,name,age,birthday);
list.add(stu);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.close(con,statement,resultSet);
}
return list;
}
@Override
public Student findById(Integer id) throws SQLException {
Student s = new Student();
Connection con = null;
Statement statement = null;
ResultSet resultSet = null;
try{
con = JDBCUtils.getConnection();
statement = con.createStatement();
String sql = "SELECT * FROM student WHERE sid ='"+id+"'";
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int sid = resultSet.getInt("sid");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Date birthday = resultSet.getDate("birthday");
s.setAge(age);
s.setName(name);
s.setBirthday(birthday);
s.setSid(sid);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.close(con,statement,resultSet);
}
return s;
}
@Override
public int insert(Student stu) throws SQLException {
Connection con = null;
Statement statement = null;
int result = 0;
try{
con = JDBCUtils.getConnection();
statement = con.createStatement();
java.util.Date d = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String f = sdf.format(d);
String sql = "INSERT INTO student VALUES('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+f+"')";
result = statement.executeUpdate(sql);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.close(con,statement);
}
return result;
}
@Override
public int update(Student stu) throws SQLException {
Connection con = null;
Statement statement = null;
int result = 0;
try{
con = JDBCUtils.getConnection();
statement = con.createStatement();
java.util.Date d = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String f = sdf.format(d);
String sql = "UPDATE student SET sid = '"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+f+"' WHERE sid = '"+stu.getSid()+"'";
result = statement.executeUpdate(sql);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.close(con,statement);
}
return result;
}
@Override
public int delete(Integer id) throws SQLException {
Connection con = null;
Statement statement = null;
int result = 0;
try{
con = JDBCUtils.getConnection();
statement = con.createStatement();
String sql = "DELETE FROM student WHERE sid = '"+id+"'";
result = statement.executeUpdate(sql);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.close(con,statement);
}
return result;
}
}
package com.itheima01.domain;
import java.util.Date;
public class Student {
private Integer sid;
private String name;
private Integer age;
private Date birthday;
public Student() {
}
public Student(Integer sid, String name, Integer age, Date birthday) {
this.sid = sid;
this.name = name;
this.age = age;
this.birthday = birthday;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
}
package com.itheima01.service;
import com.itheima01.domain.Student;
import java.sql.SQLException;
import java.util.ArrayList;
public interface StudentService {
public abstract ArrayList<Student> findAll() throws SQLException, ClassNotFoundException;
public abstract Student findById(Integer id) throws SQLException;
public abstract int insert(Student stu) throws SQLException;
public abstract int update(Student stu) throws SQLException;
public abstract int delete(Integer id) throws SQLException;
}
package com.itheima01.service;
import com.itheima01.dao.StudentDao;
import com.itheima01.dao.StudentDaoImpl;
import com.itheima01.domain.Student;
import java.sql.SQLException;
import java.util.ArrayList;
public class StudentServiceImpl implements StudentService{
private StudentDao dao = new StudentDaoImpl();
@Override
public ArrayList<Student> findAll() throws SQLException, ClassNotFoundException {
return dao.findAll();
}
@Override
public Student findById(Integer id) throws SQLException {
return dao.findById(id);
}
@Override
public int insert(Student stu) throws SQLException {
return dao.insert(stu);
}
@Override
public int update(Student stu) throws SQLException {
return dao.update(stu);
}
@Override
public int delete(Integer id) throws SQLException {
return dao.delete(id);
}
}
package com.itheima01.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private JDBCUtils(){}
private static String driverClass;
private static String url;
private static String username;
private static String password;
private static Connection con;
static {
try {
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.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 (IOException ex) {
throw new RuntimeException(ex);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
con = DriverManager.getConnection(url,username,password);
return con;
}
public static void close(Connection con, Statement stat, ResultSet res) throws SQLException {
if(con!= null){
con.close();
}
if(stat!= null){
stat.close();
}
if(res != null){
res.close();
}
}
public static void close(Connection con, Statement stat) throws SQLException {
if(con!= null){
con.close();
}
if(stat!= null){
stat.close();
}
}
}