JDBC

jdbc是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。

今天我温习了一下最开始学的java操作数据库,最原始的方法jdbc。不然太久不用就慢慢淡忘了~

jdbc最原始的方法比较繁琐,并不适合项目开发使用,只适合初学者学习java语言怎么实现代码操作数据库,作为项目开发使用,我们应该学习更高级的编程技巧和框架

作为讲解和温习,这篇文章不使用任何框架和多余的架包,只用了我简单的方法封装,尽量减少代码冗余

我的环境

1.编辑器用的idea
2.数据库为了方便起见用的mysql,版本5.5.60
3.jar包只用了一个mysql-connector-java-5.0.8-bin.jar,这里面封装着java访问mysql数据库的api
4.jdk版本1.8

jdbc操作步骤

1.添加jar包到工程中
2.将架包添加到构建路径中
3.加载驱动

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

4.建立数据库连接

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8", "mysql用户名", "mysql密码");

5.生成运行sql语句的对象

这里有两种方式
Statement

//statement每次执行sql语句,相关数据库都要执行sql语句的编译
Statement statement = conn.prepareStatement();

PreparedStatement

//PreparedStatement是预编译的,对于批量处理可以大大提高效率,也叫jdbc存储过程
PreparedStatement preparedStatement = conn.prepareStatement("sql语句");

6.执行sql语句

当使用Statement对象时
增加、删除、修改:

//i为执行sql后返回的执行成功行数
int i = statement.executeUpdate();

查询:

//返回结果集
ResultSet resultSet = statement.executeQuery("sql语句");

当使用PreparedStatement对象时
增加、删除、修改:

//i为执行sql后返回的执行成功行数
int i = preparedStatement.executeUpdate("sql语句");

查询:

//返回结果集
ResultSet resultSet = preparedStatement.executeQuery();

7.关闭数据库连接

/**
 * 关闭资源,从外层往里的顺序关
 *
 * @param resultSet   结果集
 * @param statement 执行者对象
 * @param connection 连接对象
 */
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
    try {
        if (resultSet != null) {
            resultSet.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

实战项目

首先在数据库里创建学生表

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(10) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(2) DEFAULT NULL,
  `saddress` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB CHARSET=utf8;

在java里创建Student实体类(项目结构请参照代码第一行package)

package top.coor.pojo;

/**
 * @program: review-jdbc
 * @description: 学生实体类
 * @author: coortop
 * @create: 2020-02-07 14:20
 **/
public class Student {
    private Integer sId;
    private String sName;
    private Integer sAge;
    private String sSex;
    private String sAddress;

    public Integer getsId() {
        return sId;
    }

    public void setsId(Integer sId) {
        this.sId = sId;
    }

    public String getsName() {
        return sName;
    }

    public void setsName(String sName) {
        this.sName = sName;
    }

    public Integer getsAge() {
        return sAge;
    }

    public void setsAge(Integer sAge) {
        this.sAge = sAge;
    }

    public String getsSex() {
        return sSex;
    }

    public void setsSex(String sSex) {
        this.sSex = sSex;
    }

    public String getsAddress() {
        return sAddress;
    }

    public void setsAddress(String sAddress) {
        this.sAddress = sAddress;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sId=" + sId +
                ", sName='" + sName + '\'' +
                ", sAge=" + sAge +
                ", sSex='" + sSex + '\'' +
                ", sAddress='" + sAddress + '\'' +
                '}';
    }
}

创建db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8
jdbc.username=mysql用户名
jdbc.password=mysql密码

创建连接数据库的工具类

package top.coor.util;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @program: review-jdbc
 * @description: 连接数据库的工具类
 * @author: coortop
 * @create: 2020-02-07 14:28
 **/
public class DBTools {
    static String driver;
    static String url;
    static String username;
    static String password;

    /**
     * 静态块
     */
    static {
        try {
            //使用Properties获取db.properties文件里的数据库连接信息
            Properties pro = new Properties();
            pro.load(DBTools.class.getResourceAsStream("../properties/db.properties"));
            driver = pro.getProperty("jdbc.driver");
            url = pro.getProperty("jdbc.url");
            username = pro.getProperty("jdbc.username");
            password = pro.getProperty("jdbc.password");
            //加载jdbc驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 建立连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭资源,从外层往里的顺序关
     *
     * @param resultSet  结果集
     * @param statement  执行者对象
     * @param connection 连接对象
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

封装sql操作类,不然serviceImpl里存在大量相同代码

package top.coor.util;

import top.coor.pojo.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: review-jdbc
 * @description: 封装sql操作类,不然serviceImpl里存在大量相同代码
 * @author: coortop
 * @create: 2020-02-07 15:14
 **/
public class BaseDaoUtil {
    //连接对象
    Connection conn;
    //执行者对象
    PreparedStatement stat;
    //查询出的结果集
    ResultSet rs;

    /**
     * 更新数据库,包括增加、删除、修改
     *
     * @param sql  字符串,要执行的sql语句
     * @param objs 对象数组,sql中要拼接的各种值
     * @return 是否更新成功
     */
    public boolean update(String sql, Object[] objs) {
        boolean flag = false;
        try {
            conn = DBTools.getConnection();
            stat = conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                stat.setObject(i + 1, objs[i]);
            }
            int i = stat.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBTools.close(rs, stat, conn);
        }
        return flag;
    }

    /**
     * 查询数据
     *
     * @param sql  字符串,要执行的sql语句
     * @param objs 对象数组,sql中要拼接的各种值
     * @return List<Student> 返回学生集合
     */
    public List<Student> select(String sql, Object[] objs) {
        conn = DBTools.getConnection();
        List<Student> list = new ArrayList<Student>();
        try {
            stat = conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                stat.setObject(i + 1, objs[i]);
            }
            rs = stat.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setsId(rs.getInt("sid"));
                student.setsName(rs.getString("sname"));
                student.setsAge(rs.getInt("sage"));
                student.setsSex(rs.getString("ssex"));
                student.setsAddress(rs.getString("saddress"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBTools.close(rs, stat, conn);
        }
        return list;
    }
}

service接口,所有要执行的数据库操作方法

package top.coor.service;

import top.coor.pojo.Student;

import java.util.List;

/**
 * @program: review-jdbc
 * @description: 学生表操作接口
 * @author: coortop
 * @create: 2020-02-07 14:35
 **/
public interface StudentService {
    /**
     * 添加一个学生
     *
     * @param student
     * @return
     */
    boolean addStudent(Student student);

    /**
     * 根据学生id删除指定学生
     *
     * @param sId
     * @return
     */
    boolean deleteStudent(Integer sId);

    /**
     * 根据id修改指定学生信息
     *
     * @param student
     * @return
     */
    boolean updateStudentById(Student student);

    /**
     * 获取全部学生信息
     *
     * @return
     */
    List<Student> getAllStudent();

    /**
     * 根据id查询指定学生信息
     *
     * @param sId
     * @return
     */
    Student getStudentById(Integer sId);
}

service实现类

package top.coor.service.serviceImpl;

import top.coor.pojo.Student;
import top.coor.service.StudentService;
import top.coor.util.BaseDaoUtil;

import java.util.List;

/**
 * @program: review-jdbc
 * @description: 学生表接口实现
 * @author: coortop
 * @create: 2020-02-07 14:37
 **/
public class StudentImpl implements StudentService {
    //全局获取封装的sql操作类
    BaseDaoUtil baseDaoUtil = new BaseDaoUtil();

    @Override
    public boolean addStudent(Student student) {
        //因为id在数据库里设置了AUTO_INCREMENT(自动递增),所有我在这里写的null
        String sql = "insert into student values(null,?,?,?,?)";
        Object[] objects = {student.getsName(), student.getsAge(), student.getsSex(), student.getsAddress()};
        boolean isUpdate = baseDaoUtil.update(sql, objects);
        return isUpdate;
    }

    @Override
    public boolean deleteStudent(Integer sId) {
        String sql = "delete from student where sid = ?";
        Object[] objects = {sId};
        boolean isUpdate = baseDaoUtil.update(sql, objects);
        return isUpdate;
    }

    @Override
    public boolean updateStudentById(Student student) {
        String sql = "update student set sname = ?,sage = ?,ssex = ?,saddress = ? where sid = ?";
        Object[] objects = {student.getsName(), student.getsAge(), student.getsSex(), student.getsAddress(), student.getsId()};
        boolean isUpdate = baseDaoUtil.update(sql, objects);
        return isUpdate;
    }

    @Override
    public List<Student> getAllStudent() {
        String sql = "select * from student";
        //无拼接值就放个空数组
        Object[] objects = {};
        List<Student> list = baseDaoUtil.select(sql, objects);
        return list;
    }

    @Override
    public Student getStudentById(Integer sId) {
        String sql = "select * from student where sid = ?";
        Object[] objects = {sId};
        List<Student> students = baseDaoUtil.select(sql, objects);
        if (students.size() == 1) {
            return students.get(0);
        }
        return null;
    }
}

测试,没有使用Junit架包,所以建立main方式测试

package top.coor.test;

import top.coor.pojo.Student;
import top.coor.service.StudentService;
import top.coor.service.serviceImpl.StudentImpl;

import java.util.List;

/**
 * @program: review-jdbc
 * @description:
 * @author: coortop
 * @create: 2020-02-07 15:40
 **/
public class Demo {
    /**
     * 测试时不要一起执行,请一个一个测
     * @param args
     */
    public static void main(String[] args) {
        StudentService studentService = new StudentImpl();
        //增加
        Student student = new Student();
        student.setsName("小明");
        student.setsAge(12);
        student.setsSex("男");
        student.setsAddress("陕西");
        boolean isSuccess1 = studentService.addStudent(student);
        System.out.println(isSuccess1);
        //删除
        boolean isSuccess2 = studentService.deleteStudent(1);
        System.out.println(isSuccess2);
        //查询
        List<Student> allStudent = studentService.getAllStudent();
        System.out.println(allStudent);
        Student studentById = studentService.getStudentById(1);
        System.out.println(studentById);
        //修改
        Student student2 = new Student();
        student2.setsId(1);
        student2.setsName("小红");
        student2.setsAge(13);
        student2.setsSex("女");
        student2.setsAddress("北京");
        boolean isSuccess3 = studentService.updateStudentById(student2);
        System.out.println(isSuccess3);
    }
}

这样一个完整的jdbc就写完了,不是太多代码,但这是java语言操作数据库的基础,后期学习的所有框架都是对这些步骤的进一步封装,熟练掌握底层原理才能更轻松的完成高级代码的编写~