JDBC对数据库数据进行增删改查

package com.yxfyg.crud;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.jupiter.api.Test;

import com.yxfyg.util.JDBCUtil;

public class TestCrud {
    
    @Test
    public void testQuery() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConn();
            st = conn.createStatement();
            String sql = "select * from student";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                int sno = rs.getInt("sno");
                String sname = rs.getString("sname");
                String sex = rs.getString("ssex");
                int sage = rs.getInt("sage");
                String sdept = rs.getString("sdept");
                System.out.println("sno="+ sno +",sname="+ sname +",sex="+ sex +",age="+ sage +",sdept=" + sdept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, st, conn);
        }
    }
    
    @Test
    public void testInsert() {
        Connection conn = null;
        Statement st = null;
        try {
            conn = JDBCUtil.getConn();
            st = conn.createStatement();
            String sql = "insert into student values(null,‘刘文‘,‘男‘,20,‘MA‘)";
            //返回修改的行数
            int result = st.executeUpdate(sql);
            if(result > 0) {
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(st, conn);
        }
    }
    
    @Test
    public void testUpdate() {
        Connection conn = null;
        Statement st = null;
        try {
            conn = JDBCUtil.getConn();
            st = conn.createStatement();
            String sql = "update student set sname = ‘测试‘ where sno = 5";
            //返回修改的行数
            int result = st.executeUpdate(sql);
            if(result > 0) {
                System.out.println("更新成功");
            }else {
                System.out.println("更新失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(st, conn);
        }
    }
    
    @Test
    public void testDelete() {
        Connection conn = null;
        Statement st = null;
        try {
            conn = JDBCUtil.getConn();
            st = conn.createStatement();
            String sql = "delete from student where sno = 5";
            //返回修改的行数
            int result = st.executeUpdate(sql);
            if(result > 0) {
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(st, conn);
        }
    }
}

package com.yxfyg.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
    
    static String url = null;
    static String driverClass = null;
    
    static {
        try {
            //1.创建一个属性配置对象
            Properties properties = new Properties();
            //2.使用类加载器去读取src底下的资源文件
            InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            //若使用下面这种，properties文件要位于工程根目录
            //InputStream is = new FileInputStream("jdbc.properties");
            //3.导入输入流
            properties.load(is);
            //4.读取属性
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public static void release(ResultSet rs,Statement st,Connection conn) {
        closeRs(rs);
        closeSt(st);
        closeConn(conn);
    }
    
    public static void release(Statement st,Connection conn) {
        closeSt(st);
        closeConn(conn);
    }
    
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    private static void closeRs(ResultSet rs) {
        try {
            if(rs != null) {
                rs.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            rs = null;
        }
    }
    
    private static void closeSt(Statement st) {
        try {
            if(st != null) {
                st.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            st = null;
        }
    }
    
    private static void closeConn(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            conn = null;
        }
    }
}

url=jdbc:mysql://localhost/mydb?user=root&password=root
driverClass=com.mysql.jdbc.Driver