一、概念
java和数据库的连接，使用java语言操作数据库
二、常用类及接口
1. DriverManager
DriverManager : 驱动管理器：获取和数据的连接
public class DriverManager {
//获取数据库的连接
public static Connection getConnection(String url,
String user, String password) throws SQLException {
java.util.Properties info = new java.util.Properties();
if (user != null) {
info.put("user", user);
}
if (password != null) {
info.put("password", password);
}
return (getConnection(url, info, Reflection.getCallerClass()));
}
}1.1 如果数据路版本为5.x.x.x
（1）添加的数据库的驱动（jar包）的版本要为5.x.x.x
（2）数据库驱动：com.mysql.jdbc.Driver
（3）url: jdbc:msyql://ip:port/数据库名称
1.2 如果数据路版本为8.x.x.x
（1）添加的数据库的驱动（jar包）的版本要为8.x.x.x
（2） 数据库驱动：com.mysql.cj.jdbc.Driver
（3）url: jdbc:msyql://ip:port/数据库名称
2.Connection
Connection：java和数据库连接对象（接口）
public interface Connection extends Wrapper, AutoCloseable {
//获取sql发射器 语句块
Statement createStatement() throws SQLException;
//获取sql发射器 语句块
PreparedStatement prepareStatement(String sql)
throws SQLException;
//设置是否自动提交
void setAutoCommit(boolean autoCommit) throws SQLException;
//提交
void commit() throws SQLException;
//回滚
void rollback() throws SQLException;
//关闭
void close() throws SQLException;
}3. Statement
Statement:语句块，把sql发送数据库中执行
public interface Statement extends Wrapper, AutoCloseable {
//发送执行查询的sql
ResultSet executeQuery(String sql) throws SQLException;
//发送执行增删改的sql
int executeUpdate(String sql) throws SQLException;
//关闭
void close() throws SQLException;
}4.PreparedStatement
PreparedStatement:语句块，把sql发送数据库中执行 防止sql注入
public interface PreparedStatement extends Statement {
//发送执行查询的sql
ResultSet executeQuery() throws SQLException;
//发送执行增删改的sql
int executeUpdate() throws SQLException;
//为占位符赋值
void setInt(int parameterIndex, int x) throws SQLException;
void setDouble(int parameterIndex, double x) throws SQLException;
void setString(int parameterIndex, String x) throws SQLException;
//继承父接口，关闭方法
}5.ResultSet
ResultSet: 结果集，用来封装查询到数据
public interface ResultSet extends Wrapper, AutoCloseable {
//判断下面还有没有数据
boolean next() throws SQLException;
//获取一条数据中，一列的值
String getString(int columnIndex) throws SQLException;
int getInt(int columnIndex) throws SQLException;
double getDouble(int columnIndex) throws SQLException;
String getString(String columnLabel) throws SQLException;
int getInt(String columnLabel) throws SQLException;
double getDouble(String columnLabel) throws SQLException;
//关闭
void close() throws SQLException;
}三、开发流程
（1）创建java项目
（2）创建文件夹lib，和src在同一级目录下
（3）把mysql数据库驱动（jar包）拷贝到lib文件中
（4） jar包就是一个java项目，里面是写好的java代码，被编译成了class文件，直接使用里面的类和方法
（5）每个数据库都是自己的数据库驱动包，里面就是mysql数据库厂商对于jdbc接口的实现
（6）驱动：告诉java如何操作数据库（接口的实现类）
四、完整代码
4.1 整体结构
以student表为例：
（1）创建数据库连接的工具类DbUtil
（2）创建Student实体类
（3）创建StudentVo类，用于映射student表和其他表的关系，这里以class表为例，学生表的class_id就是班级表的id，查询出学生表的所有列以及班级名称
（4）创建StudentDao类，用于构造增删改查的方法
（5）创建测试类StudentTest，进行增删改查的测试
4.2 创建数据库连接的工具类DbUtil
package com.qfedu.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DbUtil {
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
static {
//读取配置文件
//1. 使用反射读取资源转化为输入流
InputStream is = DbUtil.class.getResourceAsStream("/db.properties");
Properties p = new Properties();
try {
p.load(is);
DRIVER = p.getProperty("jdbc.driver");
URL = p.getProperty("jdbc.url");
USER = p.getProperty("jdbc.user");
PASSWORD = p.getProperty("jdbc.password");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//加载驱动
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
return conn;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//获取语句块的方法
public static Statement createStatement(Connection conn) {
try {
Statement stm = conn.createStatement();
return stm;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//关闭资源
public static void close(Connection conn,Statement stm,ResultSet rs) {
try {
if(rs!=null) {
rs.close();
}
if(stm!=null) {
stm.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
4.3 创建Student实体类
package com.qfedu.entity;
public class Student {
private Integer id;
private String name;
private String gender;
private String code;
private Integer classId;
public Student() {
super();
}
public Student(String name, String gender, String code, Integer classId) {
super();
this.name = name;
this.gender = gender;
this.code = code;
this.classId = classId;
}
public Student(Integer id, String name, String gender, String code, Integer classId) {
super();
this.id = id;
this.name = name;
this.gender = gender;
this.code = code;
this.classId = classId;
}
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 String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public Integer getClassId() {
return classId;
}
public void setClassId(Integer classId) {
this.classId = classId;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", code=" + code + ", classId=" + classId
+ "]";
}
}
4.4 创建StudentVo类
用于映射student表和其他表的关系，这里以class表为例，学生表的class_id就是班级表的id，查询出学生表的所有列以及班级名称。
package com.qfedu.vo;
//按姓名、性别、学号、班级查询
public class StudentVo {
private String name;
private String gender;
private String code;
private String className;
public StudentVo() {
super();
}
public StudentVo(String name, String gender, String code, String className) {
super();
this.name = name;
this.gender = gender;
this.code = code;
this.className = className;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
@Override
public String toString() {
return "StudentVo2 [name=" + name + ", gender=" + gender + ", code=" + code + ", className=" + className + "]";
}
}
4.5 创建StudentDao类，用于构造增删改查的方法
package com.qfedu.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.qfedu.entity.Student;
import com.qfedu.util.DbUtil;
import com.qfedu.vo.StudentVo;
public class StudentDao {
//添加方法，防止sql注入
public ArrayList<Integer> addStudent(List<Student> listStu) {
Connection conn=null;
PreparedStatement stm=null;
ResultSet rs = null;
ArrayList<Integer> list = new ArrayList<Integer>();
try {
conn = DbUtil.getConnection();
StringBuilder sb = new StringBuilder();
if(listStu!=null) {
sb.append("INSERT INTO student VALUES (null,?,?,?,?)");
if(listStu.size()>1) {
for (int i = 1; i < listStu.size(); i++) {
sb.append(",(null,?,?,?,?)");
}
}
//Statement.RETURN_GENERATED_KEYS返回本次sql操作的主键
stm = conn.prepareStatement(sb.toString(),Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < listStu.size(); i++) {
stm.setString(1+i*4, listStu.get(i).getName());
stm.setString(2+i*4, listStu.get(i).getGender());
stm.setString(3+i*4, listStu.get(i).getCode());
stm.setInt(4+i*4, listStu.get(i).getClassId());
}
}else {
return null;
}
int num = stm.executeUpdate();
System.out.println("添加成功，添加了"+num+"条数据");
//获取主键
rs = stm.getGeneratedKeys();
while(rs.next()) {
list.add(rs.getInt(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.close(conn, stm, rs);
}
return list;
}
//修改方法，防止sql注入
public void updateStudent(Student stu) {
Connection conn=null;
PreparedStatement stm=null;
try {
conn = DbUtil.getConnection();
String sql = "UPDATE student SET name=?,gender=?,code=?,class_id=? WHERE id=? ";
stm = conn.prepareStatement(sql);
stm.setString(1, stu.getName());
stm.setString(2, stu.getGender());
stm.setString(3, stu.getCode());
stm.setInt(4, stu.getClassId());
stm.setInt(5, stu.getId());
int num = stm.executeUpdate();
System.out.println("修改成功，修改了"+num+"条数据");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.close(conn, stm, null);
}
}
//删除方法，防止sql注入
public void deleteStudent(int id) {
Connection conn=null;
PreparedStatement stm=null;
try {
conn = DbUtil.getConnection();
String sql = "DELETE FROM student WHERE id=?";
stm = conn.prepareStatement(sql);
stm.setInt(1, id);
int num = stm.executeUpdate();
System.out.println("删除成功，删除了"+num+"条数据");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.close(conn, stm, null);
}
}
//StudentVo 按姓名、性别、学号、班级查询
public ArrayList<StudentVo> findStuClass(StudentVo stu,Integer currentPage,Integer pageRows) {
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
ArrayList<StudentVo> list = new ArrayList<>();
try {
conn=DbUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT s.`name`,s.gender,s.code,c.`name` className ");
sb.append("FROM student s , clazz c ");
sb.append("WHERE c.id=s.class_id ");
if(stu.getName()!=null) {
sb.append("AND s.name LIKE '%"+stu.getName()+"%' ");
}
if(stu.getGender()!=null) {
sb.append("AND s.gender = '"+stu.getGender()+"' ");
}
if(stu.getCode()!=null) {
sb.append("AND s.code = '"+stu.getCode()+"' ");
}
if(stu.getClassName()!=null) {
sb.append("AND c.`name` = '"+stu.getClassName()+"' ");
}
sb.append("LIMIT "+(currentPage-1)*pageRows+","+pageRows);
pstm=conn.prepareStatement(sb.toString());
rs = pstm.executeQuery();
while(rs.next()) {
String name = rs.getString("name");
String gender = rs.getString("gender");
String code = rs.getString("code");
String className = rs.getString("className");
list.add(new StudentVo(name,gender,code,className));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DbUtil.close(conn, pstm, rs);
}
return list;
}
}4.6 创建测试类，测试增删改查
package com.qfedu.test;
import java.util.ArrayList;
import com.qfedu.dao.StudentDao;
import com.qfedu.entity.Student;
import com.qfedu.vo.StudentVo;
public class StudentTest {
public static void main(String[] args) {
StudentDao dao = new StudentDao();
//增加
ArrayList<Student> list2 = new ArrayList<Student>();
list2.add(new Student("刘诗诗","女","1314",4));
list2.add(new Student("赵丽颖","女","1315",4));
dao.addStudent(list2);
//修改
dao.updateStudent(new Student(42,"二梦","女","1313",4));
//删除
dao.deleteStudent(41);
//查询
StudentVo stu = new StudentVo();
stu.setGender("女");
ArrayList<StudentVo> list = dao.findStuClass(stu, 2, 10);
for(StudentVo vo:list) {
System.out.println(vo);
}
}
}
本文含有隐藏内容，请 开通VIP 后查看