实现JDBC的增删查改

1.定义记录数据库的类
package com.bean;

public class Demo {
private int id;
private String name;
private String sex;
private int age;

public Demo(int id, String name, String sex, int age) {
    this.id = id;
    this.name = name;
    this.sex = sex;
    this.age = age;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getSex() {
    return sex;
}

public void setSex(String sex) {
    this.sex = sex;
}

public int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

@Override
public String toString() {
    return "Demo{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", sex='" + sex + '\'' +
            ", age=" + age +
            '}';
}

}
2.定义主类，其中findAll()即是查找函数。其中连接JDBC数据库有七个步骤：加载驱动，数据库连接，写sql语句，创建preparedstatement,执行sql语句，处理结果集，关闭连接。前两项DBUtill类中的GetConnection()函数中实现的，关闭连接也是在DBUtill类中close()函数中实现的。
package com.company;
import com.bean.Demo;
import com.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Main {
// write your code here
//mysql驱动包名
private static final String DRIVER_NAME = “com.mysql.jdbc.Driver”;
//数据库连接地址
private static final String URL = “jdbc:mysql://localhost:3306/demo”;
//用户名
private static final String USER_NAME = “root”;
//密码
private static final String PASSWORD = “root”;

public void findAll() {
    List<Demo> list = new ArrayList<>();
    Connection connection = null;
    ResultSet rs = null;
    PreparedStatement prst = null;
    try {


        //获取数据库连接
        connection = DBUtil.GetConnection();
        //mysql查询语句
        String sql = "SELECT * FROM demo";
        prst = connection.prepareStatement(sql);
        //处理结果集
        rs = prst.executeQuery();
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String sex = rs.getString(3);
            int age = rs.getInt(4);
            Demo demo = new Demo(id, name, sex, age);
            list.add(demo);
        }
        System.out.println(list.toString());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DBUtil.close(rs, prst, connection);//关闭连接
    }
}


public static void main(String[] args) throws Exception {
     Main main = new Main();
     main.findAll();
     DBUtil.insert(new Demo(5,"波波","man",21));
     main.findAll();
     DBUtil.update(new Demo(3,"向向","wom",22));
     main.findAll();
     DBUtil.delete(4);
     main.findAll();



}

}
3.定义增删查改操作的类DBUtill
package com.util;

import com.bean.Demo;

import java.sql.*;

public class DBUtil {
private static final String DRIVER_NAME = “com.mysql.jdbc.Driver”;
//数据库连接地址
private static final String URL = “jdbc:mysql://localhost:3306/demo”;
//用户名
private static final String USER_NAME = “root”;
//密码
private static final String PASSWORD = “root”;

public static Connection GetConnection()
{
    Connection connection = null;
    try {
        Class.forName(DRIVER_NAME);
        connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return connection;
}
public static void insert(Demo demo) {//增操作
    Connection connection = GetConnection();
    String sql = "insert into demo (id,name,sex,age) values (?,?,?,?)";
    ResultSet rs = null;
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) connection.prepareStatement(sql);
        pstmt.setInt(1, demo.getId());
        pstmt.setString(2, demo.getName());
        pstmt.setString(3, demo.getSex());
        pstmt.setInt(4, demo.getAge());
        pstmt.executeUpdate();

        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

}
public static void delete(int id) {//删除操作
    Connection connection = GetConnection();
    String sql = "delete from demo where id = '"+id+"'";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) connection.prepareStatement(sql);
        pstmt.executeUpdate();


        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
public static void update(Demo demo) throws Exception {
    Connection connection = GetConnection();
    String sql = "update demo set id = ?,name = ?,sex = ?, age = ? where id = ?";
    PreparedStatement pstmt;
    try {
    pstmt = connection.prepareStatement(sql);
    pstmt.setInt(1, demo.getId());
    pstmt.setString(2, demo.getName());
    pstmt.setString(3, demo.getSex());
    pstmt.setInt(4, demo.getAge());

    pstmt.executeUpdate();

    connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
    public static void close(ResultSet rs, PreparedStatement prst,Connection connection){
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (prst != null) {
        try {
            prst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}