JDBC
一、JDBC连接步骤
建立JDBC连接所涉及的编程相当简单。这是简单的四个步骤
- 导入JDBC包：将Java语言的*import*语句添加到Java代码中导入所需的类。
- 注册JDBC驱动程序：此步骤将使JVM将所需的驱动程序实现加载到内存中，以便它可以满足您的JDBC
请求。Class.forName();//Class.forName("com.mysql.cj.jdbc.Driver"); Mysql80 中多了一个cj文件
- 数据库URL配置：这是为了创建一个格式正确的地址，指向要连接到的数据库。
- 创建连接对象：最后，调用DriverManager对象的getConnection（）方法来建立实际的数据库连
接。
-创建状态通道，定义sql（进行sql语句的发送)
-取得结果集或影响的行数
-关闭资源
二、Statement(状态通道)
操作步骤一
/**
* 操作步骤1（executeQuery（），查找数据select）
*/
public static void main(String[] args) {
// 导包
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1、加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、获得链接
String userName ="root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url,userName,passWord);
//3、定义sql，创建状态通道
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from employee"); //返回一个结果集 resultSet对象接收
//取出数据
while (resultSet.next()){
//取出数据：resultSet.getXXX("列名")；xxx表示数据类型
System.out.println("姓名："+resultSet.getString("name")+
",性别："+resultSet.getString("sex")+
",出生年月："+ resultSet.getDate("birthday"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
if (connection!= null){
connection.close();
}
if (statement != null){
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//
运行结果
姓名：张三,性别：男,出生年月：1975-01-01
姓名：李四,性别：女,出生年月：1985-01-01
姓名：王五,性别：男,出生年月：1978-11-11
姓名：赵六,性别：男,出生年月：1979-01-01
操作步骤二
/**
* 操作步骤2 (executeUpdate() 增加、修改、删除操作)
*/
public static void main(String[] args) {
int result = 0;
Connection connection = null;
Statement statement = null;
//导包
try {
//1、加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、获得链接
String userName ="root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url,userName,passWord);
//3、定义sql，创建状态通道
statement = connection.createStatement();
//增加
//result = statement.executeUpdate("insert into grade value (5,'成功期')");
//修改
//result = statement.executeUpdate("update grade set gname = '优秀期' where id = 5");
//删除
result = statement.executeUpdate("delete from grade where gname='优秀期'"); //返回影响的行数int接收
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//关闭资源
connection.isClosed();
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//返回影响的行数
if (result > 0){
System.out.println("修改成功！");
}else{
System.out.println("修改失败！");
}
}
运行结果
修改成功
三、PreparedStatement(预状态通道)
/**
*预状态通道 （查找executeQuery()）
* 所有参数都由？符号，这被称为参数标记，防止sql注入的不安全行为
*/
public static void main(String[] args) {
PreparedStatement pps = null;
ResultSet resultSet = null; //结果集对象
Connection connection= null;
try {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//创建链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//定义sql 创建PreparedStatement状态通道
String sql = "select * from employee where empid = ?";
pps = connection.prepareStatement(sql);
//setXXX()方法将值绑定到所述参数，其中XXX代表要绑定到输入参数的值的Java数据类型。
pps.setInt(1,1001); //1：表示第一个？的标记，x:为传入的参数
resultSet = pps.executeQuery(); //返回一个结果集resultSet对象接收
//取出数据
while (resultSet.next()){
//取出数据：resultSet.getXXX("列名")；xxx表示数据类型
System.out.println("姓名："+resultSet.getString("name")+
",性别："+resultSet.getString("sex")+
",出生年月："+ resultSet.getDate("birthday"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (pps != null){
pps.close();
}
if(resultSet != null){
resultSet.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
运行结果
姓名：张三,性别：男,出生年月：1975-01-01
姓名：李四,性别：女,出生年月：1985-01-01
姓名：王五,性别：男,出生年月：1978-11-11
姓名：赵六,性别：男,出生年月：1979-01-01
与状态的executeUpdate() 增加、修改、删除操作 返回的是int类型 所影响的条数，省略.....
四、对比statement和PreparedStatement;
(1)statement属于状态通道，PreparedStatement属于预状态通道
(2)预状态通道会先编译sql语句,再去执行，比statement执行效率高
(3)预状态通道支持占位符?,给占位符赋值的时候，位置从1开始
(4)预状态通道可以防止sql注入，原因:预状态通道在处理值的时候以字符串的方式处理
五、Java操作两表关系
一对多
(1)创建数据表
CREATE TABLE `student` (
`stuid` int(11) NOT NULL AUTO_INCREMENT,
`stuname` varchar(255) DEFAULT NULL,
`teacherid` int(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', 'aaa', '3');
INSERT INTO `student` VALUES ('2', 'bb', '1');
INSERT INTO `student` VALUES ('3', 'cc', '3');
INSERT INTO `student` VALUES ('4', 'dd', '1');
INSERT INTO `student` VALUES ('5', 'ee', '1');
INSERT INTO `student` VALUES ('6', 'ff', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES ('1', '张三老师');
INSERT INTO `teacher` VALUES ('2', '李四老师');
INSERT INTO `teacher` VALUES ('3', '王五');
(2)创建实体类
public class Teacher {
private int tid;
private String tname;
private List<Student> list=new ArrayList<Student>(); //在一的一方增加多的一方的属性集合，进行关联
public List<Student> getList() {
return list;
}
public void setList(List<Student> list) {
this.list = list;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
}
public class Student {
private int stuid;
private String stuname;
//外键列一般不生成属性
// private int teacherid;
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
}
(3)定义dao接口
public interface TeacherDao {
//定义操作方法
//1.定义一个根据老师id查询老师信息(学生的信息)
public Teacher getById(int tid);
}
(4)定义实现类
public class TeacherDaoImpl implements TeacherDao {
@Override
public Teacher getById(int tid) {
//操作数据库
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得链接
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3.定义sql,创建预状态通道(进行sql语句的发送)
String sql="select * from student s,teacher t where
s.teacherid=t.tid and t.tid=?";
pps = connection.prepareStatement(sql);
//给占位符赋值 （下标，内容） 从1开始
pps.setInt(1,tid);
//执行sql
resultSet = pps.executeQuery();
Teacher teacher = new Teacher();
List<Student> students=new ArrayList<Student>();
while (resultSet.next()){
//1.取出各自的信息
teacher.setTid(resultSet.getInt("tid"));
teacher.setTname(resultSet.getString("tname"));
Student student = new Student();
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
//2.建立学生和老师之间的关系
students.add(student);
}
teacher.setStudentList(students);
return teacher;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
(4)定义测试类
public class Demo1 {
public static void main(String[] args) {
TeacherDao dao= new TeacherDaoImpl();
Teacher teacher = dao.getById(1);
System.out.println("老师姓名:"+teacher.getTname());
List<Student> studentList = teacher.getStudentList();
for (Student student : studentList) {
System.out.println("\t studentname="+student.getStuName());
}
}
}
多对一(学生->老师)
实体类
public class Student {
private int stuid;
private String stuname;
//外键列一般不生成属性
// private int teacherid;
private Teacher teacher;
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
}
public class Teacher {
private int tid;
private String tname;
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
}
接口：
//查询所有的学生(包含老师的信息)
public List<Student> getAll();
实现类：
public List<Student> getAll() {
//操作数据库
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得链接
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3.定义sql,创建预状态通道(进行sql语句的发送)
String sql="select * from student s,teacher t where
s.teacherid=t.tid";
pps = connection.prepareStatement(sql);
//执行sql
resultSet = pps.executeQuery();
List<Student> students=new ArrayList<>();
while (resultSet.next()){
//1.取出各自的信息
Student student = new Student();
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
Teacher teacher = new Teacher();
teacher.setTid(resultSet.getInt("tid"));
teacher.setTname(resultSet.getString("tname"));
//2.建立学生和老师之间的关系
student.setTeacher(teacher);
students.add(student);
}
return students;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
测试类：
public static void main(String[] args) {
TeacherDao dao= new TeacherDaoImpl();
List<Student> students = dao.getAll();
for (Student student : students) {
System.out.println(student.getStuName()+","+student.getTeacher().getTname());
}
}
一对一（妻子丈夫）
数据表：
CREATE TABLE `husband` (
`husid` int(11) NOT NULL AUTO_INCREMENT,
`husname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`husid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `husband` VALUES ('1', '邓超');
DROP TABLE IF EXISTS `wife`;
CREATE TABLE `wife` (
`wifeid` int(11) NOT NULL AUTO_INCREMENT,
`wifename` varchar(255) DEFAULT NULL,
`hid` int(11) DEFAULT NULL,
PRIMARY KEY (`wifeid`),
UNIQUE KEY `uq_wife_hid` (`hid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `wife` VALUES ('1', '孙俪', '1');
实体类
//丈夫
public class Husband {
private int husid;
private String husname;
private Wife wife;
public int getHusid() {
return husid;
}
public void setHusid(int husid) {
this.husid = husid;
}
public String getHusname() {
return husname;
}
public void setHusname(String husname) {
this.husname = husname;
}
public Wife getWife() {
return wife;
}
public void setWife(Wife wife) {
this.wife = wife;
}
}
//妻子
public class Wife {
private int wifeid;
private String wifeName;
private Husband husband;
public int getWifeid() {
return wifeid;
}
public void setWifeid(int wifeid) {
this.wifeid = wifeid;
}
public String getWifeName() {
return wifeName;
}
public void setWifeName(String wifeName) {
this.wifeName = wifeName;
}
public Husband getHusband() {
return husband;
}
public void setHusband(Husband husband) {
this.husband = husband;
}
}
接口：
public interface WifeDao {
//查询妻子信息(包含丈夫信息)
public Wife getWife(int wid);
//查询丈夫信息(包含妻子信息)
public Husband getHus(int hid);
}
实现类：
public class WifeDaoImpl implements WifeDao {
@Override
public Wife getWife(int wid) {
/操作数据库
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得链接
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3.定义sql,创建预状态通道(进行sql语句的发送)
String sql="select * from wife w,husband h where w.hid=h.husid and
w.wifeid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,wid);
//执行sql
resultSet = pps.executeQuery();
Wife wife = new Wife();
while (resultSet.next()){
//1.取出各自的信息
wife.setWifeId(resultSet.getInt("wifeid"));
wife.setWifeName(resultSet.getString("wifename"));
Husband husband = new Husband();
husband.setHusId(resultSet.getInt("husid"));
husband.setHusName(resultSet.getString("husname"));
//2.建立妻子和丈夫之间的关系
wife.setHusband(husband);
}
return wife;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
@Override
public Husband getHus(int hid) {
//操作数据库
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得链接
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3.定义sql,创建预状态通道(进行sql语句的发送)
String sql="select * from wife w,husband h where w.hid=h.husid and
h.husid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,hid);
//执行sql
resultSet = pps.executeQuery();
Husband husband = new Husband();
while (resultSet.next()){
//1.取出各自的信息
Wife wife = new Wife();
wife.setWifeId(resultSet.getInt("wifeid"));
wife.setWifeName(resultSet.getString("wifename"));
husband.setHusId(resultSet.getInt("husid"));
husband.setHusName(resultSet.getString("husname"));
//2.建立妻子和丈夫之间的关系
husband.setWife(wife);
}
return husband;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
测试类：
public static void main(String[] args) {
WifeDaoImpl wifeDao = new WifeDaoImpl();
Wife wife = wifeDao.getWife(1);
System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());
Husband hus = wifeDao.getHus(1);
System.out.println(hus.getHusName()+","+hus.getWife().getWifeName());
}
多对多(科目-学生)
数据表：
CREATE TABLE `middle` (
`middleid` int(11) NOT NULL AUTO_INCREMENT,
`stuid` int(11) DEFAULT NULL,
`subid` int(11) DEFAULT NULL,
PRIMARY KEY (`middleid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of middle
-- ----------------------------
INSERT INTO `middle` VALUES ('1', '1', '1');
INSERT INTO `middle` VALUES ('2', '1', '2');
INSERT INTO `middle` VALUES ('3', '1', '3');
INSERT INTO `middle` VALUES ('4', '1', '5');
INSERT INTO `middle` VALUES ('5', '2', '2');
INSERT INTO `middle` VALUES ('6', '3', '2');
INSERT INTO `middle` VALUES ('7', '4', '2');
INSERT INTO `middle` VALUES ('8', '5', '2');
INSERT INTO `middle` VALUES ('9', '6', '2');
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stuid` int(11) NOT NULL AUTO_INCREMENT,
`stuname` varchar(255) DEFAULT NULL,
`teacherid` int(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '3');
INSERT INTO `student` VALUES ('2', '李四', '1');
INSERT INTO `student` VALUES ('3', '王五', '3');
INSERT INTO `student` VALUES ('4', '赵六', '1');
INSERT INTO `student` VALUES ('5', '花花', '1');
INSERT INTO `student` VALUES ('6', '潇潇', '2');
-- ----------------------------
-- Table structure for `subject`
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`subid` int(11) NOT NULL AUTO_INCREMENT,
`subname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`subid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', 'java');
INSERT INTO `subject` VALUES ('2', 'ui');
INSERT INTO `subject` VALUES ('3', 'h5');
INSERT INTO `subject` VALUES ('4', 'c');
INSERT INTO `subject` VALUES ('5', 'c++');
INSERT INTO `subject` VALUES ('6', 'c#');
实体类:
public class Subject {
private int subid;
private String subname;
private List stulist;
public int getSubid() {
return subid;
}
public void setSubid(int subid) {
this.subid = subid;
}
public String getSubname() {
return subname;
}
public void setSubname(String subname) {
this.subname = subname;
}
public List getStulist() {
return stulist;
}
public void setStulist(List stulist) {
this.stulist = stulist;
}
}
public class Student {
private int stuid;
private String stuname;
//外键列一般不生成属性
// private int teacherid;
private Teacher teacher;
private List<Subject> subjects;
public List<Subject> getSubjects() {
return subjects;
}
public void setSubjects(List<Subject> subjects) {
this.subjects = subjects;
}
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
}
接口:
public interface SubjectDao {
//查询某个学生信息（查询出所学科目）
public Student findById(int id);
//查询某个科目以及对应的学生姓名
public Subject findBySubId(int subId);
}
实现类:
public class SubjectDaoImpl implements SubjectDao {
@Override
public Student findById(int id) {
//操作数据库
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得链接
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3.定义sql,创建预状态通道(进行sql语句的发送)
String sql="select * from student s,subject su,middle m where
s.stuid=m.stuid and su.subid=m.subid and s.stuid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,id);
//执行sql
resultSet = pps.executeQuery();
Student student = new Student();
List<Subject> subjects=new ArrayList<>();
while (resultSet.next()){
//1.取出各自的信息
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
Subject subject = new Subject();
subject.setSubId(resultSet.getInt("subid"));
subject.setSubName(resultSet.getString("subname"));
subjects.add(subject);
}
//2.建立学生和科目之间的关系
student.setSubjects(subjects);
return student;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
@Override
public Subject findBySubId(int subId) {
//操作数据库
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得链接
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3.定义sql,创建预状态通道(进行sql语句的发送)
String sql="select * from student s,subject su,middle m where
s.stuid=m.stuid and su.subid=m.subid and su.subid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,subId);
//执行sql
resultSet = pps.executeQuery();
Subject subject = new Subject();
List<Student> studentList=new ArrayList<>();
while (resultSet.next()){
//1.取出各自的信息
Student student = new Student();
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
studentList.add(student);
subject.setSubId(resultSet.getInt("subid"));
subject.setSubName(resultSet.getString("subname"));
}
//2.建立学生和科目之间的关系
subject.setStudentList(studentList);
return subject;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
测试类：
public static void main(String[] args) {
SubjectDaoImpl subjectDao = new SubjectDaoImpl();
/* Student student = subjectDao.findById(1);
System.out.println(student.getStuName());
List<Subject> subjects = student.getSubjects();
for (Subject subject : subjects) {
System.out.println("\t"+subject.getSubName());
}*/
Subject subject = subjectDao.findBySubId(2);
System.out.println(subject.getSubName());
List<Student> studentList = subject.getStudentList();
for (Student student : studentList) {
System.out.println("\t"+student.getStuName());
}
}
六、工具类定义
public class DbUtils {
//1.定义需要的工具类对象
protected Connection connection=null;
protected PreparedStatement pps=null;
protected ResultSet rs=null;
protected int k=0;//受影响的行数
private String url="jdbc:mysql://localhost:3306/kaikeba";
private String username="root";
private String password="123456";
//2.加载驱动
static{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//3.获得连接
protected Connection getConnection(){
try {
connection=DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//4.创建通道
protected PreparedStatement getPps(String sql){
try {
getConnection();//insert into users values(?,?,?,?,)
pps=connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5.给占位符赋值 list中保存的是给占位符所赋的值
protected void setParams(List list){
try {
if(list!=null&&list.size()>0){
for (int i=0;i<list.size();i++) {
pps.setObject(i+1,list.get(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//6.增删改调取的方法
protected int update(String sql,List params){
try {
getPps(sql);
setParams(params);
k= pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return k;
}
//7.查询的时候调取一个方法
protected ResultSet query(String sql,List list){
try {
getPps(sql);
setParams(list);
rs=pps.executeQuery();
return rs;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//8.关闭资源
protected void closeall(){
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
properties文件保存数据库信息-特点:key-value存储方式
db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/wzj
user=root
password=123456
工具类中读取属性文件
//方式一：
InputStream inputStream = 当前类名.class.getClassLoader()
.getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
dirverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("user");
password = properties.getProperty("password");
//方式二：
static{
//参数只写属性文件名即可，不需要写后缀
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("user");
password = bundle.getString("password");
}
七、连接池
Druid(德鲁伊)连接池
使用步骤
1 导入jar包
2 编写工具类
/**
* 阿里的数据库连接池
* 性能最好的
* Druid
* */
public class DruidUtils {
//声明连接池对象
private static DruidDataSource ds;
static{
///实例化数据库连接池对象
ds=new DruidDataSource();
//实例化配置对象
Properties properties=new Properties();
try {
//加载配置文件内容
properties.load(DruidUtils.class.getResourceAsStream("dbcpconfig.properties"));
//设置驱动类全称
ds.setDriverClassName(properties.getProperty("driverClassName"));
//设置连接的数据库
ds.setUrl(properties.getProperty("url"));
//设置用户名
ds.setUsername(properties.getProperty("username"));
//设置密码
ds.setPassword(properties.getProperty("password"));
//设置最大连接数量
ds.setMaxActive(Integer.parseInt(properties.getProperty("maxActive")));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接对象
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
八、工具类+德鲁伊+配置文件（可以直接食用）
db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC
user=root
password=123456
连接与操作数据库
public class DbUtils {
protected Connection connection = null;
protected PreparedStatement pps = null;
protected ResultSet rs = null;
protected int count = 0; //返回影响的行数
private static String url ;
private static String userName;
private static String passWord;
private static String dirverName;
//德鲁伊连接池
private static DruidDataSource dataSource = new DruidDataSource();
//2.加载驱动
static{
//读取属性文件
//参数只写属性文件名即可，不需要写后缀
ResourceBundle bundle = ResourceBundle.getBundle("db");
dirverName = bundle.getString("driver");
url = bundle.getString("url");
userName = bundle.getString("user");
passWord = bundle.getString("password");
//德鲁伊
dataSource.setUsername(userName);
dataSource.setPassword(passWord);
dataSource.setUrl(url);
dataSource.setDriverClassName(dirverName);
}
//3.获得链接
protected Connection getConnection(){
try {
connection = dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
//4.创建通道
protected PreparedStatement getPps(String sql){
getConnection(); //获得链接
try {
pps = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5.给占位符赋值 list中保存的是给占位符所赋值的值
protected void setParams(List list){
if(list != null){
for (int i = 0; i < list.size(); i++) {
try {
pps.setObject(i+1,list.get(i));
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
//6.增删改调取的方法
protected int update(String sql,List params){
try {
getPps(sql); //得到通道 创建sql语句
setParams(params); //给占位符赋值
count = pps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return count;
}
//7、查询的时候调取一个方法
protected ResultSet query(String sql,List list){ // 没有占位符 list 没有就传null值
try {
getPps(sql);
setParams(list);
rs = pps.executeQuery();
return rs;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
//8、关闭所有资源
protected void closeAll(){
try {
if(rs != null){
rs.close();
}
if(pps != null){
pps.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
2554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
