以面向对象的思想实现数据表的添加和查询，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);
  }
}