Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(1)-阿里云开发者社区

上季我们建立了与数据库的连接，本季我们将对数据库进行操作使用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;
        }

}

TestStatement.java

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();
        }

}

测试结果：

• 使用Statement 执行DDL

–可以使用Statement来执行一个数据定义语句，例如：创建一张表

TestStatement.java

Main.java

测试结果：

• 使用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();
                                }
                }
        }
}