JavaEEWEB工程師培訓(xùn)

1、JavaEEWEB工程師培訓(xùn)-JDBC+Servlet+JSP整合開發(fā)之03.JDBCStatement(l)Statement執(zhí)行DDL使用插入記錄、修改記錄、刪上季我們建立了與數(shù)據(jù)庫(kù)的連接，本季我們將對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作使用Statement執(zhí)行DML常用的插入、更新、刪除及查詢數(shù)據(jù)哈?Statement簡(jiǎn)介-Statement提供了一個(gè)操作數(shù)據(jù)庫(kù)語(yǔ)句的功能，可通過它來創(chuàng)建表、除記錄等操作?獲得Statement-可以從數(shù)據(jù)庫(kù)連接Connection中獲得StatementConnectionconn=newConnectionUtil().getConnection();Statements

2、tmt=conn.createStatement();ConnectionUtil.javapackagecom.michael.jdbc;importjava.sql.Connection;importjava.sql.DriverManager;importjava.util.Properties;publicclassConnectionUtil(/第一種方法publicConnectiongetConnection()(Connectionconn=null;try(/Class.forName加載驅(qū)動(dòng)Class.forName("com.mysql.jdbc.Driver&

3、quot;);/DriverManager獲得連接conn=DriverManager.getConnection("jdbc:mysql:/localhost:3306/jdbc_db”,"root”,"mysqladmin");returnconn;)catch(Exceptione)(e.printStackTrace();)returnnull;)/第二種方法publicConnectiongetConnection(Stringdriver,Stringurl,Stringuser,Stringpassword)(Connectionconn=

4、null;try(/Class.forName加載驅(qū)動(dòng)Class.forName(driver);/DriverManager獲得連接conn=DriverManager.getConnection(url,user,password);returnconn;catch(Exceptione)e.printStackTrace();returnnull;/第三種方法publicConnectionopenConnection()Stringdriver=""Stringurl=""Stringuser=""Stringpassword

5、=""Propertiesprop=newProperties();Connectionconn=null;try/加載屬性文件prop.load(this.getClass().getClassLoader().getResourceAsStream("DBCperties");driver=prop.getProperty("driver");url=prop.getProperty("url");user=prop.getProperty("user");password

6、=prop.getProperty("password");/Class.forName加載驅(qū)動(dòng)Class.forName(driver);/DriverManager獲得連接conn=DriverManager.getConnection(url,user,password);returnconn;catch(Exceptione)e.printStackTrace();returnnull;TestStatement.javapackagecom.michael.jdbc;importjava.sql.Connection;importjava.sql.SQLExcep

7、tion;importjava.sql.Statement;publicclassTestStatementpublicstaticvoidgetStatement()Connectionconn=newConnectionUtil().openConnection();tryStatementstmt=conn.createStatement();System.out.println(stmt);catch(SQLExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();Main.javapackagecom.michael.ma

8、in;importcom.michael.jdbc.ConnectionUtil;importcom.michael.jdbc.TestStatement;publicclassMain*paramargs*/publicstaticvoidmain(Stringargs)ConnectionUtilcu=newConnectionUtil();/第一種方法System.out.println("第一種方法:"+cu.getConnection();/第二種方法System.out.println("第二種方法:"+cu.getConnection(co

9、m.mysql.jdbc.Driver","jdbc:mysql:/localhost:3306/jdbc_db","root","mysqladmin");/第三種方法System.out.println("第三種方法："+cu.openConnection();TestStatement.getStatement();)測(cè)試結(jié)果:TasksfsfcErtwnr目?？阽枳稹?：ZSarvars-Mtin(2)Jv«AppLic«tionb;拳以汶|SiSB|國(guó)-r3第一神方法：co旬

10、.mysql.jbc.ConaectlonllQcea第二特方法心口昂加”iuiMEQii。losoeiiteemHtttysqi.jdbc.Connectianecib£3i到町!uunjWrittblShut環(huán)Et20:37有術(shù)情寫"可?使用Statement執(zhí)行DDL-可以使用Statement來執(zhí)行一個(gè)數(shù)據(jù)定義語(yǔ)句，例如：創(chuàng)建一張表TestStatement.javapackagecom.michael.jdbc;importjava.sql.Connection;importjava.sql.SQLException;importjava.sql.Statemen

11、t;publicclassTestStatementpublicstaticvoidgetStatement()(Connectionconn=newConnectionUtil().openConnection();try(Statementstmt=conn.createStatement();System.out.println(stmt);catch(SQLExceptione)(/TODOAuto-generatedcatchblocke.printStackTrace();publicstaticvoidcreateTable()(/DDL數(shù)據(jù)定義語(yǔ)句Connectionconn=

12、newConnectionUtil().openConnection();Stringsql="createtableCustomerTbl(idintprimarykeyauto_increment,namevarchar(20),emailvarchar(20)”;try(Statementstmt=conn.createStatement();/執(zhí)行SQL語(yǔ)句stmt.execute(sql);catch(SQLExceptione)(/TODOAuto-generatedcatchblocke.printStackTrace();finally(if(conn!=null)t

13、ry(conn.close();catch(SQLExceptione)(conn=null;e.printStackTrace();Main.javapackagecom.michael.main;importcom.michael.jdbc.ConnectionUtil;importcom.michael.jdbc.TestStatement;publicclassMain*paramargs*/publicstaticvoidmain(Stringargs)ConnectionUtilcu=newConnectionUtil();/第一種方法System.out.println(&quo

14、t;第一種方法:"+cu.getConnection();/第二種方法System.out.println("第二種方法:"+cu.getConnection(com.mysql.jdbc.Driver","jdbc:mysql:/localhost:3306/jdbc_db","root","mysqladmin");/第三種方法System.out.println("第三種方法："+cu.openConnection();TestStatement.getStatemen

15、t();TestStatement.createTable();)測(cè)試結(jié)果:i；*iihwcIhdiindbltijiqi*ivu-wmjaIIBFfeNH葺*MILIWIEl*(41IvStOlII1MRLjSJlKk>liMi、.4上單yfjjwjiMji'iritf.f?使用Statement執(zhí)行DML-更新?InsertTestStatement.javapackagecom.michael.jdbc;importjava.sql.Connection;importjava.sql.SQLException;importjava.sql.Statement;publicc

16、lassTestStatementpublicstaticvoidgetStatement()Connectionconn=newConnectionUtil().openConnection();tryStatementstmt=conn.createStatement();System.out.println(stmt);catch(SQLExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();publicstaticvoidcreateTable()/DDL數(shù)據(jù)定義語(yǔ)句Connectionconn=newConnection

17、Util().openConnection();Stringsql="createtableCustomerTbl(idintprimarykeyauto_increment,namevarchar(20),emailvarchar(20)"tryStatementstmt=conn.createStatement();/執(zhí)行SQL語(yǔ)句stmt.execute(sql);catch(SQLExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();finallyif(conn!=null)tryconn.close

18、();catch(SQLExceptione)conn=null;e.printStackTrace();/DML數(shù)據(jù)操作語(yǔ)句-CRUD:create、retrive、update、deletepublicstaticvoidtestInsert()/DDL數(shù)據(jù)定義語(yǔ)句Connectionconn=newConnectionUtil().openConnection();Stringsql="insertintoCustomerTbl(name,email)values('Michael','michael')"tryStatementstmt=conn.createStatement();/執(zhí)行SQL語(yǔ)句stmt.executeUpdate(sql);catch(SQLExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();finallyif(conn!=null)tryconn.close();catch(SQLExceptione)conn=n