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语言操作数据库的基础，后期学习的所有框架都是对这些步骤的进一步封装，熟练掌握底层原理才能更轻松的完成高级代码的编写~