原创

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;
}

}

```java
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);
    }
}

博客参考于:https://blog.coor.top/archives/20020714 作者:弥枳

开发者手册
  • 你若安好便是晴天
  • 2020-09-28 10:55:13.489

评论区