JavaEEWEB工程师培训

1、JavaEEWEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBCStatement(l)Statement执行DDL使用插入记录、修改记录、删上季我们建立了与数据库的连接，本季我们将对数据库进行操作使用Statement执行DML常用的插入、更新、删除及查询数据哈?Statement简介-Statement提供了一个操作数据库语句的功能，可通过它来创建表、除记录等操作?获得Statement-可以从数据库连接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加载驱动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加载驱动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加载驱动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();)测试结果:TasksfsfcErtwnr目。口玷尊】(：ZSarvars-Mtin(2)Jv«AppLic«tionb;拳以汶|SiSB|国-r3第一神方法：co旬

10、.mysql.jbc.ConaectlonllQcea第二特方法心口昂加”iuiMEQii。losoeiiteemHtttysqi.jdbc.Connectianecib£3i到町!uunjWrittblShut环Et20:37有术情写"可?使用Statement执行DDL-可以使用Statement来执行一个数据定义语句，例如：创建一张表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数据定义语句Connectionconn=

12、newConnectionUtil().openConnection();Stringsql="createtableCustomerTbl(idintprimarykeyauto_increment,namevarchar(20),emailvarchar(20)”;try(Statementstmt=conn.createStatement();/执行SQL语句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();)测试结果:i；*iihwcIhdiindbltijiqi*ivu-wmjaIIBFfeNH葺*MILIWIEl*(41IvStOlII1MRLjSJlKk>liMi、.4上单yfjjwjiMji'iritf.f?使用Statement执行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数据定义语句Connectionconn=newConnection

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

18、();catch(SQLExceptione)conn=null;e.printStackTrace();/DML数据操作语句-CRUD:create、retrive、update、deletepublicstaticvoidtestInsert()/DDL数据定义语句Connectionconn=newConnectionUtil().openConnection();Stringsql="insertintoCustomerTbl(name,email)values('Michael','michael')"tryStatementstmt=conn.createStatement();/执行SQL语句stmt.executeUpdate(sql);catch(SQLExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();finallyif(conn!=null)tryconn.close();catch(SQLExceptione)conn=n