目录
1.使用idea创建项目并创建文件夹lib放入mysql驱动jar包
一、MySQL数据生成
1.建库
CREATE DATABASE school;2 建表
CREATE TABLE student(
id INT PRIMARY KEY auto_increment,
name VARCHAR(32) not null,
sex VARCHAR(32) not null,
age INT,
height FLOAT ,
weight FLOAT,
phone_number VARCHAR(32) not null
);3 插入数据
INSERT INTO student(name,sex,age,height,weight,phone_number) VALUES('张一','男',19,177,55,'1008611');
INSERT INTO student(name,sex,age,height,weight,phone_number) VALUES('张二','男',19,199,60,'1008612');
INSERT INTO student(name,sex,age,height,weight,phone_number) VALUES('王三','男',3,230,99,'1008611');
INSERT INTO student(name,sex,age,height,weight,phone_number) VALUES('任四','男',18,180,55,'1008613');二、java操作
1.使用idea创建项目并创建文件夹lib放入mysql驱动jar包
2.创建实体类
类的名字就是数据库里我们创建表的名字，类的属性对应数据库字段
代码如下
package test01;
//创建实体类
public class Student {
// 类的属性对应数据库字段
private int id;
private String name;
private String sex;
private int age;
private int height;
private int weight;
private String phonennumber;
public Student() {
}
public Student(int id, String name, String sex, int age, int height, int weight, String phonennumber) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.height = height;
this.weight = weight;
this.phonennumber = phonennumber;
}
/**
* 获取
* @return id
*/
public int getId() {
return id;
}
/**
* 设置
* @param id
*/
public void setId(int id) {
this.id = id;
}
/**
* 获取
* @return name
*/
public String getName() {
return name;
}
/**
* 设置
* @param name
*/
public void setName(String name) {
this.name = name;
}
/**
* 获取
* @return sex
*/
public String getSex() {
return sex;
}
/**
* 设置
* @param sex
*/
public void setSex(String sex) {
this.sex = sex;
}
/**
* 获取
* @return age
*/
public int getAge() {
return age;
}
/**
* 设置
* @param age
*/
public void setAge(int age) {
this.age = age;
}
/**
* 获取
* @return height
*/
public int getHeight() {
return height;
}
/**
* 设置
* @param height
*/
public void setHeight(int height) {
this.height = height;
}
/**
* 获取
* @return weight
*/
public int getWeight() {
return weight;
}
/**
* 设置
* @param weight
*/
public void setWeight(int weight) {
this.weight = weight;
}
/**
* 获取
* @return phonennumber
*/
public String getPhonennumber() {
return phonennumber;
}
/**
* 设置
* @param phonennumber
*/
public void setPhonennumber(String phonennumber) {
this.phonennumber = phonennumber;
}
public String toString() {
return "Student{id = " + id + ", name = " + name + ", sex = " + sex + ", age = " + age + ", height = " + height + ", weight = " + weight + ", phonennumber = " + phonennumber + "}";
}
}
3 书写JDBC操作类
写之前我们要了解一下junit
junit方法可以让我们不用依赖main方法就可以直接执行
junit用法：1.方法要定义为无参无返回值的。且测试类的名字不能是Test
2.在方法上使用 @Test 这个注解
3.光标放在后面，然后使用 alt + 回车 进行自动导包，选择---Add 'JUnit4' to classpath
1 准备连接数据库的四大参数
// jdbc连接数据库的四大参数同时需要导入数据库对应jar包
private String driver = "com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC";
private String username = "root";
private String pwd = "root";2 查询
public void TestStudent() throws ClassNotFoundException, SQLException {
// 1首先在项目根目录创建lib文件夹，放入jdbc驱动程序，然后add as libary
// 2加载数据库驱动
Class.forName(driver);
// 使用驱动管理器来获得连接获得一个数据库连接对象connection
Connection connection = DriverManager.getConnection(url, username, pwd);
// 生产方法返回调用值的快捷键 ctrl +alt+v
// 4使用connection创建PreparedStatement预处理对象-PreparedStatement对象可以执行带？的sql语句
String sql = "select *from student";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 5使用PreparedStatement对象执行sql语句，获得ResultSet结果集对象
ResultSet resultSet = preparedStatement.executeQuery();
// 6判断增删改查返回的是影响的行数（返回的值是int）只查询获得的结果集（返回值是 ResultSet）
// 让结果的游标不断向下移动，没有查询到数据的时候就结束循环
List<Student> studentList = new ArrayList<>();
while (resultSet.next()) {
//根据字段名称获取表中的数据
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
int height = resultSet.getInt("height");
int weight = resultSet.getInt("weight");
String number = resultSet.getString("phone_number");
String s=resultSet.getString("sex");
//把以上数据封装到Student对象中
Student student = new Student();//一行数据就封装成了一个Student对象
student.setId(id);
student.setName(name);
student.setAge(age);
student.setSex(s);
student.setHeight(height);
student.setWeight(weight);
student.setPhonennumber(number);
//把当前行封装后的Student对象装载到 List集合中
studentList.add(student);
}
System.out.println(studentList);
if(resultSet!=null){
resultSet.close();
//7回收资源
}
if(preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}3 增加
// 增加
public void TestADD() throws Exception {
// 1首先在项目根目录创建lib文件夹，放入jdbc驱动程序，然后add as libary
// 2加载数据库驱动
Class.forName(driver);
// 3使用驱动管理器来获得连接获得一个数据库连接对象connection
Connection connection = DriverManager.getConnection(url, username, pwd);
// 4使用connection创建PreparedStatement预处理对象-PreparedStatement对象可以执行带？的sql语句
String sql = "INSERT INTO student(name,sex,age,height,weight,phone_number) VALUES(?,?,?,?,?,?);";
PreparedStatement pst = connection.prepareStatement(sql);
Student student=new Student();
student.setName("孙六");
student.setSex("男");
student.setAge(17);
student.setHeight(177);
student.setWeight(189);
student.setPhonennumber("15381434420");
// 预处理对象的sql语句有？所以要进行传参操作
pst.setObject(1,student.getName());
pst.setObject(2,student.getSex());
pst.setObject(3,student.getAge());
pst.setObject(4,student.getHeight());
pst.setObject(5,student.getWeight());
pst.setObject(6,student.getPhonennumber());
// 执行更新(增删改都叫数据库的更新 更新返回的是影响的行数)
int n = pst.executeUpdate();
// 判断受影响的函数啊n>0代表成功否则失败
if(n>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
pst.close();
connection.close();
}4 删除
// 删除
public void TestDelete() throws Exception {
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, username, pwd);
String sql="delete from student where name =?";
PreparedStatement pst = connection.prepareStatement(sql);
Student student=new Student();
student.setName("孙六");
pst.setObject(1,student.getName());
int n = pst.executeUpdate();
if (n>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
pst.close();
connection.close();
}5 修改
// 修改
public void TestRevise() throws Exception {
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, username, pwd);
String sql="UPDATE student SET sex=? WHERE name=?";
PreparedStatement pst= connection.prepareStatement(sql);
Student student=new Student();
student.setName("孙六");
student.setSex("女");
pst.setObject(2,student.getName());
pst.setObject(1,student.getSex());
int i = pst.executeUpdate();
if (i>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
pst.close();
connection.close();
}
2812
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
