JDBC 11.25
数据库在文末
bean
//实体类 注意生日有一个时间格式的转换
// 要重写 toString方法 以便以后直接输出对象
package cn.su.jdbc.bean;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Student {
private String sname;
private String sno;
private String ssex;
private java.util.Date sbirthday;
public Student() {
super();
}
public Student(Date sbirthday, String sname, String sno, String ssex) {
super();
this.sname = sname;
this.sno = sno;
this.ssex = ssex;
this.sbirthday = sbirthday;
}
public java.util.Date getSbirthday() {
return sbirthday;
}
public void setSbirthday(java.util.Date sbirthday) {
this.sbirthday = sbirthday;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSno() {
return sno;
}
public void setSno(String sno) {
this.sno = sno;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
@Override
public String toString() {
return "Student [sname=" + sname + ", sno=" + sno + ", ssex=" + ssex + ", sbirthday=" +new SimpleDateFormat("yyyy-MM-dd").format(sbirthday).toString()+ "]";
//这里有一个时间格式的转换
}
}
utils 工具类封装了jdbc的一些细节，
package cn.su.jdbc.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
public class Tool1 {
static {
try {
// 这里使用的是 mysql8 ， 多了 cj
//加载driver
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//建立连接方法
public Connection getconnecttion() {
//提供连接所需的 url，user，password
String url = "jdbc:mysql://127.0.0.1/student?serverTimezone=Asia/Shanghai ";
Connection connection =null;
try {
//建立连接
connection = DriverManager.getConnection(url, "root", "root");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//返回连接对象
return connection;
}
//释放资源方法
public void free(Connection connection,Statement statement,ResultSet resultSet) {
try {
//关闭资源前确认资源不为空，确保程序的健壮性
if (statement!=null)statement.close();
if (resultSet!=null)resultSet.close();
if (connection!=null)connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DAO data access object 将从数据获得的数据封装成对象
package cn.su.jdbc.DAO;
/**
student 有sno，sname，ssex，sbirthday几个字段
现在通过任意一个字段，或者几个字段查询想要的一条信息，并封装到对象里
传一个对象，有字段
返回对象，有表里的所有信息
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import cn.su.jdbc.bean.Student;
import cn.su.jdbc.utils.Tool1;
public class StudenDAO1 {
//使用工具类
Tool1 tool1 = new Tool1();
//c
public Student find(Student student){
StringBuffer sb = new StringBuffer("select* from student where 1=1");
List<Object> value = new ArrayList<>();
if (student.getSno()!=null) {
sb.append(" and sno =?");
value.add(student.getSno());
}
if (student.getSname()!=null) {
sb.append(" and sname =?");
value.add(student.getSname());
}
if (student.getSsex()!=null) {
sb.append(" and ssex =?");
value.add(student.getSsex());
}
if (student.getSbirthday()!=null) {
sb.append(" and sbirthday =?");
value.add(student.getSbirthday());
}
String sql = sb.toString();
System.out.println(sql);
Connection connection = tool1.getconnecttion();
PreparedStatement prepareStatement = null;
ResultSet set = null;
Student student1 = null;
try {
prepareStatement = connection.prepareStatement(sql);
// 给？赋值
for(int i=0; i< value.size() ; i ++) {
prepareStatement.setObject( i+1 , value.get(i));
set = prepareStatement.executeQuery();
}
while(set.next()) {
student1=new Student();
student1.setSno(set.getString("sno"));
student1.setSname(set.getString("sname"));
student1.setSsex(set.getString("ssex"));
// java.sql.Date date = resultSet.getDate("datetime");
// long time = date.getTime();
// Date date2 = new Date(time);
Date date2 = new Date(set.getDate("sbirthday").getTime());
student1.setSbirthday(date2);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
tool1.free(connection, prepareStatement, set);
}
return student1;
}
}
test 测试
package cn.su.jdbc.test;
import cn.su.jdbc.DAO.StudenDAO1;
import cn.su.jdbc.bean.Student;
public class Test {
public static void main(String[] args) {
StudenDAO1 sd = new StudenDAO1();
//创建对象，想通过那个字段查询，给那个字段值
Student student = new Student();
student.setSno("101");
//接收返回的对象，打印输出
Student s = sd.find(student);
System.out.println(s);
}
}
数据库代码
/*
Navicat Premium Data Transfer
Source Server : 10.50.5.170
Source Server Type : MySQL
Source Server Version : 80014
Source Host : 10.50.5.170:3306
Source Schema : student
Target Server Type : MySQL
Target Server Version : 80014
File Encoding : 65001
Date: 25/12/2020 19:51:27
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`cno`) USING BTREE,
INDEX `tno`(`tno`) USING BTREE,
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `course` VALUES ('3-245', '操作系统', '804');
INSERT INTO `course` VALUES ('6-166', '数字电路', '856');
INSERT INTO `course` VALUES ('9-888', '高等数学', '831');
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`low` int(3) NULL DEFAULT NULL,
`upp` int(3) NULL DEFAULT NULL,
`ran` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (90, 100, 'A');
INSERT INTO `grade` VALUES (80, 89, 'B');
INSERT INTO `grade` VALUES (70, 79, 'C');
INSERT INTO `grade` VALUES (60, 69, 'D');
INSERT INTO `grade` VALUES (0, 59, 'E');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`cno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`degree` decimal(10, 0) NULL DEFAULT NULL,
PRIMARY KEY (`sno`) USING BTREE,
INDEX `cno`(`cno`) USING BTREE,
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('103', '3-245', 86);
INSERT INTO `score` VALUES ('105', '3-245', 75);
INSERT INTO `score` VALUES ('109', '3-245', 68);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ssex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`sbirthday` datetime(0) NULL DEFAULT NULL,
`class` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('101', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `student` VALUES ('103', '陆君', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `student` VALUES ('105', '匡明', '男', '1975-10-02 00:00:00', '95031');
INSERT INTO `student` VALUES ('107', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `student` VALUES ('108', '曾华', '男', '1977-09-01 00:00:00', '95033');
INSERT INTO `student` VALUES ('109', '王芳', '女', '1975-02-10 00:00:00', '95031');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tsex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tbirthday` datetime(0) NULL DEFAULT NULL,
`prof` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`depart` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `teacher` VALUES ('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `teacher` VALUES ('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `teacher` VALUES ('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
SET FOREIGN_KEY_CHECKS = 1;