JDBC连接 修改 查询数据库

1.JDBC总结（模板）
a. 导入驱动包、加载具体的驱动类 Class.forName(“具体驱动类”);
b. 与数据库建立连接 connection = DriverManager.getConnection(…);
c. 通过connection，获取操作数据库的对象 （Statement\preparedStatement\callablestatement）
         stmt = connection.createStatement();
d. (查询) 处理结果集 rs = pstmt.executeQuery()
         while(rs,next()){     rs.getXxx(…);    }
        }catch(ClassNotFoundException e)
        { . . . . .
        }
        catch( SQLException e)
        { . . … . .
        }catch( Exception e)
        { . . … . .
        }finally
        { . .关闭资源 ， 与打开顺序相反
        rs.close();
if(stmt != null) stmt.close();
connection.close()；
        }

---

–JDBC中，除了Class.forName() 抛出ClassNotFoundException, 其余方法全部抛SQLException .

package NetSql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NetMysql001 {
   private static String driver="com.mysql.jdbc.Driver";//驱动程序类 
   private static String url="jdbc:mysql://localhost:3306/StudentScore?characterEncoding=utf8";
   private static String user="root";
   private static String password="ren372930";    
   public static void main(String[] args) {
       try {
          //a. 导入驱动，加载具体的驱动类
          Class.forName(driver);
          System.out.println("注册驱动成功！");
      } catch (Exception e1) {
         // TODO: handle exception
         System.out.println("注册驱动失败！");
         e1.printStackTrace();
         return;
      }
      String sql = "SELECT sNo,sName,sex,age FROM Student WHERE dept = '计算机'";
       try (  
             //b. 与数据库建立连接
             Connection conn = DriverManager.getConnection(url,user, password);
             //c. 发送sql,执行(增删改  查)
             Statement stmt = conn.createStatement();
             // 执行查询操作  用executeQuery();
             ResultSet rs = stmt.executeQuery(sql);
           ){
             while(rs.next()) {
                String no = rs.getString("sNo");
                String name = rs.getString("sName");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                System.out.println(no+" "+name+" "+sex+" "+age);
             }
          }
          catch (Exception e) {
             e.printStackTrace();
           }
     }
}

package NetSql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class NetMysql002 {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/StudentScore?useSSL=false";
    private static String user = "root";
    private static String password = "ren372930";

    public NetMysql002() {
    }

    public static void main(String[] args) {
        String selectSql = "SELECT * FROM Student WHERE dept='计算机'";
        String insertSql = "INSERT INTO Student(sNo,sName,sex,age,dept) VALUES('201201009','王毅','男','18','外语');";
        String updateSql = "UPDATE Student SET dept='金融'  WHERE sNo='201201009'";
        String deleteSql = "DELETE FROM Student WHERE sNo='201201009'";

        try {
            Throwable var5 = null;
            Object var6 = null;

            try {
                Connection conn = DriverManager.getConnection(url, user, password);

                try {
                    Statement stmt = conn.createStatement();

                    try {
                        ResultSet rs = stmt.executeQuery(selectSql);

                        try {
                            Class.forName(driver);

                            while(rs.next()) {
                                String no = rs.getString("sNo");
                                String name = rs.getString("sName");
                                String sex = rs.getString("sex");
                                int age = rs.getInt("age");
                                String dept = rs.getString("dept");
                                System.out.println(no + "  " + name + "  " + sex + "  " + age + "  " + dept);
                            }

                            int count = stmt.executeUpdate(insertSql);
                            System.out.println("添加" + count + "条记录到Student表中");
                            count = stmt.executeUpdate(updateSql);
                            System.out.println("修改了Student表的" + count + "条记录");
                            count = stmt.executeUpdate(deleteSql);
                            System.out.println("删除了Student表的" + count + "条记录");
                        } finally {
                            if (rs != null) {
                                rs.close();
                            }

                        }
                    } catch (Throwable var36) {
                        if (var5 == null) {
                            var5 = var36;
                        } else if (var5 != var36) {
                            var5.addSuppressed(var36);
                        }

                        if (stmt != null) {
                            stmt.close();
                        }

                        throw var5;
                    }

                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (Throwable var37) {
                    if (var5 == null) {
                        var5 = var37;
                    } else if (var5 != var37) {
                        var5.addSuppressed(var37);
                    }

                    if (conn != null) {
                        conn.close();
                    }

                    throw var5;
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (Throwable var38) {
                if (var5 == null) {
                    var5 = var38;
                } else if (var5 != var38) {
                    var5.addSuppressed(var38);
                }

                throw var5;
            }
        } catch (Exception var39) {
            var39.printStackTrace();
        }

    }
}

package NetSql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class NetMysql003 {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost/StudentScore?useSSL=false";
    private static String user = "root";
    private static String password = "ren372930";

    public NetMysql003() {
    }

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String selectSql = "SELECT * FROM Student WHERE dept=?";
        String insertSql = "INSERT INTO Student(sNo,sName,sex,age,dept)VALUES(?,?,?,?,?);";
        String updateSql = "UPDATE Student SET dept='金融' WHERE sNo=?";
        String deleteSql = "DELETE FROM Student WHERE sNo=?";

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(selectSql);
            ps.setString(1, "计算机");
            rs = ps.executeQuery();

            while(rs.next()) {
                String no = rs.getString("sNo");
                String name = rs.getString("sName");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                String dept = rs.getString("dept");
                System.out.println(no + "  " + name + "  " + sex + "  " + age + "  " + dept);
            }

            ps = conn.prepareStatement(insertSql);
            ps.setString(1, "201201009");
            ps.setString(2, "王毅");
            ps.setString(3, "男");
            ps.setInt(4, 18);
            ps.setString(5, "外语");
            int count = ps.executeUpdate();
            System.out.println("添加" + count + "条记录到Student表中");
            ps = conn.prepareStatement(updateSql);
            ps.setString(1, "201201009");
            count = ps.executeUpdate();
            System.out.println("修改了Student表的" + count + "条记录");
            ps = conn.prepareStatement(deleteSql);
            ps.setString(1, "201201009");
            count = ps.executeUpdate();
            System.out.println("删除了Student表的" + count + "条记录");
        } catch (Exception var21) {
            var21.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (ps != null) {
                    ps.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (Exception var20) {
                var20.printStackTrace();
            }

        }

    }
}