Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之03.JDBC Statement(2)

• Update

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 testUpdate(){    
                //DDL数据定义语句    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "update CustomerTbl set name='Redking' where id=1";    
                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();    
                                }    
                }    
        }    
}

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

}

测试结果：

• delete

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 testDelete(){    
                //DDL数据定义语句    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "delete from CustomerTbl";    
                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();    
                                }    
                }    
        }    
}

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();    
                //TestStatement.testUpdate();    
                TestStatement.testDelete();    
        }    

}

测试结果：

增删改三个操作都是使用的executeUpdate()方法

• 使用Statement 执行DML
–查询
TestStatement.java

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.ResultSet;    
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 testQuery(){    
                //DDL数据定义语句    
                Connection conn = new ConnectionUtil().openConnection();    
                String sql = "select * from CustomerTbl";    
                try {    
                        Statement stmt = conn.createStatement();    
                        //执行SQL语句    
                        ResultSet rs = stmt.executeQuery(sql);    
                        while(rs.next()){    
                                //可以通过列索引    
                                int id = rs.getInt(1);    
                                //可以通过列名称    
                                String name = rs.getString("name");    
                                String email = rs.getString(3);    
                                System.out.println(id+":"+name+":"+email);    
                        }    
                } catch (SQLException e) {    
                        // TODO Auto-generated catch block    
                        e.printStackTrace();    
                }finally{    
                        if(conn!=null)    
                                try {    
                                        conn.close();    
                                } catch (SQLException e) {    
                                        conn = null;    
                                        e.printStackTrace();    
                                }    
                }    
        }    
}

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();    
                //TestStatement.testUpdate();    
                //TestStatement.testDelete();    
                TestStatement.testQuery();    
        }    

}

 

测试结果：

先在数据库中添加几个数据，我们只测试下查询效果哈~

#################Michael分割线#########################

Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发技术讨论技术圈：[url]http://g.51cto.com/itedu[/url]

#################Michael分割线#########################