上季我们建立了与数据库的连接,本季我们将对数据库进行操作使用Statement 执行DDL、使用Statement 执行DML,常用的插入、更新、删除及查询数据哈~
• Statement 简介
–Statement 提供了一个操作数据库语句的功能,可通过它来创建表、插入记录、修改记录、删除记录等操作
• 获得Statement
–可以从数据库连接Connection中获得Statement
Connection conn = new ConnectionUtil().getConnection();    
Statement stmt = conn.createStatement();
ConnectionUtil.java
package 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("DBConfig.properties"));    
                        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;    
        }    

}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_WEB工程师培训
TestStatement.java
package 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 block    
                        e.printStackTrace();    
                }    
        }    
}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_WEB工程师培训_02
Main.java
package 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();    
        }    

}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_EE_03
测试结果:
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_WEB工程师培训_04
• 使用Statement 执行DDL
–可以使用Statement来执行一个数据定义语句,例如:创建一张表
TestStatement.java
package 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 block    
                        e.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 block    
                        e.printStackTrace();    
                }finally{    
                        if(conn!=null)    
                                try {    
                                        conn.close();    
                                } catch (SQLException e) {    
                                        conn = null;    
                                        e.printStackTrace();    
                                }    
                }    
        }    
}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_Statement_05
Main.java
package 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();    
        }    

}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_Statement_06
测试结果:
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_Statement_07
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_Java_08 
• 使用Statement 执行DML
–更新
• Insert
TestStatement.java
package 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 block    
                        e.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 block    
                        e.printStackTrace();    
                }finally{    
                        if(conn!=null)    
                                try {    
                                        conn.close();    
                                } catch (SQLException e) {    
                                        conn = null;    
                                        e.printStackTrace();    
                                }    
                }    
        }    
        //DML数据操作语句--CRUD:create、retrive、update、delete    
        public static void testInsert(){    
                //DDL数据定义语句    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "insert into CustomerTbl(name,email) values('Michael','[email]michael@java.com[/email]')";    
                try {    
                        Statement stmt = conn.createStatement();    
                        //执行SQL语句    
                        stmt.executeUpdate(sql);    
                } catch (SQLException e) {    
                        // TODO Auto-generated catch block    
                        e.printStackTrace();    
                }finally{    
                        if(conn!=null)    
                                try {    
                                        conn.close();    
                                } catch (SQLException e) {    
                                        conn = null;    
                                        e.printStackTrace();    
                                }    
                }    
        }    
}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_EE_09
Main.java
package 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();    
                TestStatement.testInsert();    
        }    

}
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_JDBC_10
测试结果:
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)_JDBC_11 
#################Michael分割线#########################
Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发技术讨论技术圈:[url]http://g.51cto.com/itedu[/url]
#################Michael分割线#########################