JavaWeb 后端 <八> 之 JDBC基礎(全)

一、JDBC简介

1、JDBC是SUN公司为了简化操作数据推出一套规范。数据库厂商的驱动就是对JDBC的实现。

2、Java Data Base Connectivity(java数据库连接),它主要由接口组成。

  java.sql.*    javax.sql.*   JDK中

  数据库的驱动jar包 (mysql-connector-java-5.0.8-bin.jar )

二、JDBC的编码步骤

0、前提:拷贝数据库的驱动到构建路径中(classpath)

1、注册驱动

2、获取与数据库的链接

3、创建代表SQL语句的对象

4、执行SQL语句

5、如果是查询语句,需要遍历结果集

6、释放占用的资源


public class JdbcDemo1 {
 
  public static void main(String[] args) throws SQLException {
    // 1、注册驱动
    DriverManager.registerDriver(new com.mysql.jdbc.Driver());
    // 2、获取与数据库的链接
    Connection conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/day15", "root", ""); //没有密码则“” 空
    // System.out.println(conn.getClass().getName()); 要想知道具体类型,就这么办
    // 3、创建代表SQL语句的对象
    Statement stmt = conn.createStatement();
    // 4、执行SQL语句
    ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");
    // 5、如果是查询语句,需要遍历结果集
    while (rs.next()) {
        System.out.println("---------------------");
        System.out.println(rs.getObject("id"));
        System.out.println(rs.getObject("name"));
        System.out.println(rs.getObject("password"));
        System.out.println(rs.getObject("email"));
        System.out.println(rs.getObject("birthday"));
    }
    // 6、释放占用的资源
    rs.close();
    stmt.close();
    conn.close();
  }
}

三、JDBC中常用的接口详解

1、DriverManager:

  作用:

    a、注册驱动:

    方式一:(不建议使用)

    DriverManager.registerDriver(new com.mysql.jdbc.Driver());

    原因:1、依赖具体驱动。2、导致驱动注册2遍

    方式二:(建议)

    Class.forName("com.mysql.jdbc.Driver");

 

    b、获取与数据库的链接

      url:SUN和数据库厂商间的协议。具体查阅数据库的文档。

      public static Connection getConnection(String url,String user,String password)

                                throws SQLException

      public static Connection getConnection(String url,Properties info)

                                throws SQLException

      public static Connection getConnection(String url)

                                throws SQLException

2、Connection

所有的数据库操作都是基于链接之上的。

Statement createStatement():创建向数据库发送sql的statement对象。


//2、获取与数据库的链接
//方式一:
//Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");
         
//方式二:
//Properties props = new Properties();
//props.setProperty("user", "root");//  参数名:参考数据库的文档
//props.setProperty("password", "sorry");
//props.setProperty("useUnicode", "true");//编码有关的参数
//props.setProperty("characterEncoding", "utf8");
//Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15",props);
         
//方式三
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15?user=root&password=sorry");
        

 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15?user=root&password=");

3、Statement

Statement stmt = conn.createStatement();

作用:代表SQL语句对象。可以向数据库发送任何的SQL语句

ResultSet executeQuery(String sql):sql一般都是查询语句

int executeUpdate(String sql):sql一般是DML语句。insert update delete。返回值,操作几条记录。

boolean execute(String sql):sql可以是任意的语句。返回值不是代表成功与否。如果是查询语句,就有结果集,返回true。没有返回结果集的,返回false。

4、ResultSet

作用:封装了查询的结果集

JavaWeb 后端 <八> 之 JDBC基礎(全)


ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");
List<User> users = new ArrayList<User>();
while(rs.next()){
  User user = new User();
  user.setId(rs.getInt("id"));
  user.setName(rs.getString("name"));
  user.setPassword(rs.getString("password"));
  user.setEmail(rs.getString("email"));
  user.setBirthday(rs.getDate("birthday"));
  users.add(user);
}

boolean next():游标下移。返回值是有无记录

boolean previous():游标上移。

boolean absolute(int count):定位到指定的行。第一行是1。

void beforeFirst():移动游标到第一行的前面。

void afterLast():移动游标到最后一行的后面。

四、释放占用的资源


//释放资源
//JDBC编码的代码模板
public class JdbcDemo5 {
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/day15", "root", "sorry");
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select id,name,password,email,birthday from users");
        List<User> users = new ArrayList<User>();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setPassword(rs.getString("password"));
            user.setEmail(rs.getString("email"));
            user.setBirthday(rs.getDate("birthday"));
            users.add(user);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } <br>      finally {
        if (rs != null) {
          try {
            rs.close();
          } catch (SQLException e) {
            e.printStackTrace();
           }
           rs = null;
        }
        if (stmt != null) {
          try {
            stmt.close();
          } catch (SQLException e) {
            e.printStackTrace();
          }
          stmt = null;
        }
        if (conn != null) {
          try {
            conn.close();
          } catch (SQLException e) {
            e.printStackTrace();
          }
          conn = null;
        }
    }
 
  }
 
}

五、JDBC进行CRUD(增刪改查)操作

工具類:


<strong><span style="color: #ff0000;">getConnection() return conn; 獲取連接<br><br>配置文件放在 dbcgf.properties 中<br><img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAbIAAAB6CAIAAABV3UEmAAATOUlEQVR4nO2d72scxxnH9+/Rq5Y41+iSOn2pV37ltyqIwtEUmjjUaSThJo1IgpIWfGohheZUAsWuCWpDDT5KcXyUvohfCJQU9U0ujRUIiSsSx8i1LVlB0xf7a55nZp6Z29u93ZO+Xx6MvDM7P3ZmP/fMzO5OpCAIgiBNUd0FgCAIapaARQiCIKIEi5998UC3/vVNGAwGO5lGsKiUio/WRmkoQHEb/euzuzAYrArLsRjfb7Xe79AIAhlhsIosxyKYOHUCGWGwKizS77Ea73CogIBFGKwKi7IbrN47HCqm/vXNj3buwmCwEg1YnG4BizBY6ebB4tU3v2O1Cd74kCRgEQYr3fxYPHj0LbPtq8+AjA1R//rmRzvfwGCwEs2Dxb+sfvfhweH+wbf7B4fpH99+/P6Ff4eTcXA+muvtKKXUTm8uOj8IvN93enNRFIXHr1s7vbm0noX1TCrvwUzAIgxWunmw+O7rj93fP1y79MErv7v+01f//MOf//HB/uHOzYsfv39h++qP3339Mf+9XgiLIxG0ESoJi94/mIBFGKx082Dx0qun9u4f3ntwuPfg8N6Dw3v3D+89yO3Sa6f893ohLOYnNVol01tnH/MQgUUYbGLmweI7K4/fvX+4dumDX7/zz1d/P7jwm7/97I2rP1nZ+NHyn+7+7/Cdlcf99zqwOIqs+HMxUSnVv7754a1vYDBYiebB4tsvt77ee/T13qM7e4++Nuztl7/nuFsH5yNNBIt5kAaU/OBcb0c7OY6SHZjr9UxeupLVjien6KXKMg8tlZHm3JxeQUJI88R0rjRgvtScWxQiA4swWOnmweJbF57YvXtg9RZ37x68deEJ20mD8xElhIbFPCSPpUXRkkiP6Knt9OaMqM5k6XGWiysaKRVh2vmBmabGwvxP24mjuJXAIgxWr3mw2F2e/eLOwZeGxQe7y7OWc9gA2DmITv9rGzDnx5ypGenw/9Lj/ERHNL1UVHO9HXde2p/2E+Oj/okBDKJhsNrNg8U3X3zy86/s3uLnXx386sUnLedMDRbj/49QqlAsOukXO5vO4GJLLrX3IRjsmJkHi6+98NRnu/sue+2Fp2wnuYa9O705FpAPorNBbK+3o0IG0fkDMa5kGcKMQfQIpVKD89Y0XYNo48SdXm9gnGKo2AM6H966A4PBSjQPFl95/vu3/ru/dumDN/7wj5d++/fnV//aeenKrdsPP7398NPb+688/337/a0vOfSot3jetraRr0ckB4nLlYdqqVEs2pI1AeRecvGUii3jKB6FLbnYllesazpMxR7n3rp1BwaDlWgeLF44d/qTLx9+8uXD/9B/4z9+8dxp1x1elfyD6FE1fU+O6+pf39z69A4MBivRPFhcfvbp5eeeXn7u9NKzp+M/0r9PLz/79PKzE8DiTm/OsZqcRwAWYTBYaTYNHxbTxqS21Qpgsf5uBIMdJ5sGLEJuAYswWOkGLE63gEUYrHQDFqdbwCIMVroBi9Ot2jcah8GOnwGLEARBRMAiBEEQEcfifoDGyS87/fbeEQwGgzXQgEUYDAYjBizCYDAYMWARBoPBiDUKi9fmo+jU6nCUClybj6Ioml3Zqv9SauVJNL9Re3lOqJ2L1Exq54q2wnsLaiZSMwuejM6skvjv1V13+ZqsNeVOabRVicXdG93FxcXFxSvbxw6LGx2Nfp3LjgjAYs22cUywqFPeWyQ5EWAxxCrC4vaVxcXF7o3tG92KsViDXV5gnuC1+YVrPBqw2AQbD4shNjEsZrncBuCqtyqwuHujm6Bw9/hhMZB3wGIT7JhiMT4y01KbtV/hY2rVzi0GYJFMxkU6FrcunopmV7aObq7OpoH6YNk5ixfH50jinNJPp2NwKd/hSssxat6Ts9MTFyYf4/RtpfKH+mxLndEGYuzutU/GbakzkVrbSkLPbRxtrqqZwBtSPDf+mwPLoNhaKy+V6RyxoSUDhzVBv23Q4aotWVe+GRbzYpujXbEVXGZiMc4ruSbppU6usHG5XDOtga2gn05awZfv9FqtWIwZkY9AqbeYESSJMFxpRVHr4k3ej00AXZsnyR7dTka+Gc5IhJurs4QyUr6WlO1mx+K1ea38lsF4pFdwuLKgV1YO9VjccclALLtjN0gQibmlzkTqTEutbR2ttdRMS51ZCGaN71zT34lpkpGCRVhb0CLHcNGgY4Ijq1pxbzGuwio/4so3mYvMctzg11xqBdHM2hGiZahNU4svOP/psl2Ncwa7mc+rR4gzzcEXmO8UWp1YvLzAMGfDosYgzq+ksS0AurxgcS2zlCkijxLwZRkJ+RpBTgsZRG9dPGXU1zmHIIXqXiTRvHbbWKixZ+/K+Y2hUSB3T7bUmWAsCucSf8cspFjm9xZ4mSeDRTnfuEbc283iizWSzVK7DQOLGt04v9xXg7eCb1pgraVlFJjvFFqNWDQ9LwsWC87iUYhQnjp8yQzQQr7lYpHPpSbjegf75FDJpM5qu1fz+AbFMlYGYlE6l2YdZ6rfgedcQ2ObjzMZLMr5mhDRj4yDjBAs+qtpvRq2VsgKafclKegrnbqty2rGIr3Jy8MiGflSZ9CYzUyV+o9VYlGbr0xEr0Alc4vSUoC1Z29MBIvUmSJuyJ4WwTFZZi5BTAaLQr7mddZZP86CTMgguiAWxVbgDwaxKVFgcdqwGB/PR758Ck9Am5Qvm+AbrVTGWpB75T2d35TonId6BtEN9Rb3OH+dnlQ6h5WdOC1YrM5bJHmNh0Xrr2CWr/SMJLBYARaZE5dQozQsptC5uTrLQGbMabLGlvK1L3MHlcpcxRYfSCo+1WgWRqKD6ablA6VALKbYMpcpvefGN545Z2fUlxCKldlcyvBX3FpmMVNvviYWCVZCGO0oFccim9QbE4tp+purtllmoV2AxQqwSKb8bq7ORq3OfKtELNrS1FJ2OoyefGO60fWcsMe5yUJQ6vHlZdvoSOtLcqjPzNUAeSXaHJ0JaMtWYM1b13tu9myHZSDvnsvX/5ulMBIW7WXmPYFjkefbUudaTiyaqxlSK4ilIliMmdji6/LjYNGsC8nL5TACiyNhMX3rj+nKtvHcYvyQSpRAargSjkXy+p1jxi1BjxUf8vOSHhznxdYnJf2lYg9L0vrymUdebDnUa/rDZXySa8MRVL23mJ1rTrqxAjMHKiNIDAgCDuPZQ0sKhbxFku/C0e29o7WWAayIFGyEVnCXis3xWZdNnHga+2o4nw8FFot5i6b28QUdmG6yPxJs9rnFMspWfrKwxhuwCKvTynrSrRIsVv/uIKyZBizC6jPjPZDCVhYW11rO12xgJ8eARVgNxt+TG9tK8xb1+UEw8aQasAiDwWDEasMiBEFQMwUsQhAEEQGLEARBRMAiBEEQ0fRisU92meoXTGXQUTORmulIcZYidbZL4g8K5tZQLWnvMCwVvZKx1ttqpq1uhYWWmO9IEvI9lu1bouT21e+UqVZVWKTv/+VvRZfvLfY7wGI56o+Np6E6K6TgCh0/32Ky5Vt1+8pYcWqozupv4LEU5FClFP0lWB86CiZGyDISWqpkLKb1MnPUq+MpcyFV9050hkLyvYimYTFEwGKgBh3pnneGAosjasnBPlfokvbDH1eQQKTv9wzyc8XCl4jFuBaDrhOLlbqlExlEJ9uj7ipgsckaG09yZ3WGAosj6lZX8o9YqFmjJQrBcMR4Y5ZFq/V20i63jjMWNX9RP90AGj0w7Lajdneoht12OoXY7ppdoQAW+9wDNy/xkiNC1snyQYf5M0sHNQMj3PJrP1RnI7U+TPJd6icdIo5m7xzGXS2Pg1w1EhL0lJkqLqRZWX+oO1/P5KNwnWmQvVIiFrOLKfcNM2VrKyStabOBmbiPmyNdagbBrPMPApIKyVTqV45WCOzPzpjHBIsObzEAi/p6yrDbtoFxTG9xqM5a29Lxc5rMRWZN1eddIW5I/cgS42af3zN5MdpqfZg4FGc7pKOYd0t8+w20QuoR1jtaZLFGrGACQeQZnPW2NBCTQq350gtrXlX5Oi9plyJussA5TWv75nF8pZJaIT4ie4sB19nSY4VQ23/XNXdSbjVechbT16+EVvD251jHGIvJ6ks8uTgyFrVwOxctWNT8SyoLPY1OZk6gmFjkPloWX+6yWoL2WfNOnsX6kMxw8ykhufdTyTXKNY632Bdv5oBQlq+JDzIYDLvOSo7sxqJ+UL9W45fKO4gWrnPul8mLKm3+i5hUJx2R6M0Rw444s1ZK2lowtF9pJWH3kbM/pxKwqLuo5a63qAlgkUBxdCz63cCyvUU+6PDNLepH5EkfbzF0Ag6UpU8zP0UvxpJraOyrUa4x5vhGei7Hn6/tJtGv7ajXOby+0kycr1RKbIVYpcwtxo6Vq/okNOtCGRMVYRwvsGP5xVrs0H5lDfX1Z/243Cfj+pY7Nz2JTQuS8XOafhbaWCyyppWxqLdo8Ql7LxZp17SOfVxzi3KNchXGYrHncoR8radoN7P3OptzeWVhUShVLHmGtxQsKhuS7KFxmbsaEynKzXQs19bRgt5+JbeCtz+rMCyaY/nxVSUWt6+whxbVhLA41iB6VCxOyFtU2h04JL3cmhSbx6kUizKk/D8VZXuL5r00MW+RVSFkcqCYQrGY+oN6CfU6mjwyHTdXC8r9yt8KAf05CIth61QjqTIs2pioKBY59+L3Vur2FllHYdPqnscdwsjimlv0YDHNy/v4GKuUXKNcRecWCz6XI+Zr3q6k1mJRzcn7crDoLRWTDaPjzC3KKbtCrTOAWS3sEAybMZT7VUgrePtzuLdY7gpMNVhkE4qayOk60fi6c21Y5F5JWy21nVg0H461zNkHr0R7sZiNSixDOaODWgdNWQplrUSP9AydXcEr0Xoc4TqTRkm9tlKw6CmV2AquiphF8l6x0R7npmNMXkE2AjWuidCCIXeK3ArO/kwjSFiMUy7VVVQVYTHxFG1b/7HTteFup89AKGOxb91jz/ZgoyzbT032lEbcXda1xg550YpNqQyMCIW9xSx3M02WqWXRUCsw+d02nuK0puC6Fcd8LkfKt++5jMJ1Zu+6kRYU8/U+/CyXSm4Fb7GV4zqzU1xwcf7g0QsyEENZmeX2le4UuRViOfqz6zHPJFrfX98xNb2fiihJFXjgFaqk2eXSHvsa57kcqPmqugUrWC0pRScei3W9eVZIxZd0qEp8Sav4czlKqV/+AH/zv5ukshaIXCqrP5euk4jF9bbz5ZBGy3ijprAa8QGomAX4t6lMrFzl9efSdRKxSOYHp4GJ/I20sVU/FpvglzXt7xOj0vtz6TqRWIQgCHILWIQgCCICFiEIgoiARQiCICJgEYIgiAhYhCAIIqr0nehUjg+LnUxV/YgsBEFjagLeorZlAbAILEJQ4zWJQfTujW7GRWARWISghmtiWLTsE+35DK1S9IOyjt2trKGBuwZalX4UU/+Mh+VzMpHzSX1raPjebxAE1avqsaj7iqNhsd8hNBt2OzrZSGS++1XgroFWZa8G6h8fpR9Ade795tsZjqcGQVDzVB0WtY8ujrGXiwtlxrmWbzU6dg30bWlgfOxI/86HvPebZ2c4RxwIghqliW2IaplbDPIW7WS0fJK739GiBn7Z2yphSyZ5N4+wvT6ARQhquCbz3OL2lXQLg1GwqNxzi/Zvc7NBdUVYdO79Fr4zHLAIQQ1Ww7GYKsVjGujbwEXCYtAgGt4iBJ1YTQSL2qqLfrpn5z8mOtVIhsyOyOV7i76930J2hgMWIajhqvVxbnnnv37HsWaixRcZWgUW/Xu/ifvVuQ5CENQcVYJF97t/o+z8x4e78nOLNEZ1WFS+Hel8+9WpgK0BIQiqUfhUBARBEBGwCEEQRAQsQhAEEQGLEARBRMAiBEEQEbAIQRBEBCxCEAQRAYsQBEFEwCIEQRARsFipPK9uQxDUQFWOxfRrtJZNC06AgEUImj5VjMX8E93AIgRB06FKsZh8ZjH/2iKwCEFQ41UhFjMaurCYMkP7EA776A39hI7xRRx5X0BrqHODGAow8m0e43vh8p6C7u/6QBA0DaoMixoLJSzq3GE7/al+h/yHESpgX0BbqMN/075ERk+N8cc+WaZ/y5twln8FEt4iBE2fKsKiTkIPFoVPzRKxQHmLU3eo/iVGDVrZn5Yzza1XXYU2KKgf8G2WAEFQM1QJFgkUPVgkTBC3aGHMEfYFFEPzZHQsp0dtPCXHpA/c2isEbxGCpksVYHH3RleH4ihYZMwx/SuKmAJzi1oe/U7U7nTaUaevHbRSz/D5BCyajiawCEHTpfKxyHYsoLoS7i0ao1U3Yvi+gHJov5PukNDuDofddrpdgmsMPZq36MYiBtEQNB2axFsuwd6ijhxzCyvR8xphqjFGYL/bzjjY6SeoVFbskYy9W60ae9HAW4Sg6VKDsMgWj8l/U0+LBAuLNWIonXc0kratRLOBfcimrcNuOx6kA4sQNF2qHYvOiUManox39SjyvoBCKHPikpjcMXWMcX17CuanplOVwCIETZfq/FSEuO4MQRBUj4BFCIIgImARgiCICFiEIAgiwmdoIQiCiIBFCIIgImARgiCICFiEIAgi+j9aHtw6gUgHSgAAAABJRU5ErkJggg==" alt=""><br>release () 施放資源<br></span></strong>

//工具类
public class JdbcUtil {
     
private static String driverClass;
private static String url;
private static String user;
private static String password;
     
static{
  try {
    ClassLoader cl = JdbcUtil.class.getClassLoader();
    InputStream in = cl.getResourceAsStream("dbcfg.properties");
    Properties props = new Properties();
    props.load(in);
    driverClass = props.getProperty("driverClass");
    url = props.getProperty("url");
    user = props.getProperty("user");
    password = props.getProperty("password");
             
    Class.forName(driverClass);
    } catch (Exception e) {
        throw new ExceptionInInitializerError(e);
        }
    }
     
    public static Connection getConnection() throws Exception{
      Connection conn = DriverManager.getConnection(url,user, password);
      return conn;
    }
    public static void release(ResultSet rs,Statement stmt,Connection conn){
      if(rs!=null){
        try {
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
          rs = null;
        }
        if(stmt!=null){
        try {
              stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
          stmt = null;
        }
        if(conn!=null){
        try {
          conn.close();
        ] catch (SQLException e) {
            e.printStackTrace();
        }
          conn = null;
        }
    }
}

 測試:


package com.yif.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
 
import org.junit.Test;
import com.yif.util.JdbcUtil;
 
public class JdbcDemo6 {
    @Test
      public void testAdd(){
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      try{
        conn = JdbcUtil.getConnection();
        stmt = conn.createStatement();
        stmt.executeUpdate("insert into users (name,password,email,birthday) values ('范青霞','123','fqx@itcast.cn','2000-10-01')");
      }catch(Exception e){
        throw new RuntimeException(e);
      }finally{
        JdbcUtil.release(rs, stmt, conn);
      }
    }
    @Test
    public void testUpdate(){
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      try{
        conn = JdbcUtil.getConnection();
        stmt = conn.createStatement();
        stmt.executeUpdate("update users set password=111 where id=4");
      }catch(Exception e){
        throw new RuntimeException(e);
      }finally{
        JdbcUtil.release(rs, stmt, conn);
      }
    }
    @Test
    public void testDelete(){
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      try{
        conn = JdbcUtil.getConnection();
        stmt = conn.createStatement();
        stmt.executeUpdate("delete from users where id=1");
      }catch(Exception e){
        throw new RuntimeException(e);
      }finally{
        JdbcUtil.release(rs, stmt, conn);
      }
    }
}

sql 注入 :

1、用户的表单输入域:防止一些特殊字符。

2、对密码加密:MD5加密

 

PreparedStatement接口

1、支持SQL的预编译。得到该对象时,就必须给他SQL语句。

2、支持参数占位符:  ?  一个问号代表着一个参数。

 

注:能用PreparedStatement就不要用Statement。

上一篇:Struts框架之 执行流程 struts.xml 配置详细


下一篇:java Gui编程 事件监听机制