通过一天的视频学习,认识了jdbc的连接原理前来小结:
游标读取数据库表的行一次读取一个,getXxx()方法读取表的列一个数据
next()方法可以让游标下移
可以把数据库的表看做是一个类,每条记录就是一个对象,所以要封装表
测试类 Jdbc08 .java
/**
* 练习查询 db4的emp数据然后打印
* 定义一个方法,查询emp表的数据将其封装为对象,然后装在集合.
* 1.定义类emp类
* 2.定义方法public 返回List<Emp> findAll()
* 3.实现方法 select * from emp
* @author 三只坚果
* @date 2019/9/14 21:17
*/
public class Jdbc08 {
public static void main(String[] args) {
List<Emp> all = new Jdbc08().findAll();
all.forEach(System.out::println);
System.out.println(all.size()+"条记录");
}
public List<Emp> findAll(){//查询所有emp数据
Connection conn =null;
Statement statement =null;
ResultSet rs =null;
List<Emp> list=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "123");
String sql="select * from emp";
statement = conn.createStatement();
rs = statement.executeQuery(sql);//rs是游标指向一行,getXxx("参数")指中列的一个数据,所以选中一个数据
//遍历结果,封装对象装在集合
Emp emp=null;
list= new ArrayList<>();
while (rs.next()){
int id = rs.getInt("id");
String ename = rs.getString("ename");
int job_id = rs.getInt("job_id");
int mgr = rs.getInt("mgr");
Date joindate = rs.getDate("joindate");
double salary = rs.getDouble("salary");
double bouns = rs.getDouble("bonus");
int dept_id = rs.getInt("dept_id");
//封装对象
emp=new Emp();
emp.setId(id);
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBouns(bouns);
emp.setDept_id(dept_id);
list.add(emp);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {//释放资源
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
}
/**
* 晚安!
* @author BinPeng
* @date 2019/9/14 22:04
*/
Emp.java 封装类 ALT+INSERT IDEA快捷键自动生成!!!
package HeiMaSQL02.emp08; import java.util.Date; /**
* 封装Emp表
* @author BinPeng
* @date 2019/9/14 21:24
*/
public class Emp {
private int id;
private String ename;
private int job_id;
private int mgr;
private Date joindate;
private double salary;
private double bouns;
private int dept_id; @Override
public String toString() {
return "Emp{" +
"id=" + id +
", ename='" + ename + '\'' +
", job_id=" + job_id +
", mgr=" + mgr +
", joindate=" + joindate +
", salary=" + salary +
", bouns=" + bouns +
", dept_id=" + dept_id +
'}';
} public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getEname() {
return ename;
} public void setEname(String ename) {
this.ename = ename;
} public int getJob_id() {
return job_id;
} public void setJob_id(int job_id) {
this.job_id = job_id;
} public int getMgr() {
return mgr;
} public void setMgr(int mgr) {
this.mgr = mgr;
} public Date getJoindate() {
return joindate;
} public void setJoindate(Date joindate) {
this.joindate = joindate;
} public double getSalary() {
return salary;
} public void setSalary(double salary) {
this.salary = salary;
} public double getBouns() {
return bouns;
} public void setBouns(double bouns) {
this.bouns = bouns;
} public int getDept_id() {
return dept_id;
} public void setDept_id(int dept_id) {
this.dept_id = dept_id;
}
}
emp 数据库表 (数据库都给你备好了!够意思吧!!!)
/*
Navicat MySQL Data Transfer Source Server : first
Source Server Type : MySQL
Source Server Version : 50725
Source Host : localhost:3306
Source Schema : db4 Target Server Type : MySQL
Target Server Version : 50725
File Encoding : 65001 Date: 14/09/2019 22:15:35
*/ SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0; -- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`ename` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`job_id` int(11) NULL DEFAULT NULL,
`mgr` int(11) NULL DEFAULT NULL,
`joindate` date NULL DEFAULT NULL,
`salary` decimal(7, 2) NULL DEFAULT NULL,
`bonus` decimal(7, 2) NULL DEFAULT NULL,
`dept_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `emp_jobid_ref_job_id_fk`(`job_id`) USING BTREE,
INDEX `emp_deptid_ref_dept_id_fk`(`dept_id`) USING BTREE,
CONSTRAINT `emp_deptid_ref_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `emp_jobid_ref_job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1001, '啦啦', 4, 1004, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO `emp` VALUES (1002, '吖吖', 3, 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO `emp` VALUES (1003, '啊吖', 3, 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO `emp` VALUES (1004, '订单', 2, 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO `emp` VALUES (1005, '李方法', 4, 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO `emp` VALUES (1006, '宋方法', 2, 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO `emp` VALUES (1007, '发', 2, 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO `emp` VALUES (1008, '猪发', 4, 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1009, 'utah', 1, NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO `emp` VALUES (1010, '吴让她', 3, 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO `emp` VALUES (1011, '沙让他', 4, 1004, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO `emp` VALUES (1012, '李让他', 4, 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO `emp` VALUES (1013, '饿龙', 4, 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1014, '饿', 4, 1007, '2002-01-23', 13000.00, NULL, 10); SET FOREIGN_KEY_CHECKS = 1;