以面向对象的思想实现数据表的添加和查询,JDBC代码超详细

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 以面向对象的思想实现数据表的添加和查询,JDBC代码超详细

点击蓝字“程序员考拉”欢迎关注!

以面向对象的思想实现数据表的添加和查询,JDBC代码超详细

以面向对象的思想编写JDBC程序,实现使用java程序向数据表中添加学生信息,并且可以实现给定身份证号查询学生信息或给定准考证号查询学生信息。

创建的数据表如下:


CREATE TABLE EXAMSTUDENT(
    FlowID INTEGER,
    Type INTEGER,
    IDCard VARCHAR(18),
    ExamCard VARCHAR(15),
    StudentName VARCHAR(20),
    Location VARCHAR(20),
    Grade INTEGER);

1.首先实现通过java程序在创建的数据表中插入一个新的student信息,输入详细信息之后显示消息录入成功。

1).新建一个Student,对应examstudent数据表。

 Student.java


package com.test.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(){ }
  @Override
  public String toString() {
    return "student [flowId=" + flowId + ", type=" + type + ", idCard=" + idCard + ", examCard=" + examCard
        + ", studentName=" + studentName + ", location=" + location + ", grade=" + grade + "]";
  }
  
}

 2).新建一个方法:void addNewStudent(Student student),把参数Student对象插入到数据库中。


public void addNewStudent(Student student){
  //1.准备符合要求的sql语句
  String sql="INSERT INTO examstudent VALUES("+student.getFlowId()+","+student.getType()
  +","+student.getIdCard()+","+student.getExamCard()+","+student.getStudentName()+","+
      student.getLocation()+","+student.getGrade()+")";
  System.out.println(sql);
  //2.调用JDBCTools类的update(sql)方法执行插入操作
  JDBCTools.update(sql);
  }

 

3).从控制台输入学生的信息。


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;
}

 

4).方法调用并测试


@Test
public void testAddNewStudent(){
  Student student=getStudentFromConsole();
  addNewStudent(student);
}

2.给定身份证号或准考证号查询学生信息


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

 

1).得到查询类型


private int getSearchTypeFromConsole() {
  System.out.println("查询类型:1.根据身份证号查询学生信息    2.根据准考证号查询学生信息  ");
  System.out.print("请输入查询类型:");
  Scanner scanner=new Scanner(System.in);
  int type=scanner.nextInt();
  if ((type!=1)&&(type!=2)){
    System.out.print("1.根据身份证号查询学生信息    2.根据准考证号查询学生信息.请重新输入查询类型:");
    throw new RuntimeException();
  }
  return type;
}

 

2).具体查询学生信息


private Student searchStudent(int searchType) {
  String sql="SELECT FlowID,TYPE,IDCard,ExamCard,StudentName,Location,Grade FROM EXAMSTUDENT WHERE ";
  Scanner scanner=new Scanner(System.in);
  if (searchType==1){
    System.out.print("请输入身份证号:");
    String str=scanner.next();
    sql=sql+"IDCard="+"'"+str+"'";
  }else{
    System.out.print("请输入准考证号:");
    String str=scanner.next();
    sql=sql+"ExamCard"+"'"+str+"'";
  }
  Student student=getStudent(sql);
  return student;
}

数据库操作获取学生信息:


private Student getStudent(String sql) {
    Student stu=null;
    Connection con=null;
        Statement statement=null;
        ResultSet resultset=null;
        
        try{
            con=JDBCTools.getConnection();
            statement=con.createStatement();
            resultset=statement.executeQuery(sql);
            if(resultset.next()){
              stu=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(statement, con);
        }
    return stu;
  }

3).打印学生信息


private void printStudent(Student student) {
  if(student!=null){
    System.out.println(student);
  }else{
    System.out.println("查无此人");
  }
}

 

完整的示例代码:

Student.java(上面已给出)

数据库操作工具类:JDBCTools.java


package com.test.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.junit.Test;

public class JDBCTools {
  //更新数据库
    public static void update(String sql){
        Connection con=null;
        Statement statement=null;
        try{
            con=JDBCTools.getConnection();
            statement=con.createStatement();
            statement.executeUpdate(sql);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCTools.release(statement, con);
        }
    }
  //获取数据库的连接
  public static Connection getConnection() throws Exception{
    String driverClass=null;
    String jdbcUrl=null;
    String user=null;
    String password=null;
    
    InputStream in=JDBCTools.class.getResourceAsStream("/jdbc.properties");
    Properties properties=new Properties();
    properties.load(in);
    
    driverClass=properties.getProperty("driver");
    jdbcUrl=properties.getProperty("jdbcUrl");
    user=properties.getProperty("user");
    password=properties.getProperty("password");
    
    Class.forName(driverClass);
    Connection connection=DriverManager.getConnection(jdbcUrl,user,password);
    
    return connection;
  }
    @Test 
    public void testGetConnection() throws Exception{
      getConnection();
    }
  //数据库释放
    public static void release(Statement statement,Connection connection){
        if(statement!=null){
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        }
        if(connection!=null){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    }
}

功能实现类:JDBCTest.java


package com.test.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import org.junit.Test;

public class JDBCTest {
  @Test
  public void testGetStudent(){
    //1.得到查询的类型
    int searchType=getSearchTypeFromConsole();
    //2.具体查询学生信息
    Student student=searchStudent(searchType);
    //3.打印学生信息
    printStudent(student);
  }
  private void printStudent(Student student) {
    if(student!=null){
      System.out.println(student);
    }else{
      System.out.println("查无此人");
    }
  }
  private Student searchStudent(int searchType) {
    String sql="SELECT FlowID,TYPE,IDCard,ExamCard,StudentName,Location,Grade FROM EXAMSTUDENT WHERE ";
    Scanner scanner=new Scanner(System.in);
    if (searchType==1){
      System.out.print("请输入身份证号:");
      String str=scanner.next();
      sql=sql+"IDCard="+"'"+str+"'";
    }else{
      System.out.print("请输入准考证号:");
      String str=scanner.next();
      sql=sql+"ExamCard"+"'"+str+"'";
    }
    Student student=getStudent(sql);
    return student;
  }
  private Student getStudent(String sql) {
    Student stu=null;
    Connection con=null;
        Statement statement=null;
        ResultSet resultset=null;
        
        try{
            con=JDBCTools.getConnection();
            statement=con.createStatement();
            resultset=statement.executeQuery(sql);
            if(resultset.next()){
              stu=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(statement, con);
        }
    return stu;
  }
  private int getSearchTypeFromConsole() {
    System.out.println("查询类型:1.根据身份证号查询学生信息    2.根据准考证号查询学生信息  ");
    System.out.print("请输入查询类型:");
    Scanner scanner=new Scanner(System.in);
    int type=scanner.nextInt();
    if ((type!=1)&&(type!=2)){
      System.out.print("1.根据身份证号查询学生信息    2.根据准考证号查询学生信息.请重新输入查询类型:");
      throw new RuntimeException();
    }
    return type;
  }
  @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){
    //1.准备符合要求的sql语句
    String sql="INSERT INTO examstudent VALUES("+student.getFlowId()+","+student.getType()
    +","+student.getIdCard()+","+student.getExamCard()+","+student.getStudentName()+","+
        student.getLocation()+","+student.getGrade()+")";
    System.out.println(sql);
    //2.调用JDBCTools类的update(sql)方法执行插入操作
    JDBCTools.update(sql);
  }
}

 

以面向对象的思想实现数据表的添加和查询,JDBC代码超详细
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 以面向对象的思想实现数据表的添加和查询,JDBC代码超详细


 上一篇
JDBC数据库基本操作 JDBC数据库基本操作
点击蓝字“程序员考拉”欢迎关注! 1.什么是JDBC? 在看JDBC的概念之前先来看看什么是数据库驱动。 数据库驱动中驱动的概念和平时听到的那种驱动的概念是一样的,比如平时购买的声卡,网卡直接插到计算机上面是不能用的,必须要安装相
下一篇 
JDBC的DAO设计模式 JDBC的DAO设计模式
点击蓝字“程序员考拉”欢迎关注! DAO:Data Access Object,是访问数据信息的类,包含了对数据的CRUD(create,read,update,delete),而不包含任何业务相关的信息,更容易实现功能的模块化,