Database Operation Methods Summary, database operation methods

Database Operation Methods Summary, database operation methods
Package com. buu. news. day3.entity; public class UserInfo {private String userID; private String UserRealName; private String sex; private String birth; private String famillyaddress; private String email; private String tel; private String userLoginName; private String regDate; private String userPasword; private String conform; private String flag; public String getUserID () {return userID;} publ Ic void setUserID (String userID) {this. userID = userID;} public String getUserRealName () {return UserRealName;} public void setUserRealName (String userRealName) {UserRealName = userRealName;} public String getSex () {return sex ;} public void setSex (String sex) {this. sex = sex;} public String getBirth () {return birth;} public void setBirth (String birth) {this. birth = birth;} public Str Ing getFamillyaddress () {return famillyaddress;} public void setFamillyaddress (String famillyaddress) {this. famillyaddress = famillyaddress;} public String getEmail () {return email;} public void setEmail (String email) {this. email = email;} public String getTel () {return tel;} public void setTel (String tel) {this. tel = tel;} public String getUserLoginName () {return userLoginName;} pub Lic void setUserLoginName (String userLoginName) {this. userLoginName = userLoginName;} public String getRegDate () {return regDate;} public void setRegDate (String regDate) {this. regDate = regDate;} public String getUserPasword () {return userPasword;} public void setUserPasword (String userPasword) {this. userPasword = userPasword;} public String getConform () {return conform;} public void s EtConform (String conform) {this. conform = conform;} public String getFlag () {return flag;} public void setFlag (String flag) {this. flag = flag ;}} creates an interface layer for the UserInfo table and defines some operations, which are called user operation interfaces. Logons, deletions, modifications, queries, and inserts new users. The operation is as follows: package com. buu. news. day3.dao; import java. SQL. resultSet; import com. buu. news. day3.entity. userInfo; /** user operation interface * 1. logon operation * 2. delete operation * 3. Modify operation * 4. query operation * 5. Insert new user operation */public interface UserDao {// you need to first define an object class that encapsulates the user/** 1. User Login operation * parameter: user object * return value: true: logon successful * false: Logon Failed */public boolean userLogin (UserInfo user);/** 2. delete user operation * parameter: user object * return value: int: 0: unsuccessful *! 0: Successful */public int userDelete (UserInfo user);/** 3. user modifies * parameter: user object * return value: int: 0: unsuccessful *! 0: Successful */public int userUpdate (UserInfo user);/** 4. user insert * parameter: user object * return value: int: 0: unsuccessful *! 0: Successful */public int userInsert (UserInfo user);/** 5. user query * parameter: user object * return value: the resultset set **/public ResultSet userSelect (UserInfo user);} creates an interface layer for UserServiceDao, which is the same as the above Code. Only an interface is added. Create UserDaoImpl, which is the implementation class of UserDao and implements the UerDao interface. The Code is as follows: (add, delete, modify, and query are included) package com. buu. news. day3.dao. impl; import java. SQL. resultSet; import com. buu. news. day3.dao. userDao; import com. buu. news. day3.entity. userInfo; import com. buu. news. day3.util. connDB; public class UserDaoImpl extends ConnDB implements UserDao {// public ConnDB conn = new ConnDB (); public int userDelete (UserInfo user) {// TODO Auto-generated method stub String SQL = "delete from userInfo w Here userID =? "; // Placeholder? String [] param = {user. getUserID ()}; // The call method must pass the array int rtn = this.exe cuteSQL (SQL, param); if (rtn> 0) System. out. println ("deleted successfully"); else System. out. println ("failed to delete"); return rtn;} public int userInsert (UserInfo user) {// TODO Auto-generated method stub String SQL = "insert into userInfo values (?,?,?,?,?,?,?,?,?,?,?) "; String [] param = {user. getUserRealName (), user. getSex (), user. getBirth (), user. getFamillyaddress (), user. getEmail (), user. getTel (), user. getUserLoginName (), user. getRegDate (), user. getUserPasword (), user. getConform (), user. getFlag ()}; int rtn = this.exe cuteSQL (SQL, param); if (rtn> 0) System. out. println ("inserted successfully"); else System. out. println ("insertion failed"); return rtn;} public boolean userLogin (UserInfo user) {// DO Auto-generated method stub return false;} public ResultSet userSelect (UserInfo user) {// TODO Auto-generated method stub String SQL = "select * from userInfo where userID =? "; String [] param = {user. getUserID ()}; ResultSet rs = this.exe cuteSelectSQL (SQL, param); if (rs! = Null) System. out. println ("query succeeded"); else System. out. println ("query failed"); return rs;} public int userUpdate (UserInfo user) {// TODO Auto-generated method stub String SQL = "update userInfo set userRealName = ?, Sex = ?, Birth = ?, FimallyAddress = ?, Email =? "; SQL + =", Tel = ?, UserLoginName = ?, RegDate = ?, UserPassword = ?, Confirm1 = ?, Flog =? Where userID =? "; String [] param = {user. getUserRealName (), user. getSex (), user. getBirth (), user. getFamillyaddress (), user. getEmail (), user. getTel (), user. getUserLoginName (), user. getRegDate (), user. getUserPasword (), user. getConform (), user. getFlag (), user. getUserID ()}; int rtn = this.exe cuteSQL (SQL, param); if (rtn> 0) System. out. println ("updated successfully"); else System. out. println ("update failed"); return rtn ;}among them, there are several key points, you need to mention the Key Point 1: this. e XecuteSelectSQL (SQL, param); and this.exe cuteSQL (SQL, param. You can define a tool class to implement top-level operations. These top-level operations are tools and reusable code logic. Key Aspect 2: Why use this? In fact, we can use class instantiation and then call the object. method to implement it. The above form is used here. Our colleagues who are implementing this interface can also inherit the tool class, so that we don't need to instantiate it. In this way, the amount of code can be further reduced. Robustness is improved. The ConnDB code of the tool class is as follows: package com. buu. news. day3.util; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement;/** basic database operations * 1. Obtain the connection object * 2. Release related resources * 3. add, delete, modify, and query the database */public class ConnDB {// define the data connection string, user name and password for accessing the database public final static String driver = "com. microsoft. sqlserver. jdbc. SQLServerDriver "; pub Lic final static String url = "jdbc: sqlserver: // localhost: 1433; DataBaseName = NewsSystem"; public final static String dbName = "sa "; public final static String dbPa = "123456";/* 1. Obtain the database connection object * return value: Connection object */public Connection getConn () {connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (url, dbName, dbPa); System. out. println ("database connection successful"); //} catch (failed t) prompted Ion e) {// to reduce the number of thrown exceptions, the parent class of ClassNotFound can be thrown. // TODO Auto-generated catch block e. printStackTrace () ;}// exception handling is required to increase program robustness return conn;}/* release all resources connected to the database * resources include: connection object, statement object, preparement object, resultset object */public void closeAll (Connection conn, Statement stmt, PreparedStatement pstmt, ResultSet rs) {try {if (rs! = Null) rs. close (); if (pstmt! = Null) pstmt. close (); if (stmt! = Null) stmt. close (); if (conn! = Null) conn. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}}/** perform the update operation: insert, modify, and delete (successful or failed) * parameter: SQL statement; param: SQL value assignment parameter * return value: int type. Number of rows affected by SQL statements on the database * 0: Meaning: insertion, modification, and deletion failed */public int executeSQL (String SQL, String [] param) {Connection conn = null; preparedStatement pstmt = null; int rtn = 0; // process the SQL statement and run the SQL statement conn = this. getConn (); // there is already a link to try {pstmt = conn. prepareStatement (SQL); // there is only one SQL statement, but more than one value is assigned. Avoid repeating the if (param! = Null) {for (int I = 0; I <param. length; ++ I) {pstmt. setString (I + 1, param [I]); // assign a parameter to the pre-compiled SQL statement. The subscripts start from 1. (The number in the array is the number of placeholders in the SQL statement, but the subscript in the SQL statement starts from 1 and the array subscript starts from 0 )}} // execute the SQL statement rtn = pstmt.exe cuteUpdate (); // only the int type parameter is returned} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();} return rtn;}/** execute query operation * parameter: SQL statement; param: SQL assignment parameter * return value: resultSet type **/public ResultSet executeSelectSQL (String SQL, String [] param) {Connection conn = null; PreparedStatement pstmt = null; // process SQL statements and execute Row SQL statement conn = this. getConn (); // there is already a link to ResultSet rtn = null; try {pstmt = conn. prepareStatement (SQL); // there is only one SQL statement, but more than one value is assigned. Avoid repeating the if (param! = Null) {for (int I = 0; I <param. length; ++ I) {pstmt. setString (I + 1, param [I]); // assign a parameter to the pre-compiled SQL statement. The subscripts start from 1.} // Execute the SQL statement rtn = pstmt.exe cuteQuery (); // only the int type parameter is returned} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}return rtn ;}} creates a database access interface. Here UserServiceDaoImpl is used to implement the UserServiceDao interface class. The program is just an interface. It is instantiated first and then returns some values. Package com. buu. news. day3.service. impl; import java. SQL. resultSet; import com. buu. news. day3.dao. userDao; import com. buu. news. day3.dao. impl. userDaoImpl; import com. buu. news. day3.entity. userInfo; import com. buu. news. day3.service. userServiceDao; public class UserServiceDaoImpl implements UserServiceDao {// create a database access interface private UserDao userdao = new UserDaoImpl (); public int userDelete (UserInfo user) {// DO Auto-generated method stub int rtn = userdao. userDelete (user); return rtn;} public int userInsert (UserInfo user) {// TODO Auto-generated method stub int rtn = userdao. userInsert (user); return rtn;} public boolean userLogin (UserInfo user) {// TODO Auto-generated method stub boolean rtn = userdao. userLogin (user); return rtn;} public ResultSet userSelect (UserInfo user) {// TODO Auto-generate D method stub ResultSet rs = userdao. userSelect (user); return rs;} public int userUpdate (UserInfo user) {// TODO Auto-generated method stub int rtn = userdao. userUpdate (user); return rtn ;}above, the business logic layer calls the data access layer, followed by the presentation layer calls the business logic layer.