Add and query data table with object-oriented idea, JDBC code is super detailed

In this paper, the JDBC program is written with the object-oriented idea, and the java program is used to add the student information to the data table, and the student information can be queried by given ID card number or by given examination number.

The created data table is as follows:

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

1. First, a new student information is inserted into the created data table through java program. After entering the detailed information, the message is successfully entered.

1). Create a new Student, corresponding to the examstudent data table.

 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 + "]";
	}
	
}

Create a new method: void addnew student (student student), and insert the parameter Student object into the database.

public void addNewStudent(Student student){
	//1. Prepare qualified sql statements
	String sql="INSERT INTO examstudent VALUES("+student.getFlowId()+","+student.getType()
	+","+student.getIdCard()+","+student.getExamCard()+","+student.getStudentName()+","+
			student.getLocation()+","+student.getGrade()+")";
	System.out.println(sql);
	//Two. Call the update(sql) method of the JDBC tools class to perform the insert operation
	JDBCTools.update(sql);
	}

3). Input students' information from the console.

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

Method call and test

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

Two. Give ID card number or admission card number to query student information

public void testGetStudent(){
	//1. Get the type of query
	int searchType=getSearchTypeFromConsole();
	//2. Specific query of student information
	Student student=searchStudent(searchType);
	//3. Print student information
	printStudent(student);
}

1). Get query type

private int getSearchTypeFromConsole() {
	System.out.println("Query type: 1.Query student information according to ID card number    2.Query the student information according to the examination permit number  ");
	System.out.print("Please enter query type:");
	Scanner scanner=new Scanner(System.in);
	int type=scanner.nextInt();
	if ((type!=1)&&(type!=2)){
		System.out.print("1.Query student information according to ID card number    2.Query the student information according to the examination permit number.Please re-enter the query type:");
		throw new RuntimeException();
	}
	return type;
}

2) specific inquiry of student information

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("Please enter ID number:");
		String str=scanner.next();
		sql=sql+"IDCard="+"'"+str+"'";
	}else{
		System.out.print("Please enter the examination Permit No.:");
		String str=scanner.next();
		sql=sql+"ExamCard"+"'"+str+"'";
	}
	Student student=getStudent(sql);
	return student;
}

Database operation to obtain student information:

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) print student information

private void printStudent(Student student) {
	if(student!=null){
		System.out.println(student);
	}else{
		System.out.println("There is no such person.");
	}
}

 

Complete sample code:

Student.java (shown above)

Database operation tool class: JDBC tools.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 {
	//Update database
    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);
        }
    }
	//Get connection to database
	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();
    }
	//Database release
    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();
        }
    }
    }
}

Function implementation class: JDBC test.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. Get the type of query
		int searchType=getSearchTypeFromConsole();
		//2. Specific query of student information
		Student student=searchStudent(searchType);
		//3. Print student information
		printStudent(student);
	}
	private void printStudent(Student student) {
		if(student!=null){
			System.out.println(student);
		}else{
			System.out.println("There is no such person.");
		}
	}
	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("Please enter ID number:");
			String str=scanner.next();
			sql=sql+"IDCard="+"'"+str+"'";
		}else{
			System.out.print("Please enter the examination Permit No.:");
			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("Query type: 1.Query student information according to ID card number    2.Query the student information according to the examination permit number  ");
		System.out.print("Please enter query type:");
		Scanner scanner=new Scanner(System.in);
		int type=scanner.nextInt();
		if ((type!=1)&&(type!=2)){
			System.out.print("1.Query student information according to ID card number    2.Query the student information according to the examination permit number.Please re-enter the query type:");
			throw new RuntimeException();
		}
		return type;
	}
	@Test
	public void testAddNewStudent(){
		Student student=getStudentFromConsole();
		addNewStudent(student);
	}
	//Enter student information from the console
	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. Prepare qualified sql statements
		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. Call the update(sql) method of the JDBC tools class to perform the insert operation
		JDBCTools.update(sql);
	}
}

Tags: Java SQL JDBC Database

Posted on Tue, 03 Dec 2019 19:19:41 -0500 by rbastien