Java Study Notes-use JDBC to add, delete, modify, and query databases (solution 1) [recommended]

Tool class basedao. Java

Package COM. ACCP. JDBC; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. preparedstatement; import Java. SQL. resultset; import Java. SQL. sqlexception; import Org. apache. log4j. logger; public class basedao {// use log4j to record Private Static logger = logger. getlogger (basedao. class); // connection driver Private Static final string driver = "com. microsoft. sqlserver. JDBC. sqlserverdriver "; // connection path priva Te static final string url = "JDBC: sqlserver: // localhost: 1433; databasename = test"; // user name Private Static final string username = "sa "; // password Private Static final string Password = "sssaaa"; // static code block static {try {// load the driver class. forname (driver);} catch (classnotfoundexception e) {e. printstacktrace (); logger. error ("failed to load driver", e) ;}/ ** get database connection */public connection getconnection () {connection conn = NULL; logger. deb UG ("start connecting to Database"); try {// establish a connection with the database conn = drivermanager. getconnection (URL, username, password);} catch (sqlexception e) {e. printstacktrace (); logger. error ("database connection failed! ", E);} logger. debug ("database connection succeeded"); Return conn;}/** close database connection, pay attention to the order of closing */Public void close (resultset RS, preparedstatement ps, connection conn) {// Note: If (RS! = NULL) {try {Rs. close (); RS = NULL;} catch (sqlexception e) {e. printstacktrace (); logger. error ("failed to disable resultset", e) ;}} if (PS! = NULL) {try {ps. close (); PS = NULL;} catch (sqlexception e) {e. printstacktrace (); logger. error ("failed to disable preparedstatement", e) ;}} if (Conn! = NULL) {try {Conn. close (); Conn = NULL;} catch (sqlexception e) {e. printstacktrace (); logger. error ("failed to close connection", e) ;}}/** query method ** SQL: SQL statement to be executed * Handler: Custom interface * OBJ: variable Parameter List */Public <t> T query (string SQL, resultsethandler <t> handler, object... OBJ) {connection conn = getconnection (); // get the connection preparedstatement PS = NULL; resultset rs = NULL; try {// create a preparedstatement object PS = Conn. preparestatement (SQL); // set the setp parameter for the query statement Arameter (Ps, OBJ); // rsw.ps.exe cutequery (); // return the return handler object. callback (RS);} catch (sqlexception e) {e. printstacktrace (); logger. error ("database operation exception", e) ;}finally {// close the connection close (RS, PS, Conn); logger. debug ("resource released successfully");} return NULL;}/** add, modify, and delete Methods ** OBJ: variable Parameter List */Public int Update (string SQL, object... OBJ) {connection conn = getconnection (); // get the connection preparedstatement PS = NULL; int rows = 0; try {// create prepared Statement object PS = Conn. preparestatement (SQL); // set the parameter setparameter (Ps, OBJ) for the query statement; // The number of affected rows in the query statement rowsw.ps.exe cuteupdate ();} catch (sqlexception e) {e. printstacktrace (); logger. error ("database operation exception", e) ;}finally {// close the connection close (null, PS, Conn); logger. debug ("resource released successfully");} return rows;}/** set the parameter */Public void setparameter (preparedstatement ps, object... OBJ) throws sqlexception {If (OBJ! = NULL & obj. length> 0) {// loop Setting Parameter for (INT I = 0; I <obj. length; I ++) {ps. setobject (I + 1, OBJ [I]) ;}}// custom interface resultsethandler <t >{ public t callback (resultset RS );}

Operation class to add, delete, modify, and query userdao. Java

Package COM. ACCP. JDBC; import Java. SQL. connection; import Java. SQL. preparedstatement; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; import Java. util. arraylist; import Java. util. list; import COM. ACCP. entity. user; public class userdao extends basedao {public static void main (string [] ARGs) {userdao user = new userdao (); list <user> List = new arraylist <user> (); // query the data list of ID <10 = User. queryuser (); // traverses the result set for (User U: List) {system. out. println (U);} // query a single data entry system. out. println (user. queryuserbyid (10); // update data user u = new user (); U. setid (2); U. setname ("Zhang Long"); U. setage (22); system. out. println ("Update" + User. updateuser (u) + "data entry"); // deletes the data system. out. println ("delete" + User. deleteuser (15) + "data entries"); // insert data user U1 = new user (); u1.setname ("Zhang San"); u1.setage (18); User. insertuser (U1);} // query multiple pieces of information public list <user> queryuser () {String SQL = "select * from users where ID <10 "; // implement the custom API resultsethandler anonymously <list <user> handler = new resultsethandler <list <user> () {@ overridepublic list <user> callback (resultset RS) {list <user> List = new arraylist <user> (); // a collection of results. User-type user = NULL; try {While (RS. next () {user = new user (); // instantiate a user object user. setid (RS. getint ("ID"); User. setage (RS. getint ("Age"); User. setname (RS. getstring ("name"); lis T. add (User); // Add to list set} catch (sqlexception e) {e. printstacktrace () ;}return list ;}; return query (SQL, Handler) ;}// query a single piece of data public user queryuserbyid (INT ID) {string SQL = "select * from users where id =? "; // Implement the custom resultsethandler interface anonymously <user> handler = new resultsethandler <user> () {user = new user (); @ overridepublic user callback (resultset RS) {try {While (RS. next () {user. setage (RS. getint ("Age"); User. setid (RS. getint ("ID"); User. setname (RS. getstring ("name") ;}} catch (sqlexception e) {e. printstacktrace () ;}return user ;}; return query (SQL, handler, ID) ;}// update data public int updateuser (User user) {string SQL = "Up Date users set name = ?, Age =? Where id =? "; Return Update (SQL, user. getname (), user. getage (), user. GETID ();} // delete data public int deleteuser (int id) {string SQL = "delete from users where id =? "; Return Update (SQL, ID);} // insert data and return the Automatically increasing primary key value public void insertuser (User user) {connection conn = getconnection (); // obtain the connection preparedstatement PS = NULL; resultset rs = NULL; string SQL = "insert into users values (?,?) "; Try {// create a pre-compiled object that can return the automatically generated primary key value PS = Conn. preparestatement (SQL, statement. return_generated_keys); PS. setstring (1, user. getname (); PS. setint (2, user. getage (); int rowsw.ps.exe cuteupdate (); // obtain the Automatically increasing primary key value rs = ps. getgeneratedkeys (); RS. next (); // obtain idint id = Rs. getint (1); system. out. println ("successfully added:" + rows + "data, ID:" + id);} catch (sqlexception e) {e. printstacktrace () ;}finally {close (RS, PS, Conn );}}}

Insert test data inserttest. Java

Package COM. ACCP. JDBC; import Java. SQL. connection; import Java. SQL. preparedstatement; import Java. SQL. sqlexception; public class inserttest extends basedao {public static void main (string [] ARGs) {inserttest user = new inserttest (); User. insertuser ();} public void insertuser () {string SQL = "insert into users values (?,?) "; Connection conn = getconnection (); preparedstatement PS = NULL; try {// disable automatic commit of transaction Conn. setautocommit (false); // create a pre-compiled object PS = Conn. preparestatement (SQL); // The number of milliseconds in the start time long start = system. currenttimemillis (); For (INT I = 0; I <10000; I ++) {ps. setstring (1, I + ""); PS. setint (2, 22); PS. addbatch (); // batch to batch command: execute batch Conn. commit (); // transaction commit // Number of milliseconds of the end time long stop = system. currenttimemillis (); // get the total elapsed time long MS = stop-start; system. out. println ("Insert 10 thousand records time:" + MS + "millisecond");} catch (sqlexception e) {e. printstacktrace (); // cancel the transaction try {Conn. rollback ();} catch (sqlexception ee) {ee. printstacktrace () ;}} finally {// open the automatic commit transaction try {Conn. setautocommit (true);} catch (sqlexception e) {e. printstacktrace () ;}close (null, PS, Conn );}}}