JDBC总结

JDBC                  Java Database Connectivity

JDBC编程步骤：

1，Load the Driver

    Class.forName();

2,Connect to the DataBase

    Driver Manager.getConnection();

3,Execute the SQL

    1,Connection.CreateStatement();

    2,Statement.executeQuery();

    3,Statement.executeUpdate();

4,Retrieve the result data

    循环取得结果 while(rs.next())

5,Show the result data

    将数据库中的各种类型转换为java中的类型(getXXX)方法

6,Close()

    close the result

    close the statement

    close the connection

/****************************JDBC实例********************************/

 

package cn.wyd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJdbc ...{

    public static void main(String[] args) ...{
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try ...{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager
                          .getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from Student");
            while (rs.next()) ...{
                String str = rs.getString("name");
                System.out.println(str);
            }
        } catch (ClassNotFoundException e) ...{
            System.out.println("驱动类没有找到");
        } catch (SQLException e) ...{
            e.printStackTrace();
        } finally ...{
            try ...{
                if (rs != null) ...{
                    rs.close();
                }
                if (stmt != null) ...{
                    stmt.close();
                }
                if (conn != null) ...{
                    conn.close();
                }
            } catch (SQLException e) ...{
                e.printStackTrace();
            }
        }
    }
}

 

DML语句的书写

try ...{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager
                        .getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
            stmt = conn.createStatement();
            String sql = "insert into Student values(3,'stu01') ";
            stmt.executeUpdate(sql);
            
        }

批处理语句的书写

try ...{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager
        .getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
            String sql = "insert into Student values(?,?)";
            ps=conn.prepareStatement(sql);
            ps.setInt(1, 5);
            ps.setString(2, "stu03");
            ps.addBatch();
            
            ps.setInt(1, 6);
            ps.setString(2, "stu04");
            ps.addBatch();
            
            ps.setInt(1, 7);
            ps.setString(2, "stu05");
            ps.addBatch();
            
            ps.executeBatch();
        } 

PreparedStatement语句的书写

try ...{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
            String sql = "insert into Student values(?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setString(2, name);
            pstmt.executeUpdate();    
        } 

public final class JdbcUtils ...{
    private static String url = "jdbc:mysql://localhost:3306/test";
    private static String name = "root";
    private static String password = "root";

    private JdbcUtils() ...{
    }

    static ...{
        try ...{
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) ...{
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() throws SQLException ...{
        return DriverManager.getConnection(url, name, password);
    }

    public static void release(Connection conn, Statement st, ResultSet rs)
            throws SQLException ...{
        try ...{
            if (rs != null)
                rs.close();
        } finally ...{
            try ...{
                if (st != null)
                    st.close();
            } finally ...{
                if (conn != null)
                    conn.close();
            }
        }
    }
}
public class CRUD ...{

    /**//*
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static void main(String[] args) throws SQLException,
            ClassNotFoundException ...{
         create();
         read();
         update();
         delete();
    }

    static void create() throws SQLException ...{
        Connection conn = null;
        Statement st = null;
        try ...{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String n = "name" + System.currentTimeMillis() % 1000;
            String sql = "insert into user(name, age, regist_date) values ('"
                    + n + "', 23, now())";
            int i = st.executeUpdate(sql);
            System.out.println(i);
        } finally ...{
            JdbcUtils.release(conn, st, null);
        }
    }

    static void read() throws SQLException ...{
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try ...{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select id, name, age, regist_date from user";
            rs = st.executeQuery(sql);
            while (rs.next()) ...{
                System.out.print(rs.getInt("id") + "   ");
                System.out.print(rs.getString("name") + "   ");
                System.out.print(rs.getInt("age") + "   ");
                System.out.print(rs.getTimestamp("regist_date") + "   ");
                System.out.println();
            }
        } finally ...{
            JdbcUtils.release(conn, st, rs);
        }
    }

    static void update() throws SQLException ...{
        Connection conn = null;
        Statement st = null;
        try ...{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String n = "name" + System.currentTimeMillis() % 1000;
            String sql = "update user set name='" + n + "' where id=1";
            int i = st.executeUpdate(sql);
            System.out.println(i);
        } finally ...{
            JdbcUtils.release(conn, st, null);
        }
    }

    static void delete() throws SQLException ...{
        Connection conn = null;
        Statement st = null;
        try ...{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "delete from user where id=4";
            int i = st.executeUpdate(sql);
            System.out.println(i);
        } finally ...{
            JdbcUtils.release(conn, st, null);
        }
    }
}