JavaEEWEB工程师培训-JDBCServletJSP整合开发之03JDBCStatement.doc

Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)上季我们建立了与数据库的连接，本季我们将对数据库进行操作使用Statement 执行DDL、使用Statement 执行DML，常用的插入、更新、删除及查询数据哈 Statement 简介Statement 提供了一个操作数据库语句的功能，可通过它来创建表、插入记录、修改记录、删除记录等操作 获得Statement可以从数据库连接Connection中获得StatementConnection conn = new ConnectionUtil().getConnection();Statement stmt = conn.createStatement();ConnectionUtil.javapackage com.michael.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.util.Properties;public class ConnectionUtil /第一种方法public Connection getConnection()Connection conn = null;try /Class.forName加载驱动Class.forName(com.mysql.jdbc.Driver);/DriverManager获得连接conn = DriverManager.getConnection(jdbc:mysql:/localhost:3306/jdbc_db,root,mysqladmin);return conn; catch (Exception e) e.printStackTrace();return null;/第二种方法public Connection getConnection(String driver,String url,String user,String password)Connection conn = null;try /Class.forName加载驱动Class.forName(driver);/DriverManager获得连接conn = DriverManager.getConnection(url,user,password);return conn; catch (Exception e) e.printStackTrace();return null;/第三种方法public Connection openConnection()String driver = ;String url = ;String user = ;String password = ;Properties prop = new Properties();Connection conn = null;try /加载属性文件prop.load(this.getClass().getClassLoader().getResourceAsStream(DBCperties);driver = prop.getProperty(driver);url = prop.getProperty(url);user = prop.getProperty(user);password = prop.getProperty(password);/Class.forName加载驱动Class.forName(driver);/DriverManager获得连接conn = DriverManager.getConnection(url,user,password);return conn; catch (Exception e) e.printStackTrace();return null; TestStatement.javapackage com.michael.jdbc;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;public class TestStatement public static void getStatement()Connection conn = new ConnectionUtil().openConnection();try Statement stmt = conn.createStatement();System.out.println(stmt); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace(); Main.javapackage com.michael.main;import com.michael.jdbc.ConnectionUtil;import com.michael.jdbc.TestStatement;public class Main /* * param args */public static void main(String args) ConnectionUtil cu = new ConnectionUtil();/第一种方法System.out.println(第一种方法:+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.getStatement(); 测试结果： 使用Statement 执行DDL可以使用Statement来执行一个数据定义语句，例如：创建一张表TestStatement.javapackage com.michael.jdbc;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;public class TestStatement public static void getStatement()Connection conn = new ConnectionUtil().openConnection();try Statement stmt = conn.createStatement();System.out.println(stmt); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();public static void createTable()/DDL数据定义语句Connection conn = new ConnectionUtil().openConnection();String sql = create table CustomerTbl(id int primary key auto_increment,name varchar(20),email varchar(20);try Statement stmt = conn.createStatement();/执行SQL语句stmt.execute(sql); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();finallyif(conn!=null)try conn.close(); catch (SQLException e) conn = null;e.printStackTrace(); Main.javapackage com.michael.main;import com.michael.jdbc.ConnectionUtil;import com.michael.jdbc.TestStatement;public class Main /* * param args */public static void main(String args) ConnectionUtil cu = new ConnectionUtil();/第一种方法System.out.println(第一种方法:+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.getStatement();TestStatement.createTable(); 测试结果： 使用Statement 执行DML更新 Insert TestStatement.javapackage com.michael.jdbc;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;public class TestStatement public static void getStatement()Connection conn = new ConnectionUtil().openConnection();try Statement stmt = conn.createStatement();System.out.println(stmt); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();public static void createTable()/DDL数据定义语句Connection conn = new ConnectionUtil().openConnection();String sql = create table CustomerTbl(id int primary key auto_increment,name varchar(20),email varchar(20);try Statement stmt = conn.createStatement();/执行SQL语句stmt.execute(sql); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();finallyif(conn!=null)try conn.close(); catch (SQLException e) conn = null;e.printStackTrace();/DML数据操作语句-CRUD:create、retrive、update、deletepublic static void testInsert()/DDL数据定义语句Connection conn = new ConnectionUtil().openConnection();String sql = insert into CustomerTbl(name,email) values(Michael,);try Statement stmt = conn.createStatement();/执行SQL语句stmt.executeUpdate(sql); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();finallyif(conn!=null)try conn.close(); catch (SQLException e) conn = null;e.printStackTrace(); Main.javapackage com.michael.main;import com.michael.jdbc.ConnectionUtil;import com.michael.jdbc.TestStatement;public class Main /* * param args */public static void main(String args) ConnectionUtil cu = new ConnectionUtil();/第一种方法System.out.println(第一种方法:+cu