目录
所谓ORM，即Object Relational Mapping。由于从数据库查询到的结果集（ResultSet）在进行遍历时，逐行遍历，然后一个字段一个字段的读取数据，取出的都是零散的数据，然后将零散数据一个一个输出，这样比较麻烦，不利于操作。所以在实际应用开发中，我们需要将零散的数据进行封装整理。
一、数据库
CREATE TABLE Animal(
aid INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
age INT(2),
num VARCHAR(10) NOT NULL UNIQUE,
birthday DATE NOT NULL );
INSERT INTO Animal VALUES(1001,'花小猫',2,'10001','2021-1-2');
INSERT INTO Animal VALUES(NULL,'壮小狗',3,'10002','2020-11-22');
INSERT INTO Animal VALUES(NULL,'皮小鼠',1,'10003','2022-3-18');二、封装Animal类
package com.bdqn.animal;
import java.sql.Date;
public class Animal {
/*
aid INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
age INT(2),
num VARCHAR(10) NOT NULL UNIQUE,
birthday DATE NOT NULL );
*/
private int aid;
private String name;
private int age;
private String num;
private Date birthday;
public Animal() {
}
public Animal(int aid, String name, int age, String num, Date birthday) {
this.aid = aid;
this.name = name;
this.age = age;
this.num = num;
this.birthday = birthday;
}
/**
* 获取
* @return aid
*/
public int getAid() {
return aid;
}
/**
* 设置
* @param aid
*/
public void setAid(int aid) {
this.aid = aid;
}
/**
* 获取
* @return name
*/
public String getName() {
return name;
}
/**
* 设置
* @param name
*/
public void setName(String name) {
this.name = name;
}
/**
* 获取
* @return age
*/
public int getAge() {
return age;
}
/**
* 设置
* @param age
*/
public void setAge(int age) {
this.age = age;
}
/**
* 获取
* @return num
*/
public String getNum() {
return num;
}
/**
* 设置
* @param num
*/
public void setNum(String num) {
this.num = num;
}
/**
* 获取
* @return birthday
*/
public Date getBirthday() {
return birthday;
}
/**
* 设置
* @param birthday
*/
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String toString() {
return "Animal{aid = " + aid + ", name = " + name + ", age = " + age + ", num = " + num + ", birthday = " + birthday + "}";
}
}
三、增
package com.bdqn.animal;
import java.sql.*;
import java.util.Calendar;
public class Insert {
public static void main(String[] args) {
// 1.加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
// 2.准备工作
String url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase";//localhost也可写成127.0.0.1，jdbcdatabase为数据库名
String user = "root";//登录数据库系统账号
String password = "123456";//登录数据库密码
// animal对象需要一个SQL的日期
Calendar calendar = Calendar.getInstance();
calendar.set(2021, Calendar.APRIL,5);
// sql类型的Date()需要参数为long的时间戳
long timeInMillis = calendar.getTimeInMillis();
// 创建Animal对象
Animal animal = new Animal(1004, "花小猪", 2, "10004",
new Date(timeInMillis));
// 编写SQL语句
String sql = "insert into Animal " +
"values(null,'"+animal.getName()+"',"+animal.getAge()+",'"+animal.getNum()+"','"+animal.getBirthday()+"');";
// 使用try-with 自动关闭资源
try (
// 3.获取连接对象
Connection connection = DriverManager.getConnection(url, user, password);
// 4.获取发送对象
Statement statement = connection.createStatement()) {
// 5.执行SQL
int result = statement.executeUpdate(sql);
// 6.接收反馈信息
System.out.println(result != 0 ? "插入成功" : "插入失败");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
四、删
package com.bdqn.animal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Delete {
public static void main(String[] args) {
// 1.加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
// 2.准备数据
String url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase";
String user = "root";
String password = "123456";
String sql = "delete from animal where name = '花小猪';";
try (
// 3.获取连接对象
Connection connection = DriverManager.getConnection(url, user, password);
// 4.获取发送对象
Statement statement = connection.createStatement()) {
// 5.执行SQL
int result = statement.executeUpdate(sql);
// 6.接收反馈信息
System.out.println(result != 0 ? "删除成功" : "删除失败");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
五、改
package com.bdqn.animal;
import java.sql.*;
public class Update {
public static void main(String[] args) {
// 1.加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
// 2.准备工作
String url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase";
String user = "root";
String password = "123456";
Animal animal = new Animal();
animal.setBirthday(new Date(121,2,4));
String sql = "update animal set `birthday` = '"+animal.getBirthday()+"' where name='花小猫'";
try (
// 3.获取连接对象
Connection connection = DriverManager.getConnection(url, user, password);
// 4.获取发送对象
Statement statement = connection.createStatement()) {
// 5.执行SQL
int result = statement.executeUpdate(sql);
// 6.接收反馈信息
System.out.println(result != 0 ? "修改成功" : "修改失败");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
六、查
package com.bdqn.animal;
import java.sql.*;
import java.util.ArrayList;
public class SelectAll {
public static void main(String[] args) {
// 1.加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
// 2.准备数据
String url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase";
String user = "root";
String password = "123456";
String sql = "select * from animal";
try(
// 3.获取连接对象
Connection connection = DriverManager.getConnection(url, user, password);
// 4.获取发送对象
Statement statement = connection.createStatement()
) {
// 5.执行SQL
ResultSet resultSet = statement.executeQuery(sql);
// 6.获取集合中的一条记录
// 创建ArrayList集合 存放Animal对象
ArrayList<Animal> animalArrayList = new ArrayList<>();
while (resultSet.next()){
// 按索引获取，从1开始 也可以使用使用字段名如getInt(aid)
int aid = resultSet.getInt(1);
String name = resultSet.getString(2);
int age = resultSet.getInt(3);
String num = resultSet.getString(4);
Date birthday = resultSet.getDate(5);
// 创建Animal对象
Animal animal = new Animal(aid, name, age, num, birthday);
animalArrayList.add(animal);
}
// 7.遍历ArrayList集合
for(Animal a : animalArrayList)
System.out.println(a);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
七、细节过程都在注释里
1190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
