2021级-JAVA12 JDBC编程

目录

8-1 sdut-JDBC-1 实现数据库表的CRUD操作

8-2 sdut-JDBC-2 实现数据库表的CRUD操作_中级（PreparedStatement)

8-3 sdut-JDBC-3 实现数据库表的CRUD操作_中级（事务)

---

8-1 sdut-JDBC-1 实现数据库表的CRUD操作

import java.sql.*;

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school?useunicode=true&characterEncoding=utf-8",
                "root",
                "123456");  //数据库服务器名称（地址)、端口号、数据库名称、用户名、密码须根据实际情况改变
        Statement st = con.createStatement();

        //向表中增加记录并显示所有记录（数据自己指定）；
        String insertSQL1 = "insert into student values(null,'嘿嘿',86);";
        String insertSQL2 = "insert into student values(null,'哈哈',99);";
        String insertSQL3 = "insert into student values(null,'呼呼',88);";
        st.executeUpdate(insertSQL1);
        st.executeUpdate(insertSQL2);
        st.executeUpdate(insertSQL3);
        System.out.println("--1.增加-------");
        String selectall = "Select * from student";
        ResultSet rs = st.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }
        //从表中删除id=1的记录，并显示所有记录；
        String deletSQL = "delete from student where id=1";
        st.executeUpdate(deletSQL);
        System.out.println("--2.删除---");
        rs = st.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }
        //修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；
        System.out.println("\n--3.修改---");
        String updateSQL = "update student set name='山东理工' where id=2";
        st.executeUpdate(updateSQL);
        rs = st.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }
        //查询表中id=3的记录并显示。
        System.out.println("\n-4.条件查询-----");
        String selectSQL = "select * from student where id=3";
        rs = st.executeQuery(selectSQL);
        if(rs.next())
        {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double score = rs.getDouble("score");
            System.out.println(id + " " + name + " " + score);
        }


    }
}

8-2 sdut-JDBC-2 实现数据库表的CRUD操作_中级（PreparedStatement)

import java.sql.*;

public class Main {
    public static void main(String[] args) throws SQLException {
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school?useunicode=true&characterEncoding=utf-8",
                "root",
                "123456");  //数据库服务器名称（地址)、端口号、数据库名称、用户名、密码须根据实际情况改变
        PreparedStatement pst = null;
        //向表中增加记录（id列自增，可只考虑姓名和成绩），并显示所有记录；
        String insertSQL = "insert into student values(null, ?, ?)";
        pst = con.prepareStatement(insertSQL);
        pst.setString(1, "ANNa");
        pst.setDouble(2, 86.5);
        pst.executeUpdate();
        System.out.println("--1.插入------");
        String selectall = "Select * from student";
        ResultSet rs = pst.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }


        //从表中删除id=? 的记录，并显示所有记录；
        String deleteSQL = "delete from student where id=?";
        pst = con.prepareStatement(deleteSQL);
        pst.setInt(1, 1);
        pst.executeUpdate();
        System.out.println("\n--2.删除-----");
        rs = pst.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }


        //修改表中记录：查询条件id=?，将name修改为：?，修改完毕显示所有记录；
        String updateSQL = "update student set name=? where id=?";
        pst = con.prepareStatement(updateSQL);
        pst.setString(1, "山东理工");
        pst.setInt(2, 2);
        pst.executeUpdate();
        System.out.println("\n--3.修改-----");
        rs = pst.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }


        //查询表中id=? 的记录并显示。
        String selectSQL = "select * from student where id=?";
        pst = con.prepareStatement(selectSQL);
        pst.setInt(1, 3);
        rs = pst.executeQuery();
        System.out.println("\n--4.查询-----");
        if(rs.next())
        {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double score = rs.getDouble("score");
            System.out.println(id + " " + name + " " + score);
        }
        rs.close();
        pst.close();
        con.close();


    }
}

8-3 sdut-JDBC-3 实现数据库表的CRUD操作_中级（事务)

import java.sql.*;

public class Main {
    public static void main(String[] args) throws SQLException {
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school?useunicode=true&characterEncoding=utf-8",
                "root",
                "123456");  //数据库服务器名称（地址)、端口号、数据库名称、用户名、密码须根据实际情况改变
        con.setAutoCommit(false);
        PreparedStatement pst = null;
        //向表中增加1条记录，id列自增，可只考虑姓名和成绩列的数据，
        String insertSQL = "insert into student values(null, ?, ?)";
        pst = con.prepareStatement(insertSQL);
        pst.setString(1, "CHRISE");
        pst.setDouble(2, 86.5);
        int result1 = pst.executeUpdate();
        //从表中删除id=? 的记录；
        String deleteSQL = "delete from student where id=?";
        pst = con.prepareStatement(deleteSQL);
        pst.setInt(1, 2);
        int result2 = pst.executeUpdate();
        int result = result1*result2;
        System.out.println(result == 1 ? "增加记录和删除记录成功" : "增加记录和删除记录失败");
        if(result == 1)
        {
            con.commit();
        }
        else
        {
            con.rollback();
        }
        // 显示所有
        System.out.println("\n---表中记录为--");
        String selectall = "select * from student";
        ResultSet rs = pst.executeQuery(selectall);
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double score = rs.getDouble(3);
            System.out.println(id + " " + name + " " + score);
        }
        rs.close();
        pst.close();
        con.close();
    }
}