JDBC学习笔记(3)——复习和练习

复习和练习

复习部分

一、获取数据库连接

1)方式一

// 获取数据库连接
    @Test
    public void testGetConnection() throws Exception {
        // 1.准备获取连接的四个字符串:user,jdbcurl,password,driverClass
        String user = "root";
        String password = "123456";
        String jdbcUrl = "jdbc:mysql://localhost:3306/atguigu";
        String driverClass = "com.mysql.jdbc.Driver";
        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user,
                password);
        System.out.println(connection);
    }

2)方式二：解耦合

public Connection getConnection() throws IOException,
            ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        InputStream inputStream = JDBCTest.class.getClassLoader()
                .getResourceAsStream("jdbc.properties");
        properties.load(inputStream);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String jdbcUrl = properties.getProperty("jdbcUrl");
        String driverClass = properties.getProperty("driver");
        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user,
                password);
        return connection;
    }

二、Statement执行更新数据的操作

我们这里把更新数据的操作。用一个通用的update函数来表示

public void update() {
        // 1.获取数据库连接
        Connection connection = null;
        Statement statement = null;
        try {
            connection=getConnection();
            // 2.调用Connection对象的createStatement()方法获取
            // Statement对象
            statement=connection.createStatement();
            // 3.准备SQL语句
            String sql="insert into examstudent values(1,4,'412824195263214584','200523164754000','张峰','郑州',85)";
            // 4.发送SQL语句：调用Statement对象的excuteUpdate(sql)方法
            statement.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            // 5.关闭数据库资源：由里向外关闭
            releaseDB(null, statement, connection);
        }
    }

三、ResultSet执行查询操作

@Test
    public void testResultSet(){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //1.获取数据库连接
            connection=getConnection();
            //2.Statement
            statement=connection.createStatement();
            //3.准备SQL
            String sql="select * from customers";
            //4.执行SQL,得到结果集
            resultSet=statement.executeQuery(sql);
            //5.处理结果集
            while(resultSet.next()){
                int id=resultSet.getInt(1);
                String name=resultSet.getString(2);
                String email=resultSet.getString(3);
                Date birth=resultSet.getDate(4);
                System.out.println(id);
                System.out.println(name);
                System.out.println(email);
                System.out.println(birth);
                System.out.println("----------");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            releaseDB(resultSet, statement, connection);
        }
    }

四、工具类的使用JDBCTools

将获取连接、执行更新操作、释放资源封装在一个JDBCTools类中

package com.atguigu.jdbc;

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 JDBCTools {
    // 更新的方法:插入、删除、更新，但是不包含select
    public static void update(String sql) {
        // 1.获取数据库连接
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            // 2.调用Connection对象的createStatement()方法获取
            // Statement对象
            statement = connection.createStatement();
            // 4.发送SQL语句：调用Statement对象的excuteUpdate(sql)方法
            statement.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭数据库资源：由里向外关闭
            release(null, statement, connection);
        }
    }

    // 获取数据库连接
    public static Connection getConnection() throws IOException,
            ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        InputStream inputStream = JDBCTest.class.getClassLoader()
                .getResourceAsStream("jdbc.properties");
        properties.load(inputStream);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String jdbcUrl = properties.getProperty("jdbcUrl");
        String driverClass = properties.getProperty("driver");
        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user,
                password);
        return connection;
    }

    // 释放数据库资源
    public static void release(ResultSet rs, Statement statement,
            Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                // TODO: handle exception
            }
        }
        if (statement != null) {
            try {
                statement.close();

            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

练习部分

我们的练习是这样的：

新建一个表：

向表中插入数据：

插入成功的话会出现这样的提示(我们从控制台获取要插入的数据):

第一步、用图形化界面SQLyog创建一个数据表examstudent

第二步、向数据表中插入数据

面向对象的思想去编程：将插入的数据封装在一个类Student中

package com.atguigu.jdbc;

public class Student {
    // 流水号
    private int flowId;
    // 考试类型
    private int type;
    // 身份证号
    private String idCard;
    // 准考证号
    private String examCard;
    // 学生姓名
    private String studentName;
    // 学生地址
    private String location;
    // 考试成绩
    private int grade;

    public int getFlowId() {
        return flowId;
    }

    public void setFlowId(int flowId) {
        this.flowId = flowId;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public String getIdCard() {
        return idCard;
    }

    public void setIdCard(String idCard) {
        this.idCard = idCard;
    }

    public String getExamCard() {
        return examCard;
    }

    public void setExamCard(String examCard) {
        this.examCard = examCard;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }

    public Student(int flowId, int type, String idCard, String examCard,
            String studentName, String location, int grade) {
        super();
        this.flowId = flowId;
        this.type = type;
        this.idCard = idCard;
        this.examCard = examCard;
        this.studentName = studentName;
        this.location = location;
        this.grade = grade;
    }

    public Student() {

    }

    // 重写Student对象的toString函数
    @Override
    public String toString() {
        return "Student [flowId=" + flowId + ", type=" + type + ", idCard="
                + idCard + ", examCard=" + examCard + ", studentName="
                + studentName + ", location=" + location + ", grade=" + grade
                + "]";
    }

}

从控制台获取输入信息作为插入student对象的相应的字段值，并插入到新创建的表中

package com.atguigu.jdbc;

import java.util.Scanner;

import org.junit.Test;

public class JDBCTestCase {

    //单元测试
    @Test
    public void testAddNewStudent() {
        Student student = getStudentFromConsole();
        addNewStudent(student);
    }

    // 从控制台输入学生的信息
    private Student getStudentFromConsole() {
        Scanner scanner = new Scanner(System.in);
        Student student = new Student();
        System.out.print("FlowId:");
        student.setFlowId(scanner.nextInt());
        System.out.print("Type:");
        student.setType(scanner.nextInt());
        System.out.print("IdCard:");
        student.setIdCard(scanner.next());
        System.out.print("ExamCard:");
        student.setExamCard(scanner.next());
        System.out.print("StudentName:");
        student.setStudentName(scanner.next());
        System.out.print("Location:");
        student.setLocation(scanner.next());
        System.out.print("Grade:");
        student.setGrade(scanner.nextInt());
        return student;
    }

    public void addNewStudent(Student student) {
        String sql = "insert into examstudent" + " values("
                + student.getFlowId() + "," + student.getType() + ",'"
                + student.getIdCard() + "','" + student.getExamCard() + "','"
                + student.getStudentName() + "','" + student.getLocation()
                + "'," + student.getGrade() + ")";
        System.out.println(sql);
        JDBCTools.update(sql);
    }

}

第三步、进行数据的查询(按第三张图片中要求的形式进行查询)

1).从控制台输入一个整数，确定要查询的类型

/*
     * 1.身份证查询。 2.用准考证查询 。 3，其他，重新输入
     */
    private int getSearchTypeFromConsole() {
        System.out.println("请输入查询类型:1.身份证查询. 2.用准考证查询 ");
        Scanner scanner = new Scanner(System.in);
        int type = scanner.nextInt();
        if (type != 1 && type != 2) {
            System.out.println("输入有误，请重新输入！");
            throw new RuntimeException();
        }
        return 0;
    }

2).根据查询类型准备sql语句

// searchType:1或者2
    private Student searchStudent(int searchType) {
        String sql = "select * from examstudent where ";
        Scanner scanner = new Scanner(System.in);
        // 1.根据输入的searchType,提示用户输入信息
        // 1.1若searchType=1,提示：请输入身份证号
        // 1.2若searchType=2,提示：请输入准考证号
        // 2/根据searchType确定SQL
        if (searchType == 1) {
            System.out.print("请输入准考证号：");
            String examCard = scanner.next();
            sql = sql + "examCard='" + examCard + "'";
        } else {
            System.out.print("请输入身份证号：");
            String IdCard = scanner.next();
            sql = sql + "IdCard='" + IdCard + "'";
        }

        // 3.执行查询
        Student student = getStudent(sql);
        // 4.若存在结果，将查询结果封装成一个Student对象
        return student;
    }

执行查询操作，将结果封装成一个Student对象

private Student getStudent(String sql) {
        Student student = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCTools.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                student = new Student(resultSet.getInt(1), resultSet.getInt(2),
                        resultSet.getString(3), resultSet.getString(4),
                        resultSet.getString(5), resultSet.getString(6),
                        resultSet.getInt(7));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(resultSet, statement, connection);
        }
        return student;
    }

打印查询结果：

 /**
      * 打印学生信息：若学生存在则打印具体信息，否则打印：查无此人
      */
      private void printStudent(Student student) {
          if (student != null) {
              System.out.println(student);
         } else {
             System.out.println("查无此人");
         }
}

写一个测试方法测试一下：

    @Test
    public void testGetStudent() {
        // 1.得到查询的类型
        int searchType = getSearchTypeFromConsole();
        // 2.具体查询信息
        Student student = searchStudent(searchType);
        // 3.打印学生信息
        printStudent(student);
    }

　　

运行结果：

请输入查询类型:1.身份证查询. 2.用准考证查询
1
请输入身份证号：3
Student [flowId=1, type=2, idCard=3, examCard=4, studentName=lili, location=dalin, grade=85]

---

本文为博主原创文章，转载请注明出处：http://www.cnblogs.com/ysw-go/
1、本博客的原创原创文章，都是本人平时学习所做的笔记，如有错误，欢迎指正。
2、如有侵犯您的知识产权和版权问题，请通知本人，本人会即时做出处理文章。
3、本博客的目的是知识交流所用，转载自其它博客或网站，作为自己的参考资料的，感谢这些文章的原创人员