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

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

3 December 2019, 19:19 | Views: 6816