[大数据]使用JDBC操作数据库

?

?

?

一、概念

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