JDBC Operational Database Statements

Very useful JDBC Operations database statements are recorded to facilitate subsequent queries.

public class Persondao {    //Add operation public      void Insert (person person) throws Exception;        Modify operation public      void update (person person) throws Exception;    Delete operation public      void Delete (String id) throws Exception;    Query by ID operation public person      Querybyid (String id) throws Exception;    Query all public      List Queryall () throws Exception;    Fuzzy query public      List querybylike (String cond) throws Exception;}

    This class needs to complete specific database operations, requires JDB code public class Persondaoimpl implements Persondao {//Add operation public void insert (person person) throws Exception {String sql = ' INSERT INTO person (id,name,password,ag              E,email) VALUES (?,?,?,?,?) ";              PreparedStatement pstmt = null;                    DatabaseConnection DBC = null;  Here is a specific action for the database try {//Connect database DBC = new DatabaseConnection ()                  ;                  pstmt = Dbc.getconnection (). preparestatement (SQL);                  Pstmt.setstring (1,person.getid ());                  Pstmt.setstring (2,person.getname ());                  Pstmt.setstring (3,person.getpassword ());                  Pstmt.setint (4,person.getage ());                  Pstmt.setstring (5,person.getemail ());                  Perform database update operations Pstmt.executeupdate ();              Pstmt.close (); } catch (ExceptioN e) {throw new Exception ("Operation exception");              } finally {//Close database connection Dbc.close (); }}//modify operation public void update (person person) throws Exception {String s QL = "UPDATE person SET name=?,password=?,age=?,email=?"              WHERE id=? ";              PreparedStatement pstmt = null;                    DatabaseConnection DBC = null;  Here is a specific action for the database try {//Connect database DBC = new DatabaseConnection ()                  ;                           pstmt = Dbc.getconnection (). preparestatement (SQL);                  Pstmt.setstring (1,person.getname ());                  Pstmt.setstring (2,person.getpassword ());                  Pstmt.setint (3,person.getage ());                  Pstmt.setstring (4,person.getemail ());                  Pstmt.setstring (5,person.getid ()); Perform database update operations                  Pstmt.executeupdate ();              Pstmt.close ();              } catch (Exception e) {throw new Exception ("Operation exception");              } finally {//Close database connection Dbc.close ();  }}//delete operation public void Delete (string id) throws Exception {String sql =              "DELETE from person WHERE id=?";              PreparedStatement pstmt = null;                    DatabaseConnection DBC = null;  Here is a specific action for the database try {//Connect database DBC = new DatabaseConnection ()                  ;                           pstmt = Dbc.getconnection (). preparestatement (SQL);                  Pstmt.setstring (1,id);                  Perform database update operations Pstmt.executeupdate ();              Pstmt.close ();             } catch (Exception e) {     throw new Exception ("Operation exception");              } finally {//Close database connection Dbc.close (); }}//By ID query operation public person Querybyid (String id) throws Exception {Pers              on person = null;              String sql = "Select Id,name,password,age,email from the person WHERE id=?";              PreparedStatement pstmt = null;                    DatabaseConnection DBC = null;  Here is a specific action for the database try {//Connect database DBC = new DatabaseConnection ()                  ;                           pstmt = Dbc.getconnection (). preparestatement (SQL);                  Pstmt.setstring (1,id);                  Perform database query operations ResultSet rs = Pstmt.executequery (); if (Rs.next ()) {//Queries out content, then assigns the queried content to the person object person = new Pers                      On (); Person. SetId (rs.getstring (1));                      Person.setname (rs.getstring (2));                      Person.setpassword (Rs.getstring (3));                      Person.setage (Rs.getint (4));                  Person.setemail (Rs.getstring (5));                  } rs.close ();              Pstmt.close ();              } catch (Exception e) {throw new Exception ("Operation exception");              } finally {//Close database connection Dbc.close ();          } return person;              }//Query All public List Queryall () throws Exception {List all = new ArrayList ();              String sql = "Select Id,name,password,age,email from";              PreparedStatement pstmt = null;                    DatabaseConnection DBC = null;               Here is a specific action for the database try {//Connect database   DBC = new DatabaseConnection ();                           pstmt = Dbc.getconnection (). preparestatement (SQL);                  Perform database query operations ResultSet rs = Pstmt.executequery (); while (Rs.next ()) {//Queries out content, then assigns the queried content to the person object person                      = new Person ();                      Person.setid (rs.getstring (1));                      Person.setname (rs.getstring (2));                      Person.setpassword (Rs.getstring (3));                      Person.setage (Rs.getint (4));                            Person.setemail (Rs.getstring (5));                  Add the queried data to the list object All.add (person);                  } rs.close ();              Pstmt.close ();              } catch (Exception e) {throw new Exception ("Operation exception");          } finally {//close database connection        Dbc.close ();          } return all; }//Fuzzy query public List querybylike (String cond) throws Exception {List all = new A              Rraylist (); String sql = "Select Id,name,password,age,email from" WHERE is the name like?              Or email like? ";              PreparedStatement pstmt = null;                    DatabaseConnection DBC = null;  Here is a specific action for the database try {//Connect database DBC = new DatabaseConnection ()                  ;                   pstmt = Dbc.getconnection (). preparestatement (SQL);                  Set Fuzzy query criteria pstmt.setstring (1, "%" +cond+ "%");                  Pstmt.setstring (2, "%" +cond+ "%");                  Perform database query operations ResultSet rs = Pstmt.executequery (); while (Rs.next ()) {//Queries out content, then assigns the queried content to the person object person       = new Person ();               Person.setid (rs.getstring (1));                      Person.setname (rs.getstring (2));                      Person.setpassword (Rs.getstring (3));                      Person.setage (Rs.getint (4));                            Person.setemail (Rs.getstring (5));                  Add the queried data to the list object All.add (person);                  } rs.close ();              Pstmt.close ();              } catch (Exception e) {throw new Exception ("Operation exception");              } finally {//Close database connection Dbc.close ();          } return all;   }      };

Ps:

1.pstmt operation, the first space, should be filled 1, not 0, otherwise the wrong column index will be reported;

2. After the query resultset, the first to make a non-null judgment, if present, then proceed to the next assignment;

3. Every time the database is connected, remember to close, of course, you can completely replace it with the database connection pool, which will be improved later.

JDBC Operational Database Statements