JDBC（Java Database Connectivity）是Java中提供的一套数据库编程API，它定义了一套用来访问数据库的标准Java类库（位于java.sql和javax.sql包中）

一、JDBC的作用

通过发送SQL语句，来完成对数据库的操作（CRUD）
JDBC：只是一套JAVA用来连接数据库的标准API（接口）而已，具体的实现还需要不同的厂商自己实现。由于它的标准是统一，所以JDBC可以通过同一套代码连接不同的数据库
mybatis,hibernate,spring-data-jpa 他们的底层，连接数据库都是使用的JDBC

二、JDBC的执行过程

30 - JDBC - 01 - 图1
上述图中：JDBC的驱动程序，是由不同的数据库厂商自己提供
30 - JDBC - 01 - 图2
在JDBC的基本操作中，最常用的类和接口包括DriverManager、Connection、Statement、PreparedStatement、CallableStatement和ResultSet。

三、JDBC连接数据库的6个步骤

步骤一：根据应用程序所用的数据库，选择JDBC驱动程序类型。
步骤二：连接到数据库，得到Connection对象。
步骤三：通过Connection创建Statement对象
步骤四：使用Statement对象提交SQL语句
步骤五：操作结果集
步骤六：回收数据库资源

四、JDBC代码体现

1、从https://mvnrepository.com/ 网上下载 mysql-connector-java.jar 驱动包
30 - JDBC - 01 - 图3
2、在项目中，创建一个目录lib (库) 跟src 平级
3、运行select version();检查数据库的版本
4、选择mysql-connector-java-5.1.47.jar 右键—Build Path — Add Buid Path

package com.woniuxy.java33.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class MainEnter {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        try {
            //使用反射加载数据库的驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //使用url name password 连接MySQL数据库
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/woniuxy?characterEncoding=utf8", "root", "123456");
            //取消自动提交
            conn.setAutoCommit(false);
            //创建Statement编译对象
            Statement state = conn.createStatement();
            //编写需要发送给MySQL的SQL语句
            String sql = "insert into user_info(user_name,age,gender) values ('坡上',18,'男')";
            //调用executeUpdate() 执行SQL语句，并查看数据库受影响的行数
            int result = state.executeUpdate(sql);//通常用来CUD
            System.out.println("rows==" + result);
            //事务提交
            conn.commit();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

五、Dao模式实现对用户CURD

**
     * 
     */
    private static final long serialVersionUID = -5181641898721350834L;
    private Long id;
    private String userName;
    private Integer age;
    private Character gender;
    public UserBean() {
        super();
        // TODO Auto-generated constructor stub
    }
    public UserBean(Long id, String userName, Integer age, Character gender) {
        super();
        this.id = id;
        this.userName = userName;
        this.age = age;
        this.gender = gender;
    }
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public Character getGender() {
        return gender;
    }
    public void setGender(Character gender) {
        this.gender = gender;
    }
    @Override
    public String toString() {
        return "UserBean [id=" + id + ", userName=" + userName + ", age=" + age + ", gender=" + gender + "]";
    }
}

package com.woniuxy.java33.dao;
import com.woniuxy.java33.bean.UserBean;
/**
 * 用户访问数据库的DAO接口
 * DAO == Database Access Object 数据库访问对象
 * @author Administrator
 *
 */
public interface IUserDao {
    /**
     * 新增一个用户
     * @param user
     */
    void saveUserBean(UserBean user);
    /**
     * 修改一个用户
     * @param user
     */
    void updateUserBean(UserBean user);
    /**
     * 删除一个用户
     * @param user
     */
    void deleteUserBean(UserBean user);
}

package com.woniuxy.java33.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.woniuxy.java33.bean.UserBean;
import com.woniuxy.java33.dao.IUserDao;
/**
 * 该类用来访问MySQL数据库，完成对用户进行CRUD
 * 
 * @author Administrator
 *
 */
public class UserDaoImpl implements IUserDao {
    @Override
    public void saveUserBean(UserBean user) {
        // TODO Auto-generated method stub
        System.out.println(user);
        // 使用JDBC连接数据库，并发送SQL语句
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/woniuxy?characterEncoding=utf8&serverTimezone=UTC", "root",
                    "123456");
            conn.setAutoCommit(false);
            Statement state = conn.createStatement();
            String sql = "insert into user_info(user_name,age,gender) values ('" + user.getUserName() + "','"
                    + user.getAge() + "','" + user.getGender() + "')";
            int result = state.executeUpdate(sql);// 通常用来CUD
            System.out.println("rows==" + result);
            conn.commit();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    @Override
    public void updateUserBean(UserBean user) {
        // TODO Auto-generated method stub
    }
    @Override
    public void deleteUserBean(UserBean user) {
        // TODO Auto-generated method stub
    }
}

public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserBean user = new UserBean(null, "松下", 25, '男');
        IUserDao userDao = new UserDaoImpl();
        userDao.saveUserBean(user);
    }

六、使用ConnnectionUtil工具类，简化代码

1、创建一个数据库的连接文件db.properties

driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/woniuxy?characterEncoding=utf8&serverTimezone=UTC
name=root
password=123456

2、创建PropertiesUtil工具类，读取db.properties

package com.woniuxy.java33.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;
/**
 * properties文件 的加载类
 * @author Administrator
 *
 */
public class PropertiesUtil {
    public static String getValue(String key) {
        Properties props = new Properties();
        FileInputStream fis = null;
        try {
            String path = System.getProperty("user.dir") + 
                    File.separatorChar + "src" + 
                    File.separatorChar +"db.properties" ;
            File file = new File(path);
            fis = new FileInputStream(file);
            props.load(fis);
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally {
            try {
                fis.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return props.getProperty(key);
    }
}

3、创建ConnectionUtil ，并编写静态代码

package com.woniuxy.java33.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * 数据库的连接工具类
 * @author Administrator
 *
 */
public class ConnectionUtil {
    private static Connection conn = null;
    static {
        try {
            Class.forName(PropertiesUtil.getValue("driverName"));
            conn = DriverManager.getConnection(
                    PropertiesUtil.getValue("url"), 
                    PropertiesUtil.getValue("name"),
                    PropertiesUtil.getValue("password"));
            conn.setAutoCommit(false);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 该方法用于返回连接对象
     * @return
     */
    public static Connection getConn() {
        return conn;
    }
    /**
     * 该方法用于提交事务
     */
    public static void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public static void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

4、简化DAO的实现类

package com.woniuxy.java33.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.woniuxy.java33.bean.UserBean;
import com.woniuxy.java33.dao.IUserDao;
import com.woniuxy.java33.util.ConnectionUtil;
/**
 * 该类用来访问MySQL数据库，完成对用户进行CRUD
 * 
 * @author Administrator
 *
 */
public class UserDaoImpl implements IUserDao {
    @Override
    public void saveUserBean(UserBean user) {
        // TODO Auto-generated method stub
        try {
            // 使用JDBC连接数据库，并发送SQL语句
            Connection conn = ConnectionUtil.getConn();
            //得到SQL编译对象
            Statement state = conn.createStatement();
            String sql = "insert into user_info(user_name,age,gender) values ('" + user.getUserName() + "','"
                    + user.getAge() + "','" + user.getGender() + "')";
            int result = state.executeUpdate(sql);// 通常用来CUD
            System.out.println("rows==" + result);
            ConnectionUtil.commit();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            ConnectionUtil.rollback();
        } 
    }
    @Override
    public void updateUserBean(UserBean user) {
        // TODO Auto-generated method stub
    }
    @Override
    public void deleteUserBean(UserBean user) {
        // TODO Auto-generated method stub
    }
}

七、实现对数据的修改和查询

将ConnectionUtil代码中static{} 做了调整，添加了close()

package com.woniuxy.java33.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * 数据库的连接工具类
 * @author Administrator
 *
 */
public class ConnectionUtil {
    private static Connection conn = null;
    static {
        try {
            Class.forName(PropertiesUtil.getValue("driverName"));
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 该方法用于返回连接对象
     * @return
     */
    public static Connection getConn() {
        try {
            conn = DriverManager.getConnection(
                    PropertiesUtil.getValue("url"), 
                    PropertiesUtil.getValue("name"),
                    PropertiesUtil.getValue("password"));
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 该方法用于提交事务
     */
    public static void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            close();
        }
    }
    public static void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            close();
        }
    }
    public static void close() {
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

在IUserDao中，添加getOneById()

/**
 * 根据ID查询一条数据
 * @param id
 * @return
 */
UserBean getOneById(Long id);

在UserDaoImpl类，实现getOneById()

@Override
public UserBean getOneById(Long id) {
    // TODO Auto-generated method stub
    UserBean user = null;
    Connection conn = null;
    try {
        conn = ConnectionUtil.getConn();
        Statement state = conn.createStatement();
        //编写SQL语句
        String sql = "select id,user_name,age,gender from user_info where id = '"+id+"'";
        //调用executeQuery() 执行查询
        ResultSet rs = state.executeQuery(sql);//ResultSet 返回结果集
        //操作结果集即可
        if(rs.next()) {
            Long uId = rs.getLong("id");
            String userName = rs.getString("user_name");
            int age = rs.getInt("age");
            String gender = rs.getString("gender");
            user = new UserBean(uId, userName, age, gender == null? null: gender.charAt(0));
        }
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    }finally {
        ConnectionUtil.close();
    }
    return user;
}

在UserDaoImpl类，实现updateUserBean()

@Override
public void updateUserBean(UserBean user) {
    // TODO Auto-generated method stub
    try {
        // 使用JDBC连接数据库，并发送SQL语句
        Connection conn = ConnectionUtil.getConn();
        //得到SQL编译对象
        Statement state = conn.createStatement();
        String sql = "update user_info set user_name = '"+
        user.getUserName()+"',age='"+
                user.getAge()+"',gender='"+
        user.getGender()+"' where id = '"+
                user.getId()+"'";
        int result = state.executeUpdate(sql);// 通常用来CUD
        System.out.println("rows==" + result);
        ConnectionUtil.commit();
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
        ConnectionUtil.rollback();
    } 
}

在main() 中，实现先查询，后修改

public static void main(String[] args) {
    // TODO Auto-generated method stub
    IUserDao userDao = new UserDaoImpl();
    //修改用户之前，先查询数据库
    UserBean user = userDao.getOneById(1L);
    System.out.println(user);
    //修改用户
    user.setUserName("王麻子");
    user.setAge(60);
    user.setGender('男');
    userDao.updateUserBean(user);
}

30 - JDBC - 01