< three >jdbc_ the embodiment of object-oriented thought

Jdbctools.java

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 {
 
 /*
  * methods to execute SQL
  *   insert,update,delete< br>  * */
 public static void update (String sql) {
  
   Connection Conn=null ;
   statement St=null;
  
   try {
    /*
    * 1, Get connection connection
    * 2, Get statement
    * 3, SQL statement
    * 4, close database connection
    *
    * */
    conn=getconnection ();
    st=conn.createstatement ();
    st.executeupdate (SQL);
   
   
   } catch (Exception e) {
    e.print StackTrace ();
  }finally{
    release (ST, conn);
   }
 }

public static Connection getconnection () throws Exception {

String driverclass = null;
String jdbcurl = null;
String user = null;
String password = null;

Read the Jdbc.properties file under the Classpath
InputStream in = JDBCTools.class.getClassLoader (). 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");
Load Database Driver
Class.forName (Driverclass);
Get a database connection by DriverManager's getconnection () method
Connection Connection = drivermanager.getconnection (jdbcurl, user, password);
return connection;

}

public static void Release (Statement St, Connection conn) {

if (st! = null) {
try {
St.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}

IF (conn! = null) {
try {
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}

public static void Release (ResultSet rs,statement st,connection conn) {

if (rs!=null) {
try {
Rs.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}

if (st!=null) {
try {
St.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}

if (conn!=null) {
try {
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}
}

Test class: Testjdbc.java

Import java.sql.Connection;
Import Java.sql.ResultSet;
Import java.sql.Statement;
Import Java.util.Scanner;

Import Org.junit.Test;

public class Testjdbc {

@Test
public void Testgetstudent () {
/*
* 1, the type of query to get
* 2, the specific inquiry student information
* 3, Print display information
* */
int Searchtype=getsearchtypefromconsole ();
Student student=searchstudent (SearchType);

Printstudent (student);
}

private void Printstudent (Student Student) {

if (student!=null) {
SYSTEM.OUT.PRINTLN (student);
}else{
System.out.println ("Check no this person!");
}
}

/*
* Specific query display information, return a student object, if not present return null
* @param searchtype:1 or 2
* @return
* */
Private Student searchstudent (int searchtype) {
/*
* Search=1: Prompt to enter the ID number
* search=2: Ticket Number
* */
String sql= "Select Flowid,type,idcard,examcard,studentname from"
+ "examstudent where";

Scanner sc=new Scanner (system.in);
if (searchtype==1) {
System.out.println ("Please enter your ID number:");
String Examcard=sc.next ();
sql=sql+ "idcard=" "+examcard+" ";

}else{
System.out.println ("Please enter the ticket number:");
String Examcard=sc.next ();
sql=sql+ "examcard=" "+examcard+" ";
}
Execute Query
Student student=getstudent (SQL);

return student;
}

Private Student getstudent (String sql) {

Student Stu=null;
Connection Conn=null;
Statement St=null;
ResultSet Rs=null;
try {
Conn=jdbctools.getconnection ();
St=conn.createstatement ();
Rs=st.executequery (SQL);

if (Rs.next ()) {
Stu=new Student ();
Stu.setflowid (Rs.getint (1));
Stu.settype (Rs.getint (2));
Stu.setidcard (Rs.getstring (3));
Stu.setexamcard (Rs.getstring (4));
Stu.setstudentname (Rs.getstring (5));
}

} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (RS, St, conn);
}

return Stu;
}

private int Getsearchtypefromconsole () {
/*
* 1: User ID card Query
* 2: User ticket Number Query
* */
System.out.println ("Please enter query type: 1. Use the ID card to inquire; 2. Use the quasi-Test number query");
Scanner sc=new Scanner (system.in);
int Type=sc.nextint ();
if (type!=1&&type!=2) {
SYSTEM.OUT.PRINTLN ("Input wrong, please re-enter!");
throw new RuntimeException ();
}
return type;
}

 public void addnewstudent (Student Student) {
  /*
   * 1, preparing SQL statements
    * 2, call the Jdbctools class's update (SQL) method to perform the insert operation
   * PROBLEM:
   *   string Type value
    * */
  string sql= "insert into examstudent values ("
    + "" + Student.getflowid () + ","
    +student.gettype () + ", '"
    + Student.getidcard () + "', '"
    +student.getexamcard () + "', '"
    + Student.getstudentname ()
    + "')";
 
  jdbctools.update (SQL);
 }
 
  @Test
 public void Testaddnewstudent () {
  
   Student Student=getstudentfromconsole ();
  addnewstudent (student);
 }

/*
* Enter student information from the console
* */
Private Student Getstudentfromconsole () {

Scanner sc=new Scanner (system.in);
Student student=new Student ();
System.out.print ("Flowid:");
Student.setflowid (Sc.nextint ());
System.out.print ("Type:");
Student.settype (Sc.nextint ());
System.out.print ("Idcard:");
Student.setidcard (Sc.next ());
System.out.print ("Examcard:");
Student.setexamcard (Sc.next ());
System.out.print ("Studentname:");
Student.setstudentname (Sc.next ());

return student;
}
}

< three >jdbc_ the embodiment of object-oriented thought