JDBC（一）

1. JDBC介绍

JDBC(Java DataBase Connectivity)，即Java数据库的连接。JDBC是一种用于执行SQL语句(DML,DDL,DQL)的Java API，可以为多种关系数据库（oracle,mysql,sqlserver）提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

Java 通过JDBC访问数据库，数据库厂家有很多，如Mysql、Oracle、SqlServer等，而JDBC提供了操作这些数据库的统一的接口、基准，让各大厂商去实现这组规范。各大数据库厂商只需要提供对应的JDBC的jar包，使java代码通过不同厂商实现的jar包访问对应的数据库。

2. JDBC完成CRUD规范步骤

2.1.规范DAO层的实现

DAO层：涉及到开发中的三层架构与MVC层级结构。DAO层叫数据访问层，全称data access object，属于一种比较底层、比较基础的操作，具体作用于某个表、某个实体的增删改查。

DAO层的创建规范：

这里包括界面层传入数据封装成为的域对象——Domain类对象。

2.2. DAO层具体实现

具体代码如下：

DAO层：

接口：

 public interface IStudentDao {

     public void insert(Student student);

     public void update(Student student);

     public void delete(Integer id);

     public Student queryOne(Integer id);

     public List<Student> queryAll();

 }

实现类Impl：

 package cn.yif.dao.impl;

 import cn.yif.domain.Student;

 import cn.yif.dao.IStudentDao;

 import cn.yif.utils.JDBCUtil;

 import java.sql.*;

 import java.util.ArrayList;

 import java.util.List;

 /**

  * @author Administrator

  * @create 2019-09-13-20:33

  */

 public class StudentDaoImpl implements IStudentDao {

     @Override

     public void insert(Student student) {

         Connection conn = null;

         Statement statement = null;

         try {

             //①加载（注册）驱动，这部分硬编码可以写也可以不写，jdbc4.0版本自带了

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

             //②获取连接

             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");

             conn = JDBCUtil.getInstance().getConnection();

             //③获取语句对象：用来执行SQL语句

             statement = conn.createStatement();

             //④执行SQL语句

             String insertSql = "insert into jdbcdemo01 (name, age) values ('"+student.getName()+"', "+student.getAge()+")";

             statement.execute(insertSql);

         }catch (Exception ex){

             ex.printStackTrace();

         }finally {

             //⑤释放资源

             JDBCUtil.getInstance().close(null, statement, conn);

         }

     }

     @Override

     public void update(Student student) {

         Connection conn = null;

         Statement statement = null;

         try {

             //①加载（注册）驱动

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

             //②获取连接

             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");

             conn = JDBCUtil.getInstance().getConnection();

             //③获取语句对象：用来执行SQL语句

             statement = conn.createStatement();

             //④执行SQL语句

             String updateSql = "update jdbcdemo01 set name = '"+student.getName()+"', age = "+student.getAge()+" where id = "+student.getId()+"";

             statement.execute(updateSql);

         }catch (Exception ex){

             ex.printStackTrace();

         }finally {

             //⑤释放资源

             JDBCUtil.getInstance().close(null, statement, conn);

         }

     }

     @Override

     public void delete(Integer id) {

         Connection conn = null;

         Statement statement = null;

         try {

             //①加载（注册）驱动

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

             //②获取连接

             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");

             conn = JDBCUtil.getInstance().getConnection();

             //③获取语句对象：用来执行SQL语句

             statement = conn.createStatement();

             //④执行SQL语句

             String deleteSql = "delete from jdbcdemo01 where id = "+ id +"";

             statement.execute(deleteSql);

         }catch (Exception ex){

             ex.printStackTrace();

         }finally {

             //⑤释放资源

             JDBCUtil.getInstance().close(null, statement, conn);

         }

     }

     @Override

     public Student queryOne(Integer id) {

         Connection conn = null;

         Statement statement = null;

         ResultSet resultSet = null;

         Student student = null;

         try {

             //①加载（注册）驱动

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

             //②获取连接

             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");

             conn = JDBCUtil.getInstance().getConnection();

             //③获取语句对象：用来执行SQL语句

             statement = conn.createStatement();

             //④执行SQL语句

             String queryOneSql = "select * from jdbcdemo01 where id = "+ id +"";

             resultSet = statement.executeQuery(queryOneSql);

             if(resultSet.next())

             {

                 student = new Student();

                 student.setId(resultSet.getInt("id"));

                 student.setName(resultSet.getString("name"));

                 student.setAge(resultSet.getInt("age"));

             }

         }catch (Exception ex){

         }finally {

             //⑤释放资源

             JDBCUtil.getInstance().close(resultSet, statement, conn);

         }

         return student;

     }

     @Override

     public List<Student> queryAll() {

         Connection conn = null;

         Statement statement = null;

         ResultSet resultSet = null;

         Student student = null;

         List<Student> list = new ArrayList<Student>();

         try {

             //①加载（注册）驱动

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

             //②获取连接

             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");

             conn = JDBCUtil.getInstance().getConnection();

             //③获取语句对象：用来执行SQL语句

             statement = conn.createStatement();

             //④执行SQL语句

             String queryAllSql = "select * from jdbcdemo01";

             resultSet = statement.executeQuery(queryAllSql);

             while (resultSet.next())

             {

                 student = new Student();

                 student.setId(resultSet.getInt("id"));

                 student.setName(resultSet.getString("name"));

                 student.setAge(resultSet.getInt("age"));

                 list.add(student);

             }

         }catch (Exception ex){

             ex.printStackTrace();

         }finally {

             //⑤释放资源

             JDBCUtil.getInstance().close(resultSet, statement, conn);

         }

         return list;

     }

 }

Domain层：

 package cn.yif.domain;

 /**

  * @author Administrator

  * @create 2019-09-13-20:23

  */

 public class Student {

     //ID表示编号

     private Integer id;

     //name表示名字

     private String name;

     //age表示年龄

     private Integer age;

     public Integer getId() {

         return id;

     }

     public void setId(Integer id) {

         this.id = id;

     }

     public String getName() {

         return name;

     }

     public void setName(String name) {

         this.name = name;

     }

     public Integer getAge() {

         return age;

     }

     public void setAge(Integer age) {

         this.age = age;

     }

     public Student() {

     }

     public Student(Integer id, String name, Integer age) {

         this.id = id;

         this.name = name;

         this.age = age;

     }

     @Override

     public String toString() {

         return "Student{" +

                 "id=" + id +

                 ", name='" + name + '\'' +

                 ", age=" + age +

                 '}';

     }

 }

工具类JDBCUtil实现：

 package cn.yif.utils;

 import java.io.IOException;

 import java.sql.*;

 import java.util.Properties;

 /**

  * @author Administrator

  * @create 2019-09-13-21:21

  */

 public class JDBCUtil {

     //使用静态static类构造单例模式

     private JDBCUtil(){ }

     //在静态代码块中创建instance的实例，这里使用static静态变量来保存

     private static JDBCUtil instance = null;

     //每次都要加载驱动，这里定义一个Properties，把资源文件里面的内容读到Properties里面

     private static Properties properties = null;

     //JDBCUtil类加载的时候，就加载注册驱动

     static {

         properties = new Properties();

         try {

             //当前线程类加载器加载获取properties文件

             properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));

             try {

                 Class.forName(properties.getProperty("driverClassName"));

             } catch (ClassNotFoundException e) {

                 e.printStackTrace();

             }

         } catch (IOException e) {

             e.printStackTrace();

         }

         instance = new JDBCUtil();

     }

     public static JDBCUtil getInstance(){

         return instance;

     }

     //抽取获取连接Connection的方法

     public Connection getConnection() throws SQLException {

        return DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));

     }

     //抽取JDBC关闭资源的close方法

     public void close(ResultSet resultSet, Statement statement, Connection conn){

         try {

             if(resultSet != null){

                 resultSet.close();

             }

         } catch (SQLException e) {

             e.printStackTrace();

         }

         try {

             if(statement != null){

                 statement.close();

             }

         } catch (SQLException e) {

             e.printStackTrace();

         }

         try {

             if(conn != null){

                 conn.close();

             }

         } catch (SQLException e) {

             e.printStackTrace();

         }

     }

 }

JDBC资源配置文件：

 driverClassName = com.mysql.jdbc.Driver

 url = jdbc:mysql://localhost:3306/jdbctest01

 username = root

 password = admin

或GitHub：https://github.com/devyf/JavaWorkSpace/tree/master/JDBC_0913_01

巴特西

JDBC（一）

1. JDBC介绍

2. JDBC完成CRUD规范步骤

2.1.规范DAO层的实现

2.2. DAO层具体实现

最新文章

热门文章