JDBC课堂练习

偏执的太偏执、 2024-04-03 13:12 146阅读 0赞

**练习题1：从控制台向数据库的表customers中插入一条数据，表结构如下：**

![7a45673b13a7414186bed00f1c9eb52a.png][]

import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.*;
    import java.util.Properties;
    
    /**
     * JDBCUtils工具类
     */
    public class JDBCUtils {
        //定义属性
        private static String user;//用户名
        private static String password;//密码
        private static String url;//url
        private static String driver;//驱动名称
    
        static {
            Properties properties = new Properties();
            try {
                properties.load(new FileInputStream("D:\\guanqia\\关卡18_JDBC\\lianxi\\src\\jdbc.properties"));
                //读取相关属性
                user = properties.getProperty("user");
                password = properties.getProperty("password");
                url = properties.getProperty("url");
                driver = properties.getProperty("driver");
            } catch (IOException e) {
                //在实际开发中异常处理
                //将编译异常转成运行异常,调用者可以选择捕获或者默认处理，方便了许多
                throw new RuntimeException(e);
    
            }
        }
    
        //连接数据库，返回Connection
        public static Connection getConnection() {
            try {
                return DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    
        //关闭相关连接
        //有需要关闭的就传入对象，没有就传入null
        public static void close(ResultSet resultSet, Statement statement, Connection connection) {
            //判断是否传入为null
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    
        public static void close(Statement statement, Connection connection) {
            //判断是否传入为null
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    
    }

import org.testng.annotations.Test;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.util.Scanner;
    
    
    public class Test1 {
    
        @Test
        public void testInsert() {
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名：");
            String name = scanner.next();
            System.out.println("请输入邮箱：");
            String email = scanner.next();
            System.out.println("请输入生日：");
            String birthday = scanner.next();//birth在数据库中是Data类型，这里是String,其实有一个隐式转换
    
            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            int insertCount = update(sql, name, email, birthday);
            if (insertCount > 0) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失败");
            }
    
    
        }
    
        //通用的增删改操作（前提是在同一个数据库中）
        public int update(String sql, Object... args) {//sql中占位符的个数与可变形参的长度相同
    
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                //1、获取数据库的连接
                conn = JDBCUtils.getConnection();
                //2、预编译sql语句，返回PrepareStatement的实例
                ps = conn.prepareStatement(sql);
                //3、填充占位符
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i + 1, args[i]);//小心参数声明错误
                }
                //4、执行
                /*
                 * ps.execute()
                 * 如果执行的是查询操作，有返回结果，则此方法返回true
                 * 如果执行增删改操作，没有返回结果，则此方法返回false
                 * 方式一:
                 */
                //ps.execute();
                //方式二：
                return ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //5、资源的关闭
                JDBCUtils.close(ps, conn);
            }
            return 0;
        }
    
    }

**练习题2：创立数据库表 examstudent，表结构如下：**

![b9392a46818b4fe4a7448f90ff16ed68.png][]

向数据表中添加如下数据：

![6dcbec57c1bb4c529b9757a6e9a1849f.png][]

**插入一个新的student 信息**

请输入考生的详细信息

Type: IDCard: ExamCard: StudentName: Location: Grade:

信息录入成功!

import java.sql.*;
    import java.util.*;
    
    public class Test2 {
    
        public static void main(String[] args) {
            Connection conn = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String strUrl = "jdbc:mysql://localhost:3306/test";
                conn = DriverManager.getConnection(strUrl, "root", "xl13579");
                System.out.println("连接数据库成功！");
    
                System.out.println("输入学生信息");
                System.out.println("FlowID:");
                Scanner sc = new Scanner(System.in);
                String b1 = sc.next();
                System.out.println("Type:");
                int b2 = sc.nextInt();
                System.out.println("IDCard:");
                String b3 = sc.next();
                System.out.println("ExamCard:");
                String b4 = sc.next();
                System.out.println("StudentName:");
                String b5 = sc.next();
                System.out.println("Location:");
                String b6 = sc.next();
                System.out.println("Grade:");
                int b7 = sc.nextInt();
                Statement st = conn.createStatement();
                st.executeUpdate("insert into examstudent values( " + b1 + ",'" + b2 + "','" + b3 + "','" + b4 + "','" + b5 + "'," + b6 + "," + b7 + ")");
    
                ResultSet rs = st.executeQuery("select * from examstudent");
                while (rs.next()) {
                    int FlowID = rs.getInt(1);
                    int Type = rs.getInt(2);
                    String IDCard = rs.getString(3);
                    String ExamCard = rs.getString(4);
                    String StudentName = rs.getString(5);
                    String Location = rs.getString(6);
                    int Grade = rs.getInt(7);
    
                    System.out.println("流水号:" + FlowID + "   四级／六级:" + Type + "   身份证号码:" + IDCard + "   准考证号码:" + ExamCard + "   学生姓名:" + StudentName + "   区域:" + Location + "   成绩:" + Grade);
                    System.out.println("信息录入成功");
                }
                rs.close();
                st.close();
                conn.close();
    
            } catch (Exception e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
    
        }
    
    }

![e4235b19a6244a0999c3ff66533afb36.png][]

import org.testng.annotations.Test;
    
    import java.sql.*;
    import java.util.*;
    
    public class Test3 {
    
        @Test
        public void testdelete() {
            System.out.println("请输入学生的考号：");
            Scanner scanner = new Scanner(System.in);
            String examCard = scanner.next();
            String sql = "delete from examstudent where examCard=?";
            int deleteCount = update(sql, examCard);
            if (deleteCount > 0) {
                System.out.println("删除成功！");
            } else {
                System.out.println("查无此人，请重新输入！");
            }
        }
    
    
        //通用的增删改操作（前提是在同一个数据库中）
        public int update(String sql, Object... args) {//sql中占位符的个数与可变形参的长度相同
    
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                //1、获取数据库的连接
                conn = JDBCUtils.getConnection();
                //2、预编译sql语句，返回PrepareStatement的实例
                ps = conn.prepareStatement(sql);
                //3、填充占位符
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i + 1, args[i]);//小心参数声明错误
                }
                //4、执行
                /*
                 * ps.execute()
                 * 如果执行的是查询操作，有返回结果，则此方法返回true
                 * 如果执行增删改操作，没有返回结果，则此方法返回false
                 * 方式一:
                 */
                //ps.execute();
                //方式二：
                return ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //5、资源的关闭
                JDBCUtils.close(ps, conn);
            }
            return 0;
        }
    
    }

[7a45673b13a7414186bed00f1c9eb52a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/8139acbbbf62454da6da71781ddd5cab.png
[b9392a46818b4fe4a7448f90ff16ed68.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/7ad55632d9964084aee47fa01081efec.png
[6dcbec57c1bb4c529b9757a6e9a1849f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/0586bd4017ef4f6298aed81109cf21e2.png
[e4235b19a6244a0999c3ff66533afb36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/f113baa808b949e3b4ba0416c5c5b9ed.png