JDBC: Java Database Connectivity ------> 通过Java代码连接\操作关系型数据库
JDBC是Sun公司提供的一套操作所有关系型数据库的接口(标准).各个关系型数据库厂商会按照这个接口提供一套连接自家数据库的实现类,将这些实现类打成Jar包,即数据库驱动Jar包.程序员只需学习JDBC接口,既可以通过这些驱动Jar包连接不同的数据库
核心API
- java.sql.Connection 数据库连接对象,连接上数据库
- java.sql.PreparedStatement 根据数据库连接对象conn获得发送SQL对象
- java.sql.ResultSet 结果集对象,保存查询语句的查询结果
- java.sql.Driver 数据库驱动对象
- java.sql.DriverManager 数据库驱动管理对象
实际开发流程
- 建表
- 创建项目
- 在项目根目录下创建lib文件夹,与src同级,用来存放项目需要jar包
- Build Path: 右键单击jar包–> Build Path --> Add to Build Path
- 在src中导入jdbc配置文件
- 构建Package结构: 公司域名倒置.模块名
- com.xxxx.entity 实体类
- com.xxxx.util 工具类
- com.xxxx.dao Dao接口
- com.xxxx.dao.impl Dao接口实现类
- com.xxxx.service Service接口
- com.xxxx.service.impl Service接口实现类
- com.xxxx.test 测试类
com.xxxx.entity 实体类
package com.xxxx.entity;
public class Student {
private Integer Student_id;
private String Student_name;
private Integer age;
private String sex;
private Double sorce;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(Integer Student_id, String Student_name, Integer age, String sex, Double sorce) {
super();
this.Student_id = Student_id;
this.Student_name = Student_name;
this.age = age;
this.sex = sex;
this.sorce = sorce;
}
public Integer getStudent_id() {
return Student_id;
}
public void setStudent_id(Integer Student_id) {
this.Student_id = Student_id;
}
public String getStudent_name() {
return Student_name;
}
public void setStudent_name(String Student_name) {
this.Student_name = Student_name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Double getSorce() {
return sorce;
}
public void setSorce(Double sorce) {
this.sorce = sorce;
}
@Override
public String toString() {
return "Student [Student_id=" + Student_id + ", Student_name=" + Student_name + ", age=" + age + ", sex=" + sex
+ ", sorce=" + sorce + "]";
}
}
com.xxxx.util 工具类
package com.xxxx.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil3 {
//private 是为了控制pp的访问权限
//static 是为了让pp成为全类共有,每次调用JDBCUtil3时,拿到的都是同一个pp
private static Properties pp = new Properties();
//静态代码块,是为了保证读取配置文件的操作只会进行一次
static{
try (//0. 在src目录下写配置文件
//1. 通过输入流读取配置文件
//类加载输入流是以src作为根目录
InputStream is = JDBCUtil3.class.getClassLoader().getResourceAsStream("jdbc.properties")) {
//2.将配置文件中的数据读取到该集合对象中
pp.load(is);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取数据库连接对象
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(pp.getProperty("driverClass"));
conn = DriverManager.getConnection(pp.getProperty("url"), pp.getProperty("user"), pp.getProperty("password"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//释放资源 ResultSet PreparedStatement Connection
public static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void closeAll(PreparedStatement ps,Connection conn) {
closeAll(null, ps, conn);
}
}
jdbc.properties
driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xxx(数据库名)?useEncoding=true&characterSet=utf-8&serverTimezone=GMT%2B8
user=root
password=root
com.xxxx.dao Dao接口
package com.xxxx.dao;
import java.util.List;
import com.baizhi.entity.Student;
public interface StudentDao {
int insert(Student s);
int delete(int id);
int update(Student s);
Student selectOne(int id);
List<Student> selectAll();
}
com.xxxx.dao.impl Dao接口实现类
package com.xxxx.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.baizhi.dao.StudentDao;
import com.baizhi.entity.Student;
import com.baizhi.util.JDBCUtil3;
public class StudentDaoImpl implements StudentDao{
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
@Override
public int insert(Student s) {
conn = JDBCUtil3.getConnection();
String sql = "insert into t_Student values(0,?,?,?,?)";
int n = 0;
try {
ps = conn.prepareStatement(sql);
ps.setString(1, s.getStudent_name());
ps.setInt (2, s.getAge());
ps.setString(3, s.getSex());
ps.setDouble(4, s.getSorce());
n = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(ps, null);
}
return n;
}
@Override
public int delete(int Student_id) {
conn = JDBCUtil3.getConnection();
String sql = "delete from t_student where Student_id = ?";
int n = 0;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1,Student_id);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(ps, conn);
}
return n;
}
@Override
public int update(Student s) {
conn = JDBCUtil3.getConnection();
String sql = "update t_student set student_name =?,age =?,sex =?,score =?where student_id=?";
//n代表数据库操作中受影响行数，若为1表示操作执行成功，若为0表示失败
int n = 0;
try {
ps = conn.prepareStatement(sql);
ps.setInt(5, s.getStudent_id());
ps.setString(1, s.getStudent_name());
ps.setInt (2, s.getAge());
ps.setString(3, s.getSex());
ps.setDouble(4, s.getSorce());
n = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(ps, conn);
}
return n;
}
@Override
public Student selectOne(int Student_id) {
conn = JDBCUtil3.getConnection();
String sql = "select * from t_Student where Student_id = ?";
Student s = null;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, Student_id);
rs = ps.executeQuery();
if(rs.next()) {
String name = rs.getString("Student_name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
double score = rs.getDouble("score");
s = new Student(Student_id,name,age,sex,score);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(rs, ps, conn);
}
return s;
}
@Override
public List<Student> selectAll() {
conn = JDBCUtil3.getConnection();
String sql = "select * from t_student";
List<Student> list = new ArrayList<Student>();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
int Student_id = rs.getInt("Student_id");
String Student_name = rs.getString("Student_name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
double score = rs.getDouble("score");
Student s = new Student(Student_id,Student_name,age,sex,score);
list.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(rs,ps,null);
}
return list;
}
}
com.xxxx.service Service接口
package com.xxxx.service;
import java.util.List;
import com.baizhi.entity.Student;
public interface StudentService {
boolean insert(Student s);
boolean delete(int id);
boolean update(Student s);
Student selectByID(int id);
List<Student> showAll();
}
com.xxxx.service.impl Service接口实现类
package com.baizhi.serviceimpl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import com.baizhi.dao.StudentDao;
import com.baizhi.dao.impl.StudentDaoImpl;
import com.baizhi.entity.Student;
import com.baizhi.service.StudentService;
import com.baizhi.util.JDBCUtil3;
public class StudentServiceImpl implements StudentService{
private Connection conn;
private StudentDao sd = new StudentDaoImpl();
@Override
public boolean insert(Student s) {
conn = JDBCUtil3.getConnection();
int n = 0;
try {
conn.setAutoCommit(false);
n = sd.insert(s);
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(null,conn);
}
return n!= 0;
}
@Override
public boolean delete(int id) {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean update(Student s) {
conn = JDBCUtil3.getConnection();
int n = 0;
try {
conn.setAutoCommit(false);
n = sd.update(s);
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(null, conn);
}
return n!=0;
}
@Override
public Student selectByID(int id) {
conn = JDBCUtil3.getConnection();
Student s = null;
try {
conn.setAutoCommit(false);
s = sd.selectOne(id);
conn.commit();
} catch (SQLException e1) {
// TODO Auto-generated catch block
try {
conn.rollback();
} catch (SQLException e11) {
// TODO Auto-generated catch block
e11.printStackTrace();
}
e1.printStackTrace();
}finally {
JDBCUtil3.closeAll(null, conn);
}
return s;
}
@Override
public List<Student> showAll() {
conn = JDBCUtil3.getConnection();
List<Student> list = null;
try {
conn.setAutoCommit(false);
list = sd.selectAll();
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
conn.commit();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtil3.closeAll(null, conn);
}
return list;
}
}
com.xxxx.test 测试类
package com.xxxx.test;
import com.baizhi.entity.Student;
import com.baizhi.service.StudentService;
import com.baizhi.serviceimpl.StudentServiceImpl;
public class StudentTest {
public static void main(String[] args) {
StudentService ss = new StudentServiceImpl();
// System.out.println("请输入学生信息");
// System.out.println("输入完成");
// boolean b = ss.insert(new Student(1,"张飞",19,"男",90.0));
// if(b) {
// System.out.println("注册成功");
// }else {
// System.out.println("注册失败");
// }
//
// if(ss.update(new Student(4,"张飞", 20, "男",97.89))) {
// System.out.println("修改成功");
// }else {
// System.out.println("修改失败");
// }
// System.out.println(es.delete(4)?"删除成功":"删除失败");
// System.out.println(ss.selectByID(2));
// ss.showAll().forEach(e->System.out.println(e));
}
}
创建数据库表
create table t_student(
student_id int primary key auto_increment,
student_name varchar(10) not null,
age int not null,
sex varchar(2) not null,
score double not null
);
8975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
